OBIEE Error: [nQSError: 27023] Ambiguous Connection Pool

Problem

With OBIEE, sometimes when you attempt to create a new DB request on a database connection, you would receive the following error. 

error : Odbc driver returned an error (SQLExecDirectW).
error : State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27023] Ambiguous Connection Pool object: "Connection Pool". (HY000)
error : SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "Connection Pool"

 But at the same time, if you create an analysis/prompt/filter with tables that belong to the same connection pool, it would work fine.

Cause

 The cause of this error can be understood from the very statement of the error thrown - "Ambiguous Connection Pool". It simply means, you have two [or more] connection pools in the RPD with the same name that you are using to query the database and OBIEE is unable to find out which one to be used.

Solution

To resolve this issue, open your RPD file, rename the problematic Connection pool to something else or simply append ‘1’ to it. Now save the connection pool, create a fresh DB Request with this new name, bingo, there it works!

And if you still wonder why it doesn’t throw an error upon using the tables of the very same ambiguous connection pool, here is the reason. All tables you have in the presentation layer have a link back to the physical tables in the physical layer which will be mapped against its corresponding connection pool. So, even though there is more than one connection pool with same name, this lineage helps BI Server to figure out the right connection pool to be used which is not the case when you create a direct DB request.

Troubleshooting Informatica pmrep and pmcmd issues

Quite often we have people complaining about the errors thrown by Informatica pmrep and pmcmd commands and that they are able to connect to the Informatica repository from the client tools but unable to do so from the terminal or cmd prompt. The solution to this is pretty simple though. All that you need to blame is the environmental variables that have been set [or unset] in the machine.

Problem

Upon calling pmrep and pmcmd from the cmd prompt or terminal, you may end up receiving the following error.

$ pmrep

Could not load program pmrep:

        Dependent module libACE.so could not be loaded.

Could not load module libACE.so.

System error: No such file or directory

$ pmcmd

Could not load program pmcmd:

        Dependent module libpmasrt.a could not be loaded.

Could not load module libpmasrt.a.

System error: No such file or directory

Solution

Unix based machines

1. Check if the LIBPATH variable is set correctly.
The LIBPATH env variable must include “<INFA_HOME>/server/bin” path.
(You may check this by typing echo $LIBPATH in terminal window)

2. If the variable doesn’t include the above said path, set it in ‘.bash_profile’ [or  ‘.profile’ whichever is applicable based on the operating system]

3. Ensure PATH variable includes LIBPATH
The PATH variable should include either LIBPATH or “<INFA_HOME>/server/bin” path.

 

 4. Save and source the file.
       Type “source .bash_profile” [or “. .profile”]

5. Now type pmrep or pmcmd, it has to work.

Windows Machines

1. Check if the LIBPATH variable is set correctly.
The LIBPATH env variable must include “<INFA_HOME>/server/bin” path.

Or echo %LIBPATH% 

2. If the variable doesn’t include the path, set it as shown below.

3. Add “<INFA_HOME>/server/bin” to PATH variable as well if it’s not present already.

4. Now type pmrep or pmcmd and get going!

Default Static ports of OBIEE

Static & Default OBIEE Ports

 If you are a OBIEE beginner and googling for the default static ports used by the prime OBIEE components, here they are.

S.No	Component	Default Port
1	WEBLOGIC
1.1	Domain	7001
1.2	Oracle WLS BIEE Managed Server	9704
1.3	Oracle WLS BIEE Managed Server SSL	9804
1.4	Node Manager	9556
2	OPMN
2.1	Oracle Process Manager Local	6700
2.2	Oracle Process Manager Remote	6701
2.3	Oracle Process Manager Request	6702
3	BIFOUNDATION
3.1	Oracle BI Server	9703
3.2	Oracle BI Server Monitor	9701
3.3	Oracle BI Presentation Services	9710
3.4	Oracle BI Scheduler	9705
3.5	Oracle BI Scheduler Monitor	9708
3.6	Oracle BI Scheduler Script RPC	9707
3.7	Oracle BI ClusterController	9706
3.8	Oracle BI ClusterController Monitor	9700
3.9	Oracle BI JavaHost	9810
4	ESSBASE
4.1	Essbase Agent	1423
4.2	Essbase Studio	5300

You may also find this information from the “StaticPorts.ini” file that comes with the OBIEE installation package.

Kindly note that these apply for Enterprise Installation and are subject to the availability. Hence they are likely to vary while the default ones are already in use.