Selecting a Table Model

The most common types of data warehouse schemas are star and snowflake schemas. Consider service and performance requirements when you choose a schema for your tables.

• In the star schema, a central fact table contains the core data for the database and several dimension tables provide descriptive attribute information for the fact table. The primary key of a dimension table associates a foreign key in a fact table, as shown in Figure 1.
  • All facts must have the same granularity.
  • Different dimensions are not associated.
  Figure 1 Star schema
  
• The snowflake schema is developed based on the star schema. In this schema, each dimension can be associated with multiple dimensions and split into tables of different granularities based on the dimension level, as shown in Figure 2.
  • Dimension tables can be associated as needed, and the data stored in them is reduced.
  • This schema has more dimension tables to maintain than the star schema does.
  Figure 2 Snowflake schema
  

This practice verifies performance using the Store Sales (SS) model of TPC-DS. The model uses the snowflake schema. Figure 3 illustrates its structure.

Figure 3 TPC-DS Store Sales ER-Diagram

For details about the store_sales fact table and dimension tables in the model, see the official document of TPC-DS at http://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp.