1. Administering Data Integration for Oracle Enterprise Performance Management Cloud
  2. EPM Integration Agent
  3. EPM Cloud to On-premises Connectivity Using the EPM Integration Agent
  4. EPM Integration Agent Scripting
  5. Integrating Oracle Autonomous Database Data Using the EPM Integration Agent
  6. Loading Data from the Oracle Autonomous Database to the EPM Cloud

Loading Data from the Oracle Autonomous Database to the EPM Cloud

You can extract data from the Oracle Autonomous Database by executing a relational database SQL query against a database in the Oracle Autonomous Database and then load the data or metadata to the Oracle Enterprise Performance Management Cloud using the EPM Integration Agent in your local network.

The EPM Integration agent is defined as a data source for an integration, and when executed provides data that is subsequently mapped and loaded to the selected target application.

As a prerequisite to loading data you must be connected to the Oracle Autonomous Database. In addition, you must have already downloaded the Oracle Wallet. The wallet provides an simple and easy method to manage database credentials across multiple domains.

To load data from the Oracle Autonomous Database to the EPM Cloud:

  1. In Data Integration, create the query definition and save the SQL query.

    To do this, see Creating an SQL Query.

    If you already have an SQL query registered in Data Integration, skip this step.

  2. Create a file that contains one header row from the source data.

    The header row must exactly match the dimension name in the target dimension. You can use an alias for a column name in the SQL query to the dimension name.

    Image shows dimension headers in a text file.

  3. Save the file as a CSV format file with a header row.

    Upload the file using the file browser when registering the application.

  4. Register the Oracle Autonomous data source application:

    1. From the Data Integration home page, and then Actions, select Applications.

    2. On the Applications page, click Image shows the Add icon..

    3. From Create Application, then Category, select Data Source.

    4. From Type, select Oracle Autonomous Database.

    5. From File, select the file that you created in step 8.

      Click Image shows the Browse icon. to browse for the file on the File Browser page.

    6. In Prefix, specify a prefix to make the application name unique.

      The prefix is concatenated with the application 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.

    7. Click OK and then click Save.

      Image shows the Application page.

  5. Click OK and then click Save.

  6. On the Application page, click Image shows the Select icon. next to Oracle Autonomous data source application and then select Application Details.

  7. On the Application Details page, click the Options tab.

  8. In Data Extract Query, specify the name of the SQL query to run against the file.

  9. In Delimiter, select the type of delimiter used in the file.

    Available delimiter symbols include the following:

    • Comma (,)

    • Exclamation (!)

    • Semicolon (;)

    • Colon (:)

    • Vertical bar (|)

  10. In Credential Store, specify the type of credential store used by the EPM Integration Agent.

    Available types of credential stores include the following:

    • Cloud
    • File

    For the Cloud credential store type, store the username/password/connect string in the application.

    For a File credential store type, create a file that stores the JDBC URL, user name and password for the Oracle Autonomous Database connection. The URL format must include the JDBC URL, path to wallet folder, user name, and password. The file name must be named appname.cred and stored in the config directory.

    The file must contain the following lines:

    jdbcurl=jdbc:oracle:thin:@<tns_name>?TNS_ADMIN=<path_to_wallet_folder>

    An example of an entry in the .cred file might look like this: 

    jdbcurl=jdbc:oracle:thin:@epmdevdb0_low?TNS_ADMIN=C:\ATP Connection\epmdevdb0_wallet
username=apps
password=w+Sz+WjKpL8[

    Note:

    The password used for both credential store types must be encrypted.

    When the type is a "Cloud" store, type the password in the user interface in the usual way. The system encrypts and stores the password.

    When the type is a "File" store, you must encrypt the password using the encryptpassword utility and store the password in the file. For more information about encrypting your password, see Encrypting the EPM Cloud User Password.

  11. In Username, specify the user name that you used to connect to the Oracle Autonomous Database.

    If you connect to the Oracle Autonomous Database using SQL Developer, specify the SQL Developer username and password.

  12. In Password, specify the password that you used to connect to the Oracle Autonomous Database.

  13. In Service Name, specify the service name.

    You can find the service name by opening the tnsnames.ora file and selecting a service name associated with the connection. A service name is an alias mapped to a database network address contained in a connect descriptor.

    You are required to select a service name when you connect to the database. The service names for Autonomous Data Warehouse connections are in the format:

    • databasename_high

    • databasename_medium

    • databasename_low

    These services map to the LOW, MEDIUM, and HIGH consumer groups.

    For example, if you create an Autonomous Database with a Data Warehouse workload type and specify the database name as DB2020, your service names are:

    • db2020_high

    • db2020_medium

    • db2020_low

    If you connect using the db2020_low service, the connection uses the LOW consumer group.

    The basic characteristics of these consumer groups are:

    • HIGH: Highest resources, lowest concurrency. Queries run in parallel.

    • MEDIUM: Less resources, higher concurrency. Queries run in parallel.

      You can modify the MEDIUM service concurrency limit. See Change MEDIUM Service Concurrency Limit for more information.

    • LOW: Least resources, highest concurrency. Queries run serially.

  14. In Fetch Size, specify the number of rows fetched (that the driver processes) with each database round trip for a query.

  15. In Wallet Location, specify the location of the directory where your wallet has been downloaded to and unzipped in the EPM Integration Agent server.

    Image shows the Application Details page.

  16. Click Save.

  17. Finish integrating the Oracle Autonomous Database data source with the EPM Cloud by completing the following:

    Task Description More Information
    General

    Add or edit an integration for file-based and direct integration sources.

    Note:

    When editing an Oracle Autonomous Database integration where the same location is used but the target application has been changed from an on-premises source to an Oracle Autonomous Database source, the integration will fail at runtime.
    		Defining a Data Integration
    Map Dimensions Map the columns in the data source to dimensions in the target application.

    Mapping Dimensions
    Map Members Map dimensions to identify how source field values translate to target dimension members. Mapping Members
    Options Define options for importing and exporting data. Also, define any source filters. Setting Data Integration Options
    Run the Integration

    When the integration is executed, the EPM Integration Agent initiates the export process in the EPM Cloud. The agent downloads the export data. Based on the selected method, the agent also constructs the appropriate INSERT statement and loads the data to the target table.

    You can review the exported data by downloading the output file from the Process Details. The database column names for the write-back are specified as the column headers in the generated file as shown below.

    		For more information, see Running an Integration