2

In the below dimensional model, all the dimension table follows the star schema except the prod table, which is snowflaked and has prod_sub table as a child table. What is the strategical point to consider snowflaking a star schema?

Sales Table - Fact Table
Prod_id  (fk)
Promo_id (fk)
store_id (fk)

Promo Table - Dimension Table
promo_id (pk)

Store Table - Dimension Table
store_id (pk)

Product Table - Dimension Table
Prod_id (pk)
prod_class_id (FK)
Prod_name
Prod_brand

Prod_sub Table- Dimension Table (snow flaked)
prod_class_id,
prod_class
prod_sub_class
prod_family
vamos3556
  • 19
  • 4
  • There is no *strategic point*. If a dimension is hierarchical with more levels (as your product with two levels *product* and *product class*) you use one dimension table per level to cover it. If a dimension is *flat* without a hierarchy you use only one table. – Marmite Bomber Jan 24 '21 at 21:48
  • You can put all that product information into a single Dimension table, there is no need to snowflake it – NickW Jan 25 '21 at 09:12

1 Answers1

0

According to Kimball : Star Schema is the best way of designing a data model for reporting, You will get the best performance and also flexibility using such a model. So no need to snowflake it.

Then your Product Table Dimension Table becomes :

Product Table - Dimension Table
Prod_id (pk)
Prod_name
Prod_brand
prod_class
prod_sub_class
prod_family
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • Great! I thought so too. So, the only necessary to snow flake it would be, if there are any multivalued attribute or multivalued dimension right? So, in this case, if the same product has multiple class , then we have to snow flake having bridge tables right? – vamos3556 Jan 25 '21 at 23:48
  • If the same product has multiple class you can snowflake your model. But keep in mind than it is a best practice to use star schema. You can have the many records in your dimension refering to the same product but with different class each. – Amira Bedhiafi Jan 26 '21 at 08:17