X

Best Practices from Oracle Development's A‑Team

  • April 20, 2021

Connecting to multiple Oracle Autonomous Databases (in the same region) from OAC with an RPD

Jay Pearson
Consulting Solutions Architect, Business Intelligence

 Background

* This blog was last tested on OAC 5.9 + ADW 19c + ATP 19c *

This article walks through the steps to configure an Oracle Analytics Cloud (OAC) BI Repository (RPD) file to connect to both an Autonomous Data Warehouse (ADW) and an Oracle Autonomous Transaction Processing (ATP) database in the same region. These steps can be applied to connect to any other Oracle Autonomous Database (ADB) - as long as it is in the same region.

This blog mostly pertains to those using an RPD. If using a Data Visualization (DV) database connection, it is not necessary to use a regional wallet because it supports uploading individual client credential connections (i.e. cwallet.sso wallet file).

 Download Regional Wallet

From either database go to Database Details -> Click on DB Connection -> From the Wallet Type dropdown - select Regional Wallet -> Click Download Wallet. 

 Configure RPD Connection

The Developer Client Tool for OAC which is needed to configure the RPD can be download from here. It is recommended that the Client Tool version match or be higher than the OAC version. Connect either offline or in the Cloud. Previously published A-Team blogs document specific configurations required for the Developer Client Tool.

In the Physical Layer - Create a separate Database Connection for each database. Then create a Connection Pool for each Database Connection. It is good practice to re-name the Connection Pools so they have unique names that correspond to the Databases they are associated with.

Copy and paste the "Data source name" from the Regional Wallet tnsnames.ora file.

i.e.

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abc_adw_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

Check "Required fully qualified table names", if reporting off multiple database schemas or a schema that is different to the username in the Connection Pool.

In this example the Connection Pool connects as Admin. However, the reporting schemas are OAC_USER (ADW) and ATP_OAC_USER (ATP). 

Model, map, and present the data as required.

 Upload Regional Wallet

Unzip the Wallet file - downloaded in Step 1.

From OAC -> Console -> Connections -> Replace the Wallet -> by uploading the Regional Wallet cwallet.sso file.

 Upload RPD

If RPD opened Offline -> Upload the changes through Snapshots -> Replace Data Model.

If RPD opened in Cloud -> Publish changes through Developer Client Tool for OAC.

 Combined Analysis Example

It is possible to join the data in the RPD if desired and create a combined presentation table.

The physical SQL is ran individually on each database then joined logically within OAC. Therefore, this may not scale with larger datasets. A safer approach for larger datasets may be to keep the two data sources separate and drill from one to another using Analysis Action Links or DV Data Actions. Another approach could be to consider a database link as outlined in this article on Making Database Links from ADW to other Databases.

 Want to Learn More?

Click here for more A-Team Oracle Analytics (OAC) Blogs.

 Summary

This article walked through the steps to configure an Oracle Analytics Cloud (OAC) BI Repository (RPD) file to connect to both an Autonomous Data Warehouse (ADW) and an Oracle Autonomous Transaction Processing (ATP) database. These steps are not necessary if using OAC 5.9+ with a Data Visualization (DV) database connection. The audience for this blog is OAC customers using an RPD file - wanting to connect to multiple Oracle Autonomous Databases (ADB) located in the same region.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha