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.
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 –
SELECT CUST_ID AS Customer_ID FROM
CUSTOMERS
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.