OBIEE 12C Connection Pools

Question

Hi,

I have a question on the physical layer connections on OBIEE 12C repository.  I have an Oracle database instance ORCL and there are two schemas: IM and RM.  So in the repository I created a database called ORCL and two connection pools, IM and RM.  Each schema is uses a different set of credentials.

When I run an analyse for IM data, it runs fine.  But when I run an analyses for RM data, it errors out saying, "ORA-00942: table or view does not exist".   Upon analyzing the logs, I noticed the RM analyse is using the IM connection pool.  Which explains why I'm getting the error.

Should I have created two separate database in the physical layer, ORCL-IM and ORCL-RM and each one only one connection pool?

saketsrv · Answer

Similiar issue like yours has already been discussed earlier on this forum and Gianni has already provided the clarification.Please have a look on below thread as well.

Gianni Ceresa · Answer

Hi,

You got many pieces of info above already but I guess it's good to go back on how things works first ...

A database object in the physical layer (in your case "ORCL") can have one or many connection pools. The standard case is that all the data always come from the first connection pool. So why does OBIEE allow you to add many more if they can't be used like you tried to do? Because it's a good practice to use a different connection (even if sharing the same credentials) for variables init blocks to avoid the login to be locked because the connection pool it's used at its full capacity. So you generally have the first connection pool for data, and a second one for init blocks, where you can set a different (bigger) number of connections etc. to make the login process as smooth as possible while keeping your data connection pool configured in a way to not kill your database (so maybe a lower number of concurrent connections as a init block query is generally a really simple select returning 1 row while an analysis can execute a query of few thousands lines of SQL returning Gbs of data).

So as other said in your case you must have 2 database objects, one for IM and one for RM. And as said again you can make links between tables of different databases in the physical layer (that's also how you can connect an Excel source, with a database source and an Essbase cube for example).

But what is the issue doing that? The BI server will start acting as a database, because it will send a piece of query to your IM connection, a piece of query to your RM connection and then the BI server end up merging the results together and returning you the unified result. If it's a small query you will maybe not see a difference, if it's a huge query dumping lot of data you will see huge tmp files and a nice load on the BI server and a poor performance: the BI server is smart but isn't a database ...

So what most people generally do in this case is to grant select access to both schemas IM and RM to the same user, so with a single "ORCL" database object and using a single connection pool with a single credential you can have objects of both schemas (a bit like your screenshot but with a single connection pool). In that case the BI server will push down to the database the full query and not to any work as everything comes from the same connection pool.

Just keep in mind that when having objects from multiple schemas in the same database you better check the "fully qualified table names" checkbox in the connection pool to avoid ending up on the wrong table by mistake (because same name but different schema).

That's it

3365160 · Answer

I encountered the same problem on 11g and 12c

I advise you to create 2 databases in the physical layer and a single connection pool for each database.

After you can make the joins you want between the tables