"Disabling" a connection pool does not appear to be working in OAC 12.2.5.1.0

Mark.Thompson

I was under the impression that OBIEE uses the first listed and available connection pool when executing queries, so that if I set No Access permission for the first listed connection pool it would not be used at all.

However, that is not the case.  This connection pool IS being used.

My connection pool list looks like this:

As noted in the first screenshot, the Prod connection pool is tagged as No Access for every role.  Therefore, I would expect the Dev connection pool (which is tagged as Read-Only for all roles) to be used.  That is not happening.  There are additional columns in the Dev database and the RPD.  When I attempt to access those columns in Answers, an error message tells me that the columns do not exist.  But if I delete the PROD connection pool, the Answers query runs just fine. This tells me that OBIEE is running the query against the first connection pool irrespective of the fact that it appears to be disabled for all roles.

The Prod connection pool looks like this:

These connection pools are in my Dev RPD.  My goal is to be able to "enable" or "disable" the first connection pool so that I can easily run queries against either Prod or Dev, without needing to change the credentials constantly within a single connection pool.  With just 6 clicks I can either enable or disable the first connection pool, upload the RPD, and be ready to roll.

What might I be missing?  We are on Oracle Analytics Cloud, version 12.2.5.1.0

[Deleted User]

Hi Mark,

I haven'r tried that specific option in the latest OAC yet but a question: Why aren't you making the DSN dynamic? It's a common source-switching approach.

Cheers

Mark.Thompson

Christian, your insights are, as usual. spot on.  We were just trying to do something quick and dirty, but dynamic DSN is certainly the way to go.

As for the functionality that I mentioned, it turns out that it DOES indeed work - but not for members of the BI Service Administrator role.  It works well for members of "ordinary" roles.  It appears to me that the BI Service Administrator role always uses the first connection pool, irrespective of whether or not it is enabled for access.  I just ran an analysis, signed in as a BI Service Administrator, with these permissions for the Prod connection pool.

The session log shows this:

Sending query to database named OracleCloud (id: <<7473475>>), connection pool named Warehouse Connection Pool - Prod, logical request hash 3c451ebc, physical request hash 66f0c0f5:

And I am seeing data that only exists in the Prod database.  So it seems that members of the BIServiceAdministrator role are not included in the evaluation of Connection Pool permissions.

[Deleted User]

Mark.Thompson wrote:As for the functionality that I mentioned, it turns out that it DOES indeed work - but not for members of the BI Service Administrator role. 

Hah put that on the every growing list of things "UNLESS you're talking about BIAdministrator" :-)