Sometimes in data warehouses, there would be more than one user created for the OBIEE application. This makes more sense in Teradata data warehouses. This is mostly done to so that any single user (Data base user id used in the connection pool) doesn’t run out of spool space.
Lets say we have three user id’s created the database DW_id1 , DW_id2 and DW_id3. If we have one connection pool in the RPD (for example using DW-id1) all the users logging into OBIEE would be using DW_id1 and all the queries would be run under that id. So there is as high chance of that Teradata user_id running out of spool space as the number of concurrent users increase, assuming they are running some heavy queries. Ideally we would like to distribute the queries send back from OBIEE to the Teradata database between these three user_id’s (databse user_id’s). Here I will discuss about a couple of approaches.
1) First Approach – Using a session variable in the connection pool for the user name. This is the most widely used approach.
I have three users created in the OBIEE environment (USER1, USER2, and USER3). As per our requirement USER1 should use DW_id1 , USER2 DW_id2 and USER3 should use DW_id3.
First up I create a table in my database.
| OBIEE_LOGIN | DW_LOGIN |
| USER1 | DW_id1 |
| USER2 | DW_id2 |
| USER3 | DW_id3 |
We now need a session variable created based on the OBIEE user_id which would be initialized to their corresponding DW_LOGIN. You will have to have a separate connection pool for the variable. Use the session variable created in the user name in the User Name text box in the connection pool properties. Once this is done every query by USER1 would be send to the database would be under DW_id1 and USER 2 DW_id2.
There are 2 major drawbacks to this approach.
a) We cannot guarantee that the database user_id’s would be evenly distributed at any given point in time. For e.g. if we have 100 users with each user id assigned one of the three database id’s assigned. The worst case scenario in this case would be that all the OBIEE users assigned to database id DW-id1 login at the same time. So you could have 33 OBIEE users using one database id and the other database id’s are not being used because those OBIEE users are not logged in.
b) Let’s assume that USER1 and USER2 have the same data security profile setup. In this case you would expect USER 2 to hit the cache created by USER1. This would not happen. To get a cache hit the logical query and all the session variables involved in that query would have to be an exact match. In this case the used_id for the database is a session variable and it is different for USER1 and USER2 and this would result in a cache miss.
So in theory if I could do something as to get rid of the session variable and introduce some sort of round robbing logic in terms of picking up the database user id I would have circumvented both the issues highlighted above.
2) Second approach – Using multiple connection pools.
First up
I create three connection pools since I have the database user id’s, one for each database id.
Use the appropriate User Names in TWO and THREE respectively.
Create a sequence in the DB.
CREATE SEQUENCE “BI_DW”.”SEQUENCE_LOGIN” MINVALUE 1 MAXVALUE 3 INCREMENT BY 1 START WITH 3 CACHE 2 ORDER CYCLE ;
Create three groups in the RPD, GROUP_ONE, GROUP_TWO and GROUP_THREE. Go to the permission tab in the connection pool and explicitly deny every group access to the connection pool. Then for Connection Pool one give read access to GROUP_ONE. Refer the picture below (GROUP_TWO)
Now create an initialization block (row wise) to initialize the RPD group “GROUP”. If you have an init block that already initializes “GROUP” then append the following query.
select ‘GROUP’,
case when
sequence_login.nextval = 1 then ‘GROUP_ONE’
ELSE CASE WHEN
SEQUENCE_LOGIN.NEXTVAL = 2 THEN ‘GROUP_TWO’
ELSE ‘GROUP_THREE’
END
END AS VALUE
from dual;
So what is happening right now is that the first user logging in would be assigned GROUP_ONE and the second one GROUP_TWO and third one GROUP_THREE and the fourth one to GROUP_ONE, since the max value for the sequence is 3. Now we have gotten rid of the session variable in the connection pool and no more cache misses. And the database user id’s would be evenly distributed in a round robin fashion.
Posts
