Changing Fact table quetion

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.

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.

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

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.

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?

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.

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).

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?

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.

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.

We are indeed using Salesforce opportunities.