TAGS

Recent Posts

Archives

How to Connect Oracle Analytics Cloud (OAC) or OBIEE to a Snowflake Data Warehouse
Posted on November 7, 2018
Author: Astha Patni, Performance Architects

Authors: Cameron Ross and Astha Patni, Performance Architects

Performance Architects recently announced our partnership with Snowflake, a cloud-based data warehousing company.  Snowflake advertises its data warehouse as allowing for instant and unlimited scalability, as well as offering storage for structured and semi-structured data.

To test these capabilities, we connected an Oracle Analytics Cloud (OAC) instance to a Snowflake data warehouse, which the Performance Architects team found allows for impressive data visualization on top of a high-performance data warehouse.  This blog post shows you what we learned and how to do this yourself.  Important note: you can use these same instructions to connect to an on-premises Oracle Business Intelligence Enterprise Edition (OBIEE) instance as the process is almost identical.

In order to connect to Snowflake via OAC, you must make changes to the OAC service’s Linux virtual machine platform.

The first step is to download the 64bit ODBC driver for Linux. To do this, log into your Snowflake data warehouse instance, click the “Help” icon, and select the “Download” button.

Download the latest version of the Snowflake ODBC driver for a Linux 64-bit machine. Download the TGZ (TAR file compressed using .GZIP) for the ODBC driver.

Copy the downloaded TGZ file in a working directory and unzip: gunzip/tmp/snowflake_linux_x8664_odbc-2.15.0.tgz.

With this a .tar file will be generated at the same location: snowflake_linux_x8664_odbc-2.15.0.tar.

Move the .tar to “/u01/app”: mv /tmp/snowflake_linux_x8664_odbc-2.15.0.tar /u01/app.

Extract the .tar file here: tar -xvf /u01/app/snowflake_linux_x8664_odbc-2.15.0.tar.

With this, a folder called “snowflake_odbc” will be generated at “/u01/ap.”

Before configuring the driver, make sure to install a driver manager. This helps to establish communication between Snowflake and the ODBC driver. The driver supports using either “iODBC” or “unixODBC” as the driver manager. For this blog, we will use “unixODBC”.

In a terminal window, make sure you are in the “snowflake_odbc” directory, and run the following to install the driver manager: sudo yum install unixODBC.

Once the install is complete make sure to update the configuration files below with the Snowflake connection details:

  • Add a Snowflake connection to your system-level “/etc/odbc.ini” file
  • Add the Snowflake driver information to your system-level “/etc/odbcinst.ini” file
  • Add all certificate authority (CA) certificates required by the Snowflake ODBC driver to the “simba.snowflake.ini” file

Edit the system-level “/etc/odbc.ini” file and make sure the entry looks like the following:

[snowflake]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=<Your_snowflake_server_name>
PORT=443
SSL=on

Edit the system-level “/etc/odbcinst.ini” file and make sure the entry looks like:

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/u01/app/snowflake_odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1

Edit the “/u01/app/snowflake_odbc/lib/simba.snowflake.ini” and update the path based on the driver directory:

ErrorMessagesPath=/u01/app/snowflake_odbc/ErrorMessages/
LogPath=/tmp/
ODBCInstLib=/usr/lib64/libodbcinst.so
CABundleFile=/u01/app/snowflake_odbc/lib/cacert.pem

Test the ODBC driver and test the DSN you created. On the command line, specify the DSN name, user login name, and password, using the following format:

isql -v <drivername> <username> <password>

On the OAC side also, we need to update the “odbc.ini” and “odbcinst.ini” files for the Snowflake driver registration.

Edit the “<domain_home>/config/fmwconfig/bienv/core/odbc.ini” file and make sure the entry looks like:

[ODBC Data Sources]
snowflake = SnowflakeDSIIDriver

[snowflake]
Driver      = /u01/app/snowflake_odbc/lib/libSnowflake.so
Description = snowflake
server      = <Your_snowflake_server_name>
role        =   sysadmin
data warehouse    = PA_DEMO
warehouse   = PA_WH

Edit the “<domain_home>/config/fmwconfig/bienv/core/odbcinst.ini” file and make sure the entry looks like:

[ODBC Drivers]
SnowflakeDSIIDriver=Installed

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/u01/app/snowflake_odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1

Next, the “LD_LIBRARY_PATH” variable must be updated in the “obis.properties” file to make the entry for the Snowflake ODBC driver.

Edit the “<domain_home>/config/fmwconfig/bienv/OBIS/obis.properties” file and append “/u01/app/snowflake_odbc/lib.”

Next, on a Windows machine, download the “ODBC Driver” for Windows. To do this, log into your Snowflake data warehouse instance, click the “Help” icon, and select the “Download” button.

From here, select the “ODBC Driver” option on the left-hand side of the screen, and then select the “ODBC Driver for a 64-bit Windows Machine” option.

This causes a file to be downloaded that should be used to install the ODBC driver.  After successfully installing the ODBC driver, go to the “Control Panel” and click on “Administrative Tools.” From here, you should be able to find your ODBC 64-bit drivers.

Click the system “DSN” tab and then click “Add.”  This will bring you to the list of all the drivers on your computer, where you should be able to locate the Snowflake driver named “SnowflakeDSIIDriver.”

Next, enter in the credentials for your Snowflake data warehouse:

After this, open up the Oracle Administration tool and create a new offline RPD. This is where the metadata file for an OAC instance can be created and modified. In the “Physical Layer” area, right-click and create a new data warehouse. This will be the connection to your Snowflake data warehouse:

Create a connection pool in the data warehouse for the Snowflake data source. Make sure to check the “Required Fully Qualified Name” checkbox. We also recommend using ODBC 3.5 as the call interface.

An important piece of the connection to Snowflake is to go to the “Connection Scripts” tab on the “Connection Pool” and make sure to specify which warehouse from your Snowflake instance you would like to use:

After creating the connection pool, right-click and select “Import Metadata.” From here, select the tables and views that you would like to bring in:

Complete the modeling of the RPD and create a new subject area where you can develop content in OAC. After the RPD modifications are completed, deploy this to the OAC environment.  Important note: If you are deploying changes in the on-premises OBIEE solution, do not use the following deployment method.

To deploy a new data model in your OAC environment, go to the home page and select the “Navigator” button and then select “Console:”

From the “Console Home Screen,” select the “Snapshots” button, then select the “Replace Data Model” button:

Next click the “Choose File” button, find the RPD file that was created with the Snowflake data warehouse connection, enter the password that was given to the RPD file, and click “OK:”

The Snowflake data should now be visible in OAC.

Although just a simple table is shown above, there are many different powerful visualizations in OAC that can be used with a Snowflake data warehouse. Snowflake’s separation of compute and storage allows OAC to query the data warehouse at a very high-performance level.

If you would like additional information about implementing OAC and/or OBIEE with Snowflake, please email sales@performancearchitects.com and we will be in touch as soon as possible!

Share
This post was posted in Technical and tagged BI , Business Analytics , Business Intelligence , Database , OAC , OBIEE , Oracle , Snowflake .
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *