February 18th, 2021
As the world of Big Data continues to evolve, we have noticed many of our IBM Cognos Analytics clients are moving to an innovative new cloud data solution, Snowflake. Snowflake is a fully managed service that’s simple to use but can power a near-unlimited number of concurrent workloads. Snowflake is a solution for data warehousing, data lakes, data engineering, data science, data application development, and for securely sharing and consuming shared data. It’s important to know that IBM Cognos Analytics Reporting off Snowflake Data can be a winning combination. In this blog, Lodestar Solutions will describe the problem many Cognos Analytics Administrators have faced and how Snowflake and Cognos Analytics can solve the problem.
The Cognos Analytics Administrator’s Historical Struggle
IBM Cognos Analytics BI Administrators have struggled for years to deliver effective reports and dashboards, in organizations that had data integrity issues. Many battled silos of data marts, data warehouses and multiple data sources. In many organizations the Cognos Analytics Administrators did not have control or influence over the data warehousing teams or how they received the data, which resulted in many Cognos Analytics Administrators resorting to what I call duct tape and super gluing reports with code that would be unnecessary if the data was in a better format or indexed.
You can have the best business intelligence reporting solutions in the world but if you have bad data you are stuck in a swamp!
It’s not the Data Warehouse Teams Fault!
Over the years Big Data made big promises but often failed to deliver the results executives were looking for. Initially, organizations leveraged Data Warehouses to store big data. A data warehouse is a repository for structured, filtered data that has already been processed for a specific purpose. The challenge was the amount of unstructured data from different sources like web apps, and social media was growing exponentially. Business Users were demanding more, and data warehousing just didn’t meet the needs. So, data lakes were created. A data lake is a vast pool of raw data, the purpose for which is not yet defined, but the data lakes required much larger storage. Without a lot of baby sitting and governance the lakes became data swamps. This was especially true of on-premise data lakes. To solve this problem companies like Snowflake have delivered a modern version of data lakes that leverage cloud technology to handle scaling. If you want to learn more about modern data lakes check out the complimentary book by Snowflake called Cloud Data Lakes for Dummies by David Baum. It’s a great short read and you might want to share this with your executives to help them better understand data lakes. Here's a link to my book review on Cloud Data Lakes for Dummies
Snowflake and Cognos Analytics
A number of IBM Cognos Analytics clients are solving this problem by deploying Snowflake. IBM Cognos Analytics has native connectivity to Snowflake with Cognos Versions 11.0.5 or higher. Click here to see a list of Business Intelligence solutions that have native connectivity with Snowflake.
How to set up Snowflake Data Source Connection in Cognos Analytics
IBM Cognos Analytics connects to Snowflake with a Java Database Connectivity (JDBC).
To set up the data source connection between IBM Cognos Analytics BI and Snowflake, you will need to:
- Download JDBC Driver
- Place snowflake jdbc (snowflake-jdbc-3.9.2.jar) file into CA_installation/drivers folder.
- Restart IBM Cognos Analytics
- Edit Snowflake connection
Select Use the following signon:
ID and Password from snowflake User Name and Password
5. Test the connection
If you want additional details, check out this IBM Blog.
IBM Cognos Analytics Connection to Snowflake with an ODBC Connection.
For IBM Cognos Analytic Administrators that want to use an ODBC connection to Snowflake there are 3rd party drivers that you can purchase and use. One such driver is the CData ODBC driver for Snowflake which can integrate Snowflake data with the drag-and-drop style of Cognos Report Studio. They provide a graphical approach to create data visualizations with no SQL required. This blog by CData, Create Data Visualizations in Cognos BI with Snowflake shows their approach on how to execute any SQL query supported by Snowflake.
IBM Cognos Analytics Reporting off Snowflake data can be a winning combination. If you are a Cognos Analytics Administrator and want to discuss how IBM Cognos Analytics can be taken to the next level with better data sources like Snowflake, contact firstname.lastname@example.org.