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. Extracting Data from On-premises Data Sources

Extracting Data from On-premises Data Sources

You can extract data from your on-premises data sources and then load the data directly to the Oracle Enterprise Performance Management Cloud using the EPM Integration Agent. The EPM Integration Agent executes a query against an on-premises relational database and then loads the data to the EPM Cloud.

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.

The EPM Integration Agent may also be extended by way of scripting to access other data sources including third-party cloud applications, non-relational sources, or any system that provides access by way of scripting, a report, or an API.

In order to use this feature and register the application created for the extraction, you must create a file that contains only sample data with one header record. The file does not contain the application name or SQL query, etc. The name of the file must be the name of the application. This type of data source is an application of the type "On Premise Database" and uses an adapter-based framework.

To create an on-premises database adapter:

  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 a SQL query registered in Data Integration, skip this step.

  2. Create a file that contains one header row from the on-premises database.

    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 target application.

  4. Create the on-premises database data source:

    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 On Premise Database.

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

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

  5. When prompted, select the file that you created in step 1.

    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.

    Create Application page

  7. Click OK and then click Save.

  8. On the Application, click Image shows the Select icon. next to on-premises data source application created in steps 3-7 and then select Application Details.

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

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

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

    Available delimiter symbols include the following:

    • Comma (,)

    • Exclamation (!)

    • Semicolon (;)

    • Colon (:)

    • Vertical bar (|)

  12. 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 the File credential store type, create a file in the on-premises environment to store the JDBC URL, 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:

    driver=oracle.jdbc.driver.OracleDriver
jdbcurl=jdbc:oracle:thin:@host:port/service
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.

  13. In JDBC Driver, select the type of JDBC driver to use when connecting to the Database Management System (DBMS).

    Available types of JDBC drivers include the following:

    • Microsoft SQL Server
    • Oracle

  14. In JDBC URL, specify the JDBC driver URL connection string.

    The JDBC Driver URL connection string enables you to connect to a relational database using Java.

    For an Oracle Thin JDBC driver, the JDBC driver URL includes the following:

    jdbc:oracle:thin:@host:port:sid

    jdbc:oracle:thin:@host:port/service

    For a MS SQL Server, the JDBC driver URL includes:

    jdbc:sqlserver://server:port;DatabaseName=dbname

  15. In Username, specify the on-premises database user name.

  16. In Password, specify the on-premises database password.

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

  18. Click Save.

    The following image shows application filters:

    Image shows the Options tab on the Application Details page.