How to Connect Customer-Managed Oracle Analytics Cloud (OAC) or OBIEE to a Snowflake Data Warehouse

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 a customer-managed 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:

  1. Add a Snowflake connection to your system-level “/etc/odbc.ini” file

  2. Add the Snowflake driver information to your system-level “/etc/odbcinst.ini” file

  3. 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/ 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/ 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