Does your organization use Oracle Planning and Budgeting Cloud Service (PBCS) or a related Oracle Enterprise Performance Management (EPM) Cloud tool to import data from other source systems? Does that data require mapping in Data Management because not all the source system values align with the metadata in your Oracle EPM application?
If you answered yes to all of the above, or have interest in any of the above, then this is the blog post for you! We will show you a method for downloading your existing mappings en masse, modifying them as needed, and then re-importing them into your integration. Save time by no longer having to key in mappings one-at-a-time!
Let’s go through an example where you are a PBCS administrator looking to load a new data file. You know that the source environment has recently added new account values, but also that you do not want to add any new members to PBCS. The best way to load the data in this case would be to take advantage of Data Management’s mapping capabilities and map the new source values represented on the file to the existing PBCS target members.
Begin by going to PBCS then Navigator then Data Management:
To access existing mappings, go to Workflow then Data Load Mapping. Select POV of interest, for example:
Updating Mappings Manually
At this point, the mappings could be updated manually through the web interface. For example, if you want to change the explicit mappings, simply go into the “Explicit” tab and modify as needed:
Say you needed to modify the highlighted section above. In our example, let us say that the source system values changed, but they are still mapping to the same PBCS revenue accounts on the right.
Through the manual method – you could update the Source Value accounts individually one at a time, for instance:
But through this example – you can see it can take a lot of time if you are making edits to multiple mappings one-by-one like this.
Let’s look at a more efficient method – exporting your mappings, modifying the export as needed within the context of Excel, and then re-importing.
Exporting the Mapping File
For the dimension of interest (in this case, Account) – there are multiple methods of exporting the mapping file. But in this example, we will look at exporting “All Dimensions”:
After clicking that, we are prompted to a repository, if you go to “inbox” you will see the “All Dimensions” file:
Download the “All Dimensions” file
Next, open the file in Excel. This is a Comma-Separated-Value (CSV) file that can be easily worked with in Excel.
Modifying the Mapping File
Once opened in Excel, the file you are working with should look something like this:
Column A - implies the dimension type for which you are doing the mapping (e.g., “ACCOUNT”, “UD1” (“User Defined 1”), etc.
Column B - implies source values coming in from file
Column C - implies target dimension members to which you are mapping
Column D - implies mapping rule name (if applicable)
Column E - implies mapping description (if applicable)
Considering this file format, we could modify all the mappings for each of the app dimensions if we wanted to.
Alternatively, we could preserve the formatting, and just modify the mappings we want. Let’s proceed in this way and look at the specific mappings that we originally intended to change earlier, deleting mapping rows that we don’t need:
Unlike the manual update method discussed earlier, here we can take advantage of Excel auto-functions to update:
And we are very quickly able to update the mappings, you could foresee the advantage of using this method if you had many mappings:
Next, save the file as a .CSV. You could give it a unique filename. Considering that we are looking to update select mappings only, it is recommended to leave all other mappings intact, unless there are other mappings you would want to delete.
Note1: You could also repeat the above steps for future iterations of mapping changes using similarly-formatted mapping files.
Note2: You can also see the advantage of using this method to add mappings! Simply add them to your file as needed.
Importing the Mapping File
Next, import the file using Data Management (“Data Load Mapping” screen then “Import” then “All Dimensions”)
Upload the mapping file you just developed/updated:
Lastly, click “OK” to import:
Make selections for import. For example, we’d like to “Replace” the existing mappings. Since we likely do not need to validate as we are preserving the system-defined file format, we’d like to execute “Online”:
After a successful import, we will see our updates reflected in the system:
Though we only dealt with a handful of accounts in this example, you can see how advantageous modification of mappings via Excel can be if you had many mappings to modify, or if you wanted to add multiple mappings. We hope this method can save you time in the future!
Note3: The import of Data Load Mappings can be done via automation as well, through the EPM Automate commands “uploadfile” and “importMapping”.
Posted on Fri, September 7, 2018
by Greg Olma