Introduction

This tutorial shows you how to register applications, define period mappings, define category mappings, and then create and run a file-based data integration. The sections build on each other and should be completed sequentially.

Background

With Data Integration you can perform Data Management integration tasks using an easy-to-navigate interface. You can define file-based and direct integration sources, create mapping rules to translate source data into the required target format, and execute and manage the periodic data loading process. Common integration tasks are done using an easy-to-navigate interface that supports and conforms to how you work.

Data Integration is available for the following Cloud Enterpprise Performance Management (EPM) business processes:

• Planning
• Tax Reporting
• Financial Consolidation and Close (FCC)
• FreeForm
• Account Reconciliation (ARCS)
• Enterprise Profitability and Cost Management

For the Planning, Tax Reporting, Financial Consolidation and Close, and Enterprise Profitability and Cost management, Data Integration is available for the following predefined roles:

• Service Administrator
• Power User
• User

In Account Reconciliation, you can assign the following roles to access Data Integration: Service Administrator, Run Integration, Create Integration.

Note:

In future releases, role definitions may change to provide additional levels of security for different roles.

Data Management is still fully supported and remains available as a menu selection on the Navigator menu. Integration-related activities are bi-directional and synchronized between Data Integration and Data Management. For example, when you map dimensions in Data Integration, any saved mappings are populated automatically in Data Management.

In this tutorial, you complete the tasks needed to define and run file-based data integrations.

Here are the integration tasks in this tutorial:

• Registering Applications
• Mapping Periods
• Mapping Categories (Scenarios)
• Defining Integrations
• Mapping Dimensions and Members
• Setting Integration Options
• Running Integrations

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into your Cloud EPM Enterprise Service instance. Before you can import a tutorial snapshot, you must request another Cloud EPM Enterprise Service instance or remove your current application and business process. The tutorial snapshot will not import over your existing application or business process, nor will it automatically replace or restore the application or business process you are currently working with.

Before starting this tutorial, you must:

• Have Service Administrator access to a Cloud EPM Enterprise Service instance.
• Have the Planning sample application (Vision) created in your instance. In the application, set the navigation flow to Financial Flow - Admin.
• Download and save the following files locally:
• GlobalPeriodMapping.xlsx
• AppPeriodMapping.xlsx
• travelexpenses.txt
• travelmappings.txt

Registering Applications

About Applications and Source Connections

Before defining integrations, you must register source and target applications. When you register an application, select an application category:

• EPM Local—Register your current service application. For example, in Planning, an EPM Local data source is defined during the application creation process. Because it associates the cubes created during this process, you can delete the registered application and create one with the input cubes you want to associate with your integration.
• EPM Cloud—Register another EPM Cloud business process with a remote service to integrate with another EPM Cloud instance business process such as Financial Consolidation and Close or another Planning instance.
• Data Source—Register source data by connecting to Oracle ERP Cloud, Oracle Netsuite, Oracle Human Capital Management Cloud, E-Business Suite, Oracle Peoplesoft, Oracle Projects, an On-Premises Database, an On-Premises File, an Oracle Autonomous Database, or load data using an Incremental File Adapter.
• Data Export—Register this application category when you need to export to a data file.
• Dimensions—Register applications that include only a class of dimensions or a dimension type.

A File-based source system is readily available and can be used to load data into your application using a data load file.

Note:

Learn more about options to register applications when defining integrations in the Registering Application section of the Administering Data Integration for Oracle Enterprise Performance Management Cloud documentation.

When working with Oracle ERP Cloud, Oracle HCM Cloud, and Oracle Netsuite, you must configure a source connection to its service URL.

Note:

Learn more on how to configure source connection in the Configure Source Connections section of the Administering Data Integration for Oracle Enterprise Performance Management Cloud documentation. The Setting Up EPM Planning Financials Budget Revisions with Oracle ERP Cloud Budgetary Control hands-on tutorial also shows you how to register an Oracle ERP Cloud source connection.

Reviewing Registered Source and Target Applications

1. On the home page, click Application, and then Data Exchange.

   

2. In Data Exchange, verify that you are on the Data Integration tab.

   

3. Click Actions, and then select Applications.

   

   
   

   A file-based data source and an EPM Local application are predefined when you first create your application. In this example, the EPM Local application for the current service, as well as a standard file-based source system, were created during the application creation process.

   

4. Select the Vision (EPM Local) row, and then click (Delete).

   Tip:

   Click on a blank space within the row. Do not click the application name.
5. At the confirmation message, click OK.

   

6. At the information message, click OK.

   

   You can optionally view the status in Job Scheduler or wait in Applications until the EPM Local application is deleted.

Registering EPM Local

1. In Applications, click (Add).
2. In Create Application, select the following:
   • Category: EPM Local
   • Application: Vision
   • Cubes: All Input Cubes
3. Verify your selections and click OK.

   

   
   

   The Vision (EPM Local) application is registered.

   

4. Click Vision.
5. In Application Details, review the Data Table Column Name mappings associated with each dimension.

   The Data Table Column Name shows the column name in the staging table, where the imported data is temporarily stored before proceeding with the export to the target.

   

6. In a text editor or viewer, review the contents of the travelmappings.txt file. Verify that the Data Table Column Name associated with HSP_View, Product, and Version in the text file match the ones in the Application Details for Vision. Modify as necessary, save your changes, and close the file.

   

7. In Application Details: Vision, on the top right, click (Return).
8. In Applications, on the top right, click X (Close).

Reviewing and Importing Period Mappings

You can use various kinds of calendars with period granularity based on your business and application requirements. You can define period mappings in three ways:

• Global Mapping
• Application Mapping
• Source Mapping

1. In Data Integration, click Actions, and select Period Mapping.

   

2. In Global Mapping, scroll down until you can see periods for FY22.

   

   
   

   You define a global mapping in cases where you do not have many target applications getting data from multiple source systems with different types of source calendars. Use a global mapping to ensure that various periods are accommodated in the individual mapping.

3. Click Actions, then select Import From Excel.
4. In Select File to Import, click (File Browser).

   

5. In File Browser, double-click inbox.
6. Verify that you are in the inbox folder and click Upload.

7. Locate where you saved GlobalPeriodMapping.xlsx, select it, and click Open.
8. If prompted with a File Status message, click OK.
9. Verify that GlobalPeriodMapping.xlsx is listed in File Browser, select its row, and click OK.

   Tip:

   Click on a blank space within the row. Do not click the file name.
10. In Select File To Import, verify that GlobalPeriodMapping.xlsx is in the File textbox and click OK.

11. When the import process completes, the imported period mappings are displayed. Review the imported periods. Scroll down until you can see periods for FY23.

    

12. On the top right, click Save.
13. Click Application Mapping.

    

    
    

    If you have multiple target applications, getting data from various source systems with complex period types, you can create application mappings in addition to global mappings. When you define an application mapping, you can modify the Target Period Month as necessary.

14. In Application Mapping, for Target Application, verify that Vision is selected.

    

15. Click Actions, then select Import From Excel.
16. In Select File to Import, click (File Browser).

    

17. In File Browser, double-click inbox.
18. Verify that you are in the inbox folder and click Upload.

19. Locate where you saved AppPeriodMapping.xlsx, select it, and click Open.
20. If prompted with a File Status message, click OK.
21. Verify that AppPeriodMapping.xlsx is listed in File Browser, select its row, and click OK.

    

    Tip:

    Click on a blank space within the row. Do not click the file name.
22. In Select File To Import, verify that AppPeriodMapping.xlsx is in the File textbox and click OK.

    

23. When the import process completes, the imported period mappings are displayed. Review the imported periods.: FY22 and FY23.

    

    
    

    

24. On the top right, click Save.
25. Click Source Mapping.

    

    
    

    Source mappings are used to include explicit and adjustment period mappings. You can create explicit period mappings to ensure that the Data Integration periods map correctly to the source system calendar periods. An adjustment period mapping is used only when you select the Include Adjustment Periods option when creating the integrations.

    Source period mappings vary depending on the source system type.

    

26. On the top right, click X (Close) to close the Period Mapping page.

Defining Category Mappings

Depending on the Cloud EPM business process you are working with, you define category mappings for your source and target. For example, in this tutorial, categories map to the Scenario dimension members in Planning.

You can define these types of category mappings:

• Global mappings—lets you define mappings across multiple applications.
• Application mappings—can be defined for a target application.

1. In Data Integration, click Actions and select Category Mapping.

   

2. In Global Mapping, click (Add).

   

   A new row is added to the page.

   

3. In the new row, click in the columns, then enter or select the following:
   • Category: Plan
   • Frequency: Monthly
   • Target Category: Plan
