OBIEE - Connection Pool

Bi Server Architecture With Client

About

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

10G 11G
Obiee Connection Pool Obiee11g Connection Pool General

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

For Report Queries, only the first authorized connection pool in each database is used to perform the queries.

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

Maximum connection

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.

Max Connections Formula for Connection Pools The default value of 10 is usually too low (it will cause queuing) but setting it too high will eat up memory

Approx 1024 kb of server memory is consumed per connection

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.

The sum of all of the 'Maximum connections' in a repository cannot exceed 800.

Call interface

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

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

OBIEE supports accessing:

  • ODBC 2.0 and 2.1 compliant databases/data sources for 8-bit character sets.
  • ODBC 3.5 compliant databases/data sources for UTF-16 (unicode) character sets.

Execute Queries Asynchronously

10G only

. “Execute Queries Asynchronously” parameter in the connection pool, this defines a parameter of our communication with the 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.
  • 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.

Multithreaded connections

When multithreaded connections is:

  • selected, Oracle BI Server terminates idle physical queries (threads).
  • not selected, one thread is tied to one database connection (number of threads = maximum connections).

Even if threads are idle, they consume memory.

The parameter DB_GATEWAY_THREAD_RANGE in the Server section of NQSConfig.ini establishes when the Oracle BI Server terminates idle threads. The lower number in the range is the number of threads that are kept open before the Oracle BI Server takes action. If the number of open threads exceeds the low point in the range, the Oracle BI Server terminates idle threads.

For example, if DB_GATEWAY_THREAD_RANGE is set to 40-200 and 75 threads are open, the Oracle BI Server terminates any idle threads.

Shared logon

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

DSN

The data source name is

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.

Enable Connection Pooling

The database connection remain open for the specified amount time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query.

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 :

Obiee Connectionpool Security

Documentation / Reference





Discover More
Card Puncher Data Processing
Exalytics - Timesten Configuration

Timesten on Exalytics To set the resource limits for TimesTen: Make the following changes and additions to the /etc/security/limits.conf file: Make the following shared memory kernel settings in...
Bi Server Architecture With Client
OBIEE - Physical Layer

The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information the physical data sources. The most common way to...
Obiee 11g Connection Pool Connection Script Timesten
OBIEE - Aggregate Persistence (Script)

The Aggregate Persistence functionality is the ability to: create and populate aggregate tables, and map them to the metadata repository in order to use the aggregate navigation capability of the...
Bi Server Architecture With Client
OBIEE - BI Server (OBIS|nqsserver|nqs)

OBI Server is also known as: OBIS (Oracle Business Intelligence Server) or nqsserver (The process name) OBI Server is principally a query and analysis server software: that can integrate data...
Obiee Bi Server Patch
OBIEE - BI Server Patch (automatic update with a patch file)

An alternate way of updating the RPD manually is to script it with an XML patch file. In this approach, you don‟t need to open the RPD through an Admin tool. Instead, the values of the variables...
Obiee Webcat Critical Folder
OBIEE - Connection Pool Change (Username, password)

method to change the connection parameter of a connection pool from a rpd file (password, ...) Example: list...
Obiee Connexion Alter Nls Date Format
OBIEE - Connection Script

The connection pool of the physical layer gives the possibility in the Connection Script tab to execute a command script: on connect before query after query on disconnect You can call...
Bi Server Architecture With Client
OBIEE - EXECUTE PHYSICAL CONNECTION POOL Statement

The EXECUTE PHYSICAL CONNECTION POOL Statement permit you to execute Physical SQL statement from a BI Server Client where: myConnectionPool is the fully qualified name of a connection pool (ie...
Obiee 11g Connection Pool Query Only Selectable
OBIEE - Initialization Block

This option will show you all connection pool that are not the first one. This option is designed to check the Initialization block who hold security/important variables. In other word, if the...
Obiee Login Please Wait
OBIEE - Login

In 11g: the login page is located here: Oracle_HOME/bifoundation/web/msgdb/pages/common/signin.html and the custom messages here: Oracle_HOME/bifoundation/web/msgdb/l_en/messages/logonmessages.xml...



Share this page:
Follow us:
Task Runner