Query in OBIEE using wrong connection pool

yaseennawaz

Hi,

I have created two connection pools Connection Pool HR Analytics (Schema hrmodel)and Connection Pool Sales Analytics(Schema Sh) both coming from orcl database.I made two business models with each of the schema and there by two subject areas.When I am querying from HR Analytics subject area the query is running fine and rendering results.When I am querying from Sales Analytics subject area.It is throwing an error saying table or view doesn't exist.When i checked the log i found that it is pointing to "HR Analytics connection pool". I am using OBIEE 11.1.1.7.

Please let me know the reason for this behaviour.

Gianni Ceresa

Hi,

the reason of this behaviour is because you forgot (or ignored) how the tool work.

Where did you see that "orcl" identify the database and so you can just add as many connection pools pointing to various accounts? Where did you saw a link between a physical table and a connection pool?

Nowhere ....

Going back to the basics of RPD: "orcl" doesn't identify a database, it's just a random label you give to that database object in the physical layer.

The connection pool is the only one pointing to a given physical source (host, username and password).

All the objects inside the same physical database come from the same connection pool.

Multiple connection pools are only used when you want to source init blocks from a different connection pool than the one for data.

If you need to use 2 different username/password you need to create 2 different database objects in the physical layer. Physical joins between objects of these 2 different databases objects are still possible, but the join will be done by the BI server and not the database as objects will come from 2 different connections.

3410125

You should have one connection pool per database in RPD as you are only importing the different schema tables from the same database. Otherwise if you really require two connection, then better do another fresh meta data import in RPD.

yaseennawaz

Thanks Gianni.....This is clarified my confusion...

Gianni Ceresa

Just keep in mind there isn't a unique rule on how to model the physical layer: even if your tables come from the same database but 2 or more different schemas doesn't mean you must create a single physical database with a single connection pool in OBIEE. You maybe don't want to have a single database user having access to both of these schemas etc. It's all about the permissions your database account has or not. And it's often a good practice to have 2 connection pool when you need it for init blocks to set values of your variables. In this case keep in mind data will use the first one and in the init block you can explicitly choose the second one so you split connections and queries on these 2.

So if you got your answer you can maybe close the thread and mark as required? For now it's still This question is Not Answered. and it will help others finding answers to similar questions.

Gianni Ceresa

Just for the records (and the users opening this thread looking for answers to same/similar topic: This question is Assumed Answered. is misleading as it's really answered and the answer is posted above, even if it hasn't been marked as such ...