4. Verify your entries and click Save.

   

   
   

   When saved, a category key is assigned to the entry.

   

5. On the top right, click X (Close) to close the Category Mapping page.

Creating File-Based Data Integrations

Defining Integrations

1. In Data Integration, click (Create ), then select Integration..

   

   
   

   The integration workflow is opened with the General page displayed. Navigate between tasks by clicking a task on the workflow bar, or the buttons on the top right.

   

2. For Name, enter TravelExpenses.

   Tip:

   Descriptions are optional.
3. For Location, enter TravelExpenses.

   Tip:

   If you have locations previously defined for another integration, you can select them here. If you select an existing location for your integration, the target application is preselected for you. If you enter a new location, make sure to select a source and target application.
4. For Source, click the down arrow and select File.

   

5. In File Browser, double-click inbox.
6. Verify that you are in the inbox folder and click Upload.
7. Locate and select travelexpenses.txt and click Open.
8. Select the travelexpenses.txt row and click OK.

   

   
   

   The travelexpenses.txt file is now associated with the Source.

   

9. Click File Options.
10. In File Import - File Options, set or verify the following:
    • Type: Delimited - All Data Type
    • Delimiter: Comma
    • Use Header for Column Names: 1

    

11. Click Next.
12. Verify the data to be imported and click Finish.

    

13. For Target, click the down arrow and select Vision.

    

14. For Cube, select Plan1.
15. For Category, select Plan.
16. Expand Location Attributes.
17. For Functional Currency, enter USD.
18. Review your selections.

    

19. On the top-right, click the arrow next to Save and select Save And Close.

    

    
    

    When saved, the integration is listed in Data Integration. The status icon on the left identifies it as a new integration that has not been executed.

    

Modifying Integrations

1. In Data Integration, click TravelExpenses.
2. In Edit Integration: TravelExpenses, review your previous integration selections on the General page.

   

Mapping Dimensions and Members

Mapping Source and Target Dimensions

You map the source dimensions to the target dimensions in the application. In addition, you can define simple transformation rules using expressions.

When mapping dimensions in file-based integrations where the source file has a header record, select source columns for each dimension. Data Integration automatically determines the column number and populates the column number. You can also manually enter the column number.

This task corresponds to defining an Import Format in legacy Data Management.

1. In Edit Integration: TravelExpenses, click Map Dimensions.

   

2. In Map Dimensions, set or verify the following:
   • Import Format: TravelExpenses
   • Type: Delimited All Data Type
   • Delimiter: Comma
3. For each source dimension, click the down arrow and select a dimension to map to the target selection. Map:
   • Account to Account
   • Amount to Amount



Tip:

Multiple source columns of the same dimension can be mapped to target dimensions. You can also use source or target expressions to map dimensions. Aside from the standard expressions, you can use the SQL expression type to assign any SQL expression that can be used in a SQL INSERT statement for a value.
4. Verify your selections and click Save.

   Notice that the Amount dimension in the target has updated to Data.

5. If your previous mappings were removed or have changed, make sure to map the Account and Amount dimensions based on the following example:

   

6. Save your changes before proceeding to the next step.

Mapping Source and Target Members

Mapping members allows you to translate source values to valid members in each target dimension.

• If you did not define target expressions, you must add a mapping for each dimension.
• If you have defined target expressions to derive the target members for a given dimension, then you don’t have to define member mappings.
• If you use conditional target expressions, then you can define member mappings for the remaining source values not covered by the conditions.
• If your data transformations can be achieved using target expression, Oracle recommends using target expressions instead of mapping members. Transformations using target expressions perform significantly better than member mapping for large data sets.

Mappings can be shared across multiple integrations using a parent location. Mappings are processed for each dimension in the default order of Account, Entity, ICP, UD1, UD2, and so on. You can override the mapping order by changing the calculation sequence in Application definition.

1. In Edit Integration: TravelExpenses, click Map Members.

   

2. In Map Members, display the list of dimensions. Click the down arrow next to Dimension.

   

   Note:

   For each dimension listed, you must define a member mapping.
3. For Dimension, select Account.
4. Click (Add).
5. In Add Member Mapping, click the icon next to Source and select Is Like.

   

   Tip:

   In Source, you select a member mapping type before specifying a source value. For descriptions of supported member mapping types, see the Add Member Mappings section of the Administering Data Integration for Oracle Enterprise Performance Management Cloud documentation.
