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

Reporting on Type 2 dimension changes

2 posters

Go down

Reporting on Type 2 dimension changes Empty Reporting on Type 2 dimension changes

Post 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?


Posts : 3
Join date : 2012-10-20

Back to top Go down

Reporting on Type 2 dimension changes Empty Re: Reporting on Type 2 dimension changes

Post  ngalemmo 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.

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

Back to top Go down

Back to top

- Similar topics

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