Star Schema
- Star Schema is a data modeling approach to classify tables as either lookup tables (dimensions or master data tables) or data tables (fact or transaction tables).
- Lookup Tables
- Lookup tables describe business entities e.g. Products, time etc.
- Lookup tables typically have fewer rows compared to data tables and often have a larger number of columns.
- Lookup tables support filtering and grouping.
- Data Tables
- Data tables contain a very large number of rows and continue to grow over time i.e. store transactional data.
- Data table contains dimension tables key columns that relate to dimension tables.
- Data table supports summarization.
- The relationship of tables in model identifies the table either as Lookup or Data table. Common relationship cardinality in Lookup and Data table is one-to-many or its inverse many-to-one.
- The "one" side is always a dimension type table while the "many" side is always a fact type table. The Collie layout methodology recommends that to place lookup tables at the top and data tables at the bottom.
- In order to optimize your data tables, consider extracting repetitive attribute columns and creating separate lookup tables for them.
- For a lookup table with two columns (Key and Description), excluding the Key column and incorporating the Description directly into the data table can be more efficient.
- Consider flattening multiple joined lookup tables into a single wider lookup table.
0 comments:
Post a Comment