Currency Conversion in ASO

In the last three days, I’ve had two different people ask me how to do currency conversion in ASO.  Procedural calculations in ASO is tricky and the documentation isn’t great (and that’s being very polite to Oracle).  So let’s take a look at how to do a simple currency conversion in ASO.  First, we’ll need to set up a database to try this out.  In my case, I’m going to start with Sample.Basic.  The problem with that of course is that it isn’t an ASO application.  I quickly converted my Sample.Basic from BSO to ASO with the conversion utility, but several additional steps were required:

  • The Measures dimension has several MDX formula’s that need to be fixed.
    • Deleted the Opening Balance member.
    • Fix the Ratio member formulas.
    • Add a new parent named Exchange Rates.
    • Add two children, CAD and Exchange Rate.
  • The Product dimension comes across as dynamic due to shared members.
    • Add a roll-up for All Products and move the 100, 200, 300, and 400 parents underneath it.
    • Change All Products and Diet to be stored hierarchies.
    • Make the Product dimension label-only.
  • We’ll use the Market dimension for our currencies.
    • Create two new parents, USA and Canada.
    • Move East, West, South, and Central under USA.
    • Move Illinois and Louisiana under Canada.
    • Rename Illinois to Ontario.
    • Rename Lousiana to Quebec.
    • Add CAD as a UDA to both of the Canada children.
  • Create a new dimension named Currency.
    • Add two children, Local and USD.
    • Make the dimension label-only.

I’ve included a zip file for download here.  This file includes the following:

  • SampASO.otl – The modified outline file discussed above.
  • SampASOFinal.txt – The sample data file used to test the currency calculation.
  • currency.csc – The calculation script we’ll discuss shortly.
  • currency.msh – The MaxL script that actually executes the currency conversion.
  • currency.bat – The batch file used to execute the MaxL script.

Once you’ve made the changes to your outline (or used the one I’ve included), load the attached data file.  This file can be loaded with no load rule.  After the load has completed, we are ready to actually try our calculation.  We have one last change to make to the outline before we get there.  We will make use of our UDA that we added to the Market dimension.  Modify the formula of the Exchange Rate member:

CASE WHEN IsUDA([Market].CurrentMember, “CAD”) THEN
([CAD],[No Market],[No Product])
ELSE
1
END

This calculation will determine the exchange rate member to reference in our actual calculation.  Essentially, we check to see if the member is flagged as CAD and if so, we find the exchange rate intersection.  If not, we just return 1 so that all of our local values get copied over straight into USD.  The only downside to this method is that we have to modify this calculation every time we need to add a currency.  Another way to approach this would be with an attribute dimension rather than a UDA.  But for now, we’ll stick with simple.  And now we have our data, we have some rates, and some logic to determine the rates.  Let’s actually calculate the USD amount from Local.

To calculate the USD amount, we’ll be using the Execute Calculation MaxL statement.  If you look up the documentation, you will see quite a few options for the ASO version of this statement.  For currency conversion, we only have a few required options:

  • local script – The CSC file that you find in the zip file.  This will contain the calculation to be executed.
  • source region – The MDX set containing all of the data elements necessary to complete our calculation.  If you try to reference a member thats not included in your set, you’ll get an error.  Think of this like an Excel spreadsheet you might reference with a formula.
  • pov – An MDX set containing the point of view.  Every intersection returned by your MDX statement will have the calculation in our CSC file executed against it.  This can get dangerous in a hurry.  Anything we do here can dramatically impact the number of cells generated and the amount of time and processing power required to complete the calculation.

Let’s examine the contents of the CSC file before we get into the MaxL script:

([USD]) := ([Local])*([Local],[Exchange Rate]);

In this example, we are setting the USD member equal to the Local member times our calculated Exchange Rate that we set up earlier.  Now let’s actually dive into our MaxL script:

execute calculation on database SampASO.SampASO with
local script_file “currency.csc”
POV “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([Profit],10,LEAVES)},
CrossJoin({Descendants([All Products],10,LEAVES)},
CrossJoin({Descendants([Market],10,LEAVES)},
CrossJoin({Descendants([Scenario],10,LEAVES)},
{[USD]}
)))))}”
SourceRegion “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([Profit],10,LEAVES),[Exchange Rate]},
CrossJoin({Descendants([All Products],10,LEAVES)},
CrossJoin({Descendants([Market],10,LEAVES)},
CrossJoin({Descendants([Scenario],10,LEAVES)},
{[Local]}
)))))}”;

I’d first like to note that if you look this statement up in the documentation, it says execute calculation on database app.db.  However, if you just copy the sample at the bottom of the documentation, they left out the word database.  So if you get a syntax error and you copied it from the sample, this might be your issue.  Now on to the first piece of our statement.  First, we reference the CSC file that we’ve already covered.  That’s pretty straight forward.  Next, we tell Essbase the POV that we want to use to execute that script.  In this example, we are actually executing the calculation on almost the entire cube, save the Currency dimension.  Luckily, this is a very small cube.

Finally, we tell Essbase what the source for all of our data is.  If you compare the two, at first glance it looks like the only difference is the that we reference Local in our source and USD in our POV.  But, if you look very closely at the second line of our statements, the POV only has Profit leaf members, while the source has the Exchange Rate member.  Now that we have our MaxL, CSC, and batch script ready to go, let’s execute the command and see what happens:

Currency01

If everything goes according to plan, we should see a large number of cells generated by Essbase.


