Skip to main content

โ„๏ธ Star and Snowflake Models

Previously, we introduced dimensions of multidimensional models that are mostly fields in the fact table or fields in dimension tables associated with the fact table. Such a model is called a Star Schema.

However, a dimension may be the result of associations between multiple tables, and such a model is called a Snowflake Schema.

Next, we will use Product as an example to introduce how to create a dimension in a snowflake schema.

Creating a Snowflake Dimensionโ€‹

Open the editing page of the Product dimension, click New Hierarchy, and then create the snowflake schema dimension within this hierarchy.

  1. Open the hierarchy property editing interface, enter the name Product Class, and the label Product Classification;
  2. In the data table area, find the product and product_class tables and drag them to the table area below the hierarchy. The table association graphic area will open automatically;
  3. Edit the table association information in the table association graphic area, click the association icon, and select the table association type;
  4. Click the button to add a new association field, and sequentially select the left key product_class_id and the right key product_class_id (MDX modeling currently supports only one association field);
  5. Drag the fields from the product_class table: product_family, product_department, product_category, product_subcategory, and from the product table: brand_name, product_name into the hierarchy area in order, allowing you to preview changes in dimension data in real-time;
  6. Open the hierarchy property editing interface, select Primary Key Table as product, and Primary Key as product_id, which means using its primary key to associate with the foreign key in the fact table as the main table in the dimension association. (When there is only one dimension table in the hierarchy, the primary key table can be left unset, only the primary key needs to be set, and the primary key list will be obtained from the unique table);
  7. Click on the hierarchy to set its properties individually;