Qlik Community

Technology Partners Ecosystem Documents

Documents & videos related to the Qlik Technology Partners Ecosystem

Qlik & Snowflake Usage Dashboard

Employee
Employee

Qlik & Snowflake Usage Dashboard

Qlik Snowflake Usage Dashboard - Analyze your Snowflake Usage with Qlik!

This Qlik Sense app combines data from multiple Snowflake tables to create an understanding of three key areas.

- Cost / Usage Analysis: There are two versions of this focused on pay-as-you go models or Enterprise credit purchases.

- Auditing / Security: Show who is logging in from where (GeoAnalytics) and metrics associated with Security and Connectivity.

- Performance & Optimization: Who is running queries, where are their issues - how does this relate to cost and usage?

Instructions and descriptions are below. The app can be downloaded from GitHub: https://github.com/Qlik-PE/Snowflake-Usage-Analysis-Dashboard. There are two versions of the dashboard, as simplified version to get started quickly, and a more complex version that supports Qlik Server Side Extensions. For more information and how to use Server Side Extensions for Qlik Sense, visit here:

https://github.com/nabeel-oz/qlik-py-tools

Upload to your Qlik Sense server, Qlik cloud (when the Snowflake driver is enabled), or Qlik Sense Desktop. Follow the instructions in the app to add your Snowflake credentials and update the GeoAnalytics connection or modify to use a public IP lookup service.

A demo version using the Qlik Partner Engineering account can be accessed here: https://pe.qlik.com/sense/app/100104c2-3917-4f44-b1b3-677c92846d60/overview:

qlik-snowflake-architecture.png

 About Qlik and the App:

Qlik is the only major analytics vendor with an entire data integration and management suite powering the data from raw to ready across an enterprise. This application specifically uses the Qlik Sense component to visualize the usage and cost data about a Snowflake instance sourced from internal tracking tables. Qlik Sense is unique in the analytics space as it is three tools in a single product encompassing an ELT engine, a high speed memory cache, and world class data visualization and analytics.

image2.jpeg

Data Model:

The data is collected from a series of methods and combined together in Qlik's in-memory associative engine. Qlik is unique in that unlike other BI/Visualization tools it can handle multi-grain fact scenarios with data at different levels of aggregation and granularity. For this application, we are combining metadata from databases, tables, and columns with query performance data, login information, storage costs, and usage costs. We also perform dynamic IP lookups to get geospatial information about user IP locations.

Data Load Script:

 

The data is extracted using Qlik load script. The load scripts are how Qlik requests the data from the source tables, sql functions, and geo-lookups. The model for this application has been broken into logical grouping of similar data by using tabs to help simplify understanding of the data imported. In order to map this application to your instance of Snowflake, you will need to create your own data connection to Snowflake. With the September 2019 release of Qlik Sense Enterprise, there is a built-in connector. Older versions of Qlik Sense will require a download of the ODBC driver from the Snowflake website.

 
 

The other element in the load script in the Qlik GeoAnalytics IP lookup.
Please note this is a licensed add-on to Qlik Sense and will not work without a license.
You can contact your Qlik representative for an evaluation license.

Alternatively, you could use the external IP via api REST lookup solution as described in the load script…

This section of the script takes the unique IP's from the Login History in-memory table and passes them to the GeoAnalytics engine and returns City, State, Country, and Lat/Long values for each IP.

About the Dashboard

Table of Contents:

This is the basic introduction to the layout and structure of the app. You can use the buttons under Table of Contents to navigate the app.

 

Usage Cost:

This dashboard shows costs and usage associated with Snowflake usage. Note how Qlik's engine has mapped estimated costs down to a user level. This is a mixed grain fact situation so costs are dynamically allocated and may not be exact -- but do give a general estimate on usage/cost comparison. Users can alter their cost per credit and storage costs based on their unique pricing models that may be applicable.

image7.jpeg

Enterprise Credit Usage:

This dashboard is based on consumption of pre-purchased credits vs usage. The Vizlib chart projects when purchased credits will run out.

image8.jpeg

Auditing / Security:

This dashboard uses the GeoAnalytics IPlookup feature to display where users are logging in from around the globe. Also allows for investigation how users are accessing the system, version of drivers used, and when/how often users are accessing the system.

image9.jpeg

Performance / Optimization:

This dashboard can be used to understand query performance, usage hotspots, query volumes vs runtime, errors, and dive deep into query details.

image10.jpeg

Storytelling:

Using the story mode provides an example of how data can be used to answer questions and present them in an instructional method. Story mode can be accessed using the Story tab on the top of the screen.

image11.jpeg

Labels (2)
Attachments
Comments
Partner
Partner

Hi 

Your link Qlik Partner Engineering link does not work. 

0 Likes
Employee
Employee
0 Likes
Contributor II
Contributor II

Hi,

Is this app can be download to my hub ?

 

Thanks

 

0 Likes
Employee
Employee

Sorry - added the link to the Github site in the document. You can download the QVF there.

Contributor
Contributor

Hi David, Very nice app. Our Snowflake implementation has more than 10K tables. Is there an alternate way to collect Database/Table/Columns information using SHOW commands? Snowflake is not returning with results for the SHOW tables/SHOW columns.

0 Likes
Employee
Employee

So - there is a way to enable "SHOW" functions in Qlik, which are turned off for some reason (will be fixed in Feb release).

https://support.qlik.com/articles/000064538

That should get you back going... 10k tables is not that large a number. You can get similar information from "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES" but  you will have to adjust the load script some.

0 Likes
Version history
Revision #:
6 of 6
Last update:
‎2019-12-05 01:40 PM
Updated by:
 
Contributors