Qlik Snowflake Usage Dashboard - Analyze your Snowflake Usage with Qlik!
This Qlik Sense app combines data from multiple Snowflake tables to create an...
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:
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.
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.
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.
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.
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.
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.
Performance / Optimization:
This dashboard can be used to understand query performance, usage hotspots, query volumes vs runtime, errors, and dive deep into query details.
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 theStorytab on the top of the screen.
Secure Data For Analytics with HYOD (Hold Your Own Key) for your Cloud Data Platform The ChallengeProbably the single biggest barrier to more rapid ...
Secure Data For Analytics with HYOD (Hold Your Own Key) for your Cloud Data Platform
Probably the single biggest barrier to more rapid adoption of Cloud Data Platforms is data privacy and security. For any organization collecting, processing, analyzing and retaining sensitive or regulated information, data privacy can quickly become a show-stopper.
Adoption of cloud services involving Personally Identifiable Information (PII), Private Health Information (PHI), other sensitive Non-Public Information (NPI) or any other government or industry regulated data usually requires implementation of a completely different trust model.
Organizations accustomed to traditional methods of protecting and controlling access to data where they managed all aspects of data security from physical data center security, to network security controls, to Role Based Access Controls coded into Databases and Applications require a paradigm shift in their thinking about data privacy.
Any cloud adoption initiative needs to take into consideration that the organization may only be left with control over a User’s Identity, the Data itself and the Application(s) used to access the data. This demands a data-centric approach that is application, database and hosting location agnostic. The same data protection, fine-grained access controls, accountability, and audit trail need to be maintained even though the data may be hosted almost anywhere and accessed from almost anywhere.
The New Reality
Cloud hosting providers along with the databases and applications that run on cloud hosted infrastructure do a great job of providing as good or better security controls as their prospective customers enjoy today on-premise. However, this is often not enough. Data privacy regulations mandate that organizations which collect the data remain responsible for its privacy and protection regardless of any contractual agreements or outsourcing.
Organizations remain accountable even when they have almost no direct control over any of the infrastructure processing the data.
Data encryption applied at the disk or file (tablespace) level only protects data from someone walking out of the data center with a disk drive. It doesn’t distinguish between privileged users who still have access to the infrastructure (DBAs, SysAdmins) but shouldn’t see sensitive data, and those who may not have access to the infrastructure but are authorized to view the sensitive data. Decryption should only occur when authorized users interact with the sensitive data, irrespective of what tool they are using, and deny the Infrastructure providers any ability to see meaningful data.
The easiest way for organzations to retain full control with virtually complete transitions to the Cloud is through Anonymization of data or rendering enough sensitive data fields inaccessible when in the Cloud and only accessible again when coming back on premise or back within your span of control. This is where Hold Your Own Key (HYOK) becomes essential. Encrypting data prior to sending to the Cloud and only decrypting once back on premise or when requested by an authorized user with legal basis/”need -to-know” is the only way to satisfy any more conservative trust models.
Full disk encryption, file level or tablespace encryption or Bring Your Own Key (BYOK) based Column level encryption do not meet any of these more stringent data protection requirements. These do not satisfy that fundamental trust model requirement of sharing only encrypted data (not the keys). The Hold Your Own Key (HYOK) concept is the desired trust model for any smart Data Controllers when their data flows to a Cloud Data Platform if they want to retain full control over access to their data.
HYOK is the KEY to Cloud Data Security and Data Governance
Regulated industries, such as financial services and healthcare, require keys be segregated from the cloud data platform compute (e.g., Snowflake). SecuPi HYOK enables companies to comply with this requirement with encryption applied to regulated columns, or applying dynamic masking or filtering access to other sensitive columns – balancing between compliance, analytics and usability of the data.
The Qlik Data Integration and Data Analytics platform integrates with SecuPi’s data privacy and protection capabilities to provide a complementary, end-to-end solution for analytics involving sensitive or regulated data for Cloud data platforms like Snowflake.
The new integrated solution protects data from the source (mainframe, Oracle, DB2, Teradata and other on-prem data stores), during ingestion via Qlik Replicate (formerly Attunity and CDC), within hybrid cloud data platform (e.g., Snowflake) & Big Data (e.g., DataBricks) until analytics results are consumed by authorized users within Qlik Sense and QlikView.
Together Qlik and SecuPi enhance the security of data within Cloud data platforms like Snowflake by ensuring that sensitive data remains encrypted in the cloud at all times (without exposing encryption keys or sensitive data).
The SecuPi integration with QlikView or Qlik Sense Enterprise enable decryption as well as other governance and fine-grained access controls including geo-fencing, row filtering, logical deletion, dynamic masking, real-time sensitive activity monitoring, classification and user behavior analytics.
The Qlik and SecuPi partnership is an excellent fit for multicloud deployments where HYOK and consistent access controls, accountability and data privacy requirements must also be met on Snowflake.
One of the most important aspects of the Qlik | SecuPi partnership is security and implementation transparency. SecuPi enables fine-grained access control, data-at-rest protection with Hold Your Own Key (HYOK) – segregating keys from the compute. This satisfies challenging data privacy regulations (GDPR/CCPA) and provides full accountability for all access to sensitive or regulated data without changes to QlikView, Qlik Sense or the underlying data repository such as Snowflake. Scalability and ease of implementation are driven by SecuPi’s ability to operate as a simple encrypt function call within Qlik Data Integration and as a transparent gateway between Qlik Sense or QlikView and the Cloud hosted data platform such as Snowflake
Figure 1: SecuPi encryption functions augment Qlik Replicate ETL process, while using SecuPi overlay when authorized users are retrieving encrypted data from Qlik Sense
Enable HYOK on Your Cloud Data Platform in 3 Simple Steps
Step 1: Install SecuPi
SecuPi Management Server and overlays come as a docker container, K8s. They can be installed either on-prem or cloud. The policies are configured centrally, and distributed to the self-contained overlays and gateways for enforcement.
Step 2: Encrypt Sensitive Ingestion Flows by calling SecuPi Encryption function calls
Leverage SecuPi HYOK to encrypt enough sensitive fields to render customer records sufficiently anonymized prior to copying sensitive data to the Cloud, and only decrypted upon retrieval by authorized users in Qlik.
Configuring a Global rule that applies an FPE encryption transformation to specific Columns
All data migrations, replication, “Lift & Shift”: ETL or ELT operations, native cloud applications and cloud analytics, are fully supported including Format Preserving Encryption (FPE) or Masking of data exported to QVD, CSV or other file formats. Only anonymized data is then stored in the cloud.Selectively encrypting specific fields before writing to output file using Qlik Replicate
Step 3: Decrypt for authorized Qlik users
Anonymized data (encrypted Columns) are then decrypted on consumption with the keys to decrypt the selected data columns remaining On-Prem (HYOK).
Authorized Users see all fields in the clear with transparent Decryption on Consumption
Unauthorized users or unauthorized data extraction or access methods see only Anonymized or Encrypted data. User attempting to download protected data into Excel
Name Column in output file (opened in Excel) protected using SecuPi FPE Encryption
Data encryption, decryption, dynamic masking, filtering, geo-fencing or obfuscation operations are all managed by policy from a single central Policy Server. Only authorized users are granted the right to access protected data elements in the clear.
SecuPi is the preferred data security partner for Snowflake and a top tier security solution partner for Microsoft Azure and Amazon AWS. This validates SecuPi’s ability to easily solve some of the most challenging data privacy compliance requirements faced by any prospective Cloud Services customer. SecuPi is frequently the enabler of expanded use of Cloud Services and Hosting where sensitive or regulated data is involved and compliance with GDPR, CCPA, HIPAA and more are required.
You can outsource everything but common sense and security
Any large or complex Qlik implementations on Snowflake or other DBaaS platforms involving multiple data sources and/or migrating to the cloud can introduce a lot of risk and be expensive to implement when PII or PHI is involved. SecuPi together with Qlik eliminates most of this risk, freeing Data Analytics teams to analyze data, not spend most of their time designing, testing and implementing essential data security controls.
In this video, we will show how easy it is to offload data from SAP ERP using Attunity Replicate into Snowflake DB, and then visualize that data with ...
In this video, we will show how easy it is to offload data from SAP ERP using Attunity Replicate into Snowflake DB, and then visualize that data with Qlik Sense. The key advantage of Replicate is that the data in synchronized in real time with Snowflake from the SAP system as new transactions trickle into the system. The demo is a multicloud solution as SAP is on AWS/Oracle, Attunity is on GCP, and Snowflake is back on AWS. Qlik Sense is running on GCP as well.
The data used in this demo is SAP IDES data from the SAP ECC module Sales and Distribution.