Reporting on attribute changes between 2 points in time

View previous topic View next topic Go down

Reporting on attribute changes between 2 points in time

Post  PeteGrace on Thu Sep 01, 2011 7:16 pm

We have a reporting requirement which weve been able to fulfil to a point using a Type 6 SCD, but our solution isnt as flexible as Id like in terms of opening it up for ad-hoc analysis by business users.

The initial requirement was to show cases where a particular attribute had changed from one month to the next, along with various measures coming from the snapshot for the earlier month. So as long as we schedule this report to run on the 1st day of each month then no problem, we can compare the current (Type 1) value with the historical (Type 2) value:

select c.case_ref, c.historical_attr_val, c.current_attr_val, f.meas_1, f.meas_2...
from fact_case_snapshots f
   inner join dim_case c on f.dim_case_key = c.dim_case_key
where f.snapshot_date = '20110731'
   and c.historical_attr_val <> c.current_attr_val

The problem is that my users now want to take it a step further and do some ad-hoc analysis of their own along similar lines, but based on changes between two historical points in time e.g. Were now in September, but we want to report on changes between 30th June and 31st July.

I should point out that we only snapshot open cases in the fact table, and thus each case thats in the snapshot for 30th June may or may not be in the snapshot for 31st July depending on whether it has been closed during that period.

Using SQL, and because I know the design of the fact table along with how it joins to the dimension, I can perform the query quite easily with a manual join using the business key and effective/expiry dates. However it feels like Im breaking several rules of dimensional modelling in doing so:

select c.case_ref, old.historical_attr_val, new.historical_attr_val, f.meas_1, f.meas_2...
from fact_case_snapshots f
   inner join dim_case old on f.dim_case_key = old.dim_case_key
   left join dim_case new on (old.case_ref = new.case_ref
      and '20110630' between new.effective_date and new.expiry_date)
where f.snapshot_date = '20110630'
   and old.historical_attr_val <> new.historical_attr_val

Even if this was an acceptable way of doing the query its not something we could expect business users to do from a Business Objects universe at least without also giving them the ability to join incorrectly with the dimension.

Can anyone come up with a suggestion for a better way of modelling this sort of scenario, or do we just have to accept that there are certain advanced reports that will always have to be done by IT pros and arent possible to produce through user-friendly self-service?

Any ideas or thoughts much appreciated.



Posts : 7
Join date : 2011-09-01

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