227 ) How did you design the dimensional model you designed.

 

227 ) How did you design the dimensional model you designed.

Correct / Ideal Answer:

I would first define the business process and grain. For retail sales, the grain could be one row per product per order line.

Dimensions could include Date, Customer, Product, Store and Promotion. The Sales fact would contain foreign keys to

those dimensions and measures such as Quantity, Sales Amount, Discount and Cost.

I would use surrogate keys for dimensions and implement SCD strategies where historical changes need to be maintained.

I would generally use a star schema for reporting because it provides simpler queries and fewer joins compared with a

highly normalized reporting model.