Recent enhancements to OAC have made this post obsolete. The latest OAC4 post is Here. The OAC5 post is Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse
This post details the steps required to prepare an OAC instance for an RPD with an Autonomous Data Warehouse Cloud (ADWC) data source connection. It uses the RPD created with the steps in Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud.
Updated and Validated May 9, 2018 for OAC 18.2.1-1473 connecting to ADWC 18.1.4
Updated and Validated May 4, 2018 for OAC 18.2.1-1393 connecting to ADWC 18.1.4
Before you Begin
Preparing Windows to Upload Files
Preparing OAC for an ADWC Connection in the RPD
Uploading the RPD to OAC
Testing the RPD ADWC Data Source Connection in OAC
Oracle Analytics Cloud 18.1.3, and 18.2.1
ADWC 220.127.116.11 for OAC 18.1. ADWC 18.1.4 for all OAC versions above
Windows 7 SP 1
Note: If you have performed the steps in the companion post Creating a Connection in Oracle Analytics Cloud to the Autonomous Data Warehouse Cloud then proceed to the Uploading the RPD to OAC section.
This post uses PuTTY and PSCP SSH applications on Windows to connect to the OAC. The PuTTY programs may be downloaded from https://www.putty.org/
For details on using these tools refer to Connecting to a Compute Node Using the PuTTY Program on Windows. If you are not using Windows substitute SCP for PSCP and SSH for PuTTY.
Obtaining the OAC Private Key and Administrator Credentials
Obtaining the OAC Public IP Address
Ensuring the OAC SSH Port is Accessible
Backing Up the OAC Instance
Obtain the SSH private key file for the OAC instance from the OAC Instance Administrator and convert the private key file to the PuTTY .ppk format. SSH requires the usage of a public / private key pair associated with the instance. Refer to Accessing an Instance Using SSH for details.
To do this:
Run the PuTTYgen program
Load the private key file obtained from the OAC instance administrator
Save the private key using the defaults SSH-2 RSA and 2048 for Number of bits. Save as type PuTTY Private Key Files (*.ppk).
Obtain the OAC instance public IP address from the Oracle Cloud My Services – OAC Instance Overview page. Refer to Viewing and Managing Services for details.
PuTTY uses port 22 for access. This port should be open in OAC via an access rule by default. For details on how to view the instance access rules, refer to Managing Access Rules.
Backup the OAC Instance. For details refer to Backing Up a Service.
The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud download the client credentials folder that contains the wallet and network configuration files.
Uploading the Client Credentials to OAC
Preparing the Wallet and Network Configuration Files
Restarting the OAC Services
in a CMD window, change to the PuTTY directory and run the PSCP command to upload the file.
cd C:\Program Files (x86)\PuTTY
pscp –i <Your OAC SSH Private Key> <Your Client Credentials Zip File> opc@<OAC Public IP>:/tmp
Note: If there is a passphrase for the private key, you are prompted for it.
Start a SSH session in the OAC instance server using PuTTY, the OAC public IP address, and the OAC instance private key file (.ppk). Connect as the opc user. Note: You may receive a warning that access is provided read-only and to contact Oracle Support prior to making any changes. This is because changes may be undone when a patch is applied.
Connect as the oracle user using sudo:
sudo su – oracle
Create a variable for the BI Domain and stop the services. Note: The variable is used throughout the remainder of this post. If you log out and return, then recreate the variable.
Make a directory for the client credentials files, change to the directory, unzip the credentials and copy the wallet file.
cd /tmp/cc; unzip /tmp/<Your Client Credentials Zip File>
cp /tmp/cc/cwallet.sso $U01_DOMAIN_HOME/bidata/components/core/serviceinstances/bootstrap/oracledb/wallets/client
Make a backup and copy the tnsnames.ora file.
cp tnsnames.ora tnsnames.ora.orig
cp /tmp/cc/tnsnames.ora .
In versions 18.2.1+, the SSL_VERSION parameter is set to UNDETERMINED and does not need to be changed.
For versions prior to 18.2.1, make a backup and edit the sqlnet.ora file to comment out the line with the SSL_VERSION parameter . For details on using the vi editor refer to Using the vi Editor.
cp sqlnet.ora sqlnet.ora.orig
Save and exit and the file should look something like this:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /bi/domain/fmw/user_projects/domains/bi/bidata/components/core/serviceinstances/bootstrap/oracledb/wallets/client)))
Stop and Start the Services. Refer to Using Commands to Start, Stop, and View Status of Oracle BI EE Processes for details.
Note: Ensure the OAC services are running before issuing these commands.
Replace the data model in OAC with the new RPD.
Viewing the OAC Console
Creating a Snapshot
Replacing the Data Model File
Login to OAC with an administrative account and navigate to the Console page. The URL is like this:
https://<OAC Public IP>/dv/ui/home.jsp?consoletab=snapshotsTab&pageid=console
Create a snapshot to save the current BI Components such as roles, models, and the catalog. From the Snapshots tab, click New Snapshot
Enter a Description and click OK.
The new Snapshot shows in the list after it is created..
From the same Snapshots page, click Replace Data Model
Click Proceed without a virus scanner if prompted.
Choose the Data Model File to upload, enter the Password used to create the file on Windows and click OK
Note: After the data model is replaced, there may be a short delay until it is available.
Create a new analysis to test the RPD ADWC Data Source Connection.
Connect to the OAC analytics home page. The URL should be
https://<OAC Public IP>/analytics
From the New menu choose Analysis
Select the subject area created in the RPD e.g. ADWC - CHANNELS
Expand a table e.g. CHANNELS
Double-Click a column to add it to the analysis
Click Results and the connection returns the rows.
This post detailed the steps required to prepare an OAC instance for an RPD with an Autonomous Data Warehouse Cloud (ADWC) data source connection.
For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS, and Oracle A-Team Chronicles for OAC