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

Introduction

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

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

Updates

Last Validation: July 24, 2018 for OAC Client version 18.2.1

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

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

Updated May 4, 2018 for OAC Client Versions 18.1.3 and 18.2.1. It is strongly recommended that you download an 18.x version of the tool to avoid OCI issues that are in the 17.4.5 version.

Topics

Preparing Windows for an ADWC Connection in the RPD

Downloading and Preparing the OAC Developer Client Tool

Using Oracle Instant Client for Windows

Creating an ADWC Data Source in the RPD

Preparing the RPD for uploading to OAC

Versions Used

OAC Developer Client Tool 17.4.5 (12.2.2.0.30), 18.1.3 (12.2.4.1.0) and 18.2.1 (12.2.4.1.0)
Oracle Instant Client 12.2.x for 17.4.5
ADWC 18.1.1.0 for OAC Client 17.4.5, 18.1.4 for All, and 18.1.5 for OAC Client 18.2.1
Windows 7 SP 1

Disclaimer (Version 17.4.5 only)

This version of the OAC Developer Client Tool requires customizations that are not supported by Oracle Support. Refer to Understanding Customization and Administration for details.

Before contacting Oracle Support with an issue unrelated to ADWC, install the OAC Developer Client Tool into a second Oracle Home directory and ensure the issue remains.

The unsupported modifications are:

Renaming the oci.dll files supplied with the tool

Preparing Windows for an ADWC Connection in the RPD

The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud 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 %BI_DOMAIN%\config\fmwconfig\bienv\core directory e.g.

%OAC_HOME%\domains\bi\config\fmwconfig\bienv\core

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

Using Oracle Instant Client for Windows

The OAC Developer Client Tool uses the Oracle Call Interface (OCI) for the ADWC connection. It produces an RPD file that is uploaded to an OAC instance where the BI Server uses OCI to read from the connection. The Oracle Instant Client provides the OCI libraries.

For Versions 18.x+, the Instant Client files are included in the product. Nothing further is required.

For version 17.4.5 only:

Unzip the Windows-64 Basic Instant Client zip file e.g. instantclient-basic-windows.x64-12.2.0.1.0.zip downloaded in Preparing Windows for an ADWC Connection in the RPD section above into a single directory e.g. C:\Oracle\IC\instantclient_12_2

Set the IC_HOME variable for use in subsequent steps.

SET IC_HOME=< Your Instant Client Unzipped Folder >

Set the PATH variable to include the Instant Client folder in the current session and future sessions.

For the current session:

SET PATH=%IC_HOME%;%PATH%

For future sessions:

Use the Control Panel to set the PATH variable as a System Environmental Variable.

The oci.dll application extension plays a key role in the OCI framework.

The 18.x+ versions use the correct oci.dll files. Nothing further is required.

The 17.4.5 version uses oci.dll files which at this time do not work with ADWC. They produce an ORA-12560: TNS:protocol adapter error.

Rename the two oci.dll files in the OAC Home so the ones in the Instant Client folder are used.

REM For Version 17.4.5 only
RENAME %OAC_HOME%\bi\bifoundation\server\bin\oci.dll oci.dll.orig
RENAME %OAC_HOME%\bin\oci.dll oci.dll.orig

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

Preparing the RPD for uploading to OAC

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

Creating a Business Model

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

Enter a Name e.g. ADWC.

Uncheck the Disabled box and click Ok

Drag the table from the Physical pane onto the ADWC business model. It appears below the model.

Drag the table a second time from the Physical pane onto the ADWC 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 Cloud (ADWC) 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 Autonomous Analytics 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 Cloud

Analytics Cloud

Preparing to Connect to the Autonomous Data Warehouse Cloud

Understanding Customization and Administration

Developer Client Tool for OAC

Creating a New Oracle BI Repository

Connect Descriptor Descriptions

Connecting Oracle Autonomous Analytics 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