Loading Data in Planning

Before You Begin

This 60-minute tutorial shows you how to import and export data in Planning, including importing exchange rates into multicurrency business processes.

Background

Planning allows you to import and export plan data. You can load data into Planning using these options:

• Data Integration and Management, for complex data mappings
• REST APIs
• EPM Automate
• Migrations, using snapshots that include Essbase data
• Import Data and Export Data in the Application cluster of Planning

This tutorial shows you how to load data using Import Data and Export Data in the Application cluster of Planning.

Importing data requires a file formatted using supported delimiters. If you need a sample, refer to the import file included in this tutorial or run the export process and use that as a template.

For a list of supported delimiter characters and exceptions, see the Other Supported Delimiter Characters topic in the Administering Planning documentation.

You export data into a flat file. You can use the exported file:

• When you need to load data to a migrated or new Planning business process
• As a backup of your Planning data
• As a template for importing data into multiple dimensions

What Do You Need?

An EPM Cloud Service instance allows you to deploy and use one of the supported business processes. To deploy another business process, you must request another EPM Enterprise Cloud Service instance or remove the current business process.

1. For the loading data sections of this tutorial:
   • Have Service Administrator access to Planning for EPM Enterprise Cloud Service or Legacy Subscription.
   • Depending on the EPM Cloud service that you purchased, upload and import one of the following snapshots into your Planning instance:
     • Legacy
     • EPM Enterprise Cloud Service
   • Upload and import this snapshot into your Planning business process. This snapshot includes the Operating Expenses - Overhead Depts FY20 form.
   • Download this data import file and save it locally.
2. For the importing exchange rate sections of this tutorial:
   • Have Service Administrator access to Planning for Legacy Subscriptions, EPM Standard Cloud Service, or EPM Enterprise Cloud Service.
   • Remove the Planning business process used in the loading data sections of this tutorial.
   • Depending on the EPM Cloud service that you purchased, upload and import one of the following snapshots into your Planning instance:
     • Legacy
     • EPM Standard Cloud Service
     • EPM Enterprise Cloud Service

     The snapshot includes a multicurrency Planning business process.

   • Download this exchange rates import file and save it locally.

For more information on uploading and importing migration snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Reviewing the Data Point-of-View

Before importing data, review the data point-of-view (POV) in the form and the contents of the import file used in this tutorial.

Reviewing the Imported Form

1. Locate the form you imported using the provided migrated snapshot. From the Planning home page, click Data.

   

   The Data Entry page is displayed.

   

2. Expand Plan.
3. Locate and click Operating Expenses - Overhead Depts FY20.

   The import file used in this tutorial imports data into this form's point-of-view.

   

4. Review and take note of the data point-of-view on the form.
5. Click Close.

Reviewing the Import File

Make sure you downloaded a local copy of the import file mentioned in the What Do You Need section of this tutorial.

1. Open the comma-separated values file.
2. Do not make any changes.
3. Review the contents of the file.

   

4. Close the file.

Importing Data

In this section, you validate the import data file and run the import data process in Planning.

Validating Import Data Files

1. From the Planning home page, click Application.
2. Click Overview.

   

3. On the Application page, click Actions.

   

4. Select Import Data.
5. On the Import Data page, click Create.

   You select or enter the required file information on the Import Data page.

   

6. Accept the default selection, Local, as the Location.

   If you select Local, you can browse for a data file stored on your local folder. If you select Inbox, you must specify the exact name of the data file stored on the server.

7. Accept the default selection, Planning, as the Source Type.

   Source types depend on how your data file is structured.

8. Accept the default selection, Comma delimited, as the File Type.

   For a list of supported delimiter characters and exceptions, see the Other Supported Delimiter Characters topic in the Administering Planning documentation.

9. For Source File, click Choose File.
10. Navigate to the location of the import file, select it, and click Open.

    

11. Select Include Metadata.

    You can import and modify metadata the same time you import data. Clearing this option prevents members from changing positions in the outline during the import process. If you do not select this option, you must make sure the required metadata already exists in the business process prior to running the import process.

