Creating a Connection in Data Visualization Desktop Version 4 to the Autonomous Data Warehouse Cloud

Introduction

This post details the steps required to create a connection in DVD Version 4 (versions 12.2.4 and earlier) to the Autonomous Data Warehouse (ADW).

If using DVD Version 5 (12.2.5+) refer to Connecting Data Visualization Desktop Version 5 to the Autonomous Data Warehouse

For additional details on ADW, visit the Autonomous Data Warehouse Cloud page. For additional details on Data Visualization Desktop (DVD), visit the Oracle Data Visualization page.

 

Updates

Latest Validation June 5, 2018 for DVD 12.2.4.3.0 connecting to ADWC 18.1.5

Recent Validations May 4, 2018 for DVD 12.2.4.1.1 and 12.2.4.2.0 connecting to ADWC 18.1.4

Updated

October 3rd, 2018 for a link to the DVD5 post.

May 4, 2018 for changes introduced in version 12.2.4.2.0

Topics

Preparing Windows

Preparing Data Visualization Desktop

Preparing the Data Warehouse Cloud Service (DWCS) Wallet

Creating an ADWC Connection

Testing the ADWC Connection

Versions Used

Data Visualization Desktop 12.2.4.0.0, 12.2.4.1.1, 12.2.4.2.0, 12.2.4.3.0

ADWC 18.1.1.0, 18.1.4, and 18.1.5

Windows 7 SP 1

Preparing Windows

The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud download and unzip the client credentials folder that contains the ADWC wallet and downloads the Oracle Instant Client zip files.

This post uses the 12.2 version of Instant Client. Unzip the 12.2. zip files into a single folder.

Preparing Data Visualization Desktop

Download and install DVD from Oracle Data Visualization Desktop Downloads.

Check the Launch after Install box so DVD starts after the installation.

If you did not check the box, start DVD after the installation completes.

The first time DVD starts, it creates a directory named DVDesktop in the folder specified in the %TEMP% environment variable. If that variable is not set, it creates the DVDesktop directory in the %LOCALAPPDATA%\TEMP folder.

Close / Exit DVD.

This post uses a Windows CMD window for examples.

Open a CMD window.

Set a Variable for your TEMP directory for the next step.

# Test if your TEMP variable is set
Set TEMP

Result is
TEMP=< Your Setting > or “Environment variable TEMP not defined”

# If you receive a result your TEMP variable is set
# If it is not set, set it
SET TEMP=%LocalAppData%\TEMP

Preparing the Data Warehouse Cloud Service (DWCS) Wallet

An ADWC connection requires the client credentials wallet to be placed in a designated DWCS directory.

After DVD has been started the first time, create the designated directory and copy the wallet to it.

SET CC_HOME=<Your Client Credentials Folder>

Placing the Wallet

Version 12.2.4.2.0 introduced changes to the placement of the ADWC wallet file.

The latest user guide may be visited at Create Connections to Oracle Autonomous Data Warehouse Cloud

As of the latest update to this blog, it instructs to place the wallet in the %TEMP%\DVDesktop\bitmp\ADWC folder rather than the %TEMP%\DVDesktop\bitmp\DWCS folder. However, it appears that neither of these locations are correct. An internal Oracle bug suggests a workaround that does work. It instructs to place the wallet in %LocalAppData%\DVDesktop\components\OBIS\dwcs

Set a variable to store the placement location.

REM For versions prior to 12.2.4.2.0 use
SET WALLET_TARGET=%TEMP%\DVDesktop\bitmp\dwcs
REM For versions 12.2.4.2.0 and later use
SET WALLET_TARGET=%LocalAppData%\DVDesktop\components\OBIS\dwcs

Make the directory

mkdir %WALLET_TARGET%
cd %CC_HOME%
copy cwallet.sso %WALLET_TARGET%

Replacing the OCI Application Extension

