Design of a multivalued dimension,

View previous topic View next topic Go down

Design of a multivalued dimension,

Post  dk2014 on Tue Feb 03, 2015 11:39 am

I have an order sales fact table modeled at the order line/item granularity. It is surrounded by several dimensions, one of which is the customer dimension.
This, among other things, allows us to calculate the sales per customer. Our business also groups customers into groups or units and therefore we need to see the sales at the group/unit as well.
Each customer can belong to one or more groups or units and therefore this would be a multivalued dimension.
I imagined designing this by creating a Unit dimension and then sticking a bridge table between the customer dimension and the unit dimension.

1. Would this be the best way to model the scenario?
2. Would the primary key from the unit dimension, say UnitKey, need to or make sense to be included in the fact table as well or everything that has to do with units would need to go through the customer dimension? I'm thinking if I just wanted to see the sales for the unit 17535, I could do that without having to know which customers belong to that unit, and would therefore need to include the UnitKey in the fact table as well but I'm not sure if this would cause any other issues.

Anyone has any advice with this? Thanks!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Design of a multivalued dimension,

Post  nick_white on Tue Feb 03, 2015 12:15 pm

You say a Customer can be associated to multiple Units but you also suggest putting the Unit key on the fact table - does this mean a transaction can only be associated to one of the Units the Customer is linked to?
If that is the case, and you only want to report on the single Unit associated to the transaction, then put the key on the Fact table rather than using a bridge table.
If there isn't a 1:1 relationship between Unit and Transaction then you'll have to use a bridge table between Customer and Unit. If you do this then make sure any queries don't start double counting e.g. if Customer A is associated to Units 1 & 2 and you query for all transactions for Units 1 & 2 then all Customer A's transactions will be counted twice (unless you ensure that they aren't)

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Design of a multivalued dimension,

Post  dk2014 on Tue Feb 03, 2015 4:23 pm

Thank you nick_white!
Transaction can only be associated to one customer and customer can belong to more than one group - so, yes, you are right, I wouldn't be able to include the UnitKey in the fact table. I just thought that it might be quicker to get the Unit sales if we had the UnitKey in the fact table but now after thinking this through I see that this would result in incorrect amounts.
I will create the bridge table and I will try to make sure I have a method for preventing double counting the transactions. Do you have any tips for that? Is using the weighting factor one way of doing this?
Thanks for your answer!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Design of a multivalued dimension,

Post  nick_white on Wed Feb 04, 2015 7:29 am

Weighting factor is the normal way to go but it still gets complicated - especially if you can filter on some or all of the values linked via the bridge. For example, a customer is linked to 2 units so you put a weighting factor of 0.5 in the bridge and force queries that use the bridge to multiple the measures by 0.5. However, if you filter the query so that only one of the Units is selected do you still want to use the weighting?
The solution is normally that whoever is writing the queries needs to have a clear understanding of the model and ensure their queries give the results they expect

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Design of a multivalued dimension,

Post  dk2014 on Wed Feb 04, 2015 10:26 am

Makes sense!
Thanks again nick_white!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Design of a multivalued dimension,

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum