Connection Pools – Best Practices

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage
• Create a separate connection pool for execution of session variables
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Simplifying Migration Process – Changing Environment Specific Variables in RPD

 

When it comes to migrating repository file between environments (Dev – TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.

Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.

First, define repository variables that can be used in the connection pool.

 

 

Reference the DSN information in the connection pool.

 

Now create a control file SetVariables.txt with the environment specific values.

_______________

‘To Open rpd – Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd    Administrator     SADMIN

‘Setting OLAP DSN variable

SetProperty    ”Variable”    ”OLAP_DSN”    Initializer    ” ‘QA_DSN’ ”

‘ Setting OLAP DSN Username variable

SetProperty    ”Variable”    ”OLAP_DSN_USER”    Initializer    ” ‘QA_User’ ”

‘Setting OLAP DSN User QA database password

SetProperty “Connection Pool” “AppDW”.”Connection Pool” “Password” “QAPassword”

 

Save

Close

Exit

______________

 

Create one per environment.

In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt

 

And the repository is now prepped for the other environment (QA in this case).