Historical and Active Transactions Reporting

View previous topic View next topic Go down

Historical and Active Transactions Reporting

Post  DMModeler on Sat Apr 07, 2012 1:15 pm

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

DMModeler

Posts : 6
Join date : 2010-05-14

View user profile

Back to top Go down

Re: Historical and Active Transactions Reporting

Post  ngalemmo on Sat Apr 07, 2012 5:14 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum