We have designed an OBIEE RPD where the physical layer is based upon a single Oracle database, however, there are multiple instances (TEST, PROD ...etc) of this database structure.

While we require most users to report against the PROD instance, it is also a requirement to occasionally be able to run the same reports against the TEST database.

While, clearly, it would be possible to temporarily amend the properties of the connection pool in the RPD to point to the TEST database, and then switch it back to PROD afterwards, this would mean that all users' reports would run against TEST while the connection was switched.

Is there a way of defining multiple alternative connection pools in the RPD and for the user id of the logged in user to determine which connection is used (e.g. so that , for example, the majority of users would run reports using a PROD connection, with the exception of a user called Test who would always use the TEST connection)?     We have attempted this by creating two connection pools and by setting differrnt access rights to each connection based on application role.  However, OBIEE always appears to attempt to use the first conection pool for the data connection.  if access is denied to the application role of the logged in user then it does not appear to try to use the 2nd conection pool, but instead fails.

I am beginning to think that the only way to achieve what we need will be  to create two separate instances of OBIEE, one for PROD and one for TEST ?