Dimensional Modelling Design
2 posters
Page 1 of 1
Dimensional Modelling Design
Hi All.
I am designing a model for our sales process and have come across a relationship that I am struggling to model efficiently.
We have the sales transactional fact table and its supporting dimensions, one of which is supplier. The way our companies accounts work is that all sales are attributed to purchase orders from suppliers, in the order the purchase orders are raised.
So as an example, we have;
- Purchase order A for 500 units of product A
- Purchase order B for another 500 units of product A from a second supplier
- The first 500 units sold are attributed to the supplier from purchase order A
- The next 500 against the supplier from purchase order B.
The problem lies in when we have 499 sales attributed to purchase order A, then we have a sale with a line item containing 3 units sold of product A. This single grain now belongs to 2 suppliers and I would have to break that line item down (thus breaking the grain).
So this would be the fact table for this example.
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
100 10001 20034 100001 1
At this point, we have now allocated 499 units against supplier 20034.
Then we have the next sale.
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
101 10001 20034 100002 3
At this grain, I can't record 2 suppliers against this one line, I would have to have something like this;
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
101 10001 20034 100002 1
102 10001 20067 100002 2
My thoughts where to have a second fact table for purchase order fulfilments that could roll up to the line item level and could then be drilled across? This seems like a bit of a messy way to resolve the problem and I hope someone may have a better idea!
Many thanks for taking the time to read my post.
I am designing a model for our sales process and have come across a relationship that I am struggling to model efficiently.
We have the sales transactional fact table and its supporting dimensions, one of which is supplier. The way our companies accounts work is that all sales are attributed to purchase orders from suppliers, in the order the purchase orders are raised.
So as an example, we have;
- Purchase order A for 500 units of product A
- Purchase order B for another 500 units of product A from a second supplier
- The first 500 units sold are attributed to the supplier from purchase order A
- The next 500 against the supplier from purchase order B.
The problem lies in when we have 499 sales attributed to purchase order A, then we have a sale with a line item containing 3 units sold of product A. This single grain now belongs to 2 suppliers and I would have to break that line item down (thus breaking the grain).
So this would be the fact table for this example.
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
100 10001 20034 100001 1
At this point, we have now allocated 499 units against supplier 20034.
Then we have the next sale.
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
101 10001 20034 100002 3
At this grain, I can't record 2 suppliers against this one line, I would have to have something like this;
Sales_PK Product_SKey Supplier_SKey LineId_DKey QtySold
101 10001 20034 100002 1
102 10001 20067 100002 2
My thoughts where to have a second fact table for purchase order fulfilments that could roll up to the line item level and could then be drilled across? This seems like a bit of a messy way to resolve the problem and I hope someone may have a better idea!
Many thanks for taking the time to read my post.
rcheeld- Posts : 2
Join date : 2014-04-15
Re: Dimensional Modelling Design
Hi,
purchase orders and sales are different events and so should be modelled in separate fact tables - this allows you to report on Purchase orders and also on sales. Obviously, when you want to relate the two fact tables it gets a bit more complicated.
I would tend towards using a bridge table in this scenario - mainly because it allows you to hold an 'allocation factor' that allocates the proportion of each purchase order to each sale e.g. 0.5/0.5 if 2 purchase order/product lines contributed equally to a sale/product line or 0.75/0.25 if 3/4 comes from one purchase and 1/4 from another.
It would also allow you to hold the reverse relationship, if that is possible in your business e.g. 1 purchase order fulfills multiple sales - 25% to Sales A and 75% to Sale B etc.
As you have stated, purchase orders and sales have different grains so if you try and put them into the same fact table then you will get problems - unless you can aggregate/simplify both so that they have the same grain.
It's probably worth going back to your business' reporting requirements as these should drive the best way of designing your model
purchase orders and sales are different events and so should be modelled in separate fact tables - this allows you to report on Purchase orders and also on sales. Obviously, when you want to relate the two fact tables it gets a bit more complicated.
I would tend towards using a bridge table in this scenario - mainly because it allows you to hold an 'allocation factor' that allocates the proportion of each purchase order to each sale e.g. 0.5/0.5 if 2 purchase order/product lines contributed equally to a sale/product line or 0.75/0.25 if 3/4 comes from one purchase and 1/4 from another.
It would also allow you to hold the reverse relationship, if that is possible in your business e.g. 1 purchase order fulfills multiple sales - 25% to Sales A and 75% to Sale B etc.
As you have stated, purchase orders and sales have different grains so if you try and put them into the same fact table then you will get problems - unless you can aggregate/simplify both so that they have the same grain.
It's probably worth going back to your business' reporting requirements as these should drive the best way of designing your model
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Dimensional Modelling Design
Thank you for your answer. I guess I was muddying the water a bit trying to fulfil purchase order allocations in the same fact table as the sales. Thank you for steering me in the right direction.
rcheeld- Posts : 2
Join date : 2014-04-15
Similar topics
» Dimensional Modelling compared to Relational (3NF +) Modelling for DSS
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Dimensional modelling
» Dimensional Modelling
» Start of Dimensional Modelling
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» Dimensional modelling
» Dimensional Modelling
» Start of Dimensional Modelling
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum