The request
“A retail sales snowflake schema: a FactSale table linked to Date, Product, and Store dimensions; normalize Product through Subcategory to Category and Store through City to Region.”
Then tryAdd a customer dimension to FactSaleAdd a brand table below Product
What is in this drawing
Read the decisions behind it.
01Fact grain
Store one row for each sale line.
02Product hierarchy
Keep category data outside the product dimension.
03Geography hierarchy
Normalize stores through city and region.
Use this when product and store hierarchies are shared and need separate maintenance.
More like this
Other examples in this family.
FAQ
Common questions
What makes this a snowflake schema?
Its dimensions are normalized into hierarchy tables, such as Product to Subcategory to Category.
What is the fact table grain?
Each FactSale row represents one recorded sale line with its measures.
Why normalize store geography?
City and region can be maintained once and reused by every store.