How to Use Oracle Analytics Cloud (OAC) External Database Connections to “Lift and Shift” the RPD

When using Oracle Analytics Cloud (OAC) with an on-premises data model, the normal process is to develop on a local RPD, and then to “lift and shift” the RPD to the cloud environment. This works fine in a cloud environment where there is a single cloud database to access. However, in a typical development environment, there may be multiple instances of OAC/database pairs in a DEV/TEST/PROD configuration.

Managing the RPD physical connection pools in that type of environment typically requires either manually editing them each time an RPD is migrated between DEV and TEST or TEST and PROD, or else developing a script to apply the proper changes to the connection pool for usernames, passwords, and connection strings.

OAC offers a way around that process via a concept known as “Database Connections,” which creates a named connection to a database cloud environment.  These connections are required when using the simple data modeler within OAC but are also available for “lift and shift” RPDs as well.   By creating the database connection in each of the development, test, and production OAC instances, the RPD can then reference that same name in the physical layer.  When the RPD is deployed via “lift and shift” to any environment, the reference to the named connection will automatically point to the correct database.

The steps involved to enable this capability are very simple.  First, create the database connection in the OAC Console.  Click on “Console,” then “Service Administration,” and then “Manage Connections:”

On the “Connection” screen, enter the name of the connection.  This is the common name used across all of the OAC instances and is referenced in the RPD.   Fill in the details for each specific database instance that applies to the environment where the connection will exist.   For the “Connect Using” option, there are three choices: “Host, Port, and Service Name;” “Host, Port, and SID;” or “TNS Connector Descriptor.” Use the one that is suited for the cloud database instance.  There are also options for providing a more secure connection using SSL if necessary.

After entering the details, click the “Test” button to confirm they are correct.

Repeat the same steps in each of the other OAC instances, using the same connection name.

The next step is to modify the RPD to reference the new connection name in a physical connection pool.  The assumption is that all the table objects and joins have already been configured using a standard database connection.

In the Oracle Analytics Cloud Developer Client Tool (also known as the RPD Admin Tool), navigate to the “Physical Layer” pane, and open the “Connection Pool” dialog for the database.   In the “Name” field, enter a name for the data source.   Select the “Externalize Connection” check box.  In “Connection Name,” enter the name of the database connection exactly as defined in “Console” previously.

One important note: When you choose to externalize a connection, several options on the dialog don’t apply. “Data Source Name;” “Shared Logon User Name;” and “Password” will be supplied from the OAC connection.

Save the RPD and follow the normal steps for “lift and shift” by replacing the data model via the “Service Administrator” console page.

The same RPD can now be deployed across all OAC instances without modifying the connection pools.

Have any questions about this process?  Send the Performance Architects team a note at or leave a note below and we’ll be in touch shortly with a response.

#database #oracle #OAC #BusinessIntelligence #BI #RPD #OACS #OracleAnalyticCloudOAC #OBIEE #analytics #Businessanalytics #businessintelligenceBI #OracleAnalyticsCloudOAC