X

Best Practices from Oracle Development's A‑Team

Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse

Last Validation: February 12, 2020 for OAC Client version 105.5 and OAC 105.5

Introduction

This post details the steps required to create an Autonomous Data Warehouse (ADW) data source connection using the Oracle Analytics Cloud (OAC) Developer Client Tool on Windows. It also prepares a complete RPD ready for uploading to both Autonomous and Classic OAC.

For additional details about ADW, visit the Autonomous Data Warehouse page. For additional details on OAC, visit the Analytics Cloud page.

Validations

February 12, 2020 for OAC Client version 105.5 and OAC 105.5

October 14, 2019 for OAC Client version 105.4 and OAC 105.4

July 16, 2019 for OAC Client version 105.3 and OAC 105.3

April 2, 2019 for OAC Client version 105.2 and OAC 105.2

February 26, 2019 for OAC Client version 19.1.1, OAC 105.1.2-3 and ADW 18.4.0.0

February 4, 2019 for OAC Client version 19.1.1

September 18, 2018 for OAC Client version 18.3.3

July 24, 2018 for OAC Client version 18.2.1

Updates

March 11, 2019 for clarity regarding variables.

October 3, 2018 to include the Externalize connection option

June 7, 2018, to use a TNS Connect Descriptor rather than a TNS Net Service name.

May 4, 2018, for OAC Client Versions 18.1.3 and 18.2.1

Topics

Preparing Windows for an ADW Connection in the RPD

Downloading and Preparing the OAC Developer Client Tool

Creating an ADW Data Source in the RPD

Preparing the RPD for uploading to OAC

Preparing Windows for an ADW Connection in the RPD

The steps described in the post Preparing to Connect to the Autonomous Data Warehouse provide the client credentials folder that contains the wallet and network configuration files, the Oracle Instant Client zip files, the latest Java JDK8 version and the Java JC8 files.

Edit the sqlnet.ora file in the client credentials folder and update the wallet location with the location of the client credentials folder e.g.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="< Your Client Credentials Folder >"))) e.g.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\ORACLE\ADW")))

The examples in this post use a CMD window.

Start a CMD window.

Set a CRED_LOC environment variable to the location of the client credentials folder.

SET CRED_LOC=<Your Client Credentials Folder>

Downloading and Preparing the OAC Developer Client Tool

You need a Windows 64–bit machine on which to install and run the Oracle Analytics Developer Client Tool. Refer to the Developer Client Tool for OAC for details on downloading the tool.

Download the installer executable and run it

Enter an oracle home e.g. C:\Oracle\12c\OAC2

Set an OAC_HOME variable to the install directory

SET OAC_HOME=< Your OAC Home Directory >

The Developer Client Tool expects the network configuration files to be in the %OAC_HOME%\domains\bi\config\fmwconfig\bienv\core directory

Copy the sqlnet.ora and tnsnames.ora files in the client credentials folder to the above folder

copy %CRED_LOC%\*.ora %OAC_HOME%\domains\bi\config\fmwconfig\bienv\core

Creating an ADW Data Source in the RPD

This post creates a new repository (RPD) for the ADWC connection. Refer to Creating a New Oracle BI Repository for details.

Start the Oracle Analytics Developer Client Tool

CD %OAC_HOME%\BI\BITOOLS\BIN
ADMINTOOL.CMD

Create a New Repository

From the File menu, click New Repository.

Enter a repository Name and a Location, select Import Metadata Yes, enter a Repository Password and click Next.

Import a Physical Database

In the Select Data Source pane, select the Oracle Call Interface (OCI) Connection Type, Enter a TNS Connect Descriptor from the tnsnames.ora file as the Data Source Name, the database User Name / Password and click Next.

An example Connect Descriptor is below. Refer to Connect Descriptor Descriptions for additional details.

(description= (address=(protocol=tcps)(port=1522)(host=< Your Host >))(connect_data=(service_name=< Your Service Name >))(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")) )

In the Select Metadata Types pane, accept the defaults and click Next.

In the Select Metadata Objects pane, select a schema and tables that are joined in the database, e.g., the SH schema and the  CHANNELS and SALES tables, from the Data Source View and move it to the Repository View.

On the Connection Pool screen, check the Require fully qualified table names and click OK.

Click Finish.

Expand the Physical Database and the Schema.

Test the connection by right-clicking on a table and selecting Update Row Count. The row count appears if you hover over the table.

Right-Click the Physical Database and Rename it e.g. ADW.

Preparing the RPD for uploading to OAC

Using the Externalize Connection Option

Optionally, the Externalize Connection option allows the connection pool to use a DV Console Connection created in OAC. Refer to Connecting to a Data Source Using an External Connection for details.

Connect to OAC and create a DV Console Connection. See Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse for details.

Open the ADW Connection Pool

Check the Externalize connection check box.

Enter the Connection name exactly as it is named in OAC (case-sensitive).

Click OK

Creating a Business Model

A simple way to test the RPD ADW connection in the cloud is to run an analysis against it in OAC. This requires a simple Business Model and Presentation Subject Area.

Right-Click anywhere in the Business Model and Mapping pane and choose New Business Model.

Enter a Name e.g. ADW.

Uncheck the Disabled box and click Ok

Drag the tables from the Physical pane onto the ADW business model. They appear below the model.

Right-Click the business model and choose Business Model Diagram > Whole Diagram

If the tables were joined in the database via a foreign key, then they appear joined in the business model.

If not, from the Diagram menu, Create a New Join by selecting the New Join tool and dragging the pointer from one table to another and Click OK.

Close the Business Model Diagram.

Creating a Presentation Subject Area

Right-Click the business model and choose Create Subject Areas for Logical Stars and Snowflakes.

Validating the RPD

From the Tools menu choose Show Consistency Checker

Click Check All Objects

Ensure there are no errors (warnings are OK) and click Close

From the File menu click Save. Click No to check global consistency

From the File menu click Exit.

Summary

The validated RPD can now be uploaded to OAC using the steps in Upload Data Models to OAC.

This post detailed the steps required to create an Autonomous Data Warehouse (ADW) data source connection using the OAC Developer Client Tool on Windows. It also prepared a complete RPD ready for uploading to OAC and OAAC.

View the post Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse for details on enabling the RPD connection in OAC.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley

 

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