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

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.

Updates

Last Validation: September 18, 2018 for OAC Client version 18.3.3

Recent Validations:       July 24, 2018 for OAC Client version 18.2.1

May 4, 2018 for all OAC Client versions connecting to ADWC 18.1.4

Updated:

October 3rd, 2018 to include the Externalize connection option

June 7th, 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

Versions Used

OAC Developer Client Tool 18.1.3 (12.2.4.1.0), 18.2.1 (12.2.4.1.0), and 18.3.3
Windows 7 SP 1

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 >”)))

The examples in this post use a CMD window.

Start a CMD window.

Set the TNS_ADMIN environment variable to the location of the client credentials folder to assist client tools in locating the network configuration files.

Note: Use the Control Panel to set the TNS_ADMIN variable as a System Environmental Variable for future sessions.

SET TNS_ADMIN=< 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

Note: Ensure the file permissions allow the Developer Client Tool to read the files in the directory. The easiest way in a development environment is to grant read access to everyone.

Set the OAC_HOME variable to the install directory.

SET OAC_HOME=< Your OAC Install 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 %TNS_ADMIN%\*.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

Creating 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.

Importing 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, Uncheck Foreign Keys, and click Next.

In the select Metadata Objects pane, Select any one schema and table, e.g. SH and CHANNELS, 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 the table and selecting Update Row Count. The row count appears.

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

Preparing the RPD for uploading to OAC

Using the Externalize Connection Option

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

Notes:

The Data Source Name, User name, and Password may be left as-is but are not used.

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 table from the Physical pane onto the ADW business model. It appears below the model.

Drag the table a second time from the Physical pane onto the ADW business model. It appears again with a suffix of #1.

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

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

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

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 posts Connecting Oracle Autonomous Analytics Version 4 to the Autonomous Data Warehouse and Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse for details on enabling the RPD connection in OAC.

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.

References

Autonomous Data Warehouse

Analytics Cloud

Preparing to Connect to the Autonomous Data Warehouse

Understanding Customization and Administration

Developer Client Tool for OAC

Creating a New Oracle BI Repository

Connect Descriptor Descriptions

Connecting to a Data Source Using an External Connection

Connecting Oracle Autonomous Analytics Version 4 to the Autonomous Data Warehouse

Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

 

 

 

Comments

  1. Hi, we have followed the steps mentioned in the blog but getting an error while Creating a Physical Database in step “Creating an ADWC Data Source in the RPD”.

    Summary of steps we followed:-

    1) Updated sqlnet.ora file with location of the client credentials folder
    2) Set TNS_ADMIN environment variable
    3) Installing Oracle Instant Client for Windows
    4) Setting PATH variable as a System Environmental variable.
    5) We are using the same admin tool mentioned in the blog.
    6) Renamed oci.dll in the mentioned location

    We are able successfully connect to ADWC DB from sqlplus but not able to establish connection in the RPD.

    error message is

    The connection has failed
    [nqSError: 17014] Could not connect to Oracle Database
    [nqSError: 17001] Oracle Error Code:12557,message:ORA-12557:
    TNS:protocol adaptor not loadable
    at OCI call OCIServerAttach.
    :

Add Your Comment