Versioning Fact

[DWHquestions]

Hi,
Just 2 basic questions would be helpfull if you can clarify.
1. Can a Fact table be versioned? I have a requirement for displaying data as it was at any point of past time in the database. For this it is required to maintain versions in Fact table. Is it possible? If so, what is this fact table called.
2. SCD is referred to only Dimension table or Fact table also?

[BoxesAndLines]

1. Yes, Snapshot fact
2. Dimension only

[DWHquestions]

Thanks for clarifications

[John Simon]

We use a publication date as another attribute in the table.
So while a record may be a "late-lander" based on it's start date, the publication date will allow us to recreate the exact same numbers as it is the date when the data was "published" to the data warehouse.

[VHF]

Although the SCD2 technique properly only apply to dimensions, many people have implemented SCD2-like facts with BeginEffectiveDate and EndEffectiveDate fields.

Ralph Kimball presented this pattern in the "Dimensional Modeling in Depth" class as "Instanteous Balances". It allows quering the state of afairs at any moment in time in the past. However, the catch is once you do this in your fact table you must ALWAYS CONSTRAIN YOUR QUERIES AGAINST THAT FACT TABLE BY DATE... FOR THE REST OF YOUR LIFE. Dr. Kimball compared this to getting hooked drugs--you need to take it every day for the rest of your life!

[Sponsored content]