Is MDX the answer here?

[eo]

I have a fact table with 8 measures. It shouls 8 values in it's original currency (what ever that might be). The fact table also shows 4 exchange rates from the original currency to 4 different reporting currencies (USD, CAD, GBP and EUR). So 1,000,000 South African rand can be converted to USD, CAD, GBP and EUR by a simple calculation using the exchange rates contained in the 4 exchange rate columns.
The cube users want to see the data in original currency, but would also like to see the data in all, or any of the 4 reporting currencies. I can create 4 named calculations for every measure, but the fact table with 8 measures, the number of measures will increase to 40 from 8. This will hamper effectiove cube navigation. Is an MDX query the answer here, where it could be done on the fly...and how is this done?

EO
Hertfordshire, England

 

[RiverGuy]

I would not do these on the fly, because one of the objectives of a data warehouse/data mart is to centralize the information. If you created a query to provide these other figures and a subsequent developer did the same for another report, he or she may produce a slightly different calculation.

That being said, I don't see 40 measures as being too many. If you organize them into logical folders, the cube should be easy to navigate (are you using SSAS 2005)? Personally, instead of the calculated members, I would be tempted to store the other currency measure values in the fact tables as opposed to the exchange rate.

 

[MDXer]

Yes you could do this in a cube using MDX SSAS provides a Measureexresson peopey which can be used for this type of stuff. However the thing to remember is that currency rates change over time and this can change how you want to handle this type of problem. If you are looking at say Daily Sales then the conversion may need to happen at the daily level, but if it is more along the lines of say an investment where you may hold something for x period and want to compare your actual profit it may be needed where you need to see the purchase price in today dollars.

This can be accomplished a number of ways

1) Build a Currency cube which has a date dimension and a currency dimension. THe measure will be the echange rate. You can then link this cube to an existing cube and use and do the calcul;ation there.

2) You can build a measure group that has 1 record for everyday with the exchnge rate for each day use these to then create the needed calculations

3) This is the most complex, but I feel best solution even not having done it yet.

you need to build the following.

Curecy dimension:
Currency_SK CurrencyName

lkp_Exchange
Currency_SK Date_SK exchanrate


the lkp table would serve as a bridge from the dimension to the fact. The granularity of this table is 1 per day per currency, unless you actually track exchange rates more frequently.

you would bring the Currency dimension into your Cube and the lkp table bring in as a measure group (You can find documentation on creating the many to many structure)

I would then create a Currency Calc dimension that would be used as a reference dimension (Similiar to the periodicity technique) that you could have a measure that was say Converted Value which would be the calculation to do the math between the selected measure and the selected currency.

Like I said the 3rd would be the most complex but also the most accurate.


As for having discrepencies between various people you need to get an agreement from the business line users or Project Manager on what and how these calculations are to be implemented. If you have this document and the processes is fully tested and approved then you have what you need when someone says the numbers don't jive with theirs. Option 3 is by no means the fastest to implement but expreience has taught me that the fast thing to implement is rarely the best way in the long run.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[eo]

Hi Paul & RiverGuy

Thanks for the responses. Before the response from RiverGuy, I already started implementing a proof of concept whereby the two fact tables with the values to be converted was replaced with named queries where the 4 reporting currency values where calculated. 8 measures now turned to 40 (per fact table), but this was managed by DisplayFolders. This seems to work okay, BUT I would like to explore an alternative solution (if it indeed provides potential benefit). I think showing an example of the data will help. Note that @GBP, @EUR and @USD shows the new values calculated using the OriginalValue / relevant exchange rate (this is shown as Currency_ER). These calculated values are the only ones NOT available in the original fact table, and therefore has to be calculated.

OrigVal - OrigCurr - EUR_ER - GBP_ER - USD_ER - @EUR - @GBP - @USD
100 - ZAR - 9.00 - 12.00 - 8.00 - e11.11 - £8.33 - $12.5
150 - AUD - 4.00 - 3.00 - 3.50 - e37.50 - £50.00 - $42.85
60 - CAD - 3.00 - 2.00 - 2.60 - e20.00 - £30.00 - $23.08

There are typically 8 OrigValues so conversion to 4 reporting currencies (only 3 shown in example) would mean in increase in measures to 40.

It might well be that RiverGuy's solution is the best, but I thought it worthwhile to ask the question re MDX as well. So if the user drags OrigValue1, OrigValue5 and OrigValue8 onto the cube browser, and an MDX query for @EUR, it will take those 3 values and calculate their EUR values based on the daily exchange rate which is already contained in the database, it does not have to be obtained from elsewhere.

I personally cannot see how this would be possible, but hey...I am an SSAS 2005 novice

So Paul, I suppose now that I have given some clarity on the data in fact, would the solution's provided still work. And if calculartions are done on the fly, would it not negatively impact query performance?

I also agree with RiverGuys statement that calculations could be done here, and elsewhere, which could mean discrepancy.



EO
Hertfordshire, England

 

[MDXer]

In regards to performance since these are relatively simple calculations I don't believe you would see any noticable change in query performance unless you are looking at an extremely large data set and then by using partitioning and such you could improve over all performance.

Here are a few links that discuss in better detail what you are trying to do.

Working with Currency Conversions (SSAS)
Thomas Pagel BI Blog
Set Currency Conversion Options (Business Intelligence Wizard) (SSAS)

The problem with precalculating your currency conversions are:
1) What if an incorrect exchange rate was entered? You would have to reaggregate the values and reprocess that data into the cube. Processing a few rows that have exchange rates would be much faster.

2) More complex analysis are later requested. How would you handle issues like I had mentioned in my intial response. Where you need to look at values at the current rate or detail the difference between the intial rate and todays rate?




Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

Look for a whitepaper or faq posted here or linked to in the not so distant future as this functionality has been added to my Backlog for development in my current SSAS solution.

-Paul

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[eo]

Hi,

Would you mind being a bit more specific?

I have been following all material I can find on the solution, but just cannot get this to work.

We have a many to many currency conversion (of which I find only very little basic instructions). I follow the instructions I can however find, but when time to process I constantly get the error..."The reporting currency dimension was not found in the cube XYZ database on the '' server"

EO
Hertfordshire, England

 

[eo]

Initial indications are that we can afterall NOT use the BI Wizard for this puprose. (And I stand corrected). A Pivot Currency is the currency against which exchange rates are entered into the measure group. But this is not how our exchange rate dimension works. We do not convert all 200 currencies to GBP. We in fact convert all 200 currencies to each other. So not 200 lines of code, but rather 40,000. We can therefore not use Pivot currency.

EO
Hertfordshire, England