OBIEE Multiple connection pools

In an ideal world the OBIEE development team has his own unlimited hardware, a DBA, an ETL guy/girl, a data architect / repository builder, some reportbuilders who really know the tool, a dashboard designer with a good feeling for DTP and off course a project leader. All these functions should be full time available or at least in the same room and all the noses should be pointing in the same direction.

In the real world the hardware is "very" limited. The DBA lives on a other continent. The ETL people have different office hours. The reportbuilders work from there home offices. The dashboard designer only has a budget of two hours a week and the project manager has twelve projects at the same time.

In this article I want to show you how you as a data architect / repository builder can help the DBA. Why ? The DBA (if he is any good) controls something very important: Resources. Stuff like CPU allotment, sorting memory size, cache settings on the DB etcetera. Since resource cost money he will limit them.

Most BI-Server setups use shared connections to the database. But if we "generalize" the connection users we can see several groups:

• Reportbuilders; needs access to all tables, can transport a lot of data during development, are in the habit of "killing" query's when they "take to long"
• Answers users: create there own "new" request each time.
• Dashboard users: Only use predefined query's
• BI-publisher: Can do a lot of query's in succession.
• WriteBack users: need insert/update rights on the DB.

The help the DBA ask him for five users named: OBIEE_R (Reportbuilders), OBIEE_A (Answers_users), OBIEE_D (Dashboard users), OBIEE_B (BI-Publisher) , OBIEE_W (WriteBack Users).

In your RPD create five connection pools:

Create five Groups:

Now set the permissions for the Connection Pool:

Don't forget to disable "Everyone".

Assign the users to there groups and check in TOAD or SQL Developer if the right connections are made:

This way you can ask your DBA to assign the right resources to every group instead of an average setting for every one.

Till Next Time

OBIEE Follow the sun / Time driven connection

One of our customers wanted a 'follow the sun' solution to connect to there international databases. Translated to the practice this means that they wanted to set a connectionpool based on the the local time. To achieve this we made a repository initationblock called "REP_IB_TNS" and a repository variable called "LOCAL_TNS_TO_USE".

In the datasource block we made a very simple query which determines which TNS to uses based on the time of the day:

We forced a refresh every 10 minutes by setting the "lifespan" of the block:

In the connection pool you make a reference by using the VALUEOF method:

n theory it takes a maximum of 15 minutes (10 minutes for "REP_IB_TNS", 5 minutes for the connection pool) to refresh all connections

Till Next Time

OBIEE Using ORACLE stored procedures part 3

If you are using the Multi User Development (MUD) be aware that the connectionscripts aren't updated when you check in.
You have to edit them manualy in the main repository.

Till Next Time

OBIEE Using ORACLE stored procedures part 2

In the connection pool you are able to define connection scripts in 4 events:

• "Execute on connect" -> Very usefull point for security logging events
• "Execute before query" -> Ideal to fill a global temp table or to do some execution logging
• "Execute after query" -> To close you execution loging
• "Execute on dissconnect -> close your security log

Sadly in the OBIEE documentation (10.1.3.x ) there is no info about these options... I did some research (mostly trail and error) on how to incoperate variables in these scripts. The syntax for this is different then in the rest of OBIEE.

All parameters have to be enclosed in single quotes:

• ':USER', ':PASSWORD'
• 'VALUEOF(REPOSITORY_VARIABLE)'
• 'VALUEOF(NQ_SESSION.SESSION_VARIABLE)'

If you want to pass something from the dashboard put in a session variable.

The execution string is build like

BEGIN SCHEMA.PACKAGE_NAME.PROCEDURE_NAME('PARAMETER'); END;

Don't forget the semicolons ';'

Till Next Time