OBIEE 11g – Is it useful to define Persistent Connection Pool?

Does OBIEE 11g write back to database while reporting? Does it create temporary tables to support processing of report query?  Does it make sense to pushes down the processing to temporary tables rather than the BI Server memory?

This blog is an attempt to discuss the persistent connection pool and its usefulness. Within explanation I will also try to answer questions raised at the start of earlier paragraph.

Persistent Connection Pool

Yes OBIEE write back to database while generating report. This can be achieved by setting up a persistent connection pool.

A persistent connection pool is a database property that is used for specific types of queries e.g. marketing queries. It is a good practice to ship function to database rather than evaluating it at BI Server. The idea is to handover task to most powerful engine to do task in which the engine is best at.

All logical queries cannot be sent to the transactional database as the database might not support all of the functions in the query. For example RANK function may not be supported by database of certain flavor; in these cases this issue can be solved by creating temporary physical table in the database and rewriting the Oracle BI Server query to reference the new temporary physical table.

A sample for database and persist connection pool is shown below.

In short a persistent connection pool pushes down the processing to temporary tables rather than the BI Server memory. One cannot assign more than one connection pool in a single database and persistent connection pool

Usage of Persistent Connection Pool

I would prefer to use temporary creation of tables rather than using files at BI Server to store intermediate result before getting the final report output sourced from it. It is faster to use temporary tables than using temporary files.

It reduces data movement between the Oracle BI Server and the database, supports unlimited IN list values, and result in improved performance.

Following are the situations where usage of persistent connection pool makes sense.

• Sibel Marketing Server to write segmentation cache result sets – Populate stored procedures, to rewrite the logical SQL result set to a managed table

• Perform a generalized sub query which stores a non-function sub query in a temporary table and then rewrites the original sub query result against temporary table.

• For non-supported functions shift – use temporary tables to store intermediate results

Pr-requisites

User issuing the Logical SQL query must have been granted the Populate privilege on the target database.

The user name specified in the connection pool must have the privileges to create DDL (Data Definition Language) and DML (Data Manipulation Language) in the database.

Some interesting Stuffs

Sybase IQ and DB2 -  "IN CLAUSE" is  matter of concern as it inserts data in those temporary table as if it was inserting data, row by row (like INSERT INTO ... VALUES (...)).

The prefer option is to use persist connection option is to set it to an Oracle DB; it writes the temporary tables in a separate DB, and faster.

I hope you find this information useful.