Direct database request in OBIEE - Part 1

We all of us must have seen the option Direct Database Request on the very first page of the OBIEE Answers. Usually we never this option and in fact we don’t need it for day to day reporting needs.

This option is actually very useful especially when you have to call database function or execute some database specific commands. This document will help you understand how to use this option.

How we can use this functionality

We have to configure certain things in front end & rpd. Let us see the front end configuration.
Login as an Administrator; go to Settings-Administration-Manage privileges section. 

Search for the component, Answers & the 2 properties – 
1) Edit Direct Database Requests
2) Execute Direct Database Request.
I have given users, “Naresh” and “Presentation Server Administrator” the access to these 2 properties as shown in the above snapshot.

Now let us see the back end or RPD configurations.
We have to allow the user to execute the Direct database request.
For the user Naresh – Open the Security manager-Click on the user Naresh-Click on the Permissions tab-click on the tab Query Limits. In my case the database name is ORCL1.

Select the “Allow” from the drop down list of the tab Execute Direct Database Requests against the ORCL1 database.
For the Administrator – we don’t require to set this option as Administrator is already having the access to execute Direct Database Requests against the ORCL1 database.
Now it is time to restart the OBI services – restart all the services – Clear the browser cache, temporary files. And login to Answers as an Administrator.

Example No 1:  Showing column in the front end that is not available in Presentation layer

I need a column “Customer ID” to be displayed in the front end which is NOT present in the presentation layer of the OBIEE RPD.
If I ask you to get the values of the customer ids in the database then what will you do? The answer is pretty simple –
The same query we are going to use in the Direct Database Request option in the OBIEE Answers.
Before that let us see the name of the connection pool which is used for the database ORCL1 wherein our CUSTOMERS table is present. This name is required later.
Okay, the name is “Connection Pool”

Let us go back to OBI Answers & click on “Create Database request”

Put the name of the connection pool & the database SQL to fetch the customer id as shown in the below snapshot. And click on the button “Validate SQL and Retrieve columns”. If you have configured the things properly & the SQL syntax is correct then you should not receive any errors. 

We can see the result by clicking on the Results tab.

Note that Customer Id column is not present in the presentation layer but still we have achieved to display the values in the front end. This is sometimes useful when you need to perform some calculation based on the value of the customer id but at the same time you want to hide the column from the presentation layer of the obi rpd. I have saved this report with the name “Customer Id” and used it in a dashboard as shown below.

Example 2 is shown in part 2.