In this blog we will walk you through, step-by-step:
- the process of creating a connection between your Fusion ERP and your Cloud EPM
- the key specifications that a metadata report must have to work correctly in OBI Publisher
- configuring EPM to pick up that report through the cloud and pulling it into workbench
Prerequisites
- Cloud EPM Administrator account, to create integrations in Cloud EPM
- A service account for the integration with General Accountant access in Fusion ERP.
- OBI Publisher experience or access to someone who does! (I’ve provided a report specification below. Any good ERP consultant will be able to create a base report for you from these specs!)
Step 1 – Creating the connection between Fusion ERP and Cloud EPM
This step basically tells Cloud EPM which credentials to use and which link to use when connecting to your particular Fusion ERP Cloud environment.
Log in to Cloud EPM and access Data Management.
Select Setup tab and select Source System
Select Add and enter a name for your Source System, and select Oracle ERP Cloud as the source type.
Select Save.
Select your created connection and select Configure Source Connection
Enter the Fusion ERP service account username and password
Enter your Fusion ERP URL – whenever you log in to Fusion, you can copy this from your URL bar. You only need up to oraclecloud.com as my redacted screenshot shows.
Select Test Connection to test, then Configure to save.
You’ve officially created your source connection! From now on, whenever you are asked for an ERP Connection, you enter the Source System Name you chose (FusionERP above) and Data Management will know to connect to that environment.
Step 2 – Create a OBI Publisher report containing parent-child metadata details.
At this point you will need your ERP consultant or administrator to create you a data model and report template within OBI Publisher. We typically create one report per dimension but you could use one report for all dimensions if you chose. This report will need to query the flexfields within Fusion ERP.
The file should contain these headings:
XX_PARAM1|Parent|Child|Description|ParentFlag|ActiveFlag
- The Description column should include the member code and should be capped at 80 characters.
- The ParentFlag column (Y/N) describes whether the member is a parent or not
- The ActiveFlag column (Y/N) describes whether the member is enabled or disabled in the ERP
- You also need a string bind parameter which can be referenced when running the report – this can be anything (e.g. abc) but is a requirement for connection. It doesn’t need to be refenced in the SQL at all.
- The associated report must be set to run as CSV by default. We tend to use the pipe character as the column delimiter because sometimes descriptions contain a comma.
How to set default run type to CSV:
If you’re interested in getting some additional help building this – speak to Brovanture – we have reams of experience setting these integrations up!
(that’s the last plug I promise…back to the tutorial!)
Part 3 – Getting Data Management to run the OBI Publisher report on-demand
This is the clever bit – thanks to Oracle we have a lovely working connector which allows you to initiate a data load on Data Management, and the report will be run on Fusion ERP and returned via the cloud to your workbench in Cloud EPM.
In this example I’ll be setting up a dimension called Region. The steps are exactly the same for any number of reports.
Firstly, run your OBI Publisher report and download the result. This should be a CSV file containing all the columns that exist in your report. It’s fine if you also have the data in this file. Make sure the file is named what you want the target application to be called!
Select Target Application – then Add a Data Source
Select Oracle ERP Cloud (Custom)
Upload the CoA_Region.csv file you downloaded earlier, and press OK
Press Save and your Target Application should be created.
Check the columns have come through correctly as below, and then press the Application Filters tab.
In the Application Filters, enter the Connection Name you set in Step 1.
In the Report Name, put the full address within OBI Publisher of your metadata report. Make sure you end the address with .xdo and point it at the report not the data model.
Enter any string for your parameter.
Remember to Save!
If you haven’t done any automatic metadata imports using Data Management before, you’ll need to create a Dimension target application too. Fortunately this is really easy.
Select Add then Dimensions, and then press OK
This will create a load of dimension target applications called Planning – Account, etc. You only need one for this integration for all dimensions, so delete the rest. Within Planning – Account, ensure you check on the Data Storage member property.
Next, create an import format. Select Import Format then Add.
Enter all key fields as shown in the screenshot below.
- Name and Description are up to your preference
- Source: your OBI Publisher report target application
- Target: Planning – Account
- File Type: Delimited – All Data Type
- File Delimiter: Pipe
Remember to hit Save!
The above sets up an import format to load from a OBI Publisher Report to a Planning dimension, using a pipe-delimited file containing non-numeric data (member names)
Once it’s saved, you’ll need to match up your source columns to your target columns.
- Child: this is your member name – matches up to Account.
- Description: this is your longer description – matches up to Alias: Default.
- ActiveFlag: this can be used to not add end-dated members – matches up to Data.
- ParentFlag: Level-0 flag, can be useful to determine stored or dynamic – matches up to Data Storage.
- Parent: this is the parent of your member – matches up to Parent.
- Remember to Save!
Next, add a Location. Press Add, then select the Import Format you just created. Give this location a sensible name, then hit Save.
Once you’ve created your Location, select Workflow and select Data Load Rule. Select the POV at the bottom and change your POV to your newly created Location.
Select Add to create a new Data Load Rule. Give it a Name, and point it at your recently created Import Format. Within Settings, ensure you enter the name of the dimension you are loading to.
Save the rule, and it’s finally time to test the integration!
Select Workbench and press Import. If you’ve followed these instructions perfectly, you should see the results of your hard work appearing in the workbench!
It will probably error out at the Validate stage as my screenshot shows, you will need to go into Data Load mappings and apply usual logic in there to map your Fusion ERP segment values to your Cloud EPM dimension members. Hopefully, most of your maps are just simple Like * to * mappings!
Everyone’s setup will be different, so I’ll leave you to puzzle out the mappings you need – but you now have a direct link between Fusion ERP and Cloud EPM for this dimension – you will need to repeat for any other dimensions you’re interested in. These rules can be automated using a simple EPM Automate script – see some of my other blogs for hints, tips, and even some sample code to get you started in the EPM Automate world!
Until next time,
Mike
