OBIEE 11g Switching Source DB Depending On Logged User

Abstract 

This is an attempt to share a document on how to switch source database depending on a logged user solution. This article is based on tech note published by Oracle for OBIEE 10g, I don’t see any reason why this can’t be used for OBIEE 11g.

Solution

1.    Create table DB_USERS in any db with the following columns:

Login_Name - BIEE user name
Dbname - Database name
Dblogin - Database User name
Dbpassword  - Database Log in password

2.    Populate this table as per your requirements. Create a connection pool in Physical layer which connects to this table (via database)

3.     Create three Non-System Session Variables: dbname,dblogin,dbpassword. Create an initialization block with the following SQL Query:

SELECT dbname,dbuser,dbpassword from DB_USERS where Login_Name=':User' and populate the above created variables. Set the connection pool of the initialization block which was created in step 2.

4.    Now for the connection pool for which dynamicity is required as per the logged in user, in connection pool of that database in physical layer set the following properties to:

Data Source Name: VALUEOF(NQ_SESSION.dbname)
User Name: VALUEOF(NQ_SESSION.dbuser)
Password:VALUEOF(NQ_SESSION.dbpassword)

There are number of possibilities e.g. using same BI instance for UAT and SIT with different data sets based on Login_Name. Setting up a preferred group of users to have access to high availability resources on database is one of the possibilities I can think of.