Tracking fact table history

View previous topic View next topic Go down

Tracking fact table history

Post  query_squidier on Thu Oct 11, 2012 1:13 pm

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!


Posts : 1
Join date : 2012-10-11

View user profile

Back to top Go down

Re: Tracking fact table history

Post  Mike Honey on Mon Oct 22, 2012 3:31 am

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 Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum