Popluate two measure fields in fact table from same SCD2 dimenion attribute

[mbrey]

I have a business requirement that I need to determine the difference between historic change of a SCD type 2 dimension attribute. At this time the dimension is tracking the promise date and status changes to an order. What the managers want to know is when a customer service rep sets a promise date with a customer they have 2 working days (no weekends or holidays) to update that promise date depending on availability and other factors. The managers want to know the initial promise date and the final (any changes within the 2 work day limit) and compare the final promise date to the ship date of the order. I am recording the order throughout its lifecycle (bid, order processing, and invoicing) in the dimension. I plan on loading the fact table with the initial, final and ship dates from the dimension after the order is finally invoiced and there are no more changes to that order. Here is an example:

.

Order	PromiseDate	Status	RowStart	RowEnd	ExportDate
S123	2012-01-20	B	1900-01-01	2012-01-02	2012-01-01
S123	2012-01-21	C	2012-01-02	2012-01-04	2012-01-02
S123	2012-01-22	C	2012-01-04	2012-01-05	2012-01-04
S123	2012-01-24	C	2012-01-05	2012-01-07	2011-01-05
S123	2012-01-24	I	2012-01-05	2079-06-06	2011-01-07

From this example I need the second record as the original promise date (status B indicates that the order is a bid and the second record is when it was flipped to an order). I need the third record (C is just one of many order statuses, and I is invoiced; end of the order cycle) for the final date since the customer service rep is allowed 2 days to modify the promise date with the customer before it is locked in. I am new to data warehousing and BI. Does anyone have any thoughts on the most efficient way of loading this fact table?

Thanks

[ngalemmo]

You are tracking business events that affect the state of an order. Why are you doing this in a dimension table?

[hang]

The best solution is using Accumulating Snapshot fact table. Please check this article for more details:
http://www.kimballgroup.com/html/10dt/DT130AccumulatingSnapshotsComplexWorkflows.pdf

[mbrey]

So with the Accumulating Snapshot I would insert a new row whenever the promise date changes or would I insert a row for the first instance of the order, another for the final promise date and another one for when the order has been processed? With either option what is the most effecient method of determining the final value of the promise date?

[hang]

Based on Kimball's accumulating snapshot fact, the grain is one order per row with all the relevant date keys as columns to indicate order milestones. ETL will revisit the order record and update the date key when the next milestone has been reached. You would also let ETL work out some key duration measurements stored in the fact table.

[mbrey]

So if I understand. One row would be ordernum, initialpromisedate, initialdatestamp, finalpromisedate, datestamp in the fact table. The initial insert would populate the keys to the related dimensions, initialpromisedate, and a datestamp. ETL runs checks for promisedate changes or order changes and populates the finalpromisedate and datestamp? Please forgive my ignorance, I am very new to BI.

Thanks.

[Jeff Smith]

You want to keep history as it was and restate history in a sense.

Couldn't you put the original Promise date on the fact and the Current Promise Date on the dimension if you only wanted to compare the original to the current. Make the current on the dimension table the same date as the original on the fact.

[mbrey]

Thanks for all the input. I have an accumulating fact table with the original and final dates and it is working great. I appreciate all the comments.

[Sponsored content]