Advanced Techniques: Issue Direct Database Requests

You can create and issue a direct request to the back-end database using a connection pool and a database-specific SQL statement, if the administrator has granted you the appropriate privileges

Topics:

You can also review and change the result columns of the database request by changing the SQL statement. After making the changes and retrieving the required results, you can incorporate the results into dashboards and agents.

• Privileges Required for Direct Database Requests

• Create and Execute Direct Database Requests

Privileges Required for Direct Database Requests

Administrators can use the Manage Privileges page in the Classic Administration page to view and set access permissions for direct database requests.

To create and issue a direct database request, you must have been granted the following privileges:

• Edit Direct Database Analysis - You can create and edit direct database requests.
• Execute Direct Database Analysis - You can issue direct requests and see the results

Oracle recommends that you keep the default privileges because they’re optimized for Oracle Analytics. Editing privileges might cause unexpected behavior or access to features.

Create and Execute Direct Database Requests

You can create a direct request to the database from the Classic home page.

Security rules for data are bypassed and can’t be applied when creating direct database requests.

1. On the Classic Home page, in the Create pane, click Analysis (or click New and select Analysis).
2. In the Select Subject Area dialog, click Create Direct Database Query.
3. In the Criteria tab, enter a connection pool and a query statement.
   • Use Connection Pool to specify the name of the connection pool to use for the query.
   • Use SQL Statement to specify the database-specific SQL statement for retrieving data from the database.
4. Click Validate SQL and Retrieve Columns and review the retrieved columns in the Results Columns field.
5. Click the Results tab to issue the request and see the results. The Results Columns field sometimes doesn’t display any columns, because the SQL statement you provided in the Criteria tab retrieved no data from the database.

There are some functions you can’t perform in analyses whose columns originated from direct database requests:

• Select columns in the Subject Areas pane, because you aren’t working with columns from a semantic model.
• Create groups or selection steps for this analysis.
• Specify conditional formatting for the columns.
• Sort the values in the retrieved columns using the Sort Ascending and Sort Descending options in the column header of a pivot table, table, or trellis view.