Help Center > > Developer Guide> Tutorial: Tuning Table Design> Table Schemas

Table Schemas

Updated at: May 15, 2019 GMT+08:00

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.
    • This schema reduces the amount of data in dimension tables and facilitates join queries.
    • This schema has more dimension tables that need to be maintained than the star schema does.
    Figure 2 Snowflake schema

This tutorial 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 SS 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_specifications.asp.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel