The ability to execute SQL statements directly against the physical data source is called as direct database request (DDR). Direct database requests is available in Oracle Business Intelligence Enterprise Edition (OBIEE) but have some limitations, which are:
- SQL statement is always executed on the physical data source.
- SQL statement is not optimized by Oracle Business Intelligence (Oracle BI Server) Server engine.
Users with the appropriate permissions can create and issue a direct database request directly to a physical back end database. The results of the request can be displayed and manipulated within Oracle BI Answers, and subsequently incorporated into Oracle BI Interactive dashboard.
The Oracle BI Server sends original unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set is then treated as an Oracle BI request.
Direct database requests completely bypass the Oracle BI Server mechanisms like security and logical business model. And one must BE CAREFUL, since in addition to SELECT queries, direct database requests also able to execute CREATE, or even DELETE and DROP statements on physical data store.
All above statements lists the the negative characteristics of direct database requests, but this feature has some benefits. These can be listed as:
- DDR allows users to write and execute complex SQL queries
- DDR allows to check physical connectivity to the database and can be used to determine performance issues.
How to Create a Direct Database Request:
In order to create a direct database request, open your web browser and navigate to the BI Answers (http://localhost:9704/analytics) to login with using an appropriate user (weblogic) having required permissions.
Select Analysis from the New menu at the the top-right corner of the page and Click Create Direct Database Request link at the bottom.
On the criteria tab, provide the Connection Pool name (the same as in the repository created by the Administration Tool) and SQL Statement (database specific SQL syntax is required) and then click Validate SQL and Retrieve Columns button.
If everything is OK, then you should have column names listed below the Result Columns part. Be sure to write connection pool name exactly the same as the name in the repository (with speces etc.) otherwise you may end up with Unresolved Connection Pool object:”..” error.
After successfully executed the SQL statement and get the result columns, click the Result tab and build your analysis as usual.