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.
  • – 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])

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 “”
POV “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([All Products],10,LEAVES)},
SourceRegion “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([Profit],10,LEAVES),[Exchange Rate]},
CrossJoin({Descendants([All Products],10,LEAVES)},

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:


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

Adding Dynamic Members from a Form in

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

Now that I have my Rapid Deployment completed, I can start to use some of the new features in  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”  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:


Compared to this in Planning:


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, 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:


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


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:


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.

Getting Started With Hyperion Planning and Rapid Deployment (Part 2 of 2)

So you finally get everything working on your shiny new Rapid Deployment and now its time to finally take a look at Planning.  The problem is, we don’t have an app.  The even bigger problem (for me and anyone else used to SQL Server) is that we don’t even have a data source for our application if we had one.  For those of you that are familiar with Oracle 11g, this probably isn’t a problem and you are already using a fresh application that you just created.  If, however, you are still reading…then you might be a little lost as to what to do next.  Before we go through these steps…I’m the furthest thing imaginable from an Oracle DBA, so if anything I’m doing isn’t necessarily “Best Practice”, feel free to leave me a comment on how we could do it better.

Before we can create a Planning application, we of course need our repository.  Since we used a Rapid Deployment, unless we want to install SQL Server, we have to use Oracle 11g as our data source.  We’ll start by opening up Enterprise Manager so that we can create a new user.  Follow these steps to get your user set up:

  1. Navigate to Database Control – admin in the start menu:PlanningRapid03
  2. Once Enterprise Manager has opened in your browser, enter SYSTEM for the username and the password you defined for your Rapid Deployment as the password while leaving Normal selected.PlanningRapid04
  3. Click on Server in the top menu area.PlanningRapid05
  4. Click on Users under the Security section.PlanningRapid06
  5. With the ADMIN user and Create Like action selected, click Go.
  6. Enter Vision for Name, enter a password twice, and click OK.
  7. Verify that the user was created successfully.

Once our database user has been created, we are ready to create our actual Planning sample application.  Follow these steps to set up your sample application:

  1. Log into workspace and click NavigateAdministerPlanning Administration.PlanningRapid10
  2. Click on Manage Data Source and click the add data source plus sign.
  3. Enter localhost for Server1521 for Port, admin for SID, Vision for User, and your password for Password under Application Database.  Enter localhost for Server, admin for User, and your Rapid Deployment password for Password under Essbase Server.  Validate your connections and then click Save.
  4. Click Manage Applications and click the add application plus sign.
  5. Select your Vision data source, enter Vision for Application, select Sample for Application Type and click Next.
  6. Verify the information and click Create.
  7. Wait for the application to be created and populated with meta-data and data.
  8. Enjoy your new sample app rather than creating one from scratch.

So with that, you should be ready to start working with Planning on your Rapid Deployment!

Getting Started With Hyperion Planning and Rapid Deployment (Part 1 of 2)

So just came out and you are thinking to yourself…I bet that’s cool.  Well, there’s only one way to find out.  Set it up!  In this post we’ll go through the absolute quickest way to start playing with in your very own virtual machine.  So what’s the fastest way (not necessarily the best…but I’m impatient)?  Rapid deployment!

Before we get started, let’s talk about what Rapid Deployment is.  Essentially, Oracle has given us a wizard to install all of the required product related to Hyperion Planning on a Windows system with very little required to make it all work.  What do you get?

  • Planning
  • Essbase
  • Calculation Manager
  • Financial Reports
  • Smart View for Office (assuming you have Office 2010 installed on your server instance)
  • Oracle Database 11g
  • Weblogic Server

What don’t you get?

  • EPMA
  • Standalone Essbase Connectivity via Smart View
  • Pre-installed Sample Applications

So now that we know what we’re getting when we are done, let’s get started!  First, we’ll need to get a virtual machine configured.  I’ll leave the steps on this to you, as if you are reading this, there is a good chance you don’t need these steps anyway.  Here are the recommended specifications of your server or virtual server as the case may be:

  • Quad Core CPU
  • 16GB RAM (I find that 12 is likely plenty for those of you with 16GB laptops that still want to be able to actually use them)
  • 200GB Storage (I set my VM up with 60GB and after I’m completely done, I still have nearly 20GB free)

You can use VMWare on your laptop, something cloud-based (like AWS), or in my case VMWare ESXi on my server at home (yes, I’m a nerd, just ask my wife).  Now that we have our hardware ready, we need to install some software:

  • Microsoft Windows Server 2008 R2 (it may work on 2012 since it is now officially supported, but guide says 2008 R2)
  • Microsoft Office 2010
  • Microsoft .net 4.0
  • Firefox (because who uses IE any more anyway?)
  • 7-zip and Notepad++ (so these may not be required, but I always find them handy to have around)

So we finally have our system ready to go, it’s time to start downloading  You can refer to the Rapid Deployment guide from Oracle here:

This guide is actually pretty complete, but there are a few problems.  First, at the time of this blog post, the edelivery site doesn’t actually have  This means thats outside of Oracle Database 11g, you can’t actually get anything you need from edelivery.  Use this link to get to the files you need for now:

Once you go to this link, you will have to spend a bit of time finding all the files needed, but they are all there.  Between these files and your Oracle DB files, you should have all of the following:PlanningRapid01


Unzip all of these files into one directory with no spaces and you should be ready to go.  Beyond this, the guide should get you from start to finish with no major issues.  I will say that I had to run through the Wizard twice, as the first time I clicked back and forth between the command line that opened to install Oracle 11g and the Wizard.  For whatever reason, that seems to have bombed the installation.  So just let it go and don’t click on anything!

If everything went as planned, you should be able to fire up Firefox (or IE if you really want to), and go to your shiny new workspace:

Now we have everything installed and all of our services are started and everything is great.  But wait!  We don’t have a Planning application to play with.  And surely we don’t want to create one from scratch (again…impatient).  Our next post will continue with the impatient theme and get you into a sample application in a hurry.  Having always used SQL Server, there was a bit of a learning curve for me.