Slowly changing fact table? 1

[JamesCarters]

The company I work for track refinery capcities around the world and what to
put this data into a data warehouse. At first glance it looks simple enough,
every quarter they take a snapshot of each refineries capacity and that is
the fact table and the dimensions would be owners, location etc.

The problem I have though is that the capacity snapshot is not a fact, it is
the best information at the time, and is subject to change, even going back
years, when better data is available. The business has a need to see best
current data as well as being able to know what the best current data was at
a certain time.

Would the best approach be to make the fact table slowly changing and then
have validity data as dimension, or is there a better approach?

 

[Craig0201]

Personally, I would add a couple of fields for DataValidFrom and DataValidTo to your fact table. The history is then adequately recorded.

I would not get hung up on the terminology FACT table. These are still facts at a point in time.

C

 

[johnherman]

Yes, you would have Effective dates for the facts, like you do on the dimensions tables.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright