Multiple Order Versions and single deliveries

Hi there,

My new client has a slightly interesting feed from the data system where they will get an Order (say Order ref O11235) and each time that order is modified it gets a new version 1,2,3,4 etc. So the unique Order ref from a business key perspective is O11235:1, O11235:2, O11235:3, O11235:4 etc.

When the order is completed and the stock dispatched it is delivered with a fill ref R55436 which is recorded as being a fill for order O11235.

I would like to create an Order dimension with a SK (1,2,3,4 etc.) mapping to the BK (O11235:1, O11235:2, O11235:3, O11235:4 etc).

I guess my question is what would be the recommended way to model this from a dimension table perspective so that I may join it to both the Fact_Fill table and the Fact_Order table?

I considered creating a zero version of the order for the order dimension table, but was interested in how this would related to my SCD type 2. I could then update the zero version record to replicate the latest order version. I could keep this zero SK in each record in the DimOrder Table. Then in the Fact_Order table I would have two fields for each record, one pointing to the zero SK for the order, and a second pointing to the true version, adn the FactFill table would have the zero SK.


Option A


Option B

Has anyone encountered this before and come up with an elegant solution?

Thanks in advance
JPO

Don't you want the latest state of the order when it is filled? If so, you simply do normal type 2 SCD processing.

We have no idea when the fill will come in, it may come in series of batches the following day for an order that was amended several times during the previous day.

The problem is that if we allocated it to the latest version of the order (standard SCD type 2) then we would (in theory) be potentially allocating it to the wrong version.

When it comes time to fill our cube, and we choose to take only the latest order version for the order details (lets assume this is version five) we could potentially miss the fills booked against order number four (this is a real scenario).