Accumulating Snapshot and Transaction Snapshot

View previous topic View next topic Go down

Was information in the Post clear enough to answer the question?

100% 100% 
[ 1 ]
0% 0% 
[ 0 ]
 
Total Votes : 1

Accumulating Snapshot and Transaction Snapshot

Post  mnachu on Tue Apr 19, 2011 5:51 pm

Hello all,

We are building a data warehouse that contains information about our day to day operations (+ lots of other fun details like sales & subscriptions).

One of the unique things about our business is we have a complex workflow and that workflow differs by Services we sell.

Example: We sell Service "A" which is purchased by Customer, it goes into following states:
1. Pending
2. Started
3. Contacted Customer (sometimes doesnt happen)
4. Sent to 3rd Party for review (somestimes doesn't happen)
5. Sent to Govt Agency for Review
6. Prepared document
7. Print
8. Ship (This is end of workflow).

Say for Service "B" the # of workflows and status could be different
1. Pending
2. Review
3. Sent to customer for Signature
4. Received from Customer
5. File with Govt Agency (This is end of it)

As you see the workflow is different by the service we sell. We sell close to 800+ services and at least have 20 different workflows. One thing that is common between all services/workflow is the Start state and all of them have a end date.

Now for a situation like this would it be even possibly to build a Accumulating snapshot or do we have to live with Transaction Snapshot like Mr.Kimball suggested in Design Tip 130?

We don't have to worry about every workflow event in accumulating snapshot but all services have at least 4-5 dates that we want to capture for now. Any new services we introduce might have completely new set of workflow and we could always change workflows for existing products for efficiency or other legal purposes.

So any advice on tracking status of workflow is appreciated.

Thanks,
Nachu

mnachu

Posts : 8
Join date : 2011-04-19

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Snapshot

Post  ngalemmo on Tue Apr 19, 2011 6:21 pm

I think you kind of answered your own question. Since you are dealing with states, an accumulating snapshot is appropriate. There would be another fact to track revenues from sales, which could either be transactional or an accumulating snapshot. An advantage of a transactional fact for sales/revenues is you can easily calculate the magnitude of change for any time period, while an accumulating snapshot tends to be smaller and does not require aggregation over all time to get values at a point in time.
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 and Transaction Snapshot

Post  BoxesAndLines on Tue Apr 19, 2011 11:00 pm

You can also track the status changes as a transaction fact. Given the large number of workflows I would lean toward this design, especially if you are not tracking elapsed time.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Snapshot

Post  mnachu on Thu Apr 21, 2011 2:03 pm

Thanks Nick and BoxesAndLines.

I think Transaction Fact is good for finding how long did it take to go from one workflow to another. But the accumulating snapshot gives the current state and easy to use for lots of analysis. So building both sounds will solve both needs.

But with the Accumulating snaptshot if I have to add a new Date in the future for a new status, can I do it? Is it allowed in DW practices to change the Fact table schema after we populate historical stuff?

Also generally Fact table rows are not updated only added, in case of Accumulating snapshot DW concepts suggest that we can update it. Almost like a Slowly changing Dimension. Is that acceptable?

Thanks,
Nachu


mnachu

Posts : 8
Join date : 2011-04-19

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Snapshot

Post  ngalemmo on Thu Apr 21, 2011 5:03 pm

But with the Accumulating snaptshot if I have to add a new Date in the future for a new status, can I do it? Is it allowed in DW practices to change the Fact table schema after we populate historical stuff?

You could consider having status as a dimension and creating new rows as the status changes. The fact would then contain only an effective and end date for a single status. Adding a new status would not affect the schema, it would just be a new row in the 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: Accumulating Snapshot and Transaction Snapshot

Post  mnachu on Thu Apr 21, 2011 8:22 pm

Thanks Nick Again.

Adding a row to a fact and using a Dimension is good idea. But, wouldn't that make it as a Transactional snapshot instead of Accumulating snapshot? or am I missing something.

Thanks,
Nachu

mnachu

Posts : 8
Join date : 2011-04-19

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Snapshot

Post  ngalemmo on Fri Apr 22, 2011 7:48 pm

It's kind of splitting hairs at that point. Transactional facts typically do not have date ranges, just one date indicating when it happened and measures are additive. An accumulating snapshot has begin and end dates, in this case tracking the effective period of the status.
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 and Transaction Snapshot

Post  mnachu on Thu Apr 28, 2011 12:10 pm

Thanks Nick. We are still working on the details. Right now we are going to use a Transactional Fact to catpure what event occured, when it occured, and what time we captured it. We will use this to determine revenue dates.

We will not build any direct cubes on top of this. When we have to build a cube we will create a Periodic/Accumulating snapshot for making the analysis easier.

Thanks,
Nachi

mnachu

Posts : 8
Join date : 2011-04-19

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Snapshot

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