* 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).
From either database go to Database Details -> Click on DB Connection -> From the Wallet Type dropdown - select Regional Wallet -> Click Download Wallet.
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.
(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.
Unzip the Wallet file - downloaded in Step 1.
From OAC -> Console -> Connections -> Replace the Wallet -> by uploading the Regional Wallet cwallet.sso file.
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.
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.
Click here for more A-Team Oracle Analytics (OAC) Blogs.
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.