Hyperion Essbase 9.3.1 – Alternative Approach to Currency Conversion in BSO Cubes – Using Calculation Scripts – Part 2
Posted by Venkatakrishnan J on December 29, 2008
In the last blog entry we saw how to go about designing the Essbase Outline to accomodate Rate loads into the same database. Today we shall look at how the currency conversion is accomplished. To begin with let us load some sample data into the Essbase Cube. The sample data is shown below
As you see, the input data is in Local currency of each city/country. Let us create a simple rule which will load the above data into the Essbase Cube.
Now, let us load the rates (the rates that we saw in the last blog entry) using a similar rules file as above.
Once the rate load is done, the next step is to convert the Local currency into the corresponding USD currency. This is achieved through calculation scripts. In effect, the Members of the Scenario dimension under Local, would store the local currency. And the Members of the Scenarion dimension under Dollars, would store the converted USD. To achieve this, let us create a simple calculation script as shown below
SET CACHE HIGH; SET CALCPARALLEL 2; FIX("Sales",@LEVMBRS("Market",0)) "Actual" = "Act" / "Actual"->"Rate Product"; "Budget" = "Bud" / "Budget"->"Rate Product"; "Actual @ Bud XChg" = "Act" / "Budget"->"Rate Product"; ENDFIX; FIX("Actual","Budget","Actual @ Bud XChg","Act","Bud") AGG("Product"); CALC DIM("Year"); ENDFIX; FIX("Actual","Budget","Actual @ Bud XChg") AGG("Market"); ENDFIX;
The above script can actually be split into 3 parts. They are
1. Currency Conversion at the lowest level
2. Aggregation of all the relevant dimension for all the Scenarios except Markets
3. Aggregation of the Markets dimension for USD converted Scenario alone.
The above trifurcation is needed in order to ensure that we are not aggregating Local Currency across Markets as it would not make sense to add Euros with Pounds. Now, let us execute the above calculation and look at the data.
As you see, the conversions have happened correctly based on the input rates. The above approach basically overwrites the existing rate values. Hence, after a calculation the rates would not exist in the cube. If rates are desired for future calculations just exclude the “Rate Product” member from the above calculation. The method above can be used/configured to suit quit a few scenarios. But the major drawback with this approach is that there are possibilities of huge amounts of cube fragmentation. Due to the complex nature of the calculations (getting the rates from a specific intersection), unnecessary blocks might be created and hence can result in reduced block density and also cube fragmentation. So, whenever this approach is used, frequent export and import of lowest level data(along with the rollups) is needed.