Adding Dynamic Members from a Form in 11.1.2.4

In my last entry I demonstrated the use of dynamic members in Custom Plan Types.  In today’s installment we’ll actually put dynamic members to a more practical use.  The main benefit of dynamic members is to give the end-user the ability to add (or remove) their own members.  But, if they have to go to the Business Rules section of Planning every time to do so, the process will get old in a hurry.  Additionally, if you’ve never used menu’s in Planning, we’ll make excellent use of them today.

The first step in this process is to create out custom menus.  Follow these steps to create the necessary menus:

  1. Click Administration, then Manage, then Menus.DynamicMembers17
  2. Click the Add Menu button.DynamicMembers18
  3. Enter Manage Entities for the name and click OK.DynamicMembers19
  4. Click on the newly created Manage Entities and click the Edit Menu button.DynamicMembers20
  5. Click on the Add Child button.DynamicMembers21
  6. Enter the following and click Save:DynamicMembers24
  7. Click on the newly added Managed Entities parent menu item and click the Add Child button.DynamicMembers23
  8. Enter the following and click Save (remember we created our business rule in Part 1):DynamicMembers24
  9. Click on the newly added Add Entity child and click the Add Sibling button.DynamicMembers25
  10. Enter the following and click Save:DynamicMembers26

Once we have our menu ready, we can create our form and add the newly created menus.  Follow these steps to create the new form:

  1. Create a new form.DynamicMembers27
  2. Enter the following and click Next.DynamicMembers28
  3. Modify your dimension to match the following and click Next.DynamicMembers29
  4. Add Manage Entities to the Selected Menus list and click Finish.DynamicMembers30
  5. Open the form and test out your new right-click menu.DynamicMembers31

Now you have a form that can be used to allow users to input their own members in a custom plan type!


Enabling Dynamic Members in Custom Plan Types in 11.1.2.4

Now that I have my Rapid Deployment completed, I can start to use some of the new features in 11.1.2.4.  Today we will focus on the new ability to create dynamic members in Planning.  Now, I know what you are thinking, “We can already do that in 11.1.2.3.”  And to a point you would be right.  As long as you just want to add new members one of the module Plan Types (Workforce, CapEx, PFP), you can add dynamic members.  But, for those of us that have custom Plan Types (yes…everyone), Oracle has finally added this functionality beyond the modules.  In part 1 of this 2-part series, we’ll run through the entire process of enabling dynamic members in a custom Plan Type.  In part 2, we’ll use custom menu’s and a form to quickly enable users to add and delete those custom members.  Here we go…

  1. Open the Dimension section of your Planning application (I’ll be using the Vision application that we created here).
  2. Select the Entity dimension.
  3. Create a Sibling Member to the Management Rollup member (or any member of your choice).
  4. Enter details as shown here:DynamicMembers01

That takes care of the easy steps.  You can now add up to 100 members to the new Dynamic Members parent.  We chose inherit for the access granted.  This let’s us maintain the proper level of security for these members based on the security of the parent.  You can also say they have no access to the members by selecting None, read-only access by selecting Read, and write-back access using Write.  It’s important to note that if you want to give the users the ability to delete members, you must give them Write access.

Now that we’ve created the parent, let’s take a look at what happens on the back-end in Essbase.  Be sure to refresh the database from Planning, and then open the outline in EAS.  You should see something like this:

DynamicMembers02

Compared to this in Planning:

DynamicMembers03

So the data is stored in Essbase just like a traditional TBD would be in the “old days”, but we don’t actually show the members in Planning.  This makes things a bit tricky from an Essbase Add-In perspective, but this is 11.1.2.4, so you should be two versions removed from that (or if you are like me…you still have it installed with the In2Hyperion Add-In).  We’ll revisit this shortly, once we get Planning to actually let us add a member.  So let’s go ahead and enable the end-user to actually add members:

  1. We’ll start by adding a run-time prompt.  Open Calculation Manager and open the Variable Designer.DynamicMembers04
  2. Expand Planning and then expand Vision.  Right-click on Plan1 and click New.DynamicMembers05
  3. Enter the details and shown here:DynamicMembers06
  4. Now let’s go back to the System View and create our two business rules (one for add, one for delete).  Expand Planning,Vision, and Plan1.  Right-click on Rules and click New.DynamicMembers07
  5. Enter the details shown here:DynamicMembers08
  6. Enter the details shown here:DynamicMembers13
  7. Save the Rule.
  8. Modify the rule as show here:DynamicMembers12
  9. Save the rule as:DynamicMembers11
  10. Deploy the rules to Planning.

Now we should be able to run our newly created Business Rule and actually add a member.  The business rule should have one prompt, the member name:

DynamicMembers14

Once we’ve entered a member name and launched the rule, we should be able to see the new member in the Entity dimension:

DynamicMembers15

Now what does this look like in Essbase?  First let’s take a look at it without refreshing Essbase:

Now let’s refresh the database from Planning and see what we get:

DynamicMembers16

The first thing we see is that the new member exists in Essbase now.  So the TBD logic has been converted over to a physical member.  Next we notice that the number of children for our Dynamic Members parent is now 101.  So the refresh process has reset the number of members that we can dynamically add back to 100.  And that’s it…we now have dynamic members working in custom Plan Types.

In the next post on this topic we’ll go into how we actually make this useful.  Because having the users go through and launch a Business Rule as necessary is not exactly user friendly.