By: Koen Verbeeck
Overview
Building a data warehouse – no matter which vendor – ultimately serves one purpose: provide your users with centralized, historized and standardized data upon which they can create insights and make decisions. So at the end of the tutorial it seems appropriate we explain how we can connect Power BI Desktop to our Snowflake database.
Using the Snowflake Connector
Luckily Power BI provides us with a native connector for Snowflake. In the Get Data screen, you can find it listed under the database section.
At the time of writing, you still need to install the ODBC driver on the machine running Power BI Desktop. This also unfortunately means you need to use a gateway if you want to run your Power BI reports with Snowflake data in the Power BI service. Microsoft is working to change this, but a release has not yet been communicated.
Once you select Connect in the Get Data screen, you need to fill in the server information and select a warehouse.
It might be a good idea to create a new warehouse specifically for Power BI. This can give you good insights in the costs associated with reporting on top of your Snowflake database. The queries from Power BI will also then not intervene with other queries that are using other warehouses.
As with most databases, you can choose Import mode or DirectQuery mode.
- Import mode might seem a bit weird at first, since you have such a powerful, scalable database at your disposal, but if the data fits into the Power BI model (1GB compressed when using Power BI Pro) it makes sense. If the data is imported, you have no extra costs. Remember, running queries on Snowflake consumes compute, and compute equals costs. If Power BI handles all the end user queries, you might save some money.
- For larger datasets, DirectQuery is a good option. Snowflake will handle the heavy lifting. You might want to test out if you want to go for a very large warehouse which can returns results fast (but costs more money) or a auto-scaling cluster of multiple warehouses which load balance the requests of multiple users.
After choosing the data connectivity mode, you need to specify the credentials. Azure AD integration is also on the roadmap of Power BI (Snowflake itself already supports Azure AD for authentication).
Once the model is loaded, you can create your visualization like with any other data source. If the warehouse is suspended, it will auto-resume (if configured) and return results to Power BI.
Additional Information
- The documentation of the Snowflake connector in Power BI.
- In the list of data sources, you can see Snowflake is still listed under databases, and not under Online Services. This means you still need a gateway to connect to the data in the Power BI Service.
