September 20, 2016 at 12:04 pm
Hi there
working through building my first DW and some question have come up that i really need an experience opinion on.
In my warehouse i have a DimCustomer which is a unique list of all users that have registered on the site and contains only attributes related to the user. I also have FactOrders which measures all the orders any Customer has made the grain of this is at each item on the order.
The system is for booking outfits for the grooms on wedding parties so typically you will have GroomA with a Bestman, Groomsman etc and the items ordered for each. Now you can be presented with the scenario where GroomA is also an on another order for a wedding party but as a Groomsman. So the role of each party member is important but only when they become part of an order.
How should i model the role? I dont think the role should be part of DimCustomer as each customer can be on many wedding parties so have many roles. Should the role be part of the Fact table? or should i add a DimRole to my schema. I was reading about junk dimensions but unsure if this qualifies.
Thanks.
September 20, 2016 at 3:01 pm
I'd go ahead an put a Role dimension in the model. Load it up with the valid roles and add a RoleID column in your fact table. Create the customer>>order>>role relationship in ETL as you load your facts. It definitely does not belong in the customer dimension because it's not an attribute of a customer, it's part of the order.
September 21, 2016 at 1:29 am
Definitely not a junk dimension, but rather a role dimension as John suggests.
You could add attributes to the customer dimension signifying which roles they have already played. For examle: HasBeenGroom, HasBeenBestMan etc.
This would allow you to quickly find people who have played multiple roles (aka returning customers).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 21, 2016 at 2:28 am
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 🙂
September 21, 2016 at 3:57 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply