ODBC or OCI?

How should you set your connection pool - OCI or ODBC?

When possible, configure your connection pools to use a “native driver” to connect to your physical databases.  For example, use OCI for connecting to an Oracle database rather than ODBC

The native drivers understand the database better than ODBC. ODBC is the lowest common denominator and uses "simple" statements to select data and then passes the load to the business intelligence server for aggregation and transformation.

The native drivers allow the load to passed from the application server to the database, and the database then returns only the result required.

A simple example would be to ask for a sales total.

Using ODBC

In ODBC the query pushed to the database would be to select the relevant sales. The sales then are returned to the BI server and the total is calculated and sent on to the dashboard. YOu may even find that all the sales records are returned to the BI server, then filtered for the relevant records, before any aggregation takes place.

Whereas:

Using OCI

Using a native driver helps the OBI server understand what the database, where the data resides, is capable of. Just how much complexity can be pushed down and passed to the database, relieving the load on the OBI server.

You will find filters, aggregations and complex calculations appearing in the physical queries being passed down with the native drivers (eg. OCI for Oracle). I have examined some queries in the process of 'debugging' various parts of the repository and been surprised with new ways and functionality in SQL that the OBIEE server passes down. I have been writing SQL queries for a good many years and OBIEE still finds ways to expand my knowledge.