Fact table changes

Our data mart is medical based type of subjects.
Recently; the government changed how some "hours" of service could be
provided to certain people. The "hours" value is stored in a Fact table.
So for example; Client XYZ used to have 50 hours; it may have 63 now.
So I see how easy it would have been to make these data changes if it had
been in a Dimension - would have been type 2 SCD - but not sure what best
approach is for changes in a Fact table. I'm almost tempted to have a
status column to identify which Fact record is current; but then I would
have much work to do in SSAS to change how the Cubes are put together.
What is the best practice when you have to make data changes to existing
Fact records in a dm/dw?I'd recommend avoiding changes to historic data in a DW unless they were
genuinely erroneous.
Is there a date dimension relationship in your fact table? If so, what if a
user was to pull up a report from the past where the 50 hours was correct
but your warehouse reported 63? Would this be incorrect? If there are no
dates one option as you suggest would be a flag. Then you could construct a
view selecting the same columns as before but only including current rows.
The only change you'd need to make in SSAS would be in the Data Source
View - to change from the table to the view. The disadvantage of this would
be that end-users couldn't report on the previous 'hours' value. This gets
worse if the value changes again.
A better option ('best practice' perhaps) would be to introduce a start and
end date key (related to your date dimension obviously) over which period
the value was current - perhaps seperate to any existing dates. This way end
users could query for the different hours value based on when it was
relevant. A new value generates a new row with new dates. If necessary you
could still construct a view for the most recent value - but you would still
have all of the data in the database to support more sophisticated analysis.
There's more work here clearly - only you can decide whether its worth it.
HTH
--
Phil
http://www.clarity-integration.com
http://www.phil-austin.blogspot.com
"Joe" <hortoristic@.gmail.dot.com> wrote in message
news:0CA262DF-F121-496C-878E-0CDEC387D159@.microsoft.com...
> Our data mart is medical based type of subjects.
> Recently; the government changed how some "hours" of service could be
> provided to certain people. The "hours" value is stored in a Fact table.
> So for example; Client XYZ used to have 50 hours; it may have 63 now.
> So I see how easy it would have been to make these data changes if it had
> been in a Dimension - would have been type 2 SCD - but not sure what best
> approach is for changes in a Fact table. I'm almost tempted to have a
> status column to identify which Fact record is current; but then I would
> have much work to do in SSAS to change how the Cubes are put together.
> What is the best practice when you have to make data changes to existing
> Fact records in a dm/dw?