Connecting to Snowflake DB in Power BI
Below are the steps to connect to Snowflake DB using DirectQuery for Power BI. The same steps can be included for Import Mode
Installation of ODBC Drivers
The first thing I did was to install the ODBC Drivers.
I installed the 64bit drivers where I had my Power BI Desktop installed, and I also installed it on all the Servers where I had the On-Premise Data gateway installed.
Below is the link that I used which should always be the latest version
https://sfc-repo.snowflakecomputing.com/odbc/win64/latest/index.html
One thing to note is all that I did was I installed the ODBC driver I did not actually do any configuration of the ODBC driver, this is because it will be configured in Power BI Desktop.
Configuration to Snowflake using Power BI Desktop
The next steps were to configure Snowflake using DirectQuery in Power BI Desktop
- I opened Power BI Desktop.
-
I then clicked on Get Data and then selected Snowflake
-
I then put in the following details, which is the Server, Warehouse and change the Data Connectivity mode to DirectQuery
- I then click on OK
-
Next I got the details to authenticate against the Snowflake DB, which I put in below.
- I then click Connect
-
I then get the Navigator to allow me to navigate to the particular database I am after.
- I then click on Load. The reason being I click Load and not Transform Data is because I am using DirectQuery
-
I can then see the table and I can start and create a report as shown below.
- I then save and upload the PBIX to my selected App Workspace
Configuration of the Snowflake data source in the Power BI Service under Manage Gateways
In the next steps I had to create and configure the Snowflake data source in the Power BI Service under the Manage Gateways.
-
In the Power BI Service, I clicked on the Settings and then selected Manage Gateways
-
Under my Gateway I clicked on Add Data Source
-
I then configured it with the same settings that I used when I connected in Power BI Desktop as shown below.
- I then clicked on Add
-
I could then see that it connected successfully.
Setting up the Power BI Dataset to use the New Data Source Connection in the Gateway
The final step is now to set up the Power BI Dataset to use the new data source connection which was created in the previous steps.
- I go into my App Workspace.
-
I then click on Settings and select Datasets
-
I go down to my dataset and I then expand the Gateway connection
- I then turn the Use a data gateway on, and then select the Maps to in the drop down.
- I then click Apply
-
I get the confirmation that it has been updated.
-
I then go back into my Power BI Report and I can successfully interact with the report using DirectQuery
Conclusion
I have shown how to connect to the Snowflake DB using DirectQuery using Power BI Desktop and then configure the Gateway in order to facilitate the DirectQuery queries.
As always, I hope that you have found this useful and if you got any questions please let me know. Thanks for reading.
Great post, Thanks for sharing!
No worries glad you found it useful
[…] Gilbert Quevauvilliers shows us how we can connect from a Snowflake DB instance to Power BI using Di…: […]
Why do we need a gateway? What is it for?
Hi there
The Gateway allows the connection from the Power BI Service to Snowflake
Sure, but why do I need a local gateway to connect to this cloud source? I won’t need one for Azure.
It all depends on where the Snowflake data source is.
We have found that there are performance issues when Direct Querying Snowflake from Power BI when you go beyond using simple aggregation measures. Have you had a similar experience? Do you know if MS has plans to update their Snowflake connector in the future to improve the queries written to Snowflake (perhaps a more native connector than ODBC)?
I have not had a great wealth of experience with snowflake.
I unfortunately do not know of any updates to the snowflake connector and if they are looking to create a native snowflake connector.
Hi Gilbert,
Good post. I have one doubt we should need Snowflake ODBC driver to connect Snowflake ??
Because Power BI has already list the Snowflake as DB to get the date.so,can we can connect snowflake directly by giving server name and ware house details.
can you tell me which one is more easiest and having good performance in Power BI reports .Direct query or import?
Hi there
You are 100% correct that you now can connect directly to Snowflake in the Power BI Service.
For me it would depend on the performance of Snowflake and if it is fast enough for what your reporting requirements are.
Or if you wanted to add in additional data then importing the Snowflake data with other data would make sense?