Tracking fact table history

Hey guys. We have a business requirement to have point-in-time and to track history. I'm going to implement Type 2 slowly changing dimensions, but our fact table data (e.g. payment amount) can change as well as facts' foreign key values to dimensions.

I'm aware that a periodic snapshot approach to the fact tables is one of the standard approaches to tracking point-in-time with facts, but we've come up with an alternative approach that I'd like your opinions on.

The gist of the approach is to mainly treat the fact tables as pseudo-SCDs but with a twist. Suppose we have a dimension & fact like so:



I've gone through the process of writing queries against this and it looks like it'll work. I was thinking of having a udf for both the dim and the fact that would accept a date parameter. For current state, one could write a query to join the dim udf to the fact udf and pass both the current date. The udfs would then say, give me the row for the date in question (or, alternatively, use the IsCurrent flag) and then do the join using IdentityDimensionKey.


What are you guys' thoughts on this? Are there any hidden pitfalls I'm not seeing? This seems like it would be a standard approach to handling history in facts but mostly all I've seen is the fact-snapshot approach.

I'd really appreciate your thoughts and thanks!

Hi query_squidier,

I think you will run into serious scalability issues with your udf. I imagine it will trigger heavy row-by-row processing rather than set-based / star join processing.

I can't follow your design idea, but in the absence of proven experience to the contrary I suggest you would be better off sticking to the "standard approach".

Good luck!
Mike