Connection Pool is not appearing to choose for Init block

As you all know while creating a session variable/dynamic repository variable, we have to select the connection pool. Otherwise we can not accomplish the variable creation. If you are not able to see any connection pool to choose, like below

then you need to do small changes in RPD setting to get the connection pools.
Steps : In RPD, Open Tools->Options, then enable the option called "Allow First Connection Pool for Init Blocks".

Thats it. :)

Maximum Connections Parameter in Connection Pool

This parameter specifies the maximum number of concurrent connections allowed at any point of time. The default value for this parameter is 10.

For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool or, if no other connection pools exists, the connection request waits until a connection becomes available.

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Test and consult with your DBA to make sure the data source can handle the number of connections specified in the connection pool. 

In addition to the potential load  associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server startup. This raises the number of connections and increases Oracle BI Server memory usage.
 
Simple Calculation to determine the Max Connections for your Applications. 

Make sure the “Max Connections” parameter on your connection pools are appropriately set (not too low….not too high!). If in doubt, you can use the following formula which assumes that no more than 4% of your users will ever be logged on and running a report at any one moment:

Max Connections =   Total Users   *   0.04   *    Max Reports on a Dashboard 

So if you have 1000 users and you have no more than 4 reports on any one dashboard, then your  “Max Connections” should be set to 160. 

Note : Max reports on a dashboard includes reports and prompts.

Restricting user access to Connection Pool

We can restrict any user from accessing the Connection Pool using the Permission option in Connection Pool.

Just click on the Permission button, then you will get "Permission - Connection Pool " window. By default, this window will show only user Groups. To see users also, check the Show all users/groups option.

For each user and group, you can allow or disallow access privileges for an object by clicking in the check box to toggle among the following options:

• A check mark indicates that a permission is granted.
• An X indicates that a permission is denied.
• An empty check box indicates that a permission has not been modified. 

In the above picture, we can find one user group (Test) and two users (U1,U2). Now, let us deny access to the Group called Test.To deny access, just make the check box cross (X), as shown below.

All the users of that group cant access the connection pool. That is when the user runs a report, then he will get an error like : [nQSError: 19007] The user does not have sufficient privilege to access the database

Note : Assume that,
           you have created two connection pools(eg. CP1 and CP2) for a Data source in the physical layer,
           and you have a user called U1 and you have denied access to the connection Pool C1.
 When a user U1 requests connection, as the user don not have permission to Connection pool C1,
 OBI Server will route the request to the second Connection Pool C2. So, If you want any user to be                     denied to access the underlying data source, remove access to all connection poll, not any single connection pool.

Connection Pool

The connection pool is an object in the Physical layer, it contains information about the connection between the Oracle BI Server and that data source.

The Physical layer in the Administration Tool contains at least one connection pool for each database. When you create the physical layer by importing a schema for a data source, the connection pool is created automatically.

You can configure multiple connection pools for a database.It is recommended that you create a dedicated connection pool for initialization blocks.

About Connection Pools for Initialization Blocks

It is recommended that you create a dedicated connection pool for initialization blocks. This connection pool should not be used for queries.

Additionally, it is recommended that you isolate the connections pools for different types of initialization blocks. This also makes sure that authentication and login-specific initialization blocks do not slow down the login process. The following types should have separate connection pools:

• All Authentication and login-specific initialization blocks.
• All initialization blocks that set session variables.
• All initialization blocks that set repository variables. 

Setting Up General Properties For Connection Pools

Name : We can set any appropriate name for the connection pool based on its usage. Usage in the sense, there may be multiple connection pools for session variables, repository variables, authentication and log in purpose.

Call Interface : specify the interface through which you want the application to communicate with underlying database.To communicate with oracle databases select either odbc or native call interfaces (oci 8i/9i, oci 10g/11g). 

• when a odbc is selected "Data source name:" is the name of the System DSN in odbc data source administrator. 
• when a oci call interface is selected "Data source name:" is the TNS Name in the tnsnames.ora file.

3. Maximum Connections: This parameter specifies the maximum number of concurrent connections allowed.

The default is 10.

For each connection pool, you must specify the maximum number of concurrent connections allowed. After this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool or, if no other connection pools exist, the connection request waits until a connection becomes available.

Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Test and consult with your DBA to make sure the data source can handle the number of connections specified in the connection pool. 

In addition to the potential load  associated with the database resources, the Oracle BI Server allocates shared memory for each connection upon server startup. This raises the number of connections and increases Oracle BI Server memory usage.

Make sure the “Max Connections” parameter on your connection pools are appropriately set (not too

low….not too high!).    If in doubt, you can use the following formula which assumes that no more

than 4% of your users will ever be logged on and running a report at any one moment:

Max Connections =   Total Users   *   0.04   *    Max Reports on a Dashboard

So if you have 1000 users and you have no more than 4 reports on any one dashboard, then your
“Max Connections” should be set to 160.

Require fully qualified table names : Select this check box, if the database requires it. When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.

Eg: Assume that, there is a table called Emp in Physical Layer, which has imported from a default schema dbo of the database OLE.

• When we check the option Require fully qualified table names for connection pool, then the requests will contain the full qualified table name like - OLE.dbo.Emp.                                                                                                                                                select ename from OLE.dbo.Emp
• When we uncheck the option Require fully qualified table names for connection pool, then the requests will contain only table name itself like - Emp.                                                                                    select ename from Emp

Data source name : The drop-down list shows the System DSNs configured on your system. A data source name that is configured to access the database to which you want to connect. The data source name needs to contain valid log on information for a data source. If the information is invalid, the database log on specified in the DSN will fail. 

Shared logon :  Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.

If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).

If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.

 

Enable connection pooling :  Allows a single database connection to remain open for the specified time for use by future query requests. Connection pooling saves the overhead of opening and closing a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.

  Timeout (Minutes) : Specifies the amount of time, in minutes, that a connection to the data source will remain open after a request completes. During this time, new requests use this connection rather than open a new one. The time is reset after each completed connection request.

If you set the timeout to 0, connection pooling is disabled; that is, each connection to the data source terminates immediately when the request completes. Any new connections either use another connection pool or open a new connection.

How to get the Connection Pool name ?

Open Administration Tool.

In Physical Layer, Expand the Database, then double click on the Connection Pool.

In Connection Pool Window, you can see the name.

Shared Logon In Connection Pool

The Username and Password fields under the Shared Logon option in Connection Pool properties, allows the Administrator to specify the USERNAME and PASSWORD of the underlying Database.

Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password.

If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).

If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.