Loading Data Via Data Management to Currency Enabled EPM Application

Currency Functionality for Hyperion Planning Applications

I have built many Oracle (Hyperion) Planning applications, both on premise and cloud (PBCS/ePBCS), over my career. Some with no currency and some with currency. Typically I’ll work with a custom currency solution and build my own currency calculation using a single currency dimension. If I ever wanted to load data to the currency members directly using Data Management, no problem, it is just another generic dimension and typically loading to either LC (Local Currency) and the reporting currency, USD. But in my case, we had to load directly to local currencies members such as USD, CAD, EUR, GBP, etc. and then the reporting currency member, which was called USD_Reporting.  

Why would I load to a local currency member directly you might ask? The application was setup to use the single currency out of the box solution (more on that below). With the Planning out of the box solution, it did not create a LC member. I was thinking, why? Typically, as stated above, you load to a LC currency member and the Entity has the currency assigned to it. I didn’t have much time so said, let’s just work with what I got. In this case, the source system had local currency member for each row of data (the currency was identified in the export) and reporting currency was already converted as well. To avoid any possible data discrepancies with currency calculation for Actuals, just made sense not to re-convert reporting currency data.  

So what happens if you decide to use the out of the box currency functionality? In my case, I did not decide it, it was decided for me. I was working on a project where the application was already built, and they needed my help with additional functionality. Sure, no problem. Noticed that the single currency dimension (not the two-dimension approach) out of the box functionality had been enabled. I had not used it yet, so I poked around.  

Each Entity requires a currency assignment. Makes sense but what I thought was cool was that the available list of currencies was directly pulled from the currency dimension members.  

So, I went to the currency dimension and selected to add a new currency. It had been awhile and forgot the great list of features available: standard currencies, their symbols, ability to mark the currency as a reporting currency, and then other defaults settings.  

On the reporting currency selection, the functionality allows you to have more than one reporting currencies. Since the out of the box currency functionality was enabled, we can now use Planning to create the currency calculation business rule. The system created calculation can do the triangulation as well. Good news, easy to setup, and supported by Oracle if any issues. Performance was fine on this application but if any tuning would be needed, it is just a Calc Manager business rule that can be modified as needed. 

So back to my original point on loading data to a currency dimension member for an application that has been setup to use the out of the box currency dimension member. What is the big deal? Well, I set it up normally with Currency being a dimension I map to. I setup the Import Format, Location, created the mapping, and finally the Load Rule. Ran the process and it failed. Why? Nothing special here. I looked at the Data Management process log and the error given was “The member [NONE] does not exist for the specified cube or you do not have access to it”. What the heck does that mean? So after some digging around, using everybody’s friend Google, and some testing, I figured it out.  

First thought though, what happens if I set the currency in the Location and replace [NONE] with the currency, i.e. USD, CAD, EUR, etc.  

Sure, that works but guess what, that is one currency at a time. And that would work fine for the Reporting Currency but not if I have every currency member in every row of data. 

So what I had to do was use the target Planning application to make this work. This is the default Planning application that is based on what you created in Planning.   

First, I updated the Currency dimension Target Dimension Class to Currency. 

I then created two (2) LOOKUP Target Dimension Class and assigned the next sequential UD.  

First one I called it CurrencyMap for two reasons, one because of the SQL logic that I will show later and two, because I have to do some mapping of the source system currency member names. 

Second one I called it CurrencyUpd and this will be used for the SQL logic that I will show later.  

Created the Location and set the Functional Currency to [NONE] 

Then for the mapping related to the CurrencyMap LOOKUP, like I said, I had the source data provide me with the Source Values but needed to load to valid members, so the need to map. If no mapping would have been required, only the final row, the Like map, would have been needed. 

For the CurrencyUpd, created a Like map with the #SQL to leverage the SQL. 

If you select the pencil (edit) icon, you can then type in this script. The important piece of the code is the “CURKEY” call out which represents the Currency dimension member field. The UD14 was from the CurrencyMap mapping but if you add a “X” to it, it will use the target value of the mapping. 

Once all the other normal setup and mapping is complete, create your Load Rule normally and run the load.  

This is a snippet from the Workbench, but the Source Currency was mapped to the valid currency member but most importantly, for each row of data, we have the ability to load to a different currency.  

Curious on what Loading Data via Data Management may look like for your organization? Contact [email protected] to connect with one of our EPM Cloud experts. Follow Elire on LinkedIn and Twitter to stay up to date on all things Oracle Cloud. Make sure to subscribe to Elire’s Monthly Cloud Newsletter for consistent and reliable Oracle Cloud insights.

SME Author