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
John Minkjan is a principal BI-consultant and OBIEE Product Expert at Ciber in the Netherlands, the text of this article is also published on his personal blog: http://obiee101.blogspot.com/