Accumulating Snapshot and Transaction Snapshot

[mnachu]

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

[ngalemmo]

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.

[BoxesAndLines]

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.

[mnachu]

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

[ngalemmo]

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.

[mnachu]

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

[ngalemmo]

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.

[mnachu]

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

[Sponsored content]