Snowflake + Power BI

Snowflake Usage Report

Matt Feks
Matt Feks
Sep 1, 2019 · 5 min read

For anyone interested, you can download the Power BI report here. I’ve added a Snowflake SQL script file (for view creation) and the PBIX file.

Git

The article below gives a quick explanation of the Snowflake Usage report and some of the steps to set this up. It’s part of a series of articles going through the power of Snowflake and Power BI.

Overview: https://www.linkedin.com/pulse/snowflake-power-bi-matthew-fekitoa/

Snowflake Setup: https://www.linkedin.com/pulse/snowflake-power-bi-setup-matthew-fekitoa/

The Snowflake Usage report is used to assist operations manage and track the ongoing cost in Snowflake. The key questions being answered here are:

  • What is my current monthly cost?
  • How is my cost tracking for the year?
  • Where are the credits being consumed (warehouse, times of day, day of week)?

At the time of writing this credits can be charged to an account on the following:

  • Warehouse Usage: (used for majority of Select and DML queries). per second charge (always charged for first 60 seconds once a warehouse has resumed)
  • Storage: per terabyte cost
  • Snowpipe Usage: (Continuous loading): per-second/per-core charge
  • Auto-clustering and Materialized views: Snowflake internally preforms these work loads.

The current Snowflake Usage report looks at the first 3 types of credit usage. There will definitely be more things added to Snowflake that will have a credit component to it (Tasks and Streams) and the usage report will need to be updated to align with how Snowflake is being used. The current report focuses on Warehouse Usage while also showing figures around Snowpipe and storage.

Snowflake Usage Report

Step 1 Snowflake Views

The views themselves will need to be in a Snowflake database other Snowflake database the Account Usage views (as this is a shared database that changes can’t be made to)

VW_REP_SNOWFLAKE_STORAGE_USAGE_MONTHLY_SUMMARY: References the STORAGE_USAGE view in the Snowflake Database. Aggregates Storage usage information at the month level.

VW_REP_SNOWFLAKE_WAREHOUSE_METERING_HISTORY: References the WAREHOUSE_METERING_HISTORY view in the Snowflake Database. Returns the hourly credit usage for a single warehouse.

VW_REP_SNOWFLAKE_PIPE_USAGE_HISTORY: References the PIPE_USAGE_HISTORY view in the Snowflake Database. Returns usage information on Snowpipe.

VW_REP_DATE_DIMENSION: Custom view that will return a date for each day for the past 720 days . This is used for our common date dimension for all 3 tables.

TABLE_USAGE_OPTION: This is a custom table built in Power BI which has the options of what to show the credits value in ($ vs credits).

Step 2: Getting Data in

Once that is set you can hit “GET DATA” in Power BI Desktop and select the views to be imported into the report.

Power BI- Get Data

Step 3: Setting up model

The model for this usage report is straight forward

Power BI — Relationship view
Power BI — Edit relationship view

Step 4: Setting up measure\parameters

Power BI — Measures

The what-if parameter in Power BI gives the ability for some simple user input. In the Snowflake usage report I’ve added the Cost Per Credit parameter. This is to allow any user to add the cost per credit in the currency they deal with.

Power BI — What-If parameter

Step 5: Visualisation time

Below is the breakdown of the different visualisations used.

Power BI — Visulisation breakdown

Step 6: Publish and schedule

Conclusion

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Matt Feks

Written by

Matt Feks

https://www.linkedin.com/in/matthew-fekitoa/

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade