Accumulating Snapshot and Transaction Fact tables : question to design and use them together
3 posters
Page 1 of 1
Accumulating Snapshot and Transaction Fact tables : question to design and use them together
Hi, I'm designing for Customer Prospects in an existing DW that follows Kimball's design approach. There are two processes on Prospects:
There are no amounts recorded during status changes or follow-up activities. Measures are always going to be counts of Prospects, Statuses, Activities etc..
I am designing to analyze
To solve (1) I'm going with an Accumulating snapshot with eff/exp dates for history as suggested by Nick G here: http://forum.kimballgroup.com/t984-accumulating-snapshot-random-of-status.
To solve (2) I want to do a fact table with the grain: one row per each Prospect, Status with Eff Start and Eff End date dimensions and active flag. Is this a correct design?
To solve (3) I want to complement above design with Activity transaction fact table with the grain: one row per Prospect, Status, Activity date.
Do my design choices address given and ad-hoc scenarios? Any thoughts on changes or better approaches?
- Change of statuses. Prospects go through different non-linear, possibly repeating, one or many statuses: Inquiry->Init Vist->C->B->A->Move-In or Closed.
- Prospect Follow-up activities: there are different types of activities done by sales persons. Activities have results dates and results.
Both processes have other dimensions that I'm leaving out for simplicity.
There are no amounts recorded during status changes or follow-up activities. Measures are always going to be counts of Prospects, Statuses, Activities etc..
I am designing to analyze
- lag times
- # of Prospects in each status by day or week etc..
- # of Prospects and Activities by Prospect status and Activity type by day (this is a consolidated analysis)
To solve (1) I'm going with an Accumulating snapshot with eff/exp dates for history as suggested by Nick G here: http://forum.kimballgroup.com/t984-accumulating-snapshot-random-of-status.
To solve (2) I want to do a fact table with the grain: one row per each Prospect, Status with Eff Start and Eff End date dimensions and active flag. Is this a correct design?
To solve (3) I want to complement above design with Activity transaction fact table with the grain: one row per Prospect, Status, Activity date.
Do my design choices address given and ad-hoc scenarios? Any thoughts on changes or better approaches?
snpr01- Posts : 13
Join date : 2009-02-03
Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together
Any suggestions on my design choices?
snpr01- Posts : 13
Join date : 2009-02-03
Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together
Sure, I don't think an accumulating snapshot is going to work here. The whole non-linear, repeating statuses, unknown number of statuses, pretty much kills the accumulating snapshot. The only other option is to go transaction fact, capturing each status change as an event. It makes the reporting of lag times more difficult as you need to pivot the table to get the rows, but given your scenario, the transaction fact will solve all of your problems.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together
Thank you for taking time to reply, B&L! I'll certainly go with the status transaction fact table.
For lag times, we decided to track only certain status changes to keep it simple. In that case our Snapshot will have one row per Prospect.
However, if pipeline history tracking is needed, I just came across this design tip by Joy Mundy : Time Stamping Accumulating Snapshot Fact Tables
For lag times, we decided to track only certain status changes to keep it simple. In that case our Snapshot will have one row per Prospect.
However, if pipeline history tracking is needed, I just came across this design tip by Joy Mundy : Time Stamping Accumulating Snapshot Fact Tables
snpr01- Posts : 13
Join date : 2009-02-03
Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together
Hi, I am new to Dimensional Modeling, I have the same scenario as you described.
We have orders, based on the order type, different work flows will be followed. It is kind of dynamic or non-linear workflows.
If you don't mind can you throw some lights or share high level your Accumulating fact and transactional fact structure.
Thanks for your help.
We have orders, based on the order type, different work flows will be followed. It is kind of dynamic or non-linear workflows.
If you don't mind can you throw some lights or share high level your Accumulating fact and transactional fact structure.
Thanks for your help.
moorthy- Posts : 1
Join date : 2014-01-15
Similar topics
» Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Design Question - Multiple Fact Tables at the same Grain
» Accumulating Snapshot and Transaction Snapshot
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Design Question - Multiple Fact Tables at the same Grain
» Accumulating Snapshot and Transaction Snapshot
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum