How to Use the Oracle Analytics Cloud (OAC) 105.2 Snowflake Connector

Oracle issued the Snowflake Connector for Oracle Analytics Cloud (OAC) in the March 105.2.0 OAC release, which means that Data Visualization (DV) in OAC can now connect directly to Snowflake with no “under-the-hood” configuration needed.

In this blog post, I demonstrate my experience with the Snowflake Connector to demonstrate how this works using the CMS Open Payment data set.  This data is published annually for free to the public to provide details on drug and medical device payments to doctors and teaching hospitals, so that consumers can learn about possible influences on their medical care.

I stored this data in Snowflake warehouse, used DV in OAC, and created a connection to Snowflake using the “Snowflake Data Warehouse” connection type:

I then provided the connection details to the cloud instance and clicked “Save.”  I had to provide the following details to verify the connection at this step:

  1. Connection Name

  2. Hostname

  3. Username

  4. Password

  5. Database Name

  6. Warehouse Name

Next, I created a data set sourcing from this connection, showing the list of schemas available in the database “PA_DEMO.”  I chose the “CMS” schema:

After choosing the schema, I reviewed the list of tables available in that schema:

I selected a table and added all the columns needed for a visualization. The solution offers the option to rename the table if needed before a dataset is created. I then clicked “Add” in order to add the dataset.

Similarly, I created other datasets to create a project with multiple datasets. The “Data” diagram looked like this after I performed this task:

Once I completed these steps, I was able to use this data to build some interesting visualizations. For example, here is the map visual showing the total cash payment amount in 2017, sorted by all U.S. physicians in each state.  The visualization demonstrates that California physicians topped the list:

The “donut” visualization shows the total payment amount by nature of payment, while the “chord diagram” visualization reflects that the “Drug” category included the largest number of payments, but that the “Devices” category included the largest individual payments. Many more interesting visuals could be built using this dataset:

Based on this exercise, I’m happy to report that the Snowflake Data Warehouse Connector works as promised by Oracle! The only issue I see right is that the connector only works for DV and can’t be used to import Snowflake metadata into the RPD. If you want to model the metadata in the OAC administration tool, then please raise an enhancement request with your contacts at Oracle support.

Need help determining how to provision your instances to make the best use of the changes? Drop us a line below or at, and we’d be happy to schedule some time to help out.

#oracle #datawarehouse #OAC #BusinessIntelligence #Snowflake #BI #cloud #OracleAnalyticsCloud #enterpriseanalytics #analytics #Essbase #DataVisualization #DV #Businessanalytics #clouddatawarehouse