Accumulating Snapshot and Transaction Fact tables : question to design and use them together

View previous topic View next topic Go down

Accumulating Snapshot and Transaction Fact tables : question to design and use them together

Post  snpr01 on Tue Sep 11, 2012 12:18 pm

Hi, I'm designing for Customer Prospects in an existing DW that follows Kimball's design approach. There are two processes on Prospects:

  1. 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.
  2. 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
  1. lag times
  2. # of Prospects in each status by day or week etc..
  3. # of Prospects and Activities by Prospect status and Activity type by day (this is a consolidated analysis)
and ad-hoc requests.

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

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together

Post  snpr01 on Wed Sep 12, 2012 4:01 pm

Any suggestions on my design choices?

snpr01

Posts : 13
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together

Post  BoxesAndLines on Thu Sep 13, 2012 9:20 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together

Post  snpr01 on Thu Sep 13, 2012 6:04 pm

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

snpr01

Posts : 13
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together

Post  moorthy on Wed Jan 15, 2014 8:19 am

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.

moorthy

Posts : 1
Join date : 2014-01-15

View user profile

Back to top Go down

Re: Accumulating Snapshot and Transaction Fact tables : question to design and use them together

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