Point in time reporting using ad hoc functions
Page 1 of 1
Point in time reporting using ad hoc functions
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
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
nash- Posts : 18
Join date : 2010-03-12
Similar topics
» Reporting on attribute changes between 2 points in time
» Point of time information from accumulating snapshot.
» How to model number of subscribers for a certain point in time
» Capturing Age at point in time - SCD2 / Calculated Member/ New Dimension??
» Problem with Single Surrogate Keys in the AS-IS/Point in Time Dimensional Modelling
» Point of time information from accumulating snapshot.
» How to model number of subscribers for a certain point in time
» Capturing Age at point in time - SCD2 / Calculated Member/ New Dimension??
» Problem with Single Surrogate Keys in the AS-IS/Point in Time Dimensional Modelling
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|