Business Intelligence – Oracle

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

Advertisements

8 Responses to “Oracle BI EE – Execute Direct Database Requests”

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

  2. […] 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 […]

  3. OBI direct said

    Very useful, thank you. Much better than the supplied documentation !

  4. Qinghe said

    Can we pass a session variable to the where condition in the direct database query to filter out the result?

    Thanks!

  5. Venkatakrishnan J said

    Yes you can. Just use this ‘@{variablename}’ in your where clause.

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

  7. sue said

    Thank you very much – that answered exactly what I was looking for!!

  8. enthropy said

    Is there any way to add prompts to reports created using direct database requests

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: