Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Tracking fact table history

2 posters

Go down

Tracking fact table history Empty Tracking fact table history

Post  query_squidier 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:

Tracking fact table history Dimfac12

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

Back to top Go down

Tracking fact table history Empty Re: Tracking fact table history

Post  Mike Honey 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
Mike Honey

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

Back to top Go down

Back to top

- Similar topics

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