Connection pool properties in OBIEE11g

Connection pool properties:

 

Name:  

The name for the connection pool.A name is assigned automatically for connection pools created upon import

Permissions: 

This option we can use to assign permissions for individual users or application roles to access the connection pool.
This is feature is not intended to be used for data access security.For example connection pool permissions do not protect cache entries.

call interface: 

Identifies the application programming interface(API) with which to access the data source.Some dstabases can be access using native AIs, some use ODBC, and some work both ways.

Maximum connections: 

The maximum number of connections allowed for this connection pool.The default is 10.
The maximum value we will decide by the database model and the configuration of hardware for the computer on which the database runs, as well as the number of concurrent users who require access.

make sure to set 'Max connection' parameter appropriately.You can use following formula to calculate appropriate connections.

Max connections = Total number of users * 0.04 * Maximum number of reports on a Dashboard

Require fully qualified table names:

When this option is selected, all the physical query request will sent to database with fully qualified names.

For example: If you import DEPT table from DBO schema from ALH databese ...then qualified table name like
          select column_name from ALH.DBO.DEPT

without this option query request will sent to database like... select column_name from DEPT

Data source name :

This is nothing but TNS name of your database where Data warehouse user resides. It needs a valid logon information to connect to database.

Shared Logon :

 If this option is checked, it enables all users whose queries use the connection pool to access the database using the same user name and password.
If  not ..connections through the connection pool use the database user id and password specified in the user profile.

 Enable connection pooling:

It allows a single database connection to remain open for a specified time for future query request.Because of this connection pooling saves overhead of opening and closing a new database request for every query request.If this option is not selected each query sent to the database will open a new connection and it will impact on performance of the reports.

Timeout:

This parameter specifies time to remains open database connection after completing the request. By this time future requests uses this connection instead of opening new connection.

If you set this parameter value to zero (0),connection pooling will be disabled and it opens new connection to every request.

Use multi threaded connections:

When this option is checked Oracle BI Server terminates idle physical queries, these idle physical queries consumes memory.

Parameters supported:

If this option is selected, all the database parameters mentioned in the feature tab of the database object of the physical layer are supported by the BI server.


Isolation level:

Isolation level will controls the transaction locking behaviour.

There are 4 types of  levels.

1) Dirty Read

When this option is set,it is possible to read dirty or uncommitted data.

2) Committed Read

When this option is set, It will not read uncommitted data or dirty data.

3)  Repeatable Read

It places locks on all data that is used in query and prevent users from updating data into the data set.

4) Serializable 

It places a range locks and prevent users from updating or inserting rows into the data set until the transaction is complete.


Connection Scripts :

Click on Connection Scripts to learn