Creating a Drill Down to a Source System Without a Landing Page

When you want to drill down to a source system without a landing page, execute a SQL query in the source database and display the results in a pop-up window inside Data Integration.

To create a query for the drill through:

  1. From the Data Integration home page, click Action, and then select Query.

  2. On the Query screen, click Image shows to Add icon..

  3. From the Create Query screen, and then in Query Name, specify the name of the query to query the data from the source system.

  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.

    The query can include filter conditions. The bind variable for the filter condition must be enclosed inside ~~ characters.

    Image shows Query page.

  5. Click Save.

  6. From the Data Integration home page, click Image shows Select icon. to the right of the integration to which to add a drill through, and then select Map Dimensions.

  7. On the Map Dimension page, and then in Drill URL, click the drill pen icon.

    For more information on the Map Dimension page, see Mapping Dimensions.

  8. In the Edit Drill URL page, specify the #agent tag and then specify parameters for the drill through URL.

    For example, using the query shown in step 4, enter: #agent?QUERY=Source Drill Query&SOURCE_ACCOUNT=$ACCOUNT$&SOURCE_PERIOD=$ATTR1$ where:

    • QUERY is the name of the query you defined in step 3.

    • SOURCE_ACCOUNT is derived from the ACCOUNT column.

    • SOURCE_PERIOD is derived from the ATTR1 column

  9. Map the columns in the source column to the dimensions in the target application to which to drill through and click Save.

  10. From the Data Integration home page, click Actions, and then select Agent.

  11. From the Agent Cluster screen, select the name of the agent to use with the drill through.

  12. From the Agent tab, and then in Web URL, select the web server gateway that enables inbound communications from the Oracle Enterprise Performance Management Cloud.

    If you are using synchronous mode, the Web Server URL you defined to perform the data load operations is used to perform the drill. No additional setup is required.

    If you are using agent in asynchronous mode, define the URL using one of the following methods:

    • Configure a https web server similar to how you configured the synchronous mode. See Configuring Synchronous Mode.

    • Assign the Agent URL as the web server URL. As a result of accessing the http URL from a https page, you need to define an Exception in the browser security settings.

      For Chrome, select Settings, then Privacy and security , then Insecure content, and then under Allow, add the following site *.oraclecloud.com.

    Note:

    You must be in your local network where the agent is running to drill when you are running in asynchronous mode.

    Image shows the Agent tab.