Last Validation: February 12, 2020 for OAC Client version 105.5 and OAC 105.5
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.
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 126.96.36.199
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
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
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
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>
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.
Enter an oracle home e.g. C:\Oracle\12c\OAC2
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 %CRED_LOC%\*.ora %OAC_HOME%\domains\bi\config\fmwconfig\bienv\core
This post creates a new repository (RPD) for the ADWC connection. Refer to Creating a New Oracle BI Repository for details.
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.
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.
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.
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).
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.
Right-Click the business model and choose Create Subject Areas for Logical Stars and Snowflakes.
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.
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