5 Replies Latest reply on May 5, 2017 12:17 PM by saketsrv

    OBIEE 12C Connection Pools




      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? 


        • 1. Re: OBIEE 12C Connection Pools
          Joel Acha

          Your suggestion would work. You could also create synonyms for example of the RM tables in then IM schema. This way, you could use a single connection pool.


          Also, have you tried using the require fully qualified table name option in the connection pool?- https://docs.oracle.com/cd/E29542_01/bi.1111/e10540/conn_pool.htm

          1 person found this helpful
          • 2. Re: OBIEE 12C Connection Pools

            Yes,If you need to use 2 different username/password for two different schema, you need to create 2 different database objects in the physical layer.

            1 person found this helpful
            • 3. Re: OBIEE 12C Connection Pools

              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

              1 person found this helpful
              • 4. Re: OBIEE 12C Connection Pools
                Gianni Ceresa


                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

                • 5. Re: OBIEE 12C Connection Pools

                  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.

                  Re: Query in OBIEE using wrong connection pool



                  1 person found this helpful