How to Parameterize Snowflake Data Sources in Power BI

In recent years (and even months), we’ve seen that Microsoft and the Snowflake Data Cloud don’t plan on slowing down investment in building integrations between the two platforms. As the possibilities for working with these two platforms in unison expands, the need for fast and easy development as well as flexibility will also grow.

The ability to create parameters for your data sources in Power BI is not new; however, there are nuances to parameterizing your Snowflake connections that one should be aware of.

Creating these types of parameters allows users to seamlessly change Snowflake data sources settings like the server, warehouse, role, schema, and database.

In this blog, we’ll cover an example of how to build those parameters.

What Are Power BI Parameters?

Simply put, a parameter is an easy way to store a value that can be used or changed repeatedly. Creating parameters for your Snowflake data sources gives you the versatility to dynamically alter the data source settings.

This enables you to do things like change the Snowflake server, switch the data source from a test environment to a production environment, and change the Snowflake role.

How to Setup Snowflake Data Sources in Power BI

Since the release of the native Snowflake connector in Power BI, setting up your connection has been extremely easy. From the Home menu, click on Get Data (or New Source if you’re in Power Query) and simply search for Snowflake. This will open a window where you will minimally need to provide the Server and Warehouse.

Note that if you are writing a custom SQL query, you will need to provide the database, both of these are in the Advanced Options section.

Creating and Configuring Parameters in Power BI

Creating a parameter in Power BI for your Snowflake data sources is just a few simple steps. This section covers two examples, how to change the database referenced in a custom SQL query and how to change the Snowflake warehouse, but know that you can essentially create a parameter for anything in the Advanced Editor window in the exact same way.

Steps 1 through 5 are the same for parameterizing anything in the Advanced Editor; therefore, they will not be repeated for both examples.

Click the Manage Parameters dropdown on the Home menu in Power Query and then New parameter.
In the new window, provide the name of your parameter. Pro-tip, use a friendly name so that when people want to use it, they know exactly what they are looking for.
Type should be Text, and Suggested Values should be List of values.
Enter the possible values in the rows that appear as well as what you want the default value to be and what the current value is.
Open the Advanced Editor from the Home menu with the Snowflake query selected.

Step 6 for changing the Snowflake database in the custom SQL:

Locate the Snowflake database in the M-code and replace it with the parameter created in step 4. To do this, close the double quotes after the FROM statement and add & ParameterName & – see the before and after screenshots below

Step 6 for changing the Snowflake warehouse in the data source settings:

Locate the Snowflake warehouse in the M-code and replace it with the parameter created in step 4. Notice that you don’t need an ampersand or quotes like in the SQL query, simply just reference the parameter.

Now that the parameter is referenced in the M-Code, it is ready to be used. See the next section on how to use the parameters.

Utilizing Parameters in Snowflake Data Sources

Now that the parameter is created, users can switch the Snowflake database easily in several locations depending on where they need to refresh the data.

If you are refreshing the dataset in Power BI desktop, there are two locations:

From the main canvas, click Transform data on the Home menu and then Edit parameters.
Select the correct parameter value from the dropdown and click Ok.

In Power Query, click on the parameter in the query menu on the left and use the drop-down to select the correct parameter value.

If you are refreshing the dataset in Power BI Service:

Go to the dataset settings in the workspace and scroll down to the Parameters section.
Type or paste in the value of the parameter and then click Apply.

Following any of the methods above, refresh the dataset as necessary to use the new value.

Conclusion

Having the ability to create parameters saves time and introduces flexibility into your Power BI datasets. Whether you’re changing the server, the database to utilize a different environment, or a Snowflake role so that someone else with a different role and the same permissions can refresh the data, parameters provide an easy way to dynamically update your Snowflake data source settings.

If you found this content useful, we highly recommend you check out our comprehensive guide that covers how Power BI & Snowflake work together to help businesses achieve eye-opening results.