Force reset of connection pool? or Switch among several Oracle schemas?

Question

Business requirement: map OBIEE user to one or more oracle schemas.  This is to control data entitlement by leveraging pre-existing architecture.

Specific question: I have configured a connection pool that uses a session variable to supply the oracle user/schema. I want to let the OBIEE user change the session variable to point to a different schema, then proceed to query against the selected schema.  How can I "reset" the connection pool after the OBIEE user changes the session variable? E.g. is there a analytics/saw.dll?resetConnection  action or similar? Must the OBIEE  user logout, then login to force a connection reset?

Is there another, best practice way to do this, to let an OBIEE user switch among several authorized oracle schemas?

rmoff · Answer

This is to control data entitlement by leveraging pre-existing architecture.

I would be inclined to stick to one Connection Pool per schema, and use Logical Table Sources to do the data security. Off the top of my head and without the Administration Tool in front of me, I'd look at a session variable-based WHERE predicate in the LTS fragmentation query to force its use/avoidance.

This is a more "proper" way of viewing the problem (rather than trying to 'pretend' to OBIEE there's only a single physical source, which there isn't), and thus might be more fruitful and resilient than hacking around in undocumented ODBC procedures etc. And for heaven's sake, stay away from Writeback, that just sounds nasty.

Bear in mind, even if you get a 'hack' to work, if it's based on undocumented process/code/calls, there's no guarantee that it won't break at any time, and at that point you're be up the creek with no support.

Probs · Answer

There is one ODBC function to reset the connection pool:

{call NQSResetConnections('Database','Connection Pool');}

I have not tried it but I assume it will reset it for all the users. Kindly test it out thoroughly before finalizing the design.

Alternate approach would be to use a write-back to store the schema details in the DB and then populate this in your session variables. This will require a logout & login.

Hope this helps..!