OBIEE 10G/11G - Direct Database Request

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - 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.

Advertising

3 - How to create a Direct Database Access ?

3.1 - Access to Direct Database Access

3.1.1 - 10G

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

3.1.2 - 11G

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

3.2 - 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.

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

Advertising

4 - How to

4.1 - Call a database Function

TT:

5 - Database Request in Dashboard

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

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

6 - Security, Privileges

6.1 - 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
Advertising

6.2 - 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

7 - Support

7.1 - ORA-00911: invalid character at OCI call OCIStmtExecute

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

7.2 - [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.