6. In the textbox next to the Source icon, enter *.
7. In the Target textbox, enter *.
8. In the Processing Order textbox, enter Accounts.
9. Verify your selections and click OK.

   

   
   

   Mappings for Account are displayed.

   

10. Import the rest of the mappings. Click Actions, and then select Import, then All Dimensions.

    

11. In Select a file to import, double-click inbox.
12. Verify that you are in the inbox folder and click Upload.
13. Locate and select travelmappings.txt and click Open.
14. Select the travelmappings.txt row and click OK.

    

15. In Select import mode and validation, set the following:
    • Import Mode: Replace
    • Validation: No Validate
    • Execution Mode: Online
16. Verify your selections and click OK.

    

17. For each dimension, click the down arrow next to Dimension, select a dimension, and verify that mappings were added for each dimension.

    Dimension	Source Type	Source Value	Target	Processing Order
    Account	Is Like	*	*	Accounts
    Data	Is Like	*	*	Data
    Entity	Is Like	*	810	Entity
    HSP_View	Is Like	*	BaseData	HSP_View
    Product	Is Like	*	P_000	Product
    Version	Is Like	*	Working	Version

18. Click Save.

Setting Integration Options

Use Options to specify and verify file options, explicit period formats, category, cube, filters for importing data, and options on how to process the data when loading to the target application.

1. In Edit Integration: TravelExpenses, click Options.

   

2. In General Options, review and verify selections.

   

3. In Target Options, for Load Method, select All data types with security.
4. Verify all other Target Options.

   

5. Click the down arrow next to Save and select Save And Close.

   

Running Integrations

Reviewing the Data Load POV

1. On the upper right, click (Home).
2. On the home page, click Financial Plan, and then Expenses.

   

3. From the vertical tabs on the left, click (Allocations).
4. Review the Allocations - HR dashboard. The HR Expenses form is displayed on the left.

   

5. In the grid, expand YearTotal, and then Q1.
6. Locate cell intersections for the January period and the following accounts:
   • 7640: Airfare
   • 7650: Car Rental
   • 7670: Accommodation
   • 7690: Meals

   You will be loading data into these accounts.

   

Running the File-Based Integration

1. Click (Navigator), then under Application, click Data Exchange.

   

2. In Data Integration, for TravelExpenses, click (Run).

   

3. In Run Integration: TravelExpenses, set or verify the following:
   • Import Mode: Replace

     Setting the import mode to Replace clears all the data for the data point-of-view in the Workbench.

   • Export Mode: Merge

     Setting the export mode to Merge overwrites existing data with the data in the load file. If data does not exist, it loads the new data.

   • Start Period: Jan-23

     The End Period is typically updated to match the Start Period.

   • End Period: Jan-23
   • File: travelexpenses.txt
4. Verify your selections and click Run.

   

   The integration execution status message is displayed.

   

   When completed, the Import, Validate, and Export icons change in color. The Status section states that the Integration process completed successfully.

   

   
   

Viewing Results in the Workbench

1. In Executing Integration: TravelExpenses, click Workbench.

   

2. In the Workbench, review the imported data.

   

3. On the top right, click X (Close).
4. In Data Integration, notice that the icon next to TravelExpenses has changed to a green checkmark, indicating that the integration completed successfully.

   

Viewing Data Load Results in a Form

1. On the upper right, click (Home).
2. On the home page, click Financial Plan, and then Expenses.

   

3. If necessary, from the vertical tabs on the left, click (Allocations).
4. In the grid, expand YearTotal, and then Q1.
5. Locate cell intersections for the January period and the following accounts:
   • 7640: Airfare
   • 7650: Car Rental
   • 7670: Accommodation
   • 7690: Meals

   The load results are displayed on the form.

   

Related Links

• Oracle EPM Tutorials YouTube Channel
• Documentation: Administering Data Integration for Oracle Enterprise Performance Management Cloud

---

Title and Copyright Information

Loading Data Using Data Integration

F56135-03

October 2023

Copyright © 2023, Oracle and/or its affiliates. 

This tutorial shows you how to register applications, define period mappings, define category mappings, and then create and run a file-based data integration

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end users are "commercial computer software" or "commercial computer software documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract. The terms governing the U.S. Government's use of Oracle cloud services are defined by the applicable contract for such services. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.