One of the great things about Power BI is all of the native connectors that exist, making it extremely easy for developers to seamlessly connect to the source system and pull their data into Power BI. At the time of writing this blog, there are well over 100 connectors that Microsoft provides, and one of the more commonly used ones, no doubt due to its rising popularity, is the Snowflake Data Cloud connector.
Suppose your team is using a gateway or a series of gateways. In that case, it’s important that you have provisioned the appropriate connections for the data sources in Power BI services so that your Power BI datasets can reliably retrieve the data either by manual or scheduled refresh.
In this blog, we’ll discuss why you need a Snowflake connection if you’re using a gateway and how to set one up.
Why do I Need a Snowflake Connection for my Gateway?
A data gateway connection in Power BI allows you to use a dataset that requires a gateway by providing a connection to the datasource that leverages the gateway you have. Essentially, data gateway connections are required for you to connect your Power BI datasets to your data’s source system (in this case, Snowflake) from Power BI Service when your organization requires a gateway.
These connections also provide an extra layer of data governance by requiring users of the gateway connection to have the proper access.
How to Set up a Snowflake Data Gateway Connection in Power BI Service
The following steps require you to have the “Connection Creator” role on the gateway for which you’re building the connection. If you do not currently have this role, contact your Power BI Tenant admin and work with them to provide you the access or create the gateway connection on your behalf.
Step 1: From the Power BI Home screen, open the settings panel by clicking on the gear icon on the top right and then select Manage Connections and Gateways
Step 2: Click + New on the top left of the new screen
Step 3: Select either On-premises or Virtual Network depending on the type of gateway you have installed
Step 4: Select the Gateway or Gateway Cluster Name that this connection will use
Step 5: Provide the Connection Name – This should be user-friendly as it will be what you end up selecting as the gateway connection to use when scheduling the dataset for a refresh.
Step 6: Provide the Server and Warehouse details of the Snowflake connection
Step 7: Choose the Authentication Method. We recommend using your Microsoft Account. Keep in mind that these credentials are encrypted.
A best practice is to use a Service Account for this authentication. Enter the account’s credentials by clicking the Edit Credentials button and following the prompts.
Step 8: Check the Single sign-on box if you’re using SSO. Check out this blog on how to enable SSO for Snowflake in Power BI.
Step 9: Set the Privacy level of the data.
Step 10: Click the green Create button at the bottom of the panel
Allowing Users to Use the Snowflake Data Gateway Connection
Now that you have created the data gateway connection, you need to provide users with access if they want to refresh the dataset in Power BI Service.
Find the connection you just made on the list and, click the three dots that appear when you hover over it, and select Manage Users. In the next screen, search for individual users, distribution lists, or security groups as a best practice. Select the access type (see screenshot below) and then click on the green Share button.
Using the Snowflake Data Gateway Connection to Refresh a Dataset
Scroll down to and expand the Gateway and Cloud Connections Settings from the dataset’s settings. Toggle the Use an On-premises or VNet data gateway to On and then select the appropriate gateway and the data gateway connection the dataset should map to (this is the connection you built in the previous section).
Closing
Power BI is an exceptionally robust business intelligence platform. One of the greatest things about it is the ease by which it allows developers to connect to their source systems while still allowing admins to maintain control of how and who gets access to the data. Building data gateway data connections gets data into the hands of end users quickly and reliably without sacrificing governance.
If you need additional help or are curious about how to better leverage Power BI, reach out to the experts at phData today for help, guidance, and best practices!
