JQAllen (2/29/2012)
Have you experienced the need for analyzing data temporally in your business? Do your existing information management systems allow you to view historical data? Typically, when you update or delete information in any application the previous state of that data is lost forevever. Have you ever wanted to be able to see what your information "used to be" as opposed to what it is now? In addition, have you ever wanted to know the state of your data in the past "as I knew it to be then" in comparison to "as I currently know it to have been then"?What is your experience in solving these problems?
That's why Data Warehouses are created.
Changes on DIMensional tables are tracked via SCD (Slowly Changing Dimensions) techniques - in general Type 2 SCD does the trick for most scenarios.
Changes on FACTual tables are tracked using snapshot-fact techniques.
In both case what happens is that you insert new rows to the target table and timestamp the valid period of each particular row.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.