Set Up Persist Connection Pools

Sometimes all of the logical query can't be sent to a transactional database that doesn't support all of the query's functions. To resolve this issue, create a write back table and set a persist connection pool to tell the Oracle Analytics query engine to reference the new write back table.

Set a persist connection pool in the following situations:

  • Populate stored procedures - Rewrites the Logical SQL result set to a managed table.

  • 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 Analytics query engine and the database, supports unlimited IN list values, and might result in improved performance.

    In these situations, the user issuing the Logical SQL query must have been granted the Populate privilege on the target database.

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.

See Set up Write Back in a Connection Pool.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon and locate and double-click a database.
  4. In the database's tab, click Connection Pools.
  5. In the connection pools list table, click a connection pool to select it and then click Detail view to open the properties pane.
  6. Click Persist connection pool and choose a connection pool.
  7. In the connection pool properties pane, scroll to Write Back and set up the write back table.
  8. Click Save.