Point in time reporting using ad hoc BI capability

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

Sure. Any BI tool can handle queries like that... it is all a matter of how you set things up. It's simple SQL after all.

One bit of advice... for the current row, you should set the expiration date to a high date in the future, such as 12/31/2999, rather than null, so that an expression such as date BETWEEN (effective date, expiration date) works properly.

Depending on the BI tool, you may also be able to create standard prompts/filters for the ad-hoc users to utilize. (e.g. Point_in_time_prompt)

Otherwise you just need to educate them how to use the various date fields appropriately.

Thank both for your reply.

Yes you are right, all that info can be retrieved using SQL based on different dates (and we got this under control in our model). The question I guess (is more at BI layer implementation level) that:

a. do we need to create separate views of data (current view, point in time view etc..) and then expose them to BI layer? And depending on type of reporting, users will choose one or the other from within BI tool? If not, then what are alternatives (best practices)?

b. is there any tool specific case study/ reference meterial on BI implementation (temporal structure) I can use. I can see loads of stuff which explains time/history management concepts but would like to see an example.

Thanks.

I would only expose one of the views, the EDW effective date view. Otherwise, your reports will always change with the other option.

The reports can change even with EDW effective date/time if there is subsequent movements of data (e.g. backdated transactions are entered, or errors are reprocessed).

It's a business requirement to report based on:
a. EDW effective date/time as well as
b. When did I know this (i.e. system insert/update datetime)? This is to be able to reproduce a report exactly as it was on a perviously run regardless of subsequent data movments (for the reporting period).

As I said, there is no problem with SQL but .....not sure how BI design should cater for it. The BI team here is advocating different data views (i.e. different semantic layer/universe per view of data). I am interested to know if there is a standard/working design solution to this?

Thanks again.

It really depends on the tool in question.

For example, Business Objects allows you define filters in its Universe. The users would have one view, but can drag a "Current Data" filter object (whatever you want to call it) or a "Select as of Date" object into the report. You can also define default behavior so that the user always gets the current view unless they explicitly do something else. The latter can have an embedded prompt to allow the user to enter the as of date of interest. Other tools have similar functions.

In fact, it is Business Objects. Thanks for you replies. Your posts give me enough ideas to start digging more detail.

Any reference material on this kind of implementation or URL link to a case study etc will be great.

Nash