Reporting on attribute changes between 2 points in time

[PeteGrace]

We have a reporting requirement which we’ve been able to fulfil to a point using a Type 6 SCD, but our solution isn’t as flexible as I’d 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:

Code:

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. We’re 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 that’s 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 I’m breaking several rules of dimensional modelling in doing so:

Code:

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 it’s 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 aren’t possible to produce through user-friendly self-service?

Any ideas or thoughts much appreciated.

Cheers,
Pete