Creating an Oracle Analytics Cloud RPD Connection to the Exadata Express Cloud Service

Introduction

This post details the steps required to create an Exadata Express 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 on Exadata Express, visit the Exadata Express Cloud Service page. For additional details on OAC, visit the Analytics Cloud page.

Topics

Preparing Windows for an Exadata Express Connection in the RPD

Installing Oracle Instant Client for Windows

Testing the Oracle Instant Client in Windows

Downloading and Preparing the OAC Developer Client Tool

Creating an Exadata Express Data Source in the RPD

Preparing the RPD for uploading to OAC

Versions Used

OAC Developer Client Tool 12.2.2.0.30
Exadata Express 18.1.4
Windows 7 SP 1

Disclaimer

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 Exadata Express, 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 Exadata Express Connection in the RPD

The steps described in the post Preparing to Connect to the Exadata Express Cloud Service download and unzip the client credentials folder that contains the wallet and network configuration files, as well as the Oracle Instant Client zip 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>”)))

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.

SET TNS_ADMIN=<Your Client Credentials Folder>

Installing Oracle Instant Client for Windows

The OAC Developer Client Tool uses the Oracle Call Interface (OCI) for the Exadata Express 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.

Unzip the Windows-64 Instant Client zip files downloaded in Preparing to Connect Oracle Analytics Cloud to the Exadata Express into a single directory e.g.

C:\Oracle\IC\instantclient_12_2

Ensure the SQL*Plus zip file is one of the files. The zip files should be:

25

Place this directory at the beginning of the PATH environmental variable. Note: Use the Control Panel to set the PATH variable as a System Environmental Variable.

SET PATH=C:\Oracle\IC\instantclient_12_2;%PATH%

Testing the Oracle Instant Client in Windows

Test the Instant Client by using SQL*Plus to create a table in Exadata Express. This requires the Exadata Express database credentials, and a TNS net service name from the tnsnames.ora file.

Open a CMD window and run the following

sqlplus /nolog
connect admin@<Your TNS Net Service Name>
–enter the password
drop table test; — in case there is one
create table test (a varchar2(10));
insert into TEST values (‘Exadata Express’);
commit;
exit

Downloading and Preparing the OAC Developer Client Tool

You need a Windows 64–bit machine on which to install and run 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.

The Developer Client Tool expects the network configuration files to be in the %BI_DOMAIN%\config\fmwconfig\bienv\core directory e.g.

C:\Oracle\12c\OAC2\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 C:\Oracle\12c\OAC2\domains\bi\config\fmwconfig\bienv\core

This version of the Developer Client Tool uses its own oci.dll file which at this time does not work with Exadata Express. It produces an ORA-12560: TNS:protocol adapter error. Renaming two oci.dll files forces the use of the oci.dll file in the Instant Client folder.

RENAME C:\Oracle\12c\OAC2\bi\bifoundation\server\bin\oci.dll oci.dll.orig
RENAME C:\Oracle\12c\OAC2\bin\oci.dll oci.dll.orig

Creating an Exadata Express Data Source in the RPD

This post creates a new repository (RPD) for the Exadata Express connection. Refer to Creating a New Oracle BI Repository in the Oracle® Fusion Middleware Metadata Repository Builder’s Guide for Oracle Business Intelligence Enterprise Edition for details.

From the Windows Start menu, start the Oracle Analytics Developer Client Tool (Administration is the name of the icon).

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.

Creating a Physical Database

From the Create New Repository – Select Data Source, select the Oracle Call Interface (OCI) Connection Type, a TNS connect descriptor from the tnsnames.ora file as the Data Source Name, the database User Name / Password and click Next.

Select the Metadata Types, unchecking Foreign Keys, and click Next.

Select the schema and tables, e.g. ADMIN and TEST, from the Data Source View and move them to the Repository View and click Finish.

Test the connection by expanding the Physical Database and right-clicking on the table and selecting Update Row Count. The row count appears (this table is empty). Hover over the table and see the date and time of the last row count.

13

Right-Click the Physical Database and Rename it to Exadata Express.

Preparing the RPD for uploading to OAC

A simple way to test the RPD Exadata Express 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. Exadata Express, uncheck the Disabled box and click Ok

Drag the TEST table from the Physical pane onto the business model.

Drag the TEST table a second time from the Physical pane onto the business model.

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

From the Diagram menuCreate a New Join by 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

This post detailed the steps required to create an Exadata Express data source connection using the OAC Developer Client Tool on Windows. It also prepared a complete RPD ready for uploading to OAC.

View the post Connecting an Oracle Analytics Cloud RPD to the Exadata Express Cloud Service 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

Exadata Express Cloud Service

Analytics Cloud

Understanding Customization and Administration

Preparing to Connect to the Exadata Express Cloud Service

Developer Client Tool for OAC

Creating a New Oracle BI Repository

Connecting an Oracle Analytics Cloud RPD to the Exadata Express Cloud Service

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

Add Your Comment