Reporting on Type 2 dimension changes

View previous topic View next topic Go down

Reporting on Type 2 dimension changes

Post  kirk@hoodriversoftware.co on Thu Sep 10, 2015 5:34 pm

I have several dimensions that accumulate Type 2 attribute changes, but I'm not sure how to allow consumers to easy consume the "change tracking" for a given dimension. For example, if I have a Product dimension with 10 attributes, each set as Type 2, I could create a view that's consumed by, say, a pivot table in Excel (Power Pivot) and use the Natural Key (say, the Product Number) as a filter, but what I really want to show is only the changes that took place over time. If I simply display the entire dimension for the given key, it won't be very user friendly.

I assume someone has anyone solved this challenge?

Thanks

kirk@hoodriversoftware.co

Posts : 3
Join date : 2012-10-20

View user profile

Back to top Go down

Re: Reporting on Type 2 dimension changes

Post  ngalemmo on Thu Sep 10, 2015 6:32 pm

It can be done in a query that uses the LEAD/LAG windowing function to look at two rows at once.  The query itself is somewhat tedious and repetitive to code, but not difficult. You can then compare the old and new values and decide how you want to display the column.

The issue with putting a windowing function in a view is performance.  As a view, windowing functions can force the database system to materialize the full set produced by the view.  It is possible an optimizer can improve on this if there is a filter on the same columns used to partition the window.  It should be able to push it down to the view to reduce the size of the result set.  Filters on non-partitioning columns cannot be resolved in that manner because it affects the contents of a window.  If such a filter is pushed down into the view, the set defined by the view becomes ambiguous, which is against relational rules.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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