12. For Date Format, accept the default selection: MM-DD-YY.
13. Click Validate.

    As a precaution, always validate your data files before running the import process.

    

    You check the status of the validation process in Job Scheduler. If errors are found, you can modify the file and revalidate it.

14. Click OK.
15. Click Close twice to close the Import Data pages.
16. From the cards displayed on top of the Application page, click Jobs.

    The current state of all submitted and scheduled jobs are displayed on the Jobs page.

17. Locate the Validate Import Data job and verify that it completed successfully.

Running the Import Process

After validating your import data file, you can run the import process.

Return to the Import Data page. You will re-enter the same selections from when you validated the import data file.

1. From the cards displayed on top of the Jobs page, click Overview.
2. On the Application page, click Actions.
3. Select Import Data.
4. On the Import Data page, click Create.
5. Accept the default selection, Local, as the Location.
6. Accept the default selection, Planning, as the Source Type.
7. Accept the default selection, Comma delimited, as the File Type.
8. For Source File, click Choose File.
9. Navigate to the location of the import file, select it, and click Open.
10. Select Include Metadata.
11. For Date Format, accept the default selection: MM-DD-YY.
12. Review your selections.

    

13. Click Import.
14. On the Information dialog box that confirms that the import data job was submitted successfully, click OK.
15. Return to Jobs to view the job status. Click Close twice to close the Import Data pages.
16. From the cards displayed on top of the Application page, click Jobs.
17. Locate the Import Data job and verify that it completed successfully.

    

Verifying the Data Load in the Form

After validating your data file, running the import process, and checking the job status, verify the data load by viewing the data in a form.

1. Return to the Planning home page. Click the Home icon on the top right portion of the page.

   

2. Click Data.
3. Expand Plan.
4. Locate and click Operating Expenses - Overhead Depts FY20.

   The imported data is displayed on the form.

   

5. Click Close.
6. Return to the Planning home page. Click the Home icon on the top right portion of the page.

Exporting Data

In this section, you export data from your Planning business process.

Running the Export Process

1. From the Planning home page, click Application.
2. Click Overview.
3. On the Application page, click Actions.
4. Select Export Data.

   

5. On the Export Data page, click Create.

   You enter export details on this page.

   

6. For Location, accept the default selection: Local.
7. For Cube, accept the default selection: Plan 1.
8. Accept the default selection, Comma delimited, as the File Type.
9. For Smart Lists, select Export Names.
10. For Dynamic Members, accept the default selection: Include.

In the Slice Definition section, you select members for the row, column, and POV.

You export asymmetric data sets. You select a dense dimension for the row, a dense dimension for the column, and the rest of the dimensions in the POV.

11. For Row, select Account.

    

12. Click Member Selector next to the Row dropdown list.

    Member Selector is displayed.

    

    For more information on using Member Selector, see the Using the Member Selector section in the Adminstering Planning documentation.

13. In Member Selector, select the following accounts: Descendants inclusive of Total Office Expenses (7003) and Descendants inclusive of Total Facility Services Expenses (7004).

    

14. Click OK.
15. For Column, select Period and open Member Selector.
16. Select Level 0 Descendants of YearTotal, and click OK.

    

17. For Point of View, open Member Selector and select members for the following dimensions:
    • HSP_View: BaseData
    • Year: FY20
    • Scenario: Plan
    • Version: Working
    • Entity: CEO Office (710)
    • Product: No Product (P_000)
18. Click OK.
19. Review your selections for the slice definition.

    

20. Click Export.

    The Data Export status dialog box is displayed.

21. When prompted where to save the file, navigate to your local folder and click Save.
22. Click Close twice to close the Export Data pages.
23. Return to the Planning home page. Click the Home icon on the top right portion of the page.

Viewing the Exported Data File

1. In your local machine's file system, locate the exported compressed (zip) file.
2. Extract the compressed file.
3. Double-click to open the extracted CSV file.

   

Scheduling Data Loads

To start scheduling import and export data jobs, you must work with the Inbox/Outbox Explorer and save the import and export process when you first create them.

Uploading your Data File to the Inbox/Outbox Explorer

Before uploading your file to the Inbox/Outbox Explorer, make sure you validate it. See the Validating Import Data Files section of this tutorial.

1. From the Planning home page, click Application.
2. Click Actions and select Inbox/Outbox Explorer.

   The Inbox/Outbox Explorer dialog box is displayed.

   

3. Click Upload.
4. In the Upload File dialog box, click Choose File.
5. Locate the import data file in your file system, select it, and click Open.

   

6. Click Upload File.
7. When the message File upload completed successfully displays, click OK.

   The import data file is now listed in Inbox/Outbox Explorer.

   

8. Click Close.

Saving Import Data Jobs

1. On the Application page, click Actions and select Import Data.
2. On the Import Data page, click Create.

   You select or enter the required file information on the Import Data page.

   

3. For Location, select Inbox.
4. Accept the default selection, Planning, as the Source Type.
5. Accept the default selection, Comma delimited, as the File Type.
6. For Source File, enter the full name of the import data file: epm_planning_Account_FY20.csv.
7. Select Include Metadata.

   You can import and modify metadata the same time you import data. Clearing this option prevents members from changing positions in the outline during the import process. If you do not select this option, you must make sure the required metadata already exists in the business process prior to running the import process.

8. For Date Format, accept the default selection: MM-DD-YY.
9. Click Save as Job.

   The Save as Job dialog box is displayed.

10. Enter a name for the job: Import Accounts FY20.
11. Click Save.
12. When the message box confirming that the job was saved displays, click OK.
13. Click Close.

    The Import Data page lists the saved job.

    

14. Click Close.

Scheduling Import Data Jobs

1. From the cards displayed on top of the Application page, click Jobs.
2. Click Schedule Jobs.

   The Schedule Job wizard is displayed.

   

3. From the list of job types, click Import Data.
4. Select to run the job now or schedule it at a later time.

   The dropdown list allows you to select a time zone.

   

5. Determine how often you want to run the job.
   • Enter a name
   • Select a pattern
   • Optionally, select an End Date



6. Click Next.
7. Review the job details, make sure the Import Accounts FY20 job is listed and selected, then click Next.
8. Review your selections.

   

9. Click Finish.

   The scheduled job is listed under Pending Jobs.

   

   When the job runs or completes, its status is displayed under Recent Activity.

   

Saving Export Data Jobs

1. From the cards displayed on top of the Application page, click Overview.
2. Click Actions and select Export Data.
3. On the Export Data page, click Create.
4. For Location, select Outbox.
5. For Cube, accept the default selection: Plan 1.
6. Accept the default selection, Comma delimited, as the File Type.
7. For Smart Lists, select Export Names.
8. For Dynamic Members, accept the default selection: Include.

   In the Slice Definition section, you select members for the row, column, and POV.

9. For Row, select Account and click Member Selector next to the Row dropdown list.
10. In Member Selector, select the following accounts: Descendants inclusive of Total Office Expenses (7003) and Descendants inclusive of Total Facility Services Expenses (7004),
11. Click OK.
12. For Column, select Period and open Member Selector.
13. Select Level 0 Descendants of YearTotal, and click OK.
14. For Point of View, open Member Selector and select members for the following dimensions:
    • HSP_View: BaseData
    • Year: FY20
    • Scenario: Plan
    • Version: Working
    • Entity: CEO Office (710)
    • Product: No Product (P_000)
15. Click OK.
16. Review your selections for the slice definition.

    

17. Click Save as Job.

    The Save as Job dialog box is displayed.

18. Enter a name for the job: Export CEO Expenses.
19. Click Save.
20. When the message box confirming that the job was saved displays, click OK.
21. Click Close.

    The Export Data page lists the saved job.

    

22. Click Close.

Scheduling Export Data Jobs

1. From the cards displayed on top of the Application page, click Jobs.
2. Click Schedule Jobs.

   The Schedule Job wizard is displayed.

3. From the list of job types, click Export Data.
4. Select to run the job now or schedule it at a later time.

   The dropdown list allows you to select a time zone.

5. Determine how often you want to run the job.
   • Enter a name
   • Select a pattern
   • Optionally, select an end date

   

6. Click Next.
7. Review the job details, make sure the Export CEO Expenses job is listed and selected, then click Next.

   

8. Review your selections.

   

9. Click Finish.

   The scheduled job is listed under Pending Jobs.

   

   When the job runs or completes, its status is displayed under Recent Activity.

   

10. Return to the Planning home page. Click the Home icon on the top right portion of the page.

Downloading your Exported Data File from Inbox/Outbox Explorer

1. From the cards displayed on top of the Application page, click Overview.
2. Click Actions and select Inbox/Outbox Explorer.

   Inbox/Outbox Explorer is displayed with exported files listed on the page.

3. For "Export CEO Expenses.zip", click Actions and select Download File.
4. Navigate to where you want to save the file locally, then click Save.

   Extract the csv or txt file if you want to view or edit the contents. You can also use the csv or txt file as an import file.

5. Close Inbox/Outbox Explorer.

Importing Exchange Rates for Multicurrency Business Processes

In this section, you import exchange rates into a multicurrency business process.

Before proceeding with the tasks in this section, you must remove the business process used in the loading data sections of this tutorial and import the required snapshot for this section, as specified in the What Do You Need? section of this tutorial.

Accessing the Exchange Rates to <Main Currency> form

Exchange rates are stored in the Account dimension and contain the ending and average rates used to convert foreign currencies into the main currency. These rates are in FX Rates-Average and FX Rates-Ending. These rates are locked and can't be deleted. Users with view permissions can view or enter the rates for the currencies on the Exchange Rates to <main currency> form (For example, Exchange Rates to USD).

From the Planning home page, click Data.

Click the form name to open it.

You add exchange rates by period for all input currencies against the main currency. You must add values across all intersections under which you wish to see the converted data. In this example, my Planning business process has multiple input currencies: USD (default), GBP, EUR, JPY, SGD.

Exporting the Exchange Rate Template

1. From the Planning home page, click Application, and then Overview.
2. On the Application page, click Actions, and then select Export Exchange Rate Template.

   

3. Open ExchangeRateTemplate.csv or save it locally.
4. In the template file:
• Enter average and ending currency exchange rates for each period and each currency.
• Specify members for the POV.
• Save the file.

Validating and Importing Exchange Rates

In this section, you import exchange rates using the sample import file from the What Do You Need? section of this tutorial.

1. On the Application page, click the Actions menu and select Import Exchange Rates.
2. Click Create.
3. Accept the default selection, Local, as the Location.
4. Accept the default selection, Planning, as the Source Type.
5. Accept the default selection, Comma delimited, as the File Type.
6. For Source File, click Choose File.
7. Navigate to the location of your exchange rate file, select it, and click Open.
8. Select Include Metadata.

   

9. Click Validate.
10. Click OK.
11. Click Close twice to close the Import Data pages.
12. From the cards displayed on top of the Application page, click Jobs.
13. Locate the Validate Import Exchange Rates job and verify that it completed successfully.

    

14. Return to the Application page. From the cards displayed on top of the Jobs page, click Overview.
15. Repeat steps 1 to 8 in this section to complete the information required on the Import Exchange Rates pages.
16. Review your selections and click Import.
17. When the Information message box is displayed, click OK.
18. Click Close twice to close the Import Data pages.
19. From the cards displayed on top of the Application page, click Jobs.
20. Locate the Import Exchange Rates job and verify that it completed successfully.

    

Reviewing the Imported Exchange Rates

1. Return to the Planning home page. Click the Home icon on the top right portion of the page.
2. Click Data.
3. Click the Exchange Rates to USD form.

   The form displays the imported exchange rates.

   

Want to Learn More?

• Documentation and related videos
• Oracle EPM Tutorials YouTube Channel