Point in time reporting using ad hoc functions

nash Sat Jul 10, 2010 12:40 am

Hi There
Is there a BI tool that can handle the following two reporting scenarios? Please note scenarios to be preferably supported through ad hoc reports that user can build on the fly? Also, if you know how to achieve this functionality in a particular BI tool, a little bit description on that will be greatly appreciated. I couldn't find any material on this either from Kimball or others (if there is, please do let me know). I will try to briefly explain the background but I am sure it will still take up some space. OK here I go....

I have a dimensional data model which, apart from other fields, has the following specific ones that I need for this example:

ID = Surrogate key
BK = Business key
Current Flag = to mark most current version of fact records
Effective Date = when the record was effective from business perspective
Expiry Date = when record expired (every update to a row is inserted as new fact row and marked as current)
Record Insert Date (system date) = when did I first know of this record
Revenue = Fact

Sample data looks like this:
ID BK Row_Insert_Dt Effective_Dt Expiry_Dt Current Revenue
-- -- ------------- ------------ --------- ------- ------
1 1010 20-Jan-10 10-Jan-10 15-Feb-10 N $100
2 1010 17-Mar-10 16-Feb-10 13-Mar-10 N $150
3 1010 19-Mar-10 14-Mar-10 NULL Y $180

Scenarios (A):
On 30 Jun 2010, a business user wants to run report for BK = 1010 to see revenues as they would have looked like on:

Reports Run on Expected Result
-------------- ---------------
12-Jan-2010 $0
20-Jan-2010 $100
17-Feb-2010 $100
15-Mar-2010 $100
18-Mar-2010 $150

Scenarios (B):
On 30 Jun 2010, a business user wants to run report for BK = 1010 selecting different reporting periods and wants to see revenues:

Reporting period Expected Result
--------------- --------------------
12-Jan-2010 $100
20-Jan-2010 $100
17-Feb-2010 $150
15-Mar-2010 $180
18-Mar-2010 $180

As you can see, depending on the intent of the user, the expected results will be different for the same dates.

Thanks for take the time to read this.

Nash