accumulating snapshot -random # of status

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?

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

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

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

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.

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.

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

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
1	10	12345	InProcess	31st DEC 10:30 AM
2	10	12345	Issued	31st DEC 10:35 AM
3	21	91872	Pending	30st DEC 11:30 AM
4	21	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

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.

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