Sunday, January 18, 2009

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


Christian Berg said...

John, I really like you, but for that post I think I have hate you. Not something I need to read after a weekend spent in the office thanks to EXACTLY your real-world definition (strike that, it's actually worse). ;-)


Robert said...

Do you know how OBIEE will function when more than one connnection pool exists. I am thinking about solving the problem of multiple data sources Dev/Test by creating 2 connection pools then creating two accounts for our dev/test users then define 2 groups one for dev one for test then assign a users dev account to the dev group and the dev connection pool to the dev group. I would like to hear your thoughts on that approach and any other suggestions you might have.

John Minkjan said...

Hi Robert,

Using two connection pools should work fine. Just be absolutly sure that member are only in one group. Also most development streets have physically seperated network connections which might cause a problem.