How to update an accumulating snapshot fact table?

• aaa121

  SSCertifiable

  Points: 5902

  More actions

  August 10, 2009 at 7:45 am

  #137918

  Hi All,

  This is my first time implmenting an accumulating snapshot fact table and I require some guidance.

  During a load I can have the same item inserted as a new record and moving through more than one stage of the process which I am tracking within the fact table.

  I have read this excellent article by Vincent Rainardi and this mentions the use of a delta fact table. This will not work for my sceanrio as it only captures a maximum of 2 changing values - previous and after - whereas I could have several changes.

  http://www.sqlservercentral.com/articles/Design+and+Theory/2769/

  What I really need to know is how best to handle the updates. My SSIS package basicially takes all the staged data looks up the surrogate keys and inserts it into my fact table.

  I now need to extend it to do the following;

  1. Handle both a new record and any subsequent movements through in the process within a load.

  2. Update the existing date keys when a existing record has moved through another stage in the process.

  This really feels very similar to managing SCD-1's in dimension processing! Anyone able to advise?

  Thanks