Table of Contents

About

The Direct Database Request permit you to perform SQL statement directly to a datasource. The Oracle BI Server sends unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set can be rendered in Oracle BI Presentation Services, and then charted, rendered in a dashboard, and treated as an Oracle BI request.

Becareful, you can perform SELECT but also CREATE or even DELETE and DROP statement!

You bypass completely the OBI Server mechanisms such as :

You can use it to perform complex query such as using a table function.

How to create a Direct Database Access ?

Access to Direct Database Access

10G

Click on answer and you should see a link in the subject area. (If not see the security paragraph below)

Obiee Database Request Subject Area

11G

In the global header, select New, then Analysis, then Create Direct Database Request.

Creation

Enter :

  • the connection pool
  • the sql statement

You can find the name of the connection pool in the Oracle BI Repository in the physical layer.

Obiee Connection Pool

Obiee Database Request Answers

You can use in the SQL statement a variable. Below, a example with a presentation service variable with a default value of 'Photo'.

How to

Call a database Function

TT:

Obiee Jdbc Odbc Function Direct Connection

Database Request in Dashboard

Add a prompt in the dashboard to update the presentation service variable and the Direct Database Request can be filtered.

Obiee Dashboard Database Request

And the download link works perfectly and gives you the filtered data (Tested on version 10.3.4.1)

Security, Privileges

OBI Presentation Service

You must grant your account with the privilege “Write Back to database” :

  1. Login in OBI Presentation Service Website
  2. Follow this links : Setting / Administration / Manage Privileges
  3. Grant the privilege Answers/“Execute Direct Database Requests” to the permitted group

Administration Tool

You must grant the direct access database to the user through the Administration Tool in the security part.

  1. Login in OBI Administration
  2. Go to the menu : Manage / Security
  3. Grant the privilege “Execute Direct Database Access” to the permitted group of user

write_back_execute_direct_database_request.jpg

Support

ORA-00911: invalid character at OCI call OCIStmtExecute

Be careful that you have to suppress the semicolon at the end of the statement.

[nQSError: 27022] Unresolved Connection Pool object: ….. (HY000)

Be sure to have the name of the database and the name of the connection pool framed by two doubles quotes.

Example: 

database."Connection Pool"

is not good whereas 

"database"."Connection Pool"

is good.