Monday, March 11, 2013

How-to: Direct Database Request Configuration OBIEE 11g

2:00 PM

Share it Please
The ability to execute SQL statements directly against the data warehouse, also known as 'Direct Database Requests' is not new to OBIEE 11g. Usually used a last resort, the direct database request has many limitations including:

  • Physical SQL statement is always executed
  • Does not utilize OBIEE 11g's BI Server engine for maximum query optimization
  • Increased run time

None the less, if a client has a specific reporting requirement that cannot easily be tied into an existing data model, then the direct database request becomes a viable alternative.

As most OBIEE Architects know, to enable Direct Database Requests is done within Answers in the 'Manage Privileges' Section:




After applying the above security privilege, the 'expected result' is for a member of the applicable application role to be granted the ability  execute direct database reports. Which in turn leads to a surprise when the user is still unable to execute a direct database request.


Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 13017] User or application role has not been granted the Direct Database Access privilege to access the database. Please verify the User/Group Permissions in the Oracle BI Administration Tool.Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error.
 

Why?


What many developers forget to remember is that components of security are still managed within the repository, even in OBIEE 11g. 

The ability to control who can execute direct database requests is managed within the Admin Tool's Identity Manager. For every applicable application role, you must grant direct database requests to each specific subject area:

Manage -> Identity -> Application Roles Tab -> Permissions




Note that by default 'Execute Direct Database Requests' is set to ignore, which inherits privileges granted by its parent security role. This normally implies 'disallow'. By setting the  the permission to 'allow' , you are granting the user the ability to run a direct database request against a specific subject area.

From a security perspective, granting access to specific subject areas is the preferred method of authorization when the alternative is a blank 'grant' or 'deny' via Manage Privileges.

As outlined in my primer on object level security & inheritance , inheritance applies but direct approval or denial supersedes any implied security rules.



keywords: identity manager, direct database requests, obiee 11g, answers, manage privileges, security, OPR4ONWY:U9IM8TAC:OI2DL65P

6 comments:

  1. Thanks for sharing this John. Your post is informativae and very useful. I couldn't find much about direct database request configuration except here.

    ReplyDelete
    Replies
    1. Do you know if this can set pdqpriority(informix)?

      Delete
  2. Hi John,

    I have done the necessary settings in the Presentation Services and also in the RPD.I am not sure why I am still not able to call/execute any function through Direct Database Request in my OBIEE Dev Link.
    However,I am able to execute the function through my local server's Presentation Service.The local server has the same RPD and the same Presentation server settings as the Dev server.

    Could you please help?

    ReplyDelete
    Replies
    1. Note-I am working on OBIEE 10g.I cannot upload the RPD to the Dev Server directly.I send it to my client,who merges my subject area wiht the exsiting RPD on the server.Do you think I need to get back to him to change some settings at his end?But I am able to run other queries(which do not call functions)through DDR.

      Delete
  3. Hi John,
    Your post is really great. I am calling a procedure which insert the data into a dummy table when ever it called. I called that proc using the Direct database but when i called that proc using the direct DB i am seeing couple of entries in the table. i am not sure why the direct connect is running so many times the PROC.

    Thanks in advance.

    ReplyDelete
  4. Thank you for the sharing, it helped a lot

    ReplyDelete