Exporting EPM Planning Data to a File

For this blog we will be covering a common use case of exporting data from an EPM application to a file, for upload to a third-party system. Luckily for us, it is possible to export data from EPM Planning using the “Data Export to File” functionality in Data Integration. Here I will detail how to set this up, and some key settings to look out for.

First, we need to setup a new Application, using the Action menu in Data Integration, and select Category Data Export and type Data Export to File.

 

Next, upload a text file containing the required column headings and save. For example, I have created a file named Generic_Export.txt with 11 comma delimited columns, as below:

 

This will create an application with the name of the file, which will be our target for the integration, containing all the columns listed in the file.

Note: columns can be added manually later if needed!

Now we can setup the integration, with our Planning application as the Source and the file format Generic_Export as the Target. In the Map Dimensions area, select which Planning dimension should map to which file column for the export.

Note: make sure to set the most appropriate delimiter here – if any of your member names contain commas, then be sure to select another delimiter, e.g. Pipe, rather than Comma.

Within the Options, we can set the Filters that will be applied to the export. Here we can use substitution variables and relative functions to dynamically pick up the relevant data intersection for the export.

Then there are some additional Options to be set. The following options are worth noting:

• Source Cube – if you have multiple plan types, ensure the relevant plan type has been selected
• Data Extract Option – this will depend on what you are exporting, the options here are; “All Data”, “Stored Data Only” (recommended) or “Stored and Dynamic Calculated Data (Dense Only)”
• Data Number of Decimal – for example if there is a preference to export only 2 decimals
• Download File Name – this will be the name of the file produced in the inbox/outbox so make sure it is unique
• End of Line Character – Windows or Linux
• Accumulate Data – for example if mappings are applied, should the data aggregate (Yes) or export all individual lines (No)
• Pivot Dimension – if left blank there will be 1 single data column in the file, if not the specified dimension will be across the columns e.g. Period

Note: These are the only formatting options available and are quite limited, so typically the file may require some manual file manipulation before it can be uploaded to a third-party system.

Once setup, the export can be run from the Data Integration list. Select the import and export mode, along with the start and end period. Export mode “Replace” will overwrite any existing file.

Note: Unfortunately, it is documented by Oracle that you can only export one year at a time – if you try to run the export over multiple years, the data will duplicate.

The file can then be found in the inbox/outbox, from within Application Overview, using the Actions menu. From here you can download the file – it’s as easy as that!