Creating a Data Export File Integration

Data Export to File enables you to export data from an Oracle Fusion Cloud Enterprise Performance Management application to a data file. You can use the data file to load data into an Oracle ERP Cloud application or an external system.

When creating a data export to file application, note the following:

  • When dimensions are created, the order of the column in the data file is used to assign the column order. The first column in file is assigned ACCOUNT data column.

  • It is recommended that you export only a year at a time. If date ranges cross a year boundary, data may be duplicated.

  • The POV category is not validated.

  • Check Rules can be created from a data export file. Check rules cannot be based on target values.

  • Write-back is not applicable from data export files.

  • Only numeric data types are supported. If you need to export text, SmartList, and dates, use an EPM Data File application with the type: "EPM Data File," and the data extract option "Level 0 Data." Use this application as the target when creating your integration.

    You can also use the groovy public class DataExporter. For more information, see Class DataExporter.

  • The default Workflow Mode setting is Simple for Data Export to File. When you are exporting data from the Cloud EPM, there is no drill support so the default workflow mode is set to Simple.

    If you want to see the data in Workbench for testing purpose set the Workflow Mode to Full. For more information, see Defining Application Detail Options.

  • To add a timestamp, create a new export column in the target application, then create a SQL Like Mapping for the next column and in the script just type CURRENT_TIMESTAMP.

    Note this method uses a full time stamp, such as 21-JAN-22 04.14.33.677306 PM +00:00, which is based on the server time and not necessarily the local time.

    To format the timestamp, use the TO_CHAR syntax. For more information, see TO_CHAR (datetime).

  • Target values are not validated for data export to file applications.

  • When the integration is executed, Data Integration exports the data. Data Integration creates an output data file. The name of the data file is <Target App Name>_<Process ID>.dat, and it is written to the <APPL ROOT FOLDER>/outbox directory. You can access the data file from the Process Details page from the OUTPUT file column.

  • Using the Download File Name field in Data Export Target Application options, you can also specify your own file name when generating a data export output file as part of a data export to file integration definition, for example, PlanExport.csv.

    The system creates the output file in the platform inbox/outbox folder. The inbox/outbox folder is accessible by navigating to the Application card from the Home page, then Overview, and then from the Actions drop-down, selecting Inbox/Outbox Explorer.

    You can also create the file under Data Integration folders by prefixing the folder to the file name, for example inbox/PlanExport.csv or outbox/PlanExport.csv.

    Note:

    Sub-folders under the inbox are supported, for example, inbox/userx/PlanExport.csv. You cannot create sub-folders under the outbox.

    If the Download File Name field is left blank, the output file is created in the outbox folder with the name <Application Name>_<Process ID>.dat.

  • If you have a data export job scheduled on a daily basis and need to have the output file distributed to an individual automatically, use the Download Application Snapshot in REST API for Enterprise Performance Management Cloud to download a particular file, provide the path to that file as the value of applicationSnapshotName. For example, to download a Data Integration file called s112.csv in the inbox, refer to the file as "inbox\s112.csv" as the path parameter.

To create a Data Export File integration definition:

  1. Create a .CSV file with the list of columns in the order that you want exported.

    Note:

    Do not include the Amount column in the data file. If it's included, you can delete it after the application is created.

    The name of the file is the name of the application so name the file appropriately.

  2. From the Data Integration home page, and then from the Actions menu, then from the Setup actions drop-down, select Applications .
  3. On the Applications page, click Image shows the Add icon..
  4. On the Create Application page, and then Category, select Data Export.
    Image shows the Create Applications page.
  5. From Type, select Data Export to File.
  6. From File, select the name of the source file from which to create the application.

    You can also click Image shows the Search icon. and search for a file from the File Browser page.

  7. In Prefix, specify a prefix to make the application name unique.
    The prefix is concatenated with the file name to form a unique application name. For example, if you want to name an application with the same name as an existing one, you can assign your initials as the prefix.
  8. Click OK.

    The system registers the application.

  9. From the Application page, click Image shows Select icon. to the right of the data target application, and then select Application Details.
  10. Select the Dimensions tab.
  11. In Application Details, select the Dimension Details tab.
  12. Edit the Dimension Name and Data Column Name as needed.
  13. In Sequence, specify the order in which the maps are processed.

    For example, when Account is set to 1, Product is set to 2, and Entity is set to 3, then Data Integration first processes the mapping for Account dimension, followed by Product, and then by Entity.

  14. In Column Order, specify the order of each column in the data export file.
    By default, Data Integration assigns the "Account" dimension as the first column in the order.
  15. Click Save.
  16. In Application Details, select the Options tab.
  17. Select any applicable properties and values for the data export file.
    For more information, see Data Export Target Application Options.
  18. Click Save.
  19. From the Data Integration home page, click Image shows the Add icon. (Create), and then select Integration.

    The General page is displayed in Create Integration view.

  20. In Name and Description, enter a name and description for the new direct integration.
  21. In Location, enter a new location name, or pick an existing location to specify where to load data.

    A location is used to link a Source and Target along with the associated member mappings. You can define multiple integrations within a same location with different set of Options and Filter criteria. The location is primarily used to control access to an integration. You can use Location Security to control access to end users.

    If you enter a new location, you must provide the Source and Target. When you save the integration, Data Integration creates the location automatically.

    If you select an existing location, Data Integration populates the Source and Target values automatically and you cannot change them.

  22. Click Image shows the Source icon. (Select a Source).
  23. From the Select a Source page, select the direct integration source system.
  24. Click Image shows the Target icon. (Select a Target).
  25. From the Select a Target page, select the data export file target application.
  26. Click Save.
  27. Select Map Dimensions and map source dimension members to their corresponding target application dimension members.

    For more information, see Mapping Dimensions.

  28. Run the integration to extract the data from the source system and load it to the data export file target application.
    For more information, see Running an Integration.