June 25, 2010 at 3:12 pm
Hi,
I've just started modeling our DW, but one thing that just came to mind is currency conversion. We plan on updating the DW after Month End from each of our offices, which are in the US and across Europe, but I'm not sure how best to do currency conversion so data between different offices can be combined.
When the time comes I'll pose this question to management, but for now while making rough models and setting-up a proof-of-concept I'd like to know how others have done it. Our main office is in the US so most reports there will be in USD, but I'd like each regional office to be able to run reports in their native currency. For this I'm not sure if it's best to have two separate sets of data, one native and one USD, or to keep one set of data in the native currency and have a Dimension table with current conversion to be used to convert the data to any other currency at run-time.
Technically and logically I'm not sure how this will come together, but any thoughts?
Thanks for any advise or suggestions, and take care. Also we're planning on using SSAS 2008 for everything and I'm playing out the model for now in Visio 2007, which is working out great.
Edit: I just noticed I misspelled 'Currency' in the Topic title, but it won't let me change it. Blah, sorry 🙂
Sam
June 27, 2010 at 5:19 pm
At the granular level I would store all figures in the currency of origin and I'll identify the currency.
I would also have a DIM_CURRENCY_CONVERSION table storing daily conversion rates for all relevant currencies.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 27, 2010 at 6:10 pm
PaulB-TheOneAndOnly (6/27/2010)
At the granular level I would store all figures in the currency of origin and I'll identify the currency.I would also have a DIM_CURRENCY_CONVERSION table storing daily conversion rates for all relevant currencies.
I'll second that.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2010 at 7:55 am
If the data is coming from a system that stores both the local currency and USD, which is most ERP and accounting systems in use these days, then just pull both into the DW. Have columns for LC and for USD, then users can choose which to use. If you have more than just EUR and USD, then you will also have to pull the currency descriptor as well.
June 28, 2010 at 11:52 am
Ross McMicken (6/28/2010)
If the data is coming from a system that stores both the local currency and USD, which is most ERP and accounting systems in use these days, then just pull both into the DW. Have columns for LC and for USD, then users can choose which to use. If you have more than just EUR and USD, then you will also have to pull the currency descriptor as well.
I agree this approach works in certain conditions but I do prefer a design that solves all cases - including the ones still beyond the horizon.
Having said that I'll stick with the original currency at granular level -including currency id- and a DIM table storing daily conversion rates for all relevant currencies.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 28, 2010 at 1:05 pm
Thanks for the great suggestions. Using the scenario of keeping the currencies in their native format with a Currency Dim table with daily conversion rates (which we already have in our OLTP system), how transparent could SSAS convert between currencies? For example if I have a perspective for Mexico that only brings in their data, it wouldn't need conversion, but if management wants a report with all perspectives (say US and Mexico), how transparent could this be done so they can only select the amounts without thinking of currency and SSAS converts MX to USD for the Mexico records? Or is this beyond the built-in capabilities of SSAS or requires some funky MDX coding?
Thanks again --
Sam
June 28, 2010 at 2:53 pm
I just ran across one of Ralph Kimball's articles addressing this exact problem:
http://intelligent-enterprise.informationweek.com/020221/504warehouse1_1.jhtml
In my modeling I'll follow his suggestion, which several replies to my OP also recommended, and create two currency fields where possible ... one for local currency (USD) and one for original currency with a dimension for currency. I'll populate these during ETL process so there's no need to calculate the currency at report run-time.
FYI for anyone else looking at a similar scenario --
Sam
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply