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.