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
  5. Creating an SQL Query

Creating an SQL Query

The Query option enables you to save the SQL query definition when creating an SQL data source. This type of data source is associated with an on-premises database adapter, which enables you to extract data from an on-premises database and load it to the Oracle Enterprise Performance Management Cloud using the EPM Integration Agent.

  1. From the Data Integration home page, click Action, and then Query.
  2. On the Query screen, click Add (Image shows to Add icon.).
  3. From the Create Query screen, and then in Query Name, specify the SQL query name.

    The query name is also used on the Application Filter page in Data Management to identify the data extract query when registering the SQL data source in the target application.

  4. In Query String, specify the SQL statement used to extract the data based on the header row in the file.

    You can use an alias for a column name in the SQL query to the dimension name.

    When specifying the string for a header row value and the string is mixed case, enclose the string in double quotation marks. When a string with mixed case characters is not enclosed within double quotation marks, it is resolved in uppercase.

    For example, if your header row uses the alias names Acct,Prod,Ent,Amt,Loc,Dat, then you might enter values as follows:

    SELECT ENTITYX AS "Ent" ,ACCOUNTX AS "Acct",UD3X AS "Prod",AMOUNTX AS "Amt", L.PARTNAME "Loc", P.PERIODDESC AS "Dat"FROM TDATASEG T, TPOVPARTITION L, TPOVPERIOD P WHERE T.PARTITIONKEY = L.PARTITIONKEY AND T.PERIODKEY = P.PERIODKEY AND L.PARTNAME = ~LOCATION~ AND P.PERIODDESC = ~PERIOD~

    Image shows the Create Query screen.

  5. Click Save.