Connection pool

This articles regroups all the articles and talk about the connection pool features of the BI Server such as:

• the connection script tab
• … see below

10G	11G

To automate connection pool changes for use in a process such as production migration, consider using the Oracle BI Server XML API (11g).

Init Block / Session Variables

It is recommended that you create a dedicated connection pool for initialization blocks.
Another thing to check is whether or not the init blocks are using the same connection pool that the queries are using. If so, best practices dictate that init blocks (especially authentication) be given their own connection pool, otherwise you might have a situation where nobody can authenticate because the init blocks are waiting along with all the other queries for a connection pool thread.
When a user logs one these Init Blocks are run against the database to populate the session variables, each will use up a connection. If you are using the same connection pool as the one used for your data queries, this means the limit of Max connections will be more quickly reached, and would result in new users being unable to log on as the Init Block queries are queuing up, and cannot be run when the user logs on.
So we recommend that you create dedicated connection pools for your session variables, and associate these new connection pools to the Init Blocks that populate your session variables. You can initially set the Max connections on these connection pools to 64.

Connection Pool Parameters

How to calculate the maximum connection parameter ?

The rule of thumb is to use the number of simultaneous users times the number of requests on a dashboard. Also important is to know how much connections can be handled by the database, which is specific per database system (like DB2 has to start agents, Oracle needs to configure #connections in the init.ora
The Maximum Connections setting governs the number of active queries on the db. When the limit is reached, then the system waits for a connection to become available. As a single request can generate multiple db queries, it is possible for a maximum connections of 64 to be reached with less than 64 users. Also, a dashboard can have lots of requests so it is possible for a few users to occasionally hit the limit.
The default value of 10 is usually too low (it will cause queuing) but setting it too high will eat up memory and the BI Server process may not even start.
As a “rule of thumb”, calculate it using the following theory:

• At any one time, 10-20% of your users will be logged in
• Of those users logged in, only 10-20% will actually be running queries
• There are a maximum of X reports on any one Dashboard Page

So if you have 1000 users in total and a maximum of 4 reports per dashboard page:

• Maximum Connections = 1000 x 0.2 x 0.2 x 4 = 160

Or in other word:

Maximum number of connections = 
  20% of concurrent users 
  * number of reports per dashboard.

Approx 1024 kb of server memory is consumed per connection

Call interface

The call interface represents the driver that you use to connect to the data source. You have three methods:

• ODBC
• OCI
• XML. Xml is a build-in drivers to be able to connect/read XML files in a specific format.

Execute Queries Asynchronously

“Execute Queries Asynchronously” parameter in the connection pool, this defines a parameter of our communication with the Oracle database. If this option is checked, the query is run asynchronously, and can be cancelled by the Analytics Server while the query is running, and using the same connection. If the option is not checked, the query is run synchronously and the Database will not receive the cancel call until the query has completed. Asynchronous mode is more efficient but it is less 'safe', as it is more complex to handle. It is useful to test your queries with the option unchecked (safer) ,as part of the troubleshooting.

User and Password

In place of the id and password, you can use the intern variable :USER and :PASSWORD for pass through.

Isolation Level

For ODBC and DB2 gateways only. Isolation Level :

• Committed Read

Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.

• Dirty Read

0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.

• Repeatable Read

Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.

• Serialization

Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

Shared logon

OBIEE - How to define one different database credential for each user (the shared logon) ?

DSN

The data source name is

• for ODBC, the data source name
• for OCI a connect identifier

Parameters Supported

If this option is not selected, and the database features table supports "parameters", special code executes that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database.

Support

Security

If you have this error :

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. 
[nQSError: 19007] The user does not have sufficient privilege to access the database ORCL. (HY000)
SQL Issued: SELECT B.DATA saw_0 FROM SH ORDER BY saw_0

It comes from the OBIEE connection pools security parameters from the database. Suppose that we have only one connection pool for a database, the image below show that nobody have been granted to access the connection excepted the Administrator :