Version 12.2.4.2.0 introduced changes to the oci.dll application extension. I understand some folks are successful using the oci.dll file out of the box. I have had to replace it with the version that is in the Oracle Instant Client 12.2 offering. The error I receive is

(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCPS)(HOST = < host >(PORT = 1522)))(CONNECT_DATA =(SERVICE_NAME = < DB Service Name >)) (security=(MY_WALLET_DIRECTORY=C:\Users\< username >\AppData\Local\DVDesktop\components\OBIS\dwcs)))

[2018-05-03T12:14:44.191-07:00] [OBIS] [ERROR:1] [] [] [ecid: ] [sik: ssi] [tid: 3218] [nQSError: 17014] Could not connect to Oracle database.

[[file: server\Query\Execution\DbGateway\Oci10g\..\Oci8\Src\SQXDGOci8.cpp; line: 489
[nQSError: 17001] Oracle Error code: 12170, message: ORA-12170: TNS:Connect timeout occurred
at OCI call OCIServerAttach.

If you are unsuccessful, follow these steps to replace it.

Set two variables, backup the installed oci.dll and copy the Instant Client version

set DVHOME=< Your DVD Install Directory >
set ICHOME=< Your Unzipped Instant Client Directory >

copy %DVHOME%\lib\oci.dll %DVHOME%\lib\oci.dll.orig

copy /Y %ICHOME%\oci.dll %DVHOME%\lib

Creating an ADWC Connection

Restart DVD.

From the DV Home Page, click Create > Connection.

If using version 12.2.4.0.0, select the Oracle Data Warehouse Cloud Connection Type. Versions 12.2.4.1.1 and later use the Oracle Autonomous Data Warehouse type.

Enter a New Connection Name. The required entries for HostPort, and Service Name are obtained from the tnsnames.ora file in the unzipped client credentials folder. The Username and Password are obtained from the ADWC service administrator. Note: Use the database service name and not the TNS net service name.

Clicking Save validates the connection information and creates the connection.

Note prior to 12.2.4.2.0: After the connection is created, DVD creates a directory named ‘weblogic‘.'<Your connection name>‘ in the %LOCALAPPDATA%\DVDesktop\components\OBIS\dwcs folder. It then copies the cwallet.sso file to the new directory. 12.2.4.2.0 does not do this.

An example is below:

c:\>dir %LOCALAPPDATA%\DVDesktop\components\OBIS\dwcs
Volume in drive C is System
Volume Serial Number is A858-BB18

Directory of C:\Users\dcarley\AppData\Local\DVDesktop\components\OBIS\dwcs

04/17/2018 09:18 AM <DIR> .
04/17/2018 09:18 AM <DIR> ..
04/17/2018 09:18 AM <DIR> ‘weblogic’.’ADWC2a’
0 File(s) 0 bytes
3 Dir(s) 134,913,708,032 bytes free

c:\>dir %LOCALAPPDATA%\DVDesktop\components\OBIS\dwcs\’weblogic’.’ADWC2a’
Volume in drive C is System
Volume Serial Number is A858-BB18

Directory of C:\Users\dcarley\AppData\Local\DVDesktop\components\OBIS\dwcs\’we
logic’.’ADWC2a’

04/17/2018 09:18 AM <DIR> .
04/17/2018 09:18 AM <DIR> ..
04/17/2018 09:18 AM 5,253 cwallet.sso
1 File(s) 5,253 bytes
2 Dir(s) 134,913,138,688 bytes free

Testing the ADWC Connection

Testing the connection involves starting to create a data set and viewing the available schemas.

From the DV Home Page, click Create > Data Set.

Click the New Connection Name.

View the Schemas available.

Summary

This post detailed the steps required to create a connection in Data Visualization Desktop to ADW.

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

Oracle Data Visualization

Preparing to Connect to the Autonomous Data Warehouse Cloud

Create Connections to Oracle Autonomous Data Warehouse Cloud

Connecting Data Visualization Desktop Version 5 to the Autonomous Data Warehouse

Oracle A-Team Chronicles for OAC

Add Your Comment