ps_vbdev (9/21/2016)
Great i must be progressing as this was what i was thinking :-). My only reservation on adding a DimRole to the model was because the FactOrder grain is at ordered items level and the role is effectively at customer order level i guess i thats what was confusing me.Great idea on the additional attributes in the DimCustomer def going to implement that as returning customers was mentioned 🙂
DimRole is indeed at a different grain. Typically this is implemented in an OLTP relational model as two different tables: OrderLine and OrderHeader. The role would then be found at the OrderHeader. However, in the star schema you have denormalized the schema, which means order line and order header information are now in the same table. So it's normal that DimRole appears at the order line level, but the info just gets repeated over each order line (redundancy, which is OK in star schemas).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP