OBIEE 10G/11G - persist connection pool
About
A persist connection pool is a database property that is used for specific types of queries (typically used to support Marketing queries).
In some queries, all of the logical query cannot be sent to the transactional database because that database might not support all of the functions in the query. This issue might be solved by:
- temporarily constructing a physical table in the database
- and rewriting the Oracle BI Server query to reference the new temporary physical table.
You can use the persist connection pool in the following situations:
- Populate stored procedures. Use to rewrite the Logical SQL result set to a managed table. Typically used by Oracle's Siebel Marketing Server to write segmentation cache result sets.
- Perform a generalized subquery. Stores a nonfunction subquery in a temporary table, and then rewrites the original subquery result against this table. Reduces data movement between the Oracle BI Server and the database, supports unlimited IN list values, and might result in improved performance.
The persist connection pool functionality designates a connection pool with write-back capabilities for processing this type of query. You can assign one connection pool in a single database as a persist connection pool. If this functionality is enabled, 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.
Articles Related
Privileges
In these situations, the user issuing the Logical SQL query must have been granted the Populate privilege on the target database.