Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tony Wilkinson
Starting Member
1 Post |
 Posted - 2011-09-06 : 10:01:48
|
| We've recently had a 'discussion' at work regarding how best to handle certain reference data in a warehouse.We have a number of reporting channels from the warehouse... cubes, business object reports etc... and they all source data slightly differently.One of the reporting requirements we have is to report the same data in different currencies so we have to deal with currency conversion in the different reporting channels. In one cube we may provide 6 reporting currencies (the 'Convert To' currency) and in another we may provide 8 different reporting currencies. These are subject to change according to business requirements so we need a way of the cube data sourcing to be aware of how to populate the Reporting Currency dimension. The ame sort of issue extends to the actual exchange rates we bring into the cubes as well. We store a number of different sets of exchange rates in the warehouse which are identified by a code. ie. Year End Rates, Current Month, Financial Rates, etc. Which exchage rates are used is again based on changing business requirement.So how do we implement this level of configuration?Options we've discussed are:i. hard code the where conditions into the cube DSV's or BO Universe queriesii. add additional attributes to the warehouse tables which allow the cubes to identify the appropriate data for themselves. these attributes would be maintained by the ETL processes based on config filesiii. store the configuration in a config table in the warehouse and implement a table valued UDF to retrieve the data. eg. udfGetReportingCurrencies('Cube X')The cubes and other data source queries call the same function with the appropriate parameter and the function is responsible for returning the correct data based on the values stored in the config table. I would appreciate any thoughts/views/opinions/experience etc. Not just on this specific issue but also on the more general issue of how to manage reporting config in a warehouse environment.Many ThanksTony |
|
|
|
|
|
Integration of reference data