Foreign Exchange/FX rates are critical for FCCS or Planning and other EPM applications of any organization that manages data in multiple currencies. Users can enter the FX rates through a form created in these EPM applications but unfortunately, automating this process is not an easy task. This blog will cover detailed steps on how can automate FX rates load into EPM applications through Data Management/Data Exchange.
Before jumping into the process of how to automate the FX rates load, let’s understand how most organizations manage Exchange Rates.
Typically organizations maintain Exchange rates in their ERP application. Daily conversion rates of currencies from all around the world are sourced using different services and these rates are used to provide Average (AVG) and End of Month (EOM) rates for EPM applications.
So How do most EPM administrators load FX rates?
Well, it depends on what their process is but the short answer is, manually, every month.
Let’s get started
Now, this blog covers the FX rates load automation for the below scenario:
- Source System: EBS on Premise DB
- Target System: Planning Cloud application
In case you have rates coming in as a file from the source, you can create a file-based integration that’ll be much simpler to load the rates. My source data looks like this that exists in the EBS on-premise database so I had to write a query to fetch the below data and load it into EPM.
The first step obviously is to create a source application in Data Exchange.
Navigate to Data Exchange in EPM Cloud and go to Applications. Create a new application as below. In my case, my source is on-premise DB. I created the file below that needs to be uploaded before you create an application.
FXRate.csv
The above fields exist in my source DB that I want to extract and load in my Planning application.
After the application is created, go to the Options tab and provide the connection and query details.
After the source application is created, right-click the application click on Edit Option, and provide the period parameter that will be passed dynamically.
Create the Target Application now as EPM local. In my case, I’m loading rates to a separate cube in Planning where my other data is also sitting.
Ensure Period and Category Mapping are already in place and Navigate to Actions->Query to create a query that will extract the data from the EBS on-premise DB. I’m using the below query to extract the data. Now some conditions apply before the data extracts.
In my source file, I have a field called CONVERSION_DATE that stores the rates for each day of the month and another field called CREATION_DATE that shows the last day of the previous month. I excluded all the currencies except MXN and CAD for which I needed the data. This query can be modified as per what data is required for the rates.
The final step is to create the integration and load the rates.
The cube has the below dimensions in Planning. Map all the dimensions to a single member apart from the ones coming from the source.
Once Dimensions are mapped, you can map members to a single member based on the source data. In my case, everything is mapped to No member apart from Account where it is mapped as below.
Account Mapping
The Currency mapping will automatically be taken through the Currency dimension in the cube.
Specify the period parameter to dynamically input the period value when you run the integration.
Now we’re all set to run our integration, load the data and verify your FX rates in the application.
Check out the other blog on how to load FX Rates for Oracle FCC.
To get the detailed overview on the process of loading FX Rates for EPM Cloud, visit the official documentation here.
Leave a Reply