Modeling Question - Accumulating Snapshot vs. Event Transactions

View previous topic View next topic Go down

Modeling Question - Accumulating Snapshot vs. Event Transactions

Post  jimbo1580 on Fri Oct 23, 2009 10:17 am

I have a business process that has 6 distinct steps from start to conclusion for each instance of the process, we'll call it a case. Each case has two employees assigned to it, 1 owner and 1 co-owner. The owner and co-owner each have different responsibilities and each put in work to get the case to move through the process. The process can take anywhere from 2 months to 2 years to complete. Since the process can take some time, the owner and/or co-owner can change for a case over time. Our business wants to be able to generate metrics about when each of the 6 steps complete as well as who were the owners and co-owners at the time each step completed. They use this to allocate compensation. So if a case currently has owner A, but was assigned to owner B at the time of step 2 completion. They need to attribute that step to owner B.

My approach to modeling this is twofold. I was going to have an accumulating snapshot table for the process. This table would be linked to the current owner and co-owners only. I would not worry about who was the owner at what point in time through SCDs. At the same time, I would have a transaction fact table to record each event of a process step completing, recording the owner and co-owner at that time. Any reports that want to know who did what would use the event transaction table.

Does this sound like a good approach? Other suggestions?

Thanks

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Modeling Question - Accumulating Snapshot vs. Event Transactions

Post  cridal on Mon Nov 02, 2009 6:29 pm

I think the two-way strategy of transactional fact and accumulating snapshot fact is a good approach.

Presumably in your accumulating snapshot fact you would have columns signifying closure of individual steps within a process. So you would have Step1Closed, Step1ClosedDate, ..., Step2Closed, Step2ClosedDate, ... etc. That's the essence of accumulation snapshot; major milestones during the life of the process get recorded. Why not, then, also include Step1ClosedOwnerKey, Step1ClosedCoOwnerKey, ..., Step2ClosedOwnerKey, Step2ClosedCoOwnerKey, ..., etc. in there. Having the owners there would significantly ease generation of reports and attributions.

So your ETL process would first add new details to your transactional fact table, but then have post-process updates to the accumulating snapshot fact table when certain transactions caused closures of individual steps (also updating Owners there).

cridal

Posts : 9
Join date : 2009-03-27

View user profile

Back to top Go down

Re: Modeling Question - Accumulating Snapshot vs. Event Transactions

Post  jimbo1580 on Tue Nov 03, 2009 9:27 am

We have discussed putting the owner and co-owner keys for each step directly in the accumulating fact table for the purpose that you mention, to make it easier for reporting, but our fact table became large in my eyes (compared to examples that I have seen and studied). There are 9 events in the process that need to be captured in the accumulating fact table. Each event has 1 date, 9 metrics, and would have 1 owner and 1 coowner. Is this a reasonable fact table design?

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Modeling Question - Accumulating Snapshot vs. Event Transactions

Post  ngalemmo on Tue Nov 03, 2009 12:05 pm

I would go with your original idea and include event/step as a dimension to the fact rather than replicate keys and measures across the fact. You can still have a view, if necessary, that flattens things out. But analysis on specific events is much simpler using a dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling Question - Accumulating Snapshot vs. Event Transactions

Post  cridal on Tue Nov 03, 2009 4:18 pm

Even with 9 steps for every process, it still is a solid design choice in my opinion...

Accumulating snapshot fact tables are different from transactional fact tables, which usually are skinny (containing just surrogate keys to dimensions and numerical facts) and long. It's OK if accumulating snapshot facts are wide, since they will be shorter, so still quite manageable (one row for one process in your case, I imagine).

The real win is richness and easiness of analytics you can derive from them. In your case you can run all kinds of reports calculating lags between steps, metrics describing efficiencies of the process, etc... For other types of analysis use your transactional fact table.

cridal

Posts : 9
Join date : 2009-03-27

View user profile

Back to top Go down

Re: Modeling Question - Accumulating Snapshot vs. Event Transactions

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