accumulating snapshot -random # of status

View previous topic View next topic Go down

accumulating snapshot -random # of status

Post  dellsters on Thu Feb 24, 2011 12:35 pm

I need to design an order fact that can go through any number of statuses. Let's say I have 5 statuses. The order can go from status 1-3-2-3. So for the one order, there can be multiple of the same status and any number of status. How would I model this? Would I just have to have a bridge table between the order fact and the status table?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  BoxesAndLines on Thu Feb 24, 2011 4:34 pm

Go deep not wide. Include date as part of the grain to cover same status over time.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  ngalemmo on Thu Feb 24, 2011 6:34 pm

If you need history, you could create an accumulating snapshot fact table. Basically keep multiple versions of the fact with effective/expiration timestamps.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: accumulating snapshot -random # of status

Post  frankee787 on Tue Nov 27, 2012 2:37 am

BoxesAndLines wrote:Go deep not wide. Include date as part of the grain to cover same status over time.

So do you mean it is advisable to keep each and every transitional STATUS (as a surrogate key from a dimension) in a single fact table along with other required facts/measures?

Regards,
Franklin

frankee787

Posts : 6
Join date : 2012-11-26

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  BoxesAndLines on Tue Nov 27, 2012 10:57 am

Yep. If you need to track each and every status change and there's no rhyme or reason to how many status changes can occur, a transactional status fact is your best bet.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  hang on Tue Nov 27, 2012 6:23 pm

What about combining ngalemmo's approach with Kimball's accumulating snapshot in a single fact table as in this article:

http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/

Like it or not, it seems Kimball's camp have started putting effective date pair in more fact tables that have less focus on fact additivity but more on point in time factual picture.

However, I still think B&L's status transaction fact is valuable from the perspective that the number of status changes during a period of time is important, and also as the base fact for updating the accumulating snapshot.

hang

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

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  frankee787 on Tue Nov 27, 2012 10:52 pm

BoxesAndLines wrote:Yep. If you need to track each and every status change and there's no rhyme or reason to how many status changes can occur, a transactional status fact is your best bet.

Thanks a lot.

Let me pin this note on my desk !!

Regards,
Franklin

frankee787

Posts : 6
Join date : 2012-11-26

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  frankee787 on Thu Nov 29, 2012 4:41 am

BoxesAndLines wrote:Yep. If you need to track each and every status change and there's no rhyme or reason to how many status changes can occur, a transactional status fact is your best bet.

Hmmm....Strangely after trying to go ahead with this approach I have noticed that only "STATUS" and "STATUS_TIMESTAMP" are changing mostly in the FACT table. However the other columns are quite repetitive.

eg:

ID PROCESS_ID_KEY SOME_COL_KEY STATUS STATUS_TIMESTAMP
110 12345InProcess 31st DEC 10:30 AM
210 12345Issued 31st DEC 10:35 AM
321 91872 Pending 30st DEC 11:30 AM
421 91872 Stopped 30st DEC 11:31 AM
5 21 91872 Issued 30st DEC 11:32 AM
Is this still the way to go about with this approach or am I missing something ?

Regards,
Franklin

frankee787

Posts : 6
Join date : 2012-11-26

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  BoxesAndLines on Thu Nov 29, 2012 10:13 am

Nope. Within an order, I would imagine most dimensions would stay the same. Instead of a timestamp, you should be using a time and date dimension.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

Post  frankee787 on Thu Nov 29, 2012 10:33 am

BoxesAndLines wrote:Nope. Within an order, I would imagine most dimensions would stay the same. Instead of a timestamp, you should be using a time and date dimension.

For simplicity I have kept TIMESTAMP, but definitely it will be a DATE/TIME dimensional table.

But my query were regarding other items of an ORDER, they would all be the same for an order and hence would be repeated again and again.

We have approximately 7 or 8 attributes on our PROCESS like COL1,COL2,COL3. So would it be advisable to have them repeating in this FACT table just because the STATUS and TIMESTAMP are to be tracked?

Regards,
Franklin

frankee787

Posts : 6
Join date : 2012-11-26

View user profile

Back to top Go down

Re: accumulating snapshot -random # of status

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