In this article we are going to break with one of the key mantras for Oracle EPM Cloud integration:
You don’t have to use Data Management to load your data to the Oracle EPM Cloud
As EPM professionals, we take great pride in designing our Oracle EPM applications and building smart models and Business Rules. The Oracle EPM platform provides a lot of flexibility for customizing your solutions to fit exactly what your users need. But when it comes to data integration, the only option seems to be Data Management (or FDMEE). Whether we are processing GL balances, FX rates, employee related information or sales opportunities, it is often cumbersome to get the data in the system.
Why look for an Alternative to Data Management?
Many customers and consultants would love to have an alternative to using Data Management for integrating their data. Here are some of the reasons:
- Too many configuration steps for simple data loads (Locations, Import Formats, Data Mappings for all dimension, etc.)
- Handling kickouts is not integrated (requires manual steps)
- Metadata loads aren’t straight forward (too many dimensions to map)
- Limited access to 3rd party data sources (how can I load data from ADP, Salesforce, Workday etc.?)
- Transparency (how do I quickly find the relevant information in the cryptic log file)
- Ability to track changes during the project life cycle
- Migration between environments
- Too many 1-to-1 interfaces instead of a centralized architecture for managing data consistently (this prevents your from building a Foundation for Growth)
- Broader automation requires EPM Automate or REST API
In this article you will see how easy it is to load data to your EPM applications using ICE Cloud – WITHOUT Data Management! Instead you will see how we can easily integrate the data using ICE Cloud’s Application Intelligence (AppIntel) module for Oracle EPM.
A note for those of you who love Data Management: ICE Cloud also integrates with Data Management/FDMEE. This allows you to keep your existing data loads, but you can use ICE Cloud to improve your overall automation processes, access data from any source system and take advantage of the many other features that ICE Cloud offers.
Do you want to watch the video instead?
Let’s take a look at a very basic use case
It’s time to roll up our sleeves and get started with a simple integration, just to learn some of the basics first before we are going into more detail. Here is the simple example with our integration requirements: we want to upload data from a text file to a PBCS application and apply some mappings and filters.
- The file is called GL_Balances.txt
- Our PBCS application is called Vision which has a plan type called Plan1.
- We need to map the values from the source file to the dimensions in the plan type.
- There are a few fields which require more specific mappings:
- Period: get the month (format: Mon) from the Period_Name field (format: Mon-yy)
- Year: get the two-digit year with “FY” prefix from the Period_Name field (format: Mon-yy)
- Product: need to add prefix “P_”
- Entity: replace Entity “000” with “NoEntity“, otherwise keep Entity as is
- Amount: the amount values in the file contain commas as a thousand separator. These commas need to be removed.
- Scenario: “Actual“
- Version: “Final“
- HSP View: “BaseData“
- We need to apply a filter on the Ledger field: load only records from “PRIMARY” ledgers
Accessing the Source File
Our data source is a file named GL_Balances.txt which has thousands of rows and looks like this in a text editor (the file is tab delimited and the first row contains the column names):
In order to work with a file in ICE Cloud, we need to synchronize the structure of the file in ICE Cloud. This is as simple as clicking the Sync Metadata button and then ICE Cloud determines the column definitions automatically. Each of these columns represents one column from the GL_Balances file that we are going to load to PBCS:
We can also preview the data from the file in ICE Cloud by clicking the Refresh Data button. If the data is displayed in the Data Preview grid, we can be sure that everything has been configured correctly.
Selecting the Target
We want to load data to the Plan1 plan type in the Vision PBCS application. ICE Cloud’s Application Intelligence (AppIntel) module for Oracle EPM makes connecting to this plan type really easy: it knows which dimension this plan type includes and automatically creates a resource with a dedicated column for each dimension.
Configuring a Load Data Task
All we need for this simple example is a Load Data task for PBCS. Now here is one of the great things about ICE Cloud: there are a lot of steps that need to happen in order to load the data from a file to PBCS, but ICE Cloud does all the heavy lifting for you. With ICE Cloud, our users can just focus on the business logic without having to worry about the technical implementation.
Step 1: Create an Integration with a Transformation task
First, create a new Integration and change its name to Load GL Balances to Plan1. Second, drag and drop a Load Data task from the Task Selector onto the canvas.
Step 2: Define the Target Resource
We want to load data to the Vision PBCS application and choose the Plan1 plan type. Switch to the Configuration tab of the Load Data task and select your Target Application as Plan1 – Vision – PBCS Data and the Target Resource as Plan1.
Step 3: Define the Source Resources
In this case, we only have one data source: the GL Balances file. Choose the Source Application (File – ICE Client) and select the GL_Balances resource. Then enter an alias to reference this resource in our mappings.
Step 4: Apply Mappings and Filters
Switch to the Mapping tab and apply the relevant mappings by dragging the source columns on the correct target columns. There are a few mappings we need to tweak so that they have the correct values. To do so, just click on the target column and edit the expression.
Here is the logic that we need to apply for this PBCS Load Data task:
| PBCS Dimension/ Value | Mapping Expression | Description |
|---|---|---|
| Account | b.ACCOUNT | Drag & drop only |
| Period | Left( b.PERIOD_NAME , 3 ) | Get first 3 characters of Period Name field |
| HSP_View | ‘BaseData’ | Hardcoded |
| Scenario | ‘Actual’ | Hardcoded |
| Year | ‘FY’ + Right( b.PERIOD_NAME , 2 ) | Prepend “FY” to the last two digits of Period name |
| Version | ‘Final’ | Hardcoded |
| Entity | CASE WHEN b.ENTITY = ‘000’ THEN ‘NoEntity’ ELSE b.ENTITY END | Simple condition |
| Product | ‘P_’ + b.PRODUCT | Add dimension prefix |
| Data | Replace( b.AMOUNT , ‘,’ , ” ) | Remove comma from amount value |
Applying a filter is just as simple: click on the Filter icon to the left of any source column and enter the filter expression in the pop-up window.
Step 5: Define the Advanced options for loading data to PBCS
Here is one of the reasons why it is so much more efficient to implement integration process with ICE Cloud: our Application Intelligence (AppIntel) modules simplify a lot of steps that would otherwise be hard if not impossible to implement. With ICE Cloud, on the other hand, you just need to check a box.
Note: for now we are only going to look at two options, but the third one – Perform Data Checks – is incredibly powerful. You can read about it in one of our next articles.
There are three Advanced options for loading data to PBCS:
Let’s take a look what the purpose of these options is:
- Aggregate Data Columns: check this option to summarize all records for the same intersection into one record. This is a common requirement to avoid records from overwriting each other.
Here is an illustration of this feature:
- Suppress Zero Values: check this option to exclude records with zero amounts. This is a common requirement as well as it reduces the number of blocks that need to be created in Essbase.
Here is an illustration of this feature:
- Perform Data Checks: this is a very powerful feature to handle kickouts. We will look at this in a later article to understand how it works in detail.
Running the Process
Let’s see how easy it is to load the data to PBCS. All we need to do is click on the Play ( ) button in the Action Bar and then confirm that we want to run the process.
Note: to keep things as simple as possible, we are not using Variables in this Integration. Variables are very powerful and are definitely considered an important part of our best practices. However, for now we can work around it as we are focusing on the integration aspect of the process. If we had a variable defined, though, we would see a prompt with its default value on the launch screen and could change it as needed.
Viewing the Process logs
After starting the process, we can click on the button Open Process Monitor which shows the data load process as it is running.
Whether the process is still running or has completed, we can view the process logs to see exactly what is going on – and sessions like this one are available for all process that were ever executed with ICE Cloud:
- ICE Cloud provides summary information about the process, like PBCS Job Details, Data Preview, Outputs and Inputs
- By clicking on Details, users can access the step-by-step actions that ICE Cloud is performing in the background to take care of several other steps which are required to load the data, like transforming the data, deleting an existing file from the PBCS inbox (if it exists), uploading the transformed file, running the import job and checking the overall job status.
Note: if you are familiar with Import Data jobs in Oracle EPM Cloud, you are probably aware that Import Data jobs fail if there are any errors. ICE Cloud solves this problem with an intuitive out-of-the-box functionality. You can learn about this great feature in one of our next articles.
Here is a complete PBCS automation process
Conclusion
In this article you’ve seen how easy it is to load data to PBCS using ICE Cloud. While this is just a simple example, the savings in time and effort become absolutely significant when integrations are more complex. In addition, ICE Cloud expands your integration and automation options beyond anything that you’ve seen before in regards to EPM integration.
ICE Cloud is a powerful Finance Transformation platform which simplifies data management for Oracle EPM, but also many other applications. In this example we are only scratching the surface of what ICE Cloud can do.
Learn more about the ICE Cloud Finance Transformation platform or sign up for a Free Proof of Concept to see ICE Cloud in action.
