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.