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?

The capacity measurements you take at a given time are "facts", presumably
based on the best available data at the time. I assume you have a time
dimension in your data mart. You could consider modeling the capacity facts
such that the time that the measurement was taken is part of the key of the
fact table.

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server...dp/1590599837/


"JamesCarters" <JamesCarters@discussions.microsoft.com> wrote in message
newsCF9B3E2-E9CD-4854-BAB1-293D4F6120B3@microsoft.com...
> 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?