Oracle BI EE – Execute Direct Database Requests
Posted by Venkatakrishnan J on September 18, 2007
This article is just to show the one of the features of OBI EE. It is not recommended to use this feature since it bypasses the metadata layer and executes direct database requests. If it has to be used in your environment, do it with utmost care and caution taking all the security into consideration.
OBI EE can execute user provided SQL on a database and can get the results back to the end user. By default this feature is turned off. In order to enable it, there are 2 steps that one would have to follow.
The first step is to enable the database in your physical layer to allow for direct database requests.
Once this is done, we would have to login to the presentation services and give the necessary “execute direct database requests” permission to the user. This is turned off by default even for the Administrator. Lets turn it on for the administrator using the manage privileges.
Go to Answers->Settings->Administration->Manage Privileges
Then enable direct database requests privilege for the user group that you need. In my case, just for demonstration, i have given that permission to everyone.
Once this is done, go to Answers and click on Execute Direct Database Requests and then enter the query along with the connection pool name.
As you would see here, one can enter any query that the database would support. For more details on securing this feature refer BI Server Guide and Presentation Services Guide
FreeDomainHost said
Wow cool site! to be honest, I am surprise of the power of WordPress!
keep it up and hope you post more ! So that we benefit from it!
Oracle BI EE 10.1.3.2/3 - Ref Cursors and Pipelined functions « Business Intelligence - Oracle said
[…] way of testing things out. If you have not set direct database requests, i have blogged about it here showing you how to set it up. I have created a connection pool that basically points to my scott […]
OBI direct said
Very useful, thank you. Much better than the supplied documentation !
Qinghe said
Can we pass a session variable to the where condition in the direct database query to filter out the result?
Thanks!
Venkatakrishnan J said
Yes you can. Just use this ‘@{variablename}’ in your where clause.
OBI DDR said
Thanks for the tip. I had having difficulty using the variable name. If I enclosed it in certain kinds of quotes, it was just treated as a string literal. If I did not enclose it in quotes, I get an OCI error from the Validate SQL step. Finally it works by enclosing it using quotes like this: ‘@{NQ_SESSION.USER}’.
But is there a way of printing out the value in the query ? Thank you.
sue said
Thank you very much – that answered exactly what I was looking for!!
enthropy said
Is there any way to add prompts to reports created using direct database requests