Introduction

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

Background

In Oracle Sales Planning, you can use Data Integration to copy or move data 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.

For Sales Planning, Data Integration is available for the following predefined roles:

• Service Administrator
• Power User
• User

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:

• 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. The instance should not have a business process created.
• If you haven't already, register for a free Oracle Cloud Customer Connect account so you can access the required files.
  • Download the latest Sales Planning Sample Application snapshot from this idea page.
  • Upload and import the latest Sales Planning Sample Application snapshot into your Planning instance.
• Save this data file and this data map locally.
• Download this snapshot to your local folder. This snapshot includes the Verify QTP Data Load form used in this tutorial.

Migrating Data Forms

In this section, you import a data form using Lifecycle Management.

Importing the Verify QTP Data Load Form

1. On the home page, click Tools, and then Migration.



Lifecycle Management is displayed.



2. In Migration, click Snapshots.



3. Click Upload.



4. In the Upload dialog box, click Choose File to select the file.
   • Locate and select the Verify QTP Data Load Form.
   • Click Open.

   Note:

   Depending on your browser, the button label may display Choose File or Browse.



5. Click Upload.



6. When a message stating that the File uploaded and processed successfully is displayed, click OK.



The snapshot is now listed on the page.

7. For Verify QTP Data Load Form, click(Actions), and then select Import.

   

8. When prompted to proceed with the import, click OK.



9. In Migration Status Report, click Refresh until the status displays Completed.



10. Click Close.

Reviewing the Data Load Point of View of the Form

1. Click (Navigator), and under Create and Manage, click Forms.

   

2. In the content area of Form and Ad Hoc Grid Management, select Verify QTP Data Load and click (Edit) to review the form.

   

   The form is opened with Properties displayed.

3. Click the Layout tab.



4. In Point of View, review the member selections and confirm that the Year member selection is set to FY24.

   

5. On the bottom right, click Finish to close the form.



6. Return to the home page. Click (home).
7. Click Data.

   

8. Click Verify QTP Data Load.

   

   You will be loading data to the members in the data point-of-view displayed on this form.

   

Registering Applications

Before defining integrations, you must register source and target applications. A file-based source system is readily available and can be used to load data into your application using a data load file.

Reviewing Source and Target Applications

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

   

2. In Data Exchange, verify that you in Data Integration.

   

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.

   

Registering EPM Local

1. In Applications, click (Add).



2. In Create Application, select the following:
   • Category: EPM Local
   • Application: SalesPln
   • Cubes: Input Cubes - OEP_QTP
3. Verify your selections and click OK.

   

   The SalesPln-OEP_QTP (EPM Local) application is registered.

   

4. Click SalesPln-OEP_QTP.
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 Application Details: SalesPln-OEP_QTP, click (Return).
7. In Applications, click X (Close).

Reviewing 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, click (Filter).
3. In Target Year, enter FY24.
4. Review the Global Mappings for FY24.



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.

5. On the top right, click X (Close).

Defining Category Mappings

Depending on the Cloud EPM business process you are working with, you define category mappings for your source and target.

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: OEP_Actual
   • Frequency: Monthly
   • Target Category: OEP_Actual
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 and Location, enter SPCOrders.

   Tip:

   Descriptions are optional.

   

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.
3. For Source, click the down arrow and select File.

   

4. In File Browser, click the inbox down arrow and select Create Folder.








5. For folder name, enter SalesPln.



6. Verify that you are in the SalesPln folder and click Upload.

   

7. Locate and select ActualBookings2024.csv and click Open.
8. Select the ActualBookings2024.csv row and click OK.



The ActualBookings2024.csv 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 SalesPln-OEP_QTP.

    

14. For Category, select OEP_Actual.

    

15. Expand Location Attributes.
16. For Functional Currency, enter USD.
17. Review your selections.

    

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

    

Modifying Integrations

1. In Data Integration, click SPCOrders.



2. In Edit Integration: SPCOrders, 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.

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

   

2. In Map Dimensions, set or verify the following:
   • Import Format: SPCOrders
   • Type: Delimited - All Data Type
   • Delimiter: Comma

   

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

   

4. Re-open the Integration, click SPCOrders.



5. In Edit Integration: SPCOrders, click Map Dimensions.

   

6. For each source dimension, click the down arrow and select a dimension to map to the target selection. Map:
   • Data to Data
   • Measures to Measures
   • Product to Product
   • Sales Channel to Sales Channel
   • Territory to Territory
   • Version to Version



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.
7. Verify your selections and Save your changes before proceeding to the next step.

   Account dimension mappings will be done in 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: SPCOrders, click Map Members.

   

2. For Dimension, select Accounts.

   

   Note:

   For each dimension listed, you must define a member mapping.
3. Click (Add).
4. In Add Member Mapping, click the == (Explicit) 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.
5. In the textbox next to the Source icon, enter *.
6. In the Target textbox, enter All Channel Accounts.
7. In the Processing Order textbox, enter Accounts.
8. Verify your selections and click OK.

   

   Mappings for Account are displayed.

   

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

   

10. In Select a file to import, double-click inbox and select SalesPln folder.
11. Verify that you are in the SalesPln folder and click Upload.

    

12. Locate and select spcmaps2024.txt and click Open.
13. Select the spcmaps2024.txt row and click OK.

    

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

    

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

    

    
    

    

    
    

    

    
    

    

    
    

    

    
    

    

17. 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: SPCOrders, 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

Running the File-Based Integration

1. In Data Integration, for SPCOrders, click (Run).

   

2. In Run Integration: SPCOrders, 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-24
   • End Period: Jun-24
   • File: ActualBookings2024.csv
3. 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: SPCOrders, click Workbench.

   

2. In the Workbench, review the imported data.

   

3. On the top right, click X (Close).

In Data Integration, notice that the icon next to SPCOrders has changed to a green checkmark, indicating that the integration completed successfully.

Viewing Data Load Results in a Form

1. Return to the home page. Click (home).
2. Click Data.

   

3. Click Verify QTP Data Load.

   

   The data you loaded using Data Integration is now displayed on the form.

   

Before you can start planning, make sure to run the required rules to process and aggregate the loaded historical data.

Related Links

• Documentation and related videos
• Oracle EPM Tutorials YouTube Channel

---

Title and Copyright Information

Loading Data into Sales Planning using Data Integration

G11141-01

July 2024

Copyright ©2024,

Oracle and/or its affiliates.