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

View previous topic View next topic Go down

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

Post  mbrey on Fri Jan 20, 2012 4:57 pm

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:

.
OrderPromiseDate Status RowStart RowEnd ExportDate
S123 2012-01-20 B1900-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

mbrey

Posts : 4
Join date : 2012-01-20

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Jan 20, 2012 8:49 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  hang on Fri Jan 20, 2012 9:14 pm

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  mbrey on Sat Jan 21, 2012 2:22 pm

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?

mbrey

Posts : 4
Join date : 2012-01-20

View user profile

Back to top Go down

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

Post  hang on Sat Jan 21, 2012 8:59 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  mbrey on Tue Jan 24, 2012 6:14 pm

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.

mbrey

Posts : 4
Join date : 2012-01-20

View user profile

Back to top Go down

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

Post  Jeff Smith on Tue Jan 24, 2012 7:26 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  mbrey on Fri Feb 17, 2012 6:23 pm

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.

mbrey

Posts : 4
Join date : 2012-01-20

View user profile

Back to top Go down

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

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