Changing Fact table quetion

View previous topic View next topic Go down

Changing Fact table quetion

Post  JSchroeder on Mon May 13, 2013 10:30 am

We have a Fact table for sales opportunities. It contains a create date key, customer key, projected close date key, amount key and salesperson key. The amount and close date change over the life of the opportunity. And we need to be able to track those changes. i.e how often did the projected close date and amounts change and how much did they change? Would this typically be done with a fact table that records a transaction for every time the opportunity changes?

Edit: Also, the fact acts as an accumulating snapshot as well. It goes through stages as the opportunity moves from opened to closed.


Last edited by JSchroeder on Mon May 13, 2013 10:58 am; edited 1 time in total

JSchroeder

Posts : 12
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  ngalemmo on Mon May 13, 2013 10:57 am

JSchroeder wrote:Would this typically be done with a fact table that records a transaction for every time the opportunity changes?

Yes, that is essentially what an accumulating snapshot fact table is.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Changing Fact table quetion

Post  JSchroeder on Mon May 13, 2013 10:59 am

Ok, isn't it somewhat different from an accumulating snapshot because the amount or projected close date can change without the status changing.

JSchroeder

Posts : 12
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  ngalemmo on Mon May 13, 2013 12:44 pm

Accumulating snapshots record versions of the fact row. It doesn't matter what on the row changed. The timestamps for tracking when a row is effective are different than the timestamps indicating when a status is effective. The former are internal timestamps tracking rows while the latter is data in the row.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Changing Fact table quetion

Post  JSchroeder on Mon May 13, 2013 1:57 pm

Thanks,

My next question is then, if the status of an opportunity changes, do we update all the previous rows or only the most current?

JSchroeder

Posts : 12
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  ngalemmo on Mon May 13, 2013 3:30 pm

You create a new current row and expire the old current row. It's very similar to a type 2 dimension. You would frame queries against the fact in terms of the point in time of interest: current state, which looks are the active current row, or at some point in the past where you look at rows that were in effect at the desired 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: Changing Fact table quetion

Post  BoxesAndLines on Mon May 13, 2013 4:27 pm

In my accumulating snapshots, I update the one row. There are no versions. If you need version history, create a transaction fact (which is usually used to load the accumulating snapshot).
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  JSchroeder on Mon May 13, 2013 4:45 pm

I too was under the impression that an accumulating snapshot will have only one record per instance. So you're saying that I could create two fact tables - one for transactions that contains the complete history and one for the accumulating snapshot that contains just one row for each instance?

JSchroeder

Posts : 12
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  ngalemmo on Mon May 13, 2013 5:35 pm

My apologies, I use a different interpretation of the term 'accumulating'. I look at it to mean you collect history of the states of the rows.

If you define 'accumulating snapshot' to mean a fact table that contains measures as of a particular point in time (such as a month-end inventory balance fact table), that's not what I'm referring to. To me, that is a simple snapshot fact with additional dimensionality. If a particular balance is restated, it is updated in place based on the dimensionality.

The accumulation of history, on the other hand, involves expiring the prior row and creating a new row with the updated values. This allows you to restate historically what was reported at different points in time relative to when the information was available in the warehouse.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Changing Fact table quetion

Post  cjrinpdx on Tue May 14, 2013 3:27 pm

It sounds like you are modeling Salesforce opportunities? You may consider two fact tables that could be sliced together by common dimension. One accumulating snapshot fact table to model the opportunity as it flows through the sales pipeline, and a transactional fact table to capture the changes to the projected close date and amount. If you are in fact using Salesforce you can configure the opportunity object to capture history on those fields, thus creating your CDC mechanism for your transactional fact table.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Changing Fact table quetion

Post  JSchroeder on Tue May 14, 2013 3:41 pm

We are indeed using Salesforce opportunities.

JSchroeder

Posts : 12
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Changing Fact table quetion

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