Snowflake + Power BI
Snowflake Usage Report
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.
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.
Step 1 Snowflake Views
The report itself will make use of the following views in Snowflake. I’ve added some additional columns in the view to simplify the Power BI report. The view DDL can be downloaded in the GIT repository mentioned above.
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
Power BI has a connector into Snowflake so its fairly simple to get started. The snowflake connector itself requires the Snowflake ODBC connector which can be downloaded here.
Once that is set you can hit “GET DATA” in Power BI Desktop and select the views to be imported into the report.
Step 3: Setting up model
Once the tables are in Power BI you’ll want to set up any relationships required for the report. For the Usage report I’ve set up a one to many relationship from the Date Dimension ( on date) to each of the dates in the Snowflake Operation views.
The model for this usage report is straight forward
Step 4: Setting up measure\parameters
Measures (using DAX) and parameters are an important concept in Power BI to really get some insights into your data. Measures will usually aggregate data (Sum, Min, Max) and are calculated while interacting with your data (i.e. when you change a filter). I’ve kept the filters here to Year to date\Month to date calculations on the credit usage columns
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.
Step 5: Visualisation time
The last piece to this puzzle is setting up the visualisations themselves. With all the data now available its up to you to set up how you want to show the report. I’ve kept mine below using simple line\bar charts showing how measures have changed over a date.
Below is the breakdown of the different visualisations used.
Step 6: Publish and schedule
I won’t go into much about getting the Power BI gateway set up or publishing the report other than as right now, the Snowflake Power BI connector requires a Gateway for the Power BI service to connect to Snowflake (yes that means data has to go through your gateway and not directly connecting from the service). I’m sure this will change shortly with the impact Snowflake is having but for now “Set up a gateway”.
Conclusion
And that’s it, the Snowflake Usage report is up and running and gives users a different way to understand how credits are being used in the environment. This provides a great way for clients already using Power BI to have Snowflake usage in there current reporting solutions, and gives users different ways of looking at this usage data.
