Historical and Active Transactions Reporting
2 posters
Page 1 of 1
Historical and Active Transactions Reporting
Hi
We are planning to build a sales data mart where the fact table would be linked with customer, product, store, date dimensions. There is a requirement to implement SCD type 2 for maintaining historical data with the dimensions.
Now it is understood that any type 2 change for a given customer record would segment the fact table data where a set of fact records would point to old surrogate key for the same customer id (business key) and any new transactions (facts) captured would point to the new surrogate key.
In the dimension model old and current dimension records can be identified by a flag called 'Current = Y or N'
Now assume the type 2 change occured in the middle of the month ( may be for two dimensions customer, product) and user is generating a report at the end of the month.
In Businessobjects reporting tool when the business user is given access to generte ad-hoc reports, would the user expected to drag and drop the 'current object' against each dimension like customer , proudct to get the latest active data.
I think this might put more burden on the user.
Would like to know What is the industry standard approach
Thanks
We are planning to build a sales data mart where the fact table would be linked with customer, product, store, date dimensions. There is a requirement to implement SCD type 2 for maintaining historical data with the dimensions.
Now it is understood that any type 2 change for a given customer record would segment the fact table data where a set of fact records would point to old surrogate key for the same customer id (business key) and any new transactions (facts) captured would point to the new surrogate key.
In the dimension model old and current dimension records can be identified by a flag called 'Current = Y or N'
Now assume the type 2 change occured in the middle of the month ( may be for two dimensions customer, product) and user is generating a report at the end of the month.
In Businessobjects reporting tool when the business user is given access to generte ad-hoc reports, would the user expected to drag and drop the 'current object' against each dimension like customer , proudct to get the latest active data.
I think this might put more burden on the user.
Would like to know What is the industry standard approach
Thanks
DMModeler- Posts : 6
Join date : 2010-05-14
Re: Historical and Active Transactions Reporting
If you are implementing type 2 dimensions, it is common to need to support both current and at the time reporting. With BOBJ, your options are either to provide both a current and at the time folders for dimensions or implement two universes. Most of the time, you wind up implementing two universes because most of the time uses want the current dimension state, and using them is simpler. And, to be honest, most of the time I've only implemented type 1, because when you talk to the business about it, they simply don't have any interest in old values (this does depend on the industry as well), and when they do, it is usually just a few attributes. About the only thing they care most about historical perspective is with hierarchies.
Similar topics
» Finding Active Employee for a specific period of time.
» Historical Measures Missing
» Is An Historical Database Always a Data Warehouse?
» Architecture questions : implement historical data view without SCD?
» Does a SCD Type 1 Change Response Always Update All Historical Records?
» Historical Measures Missing
» Is An Historical Database Always a Data Warehouse?
» Architecture questions : implement historical data view without SCD?
» Does a SCD Type 1 Change Response Always Update All Historical Records?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum