Business Intelligence – Oracle

Hyperion Essbase 9.3.1 – Currency Conversion in ASO Cubes – Using MDX and Dynamic Hierarchies – Approach 1

Posted by Venkatakrishnan J on December 31, 2008

In the couple of blog entries here and here, we had seen an alternative approach to currency conversion in BSO cubes. One of the main reasons why i introduced that approach was to make sure that approach to currency conversion remains seamless as we move to ASO. The idea and the approach remains the same i.e we would be loading the rates and data within the same cube. The major difference between the currency conversion in ASO and BSO is that ASO currency conversion is dynamic i.e the conversion happens during data retrieval. In BSO the currency conversion happens during the data aggregation/calculation. To start with let us use the Aggregate Outline Migration Wizard to migrate the outline from BSO to ASO. When you migrate, ensure that you are using interactive migration so that you can modify the violating members manually. Following are the changes that one would have to make in the outline

1. Convert the Products dimension to have Multiple Hierarchies. And change the Diet member to be having a dynamic Hierarchy. This is because ASO does not support multiple shared members within a stored hierarchy.

2. Convert the Scenario dimension to have multiple hierarchies. The idea is to load all the input Local currency data into the Stored local hierarchy. And all the conversions would happen under the Dollar dynamic hierarchy. We need to convert Dollar to dynamic hierarchy since ASO does not support calculations on non-Accounts dimensions.

3. Change the input data for Rates to be loaded against Act and Bud intersection members instead of Actual and Budget.

4. Create a new ASO cube and copy the members from the wizard to the ASO outline.

5. Copy the rule files that we created the other day from the BSO cube to the new ASO cube.

6. The outline conversion wizard does not convert the BSO formula automatically to ASO MDX formulas. So, change the formulas for the below shown members

Margin % ----- ([Margin] - [Sales])/[Margin] * 100
Profit % ----- ([Profit] - [Sales]) / [Profit] * 100
Variance ----- [Actual] - [Budget]
Variance % ----- ([Actual] - [Budget]) / [Actual] * 100

7. This will ensure that the outline is now ASO compliant. In order to accomodate the currency conversion, we need to add formulas to the Actual, Budget and Actual @ Budget XChg members. The formulas are given below

Actual ----- [Act] / ([Act],[Rate Product]).value
Budget ----- [Bud] / ([Bud],[Rate Product]).value
Actual @Budget XChg ----- [Act] / ([Bud],[Rate Product]).value

As you see, all we are doing is we are making the Actual, Budget members to be dynamically calculated using the MDX formulas above.

8. Let us load the data using the same rule files which we copied over from BSO. Once that is done, let us look at the data to see whether the calculations are done correctly.

As you see, the numbers above match exactly with what was there in the BSO cube. But the drawback with ASO is that, certain BSO specific features like Expense Reporting etc are not supported. These would have to be manually handled using MDX and UDAs. Also, the above approach might not be suitable in all the cases whenever retrieval performance is very important. For such cases, let us look at another approach in a future blog entry.

Belated New Year Wishes everyone!!!

3 Responses to “Hyperion Essbase 9.3.1 – Currency Conversion in ASO Cubes – Using MDX and Dynamic Hierarchies – Approach 1”

  1. […] by Venkatakrishnan J on January 5, 2009 In the previous blog entry here, i had covered an approach to do Currency Conversion in ASO Cubes. In most cases, that would […]

  2. Mark_S said

    Excellent article, much appreciated. I have an interesting problem that’s a twist on your scenario. I am getting data that’s all in one currency (USD, in fact), and my task is to create a way within an ASO cube to calculate local currency values appropriate to the Market.

    In my cube’s Market dimension, I’ve assigned UDAs to each market that are actually currency codes. And I’m also loading a rate for each currency in a separate branch of my account hierarchy. I have a Scenario dimension that has a stored USD member that I’m loading data into, and a calculated member that I’m still figuring out.

  3. Aj said

    How would you handle multiple currency conversions and still aggregate in USD? Suppose we have 3 products who are sold under New York Location under 3 different currencies, how would you handle that?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: