Importing Data

Before importing data, you must:

  • Define or load metadata and refresh the database. See Importing Metadata.

  • Prepare the data load file. Source data must be in a business process-specific data file format or in an Oracle Essbase data file format.

  • Ensure the data load files do not contain any thousands separators, quotes, and so on, around data values. Up to one single . (decimal point) character is supported.

  • Ensure the data import file type is .csv, .txt, or .zip.

You can create a data import file based on a template, or you can generate one based on the source system. To use a template, run the export data process in your business process and use the exported file as a template. See Exporting Data.

Note:

When importing data, no validation is performed on intersections to determine which are valid, and data is loaded to all intersections. To review data that has been loaded into invalid intersections, run the Invalid Intersection report before importing data to see and clear invalid intersections. See Working With Invalid Intersection Reports.

To import data:

  1. Click Application, and then click Overview.
  2. Click Actions, and then click Import Data.
  3. Click Create and make your Import Data selections:

    Table 6-3 Data Import Options

    Data Import Option Description

    Location

    Select the location of the data import file:

    Note: Valid data import file types are .csv, .txt, or .zip.

    Source Type

    Select the source type for the data import file:

    • Default: Select this source type to load data in a business process-specific data file format. If you select this option, you must choose a File Type and a Date Format. File type options are Comma delimited, Tab delimited, and Other. Date format options are MM-DD-YYYY, DD-MM-YYYY, and YYYY-MM-DD.

      Note:

      • If an import error occurs during a business process-specific data load, the import will continue to load valid data (partial load). Any errors will be noted in the import and export status. See Viewing Data Import and Export Status.

      • For a list of supported delimiter characters and exceptions, see Other Supported Delimiter Characters.

      • If data is exported to a file in a business process-specific format, you can use Notepad to change the database name in the file; for example, ASOCube, and then import the data file into the aggregate storage outline (provided all dimensions exist in the aggregate storage outline).

    • Essbase: Select this source type to load data in an Essbase data file format. If you select this option, you must choose a cube.

      For Delimiter, specify the delimiter for the Essbase data; options are Space or Tab.

      Note: Stop In Case of Error is selected by default. If this option is selected and any import errors occur during an Essbase data load, the import will quit and no data will be loaded. If this option is cleared, data will continue to load even if there are errors.

    Source File

    • For Local, click Browse to navigate to the source file.

    • For Inbox, enter the name of the file in Source File.

    Include Metadata (optional)

    Clear this option to improve data import performance and to prevent members from changing position in the outline during data import. Regardless of this option setting, the required metadata must already exist in the business process and in the Essbase outline before you can load the application data.

    Missing Data Label

    Specify how you want missing values to display in forms. Enter an alphabetic value that does not exceed 16 characters. You may begin the value with a # (number sign). If no value is entered, the system defaults to #MISSING.

    Note: If you're using a data import file that's based on a template (in other words, you ran the export data process in your business process and you're using the exported file as a template), this label must match the Missing Data Label that was defined during data export. See Exporting Data.

  4. Click Save as Job to save the import operation as a job, which you can schedule to run later.

    Note:

    • Save as Job is available only when Inbox is selected.

    • Saving an import operation as a job is useful to batch a load sequence, for example, import metadata, then import data, then run rules when the data load is complete. See Managing Jobs.

    • You can run up to five import jobs at one time.

    • If you want to review the data that was rejected during the data import job, you can specify an Error File on the Save as Job dialog. This file will provide information about the data records that were not imported. If an error zip file is specified, the zip file is stored in the Outbox where you can download the file using Inbox/Outbox Explorer. See Uploading and Downloading Files Using the Inbox/Outbox Explorer.

    Tip:

    To streamline the job creation process for subsequent Import Data jobs, you can duplicate an existing job using the Save As option, then update it. See Duplicating Jobs.

  5. Optional: Click Validate to test whether the import file format is correct.
  6. Click Import to run the import operation.

Note:

There is no import option in this business process to clear data before import. Therefore, the import will not overwrite existing data values with #MISSING values. However, you can run a clear cube job prior to import, or clear data with a script/rule or using the REST API:

  • You can run a Clear Cube job that enables you to clear specific business process data within input and reporting cubes. See Clearing Cubes.

  • You can write a business rule that uses the CLEARDATA Essbase command to clear specific data values. See CLEARDATA in Technical Reference for Oracle Analytics Cloud - Essbase.

  • You can use the Clear Data Slices REST API to clear business process and Essbase data for a specified region. See Clear Data Slices in Oracle Fusion Cloud EPM REST APIs.

Caution:

If the evaluation order in the application is changed after exporting data, the import may have unexpected results.

If a value in a data load input file doesn't match the evaluated driver member type; for example, if the evaluated type of a driver member is "date" and it's corresponding input value isn't a valid date format, a driver member casting error occurs. See Driver Member Casting Errors.

Table 6-4 Videos

Your Goal Watch This Video
Learn the highlights of loading data into your business process. video icon Overview: Loading Data in Planning and Budgeting Cloud

Table 6-5 Tutorials

Your Goal Learn How
Learn how to import and export data in your business process, including importing exchange rates into multicurrency business processes. tutorial icon Loading Data in Planning