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.

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

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.