As the adoption of the Snowflake Data Cloud and Power BI continues to gain momentum across all business and vertical markets, it’s important that the capabilities, user experience, and integration of Snowflake and Power BI continue to evolve to provide best-in-class experiences to our users. To this end, Snowflake and Microsoft have developed a strong partnership focused on listening to our mutual community of users and delivering the capabilities they demand to achieve productivity, flexibility, and agility when using our platforms.
The most recent success of the partnership between the Snowflake and Power BI teams is the release of the highly requested feature, Custom SQL Support for the Snowflake Connector. This exciting new capability is available now in the latest version of Power BI! Custom SQL Support unlocks time savings, flexibility, and various new capabilities for Power BI and Snowflake users, enhancing their productivity and effectiveness. Additionally, Custom SQL is supported in both DirectQuery and Import modes, further enhancing your team’s ability to customize your data models to accommodate business needs.
Some of the Benefits of Custom SQL Support in Snowflake Connector for Power BI
Custom SQL Support for the Snowflake Connector unlocks new capabilities, including:
Enhanced Ability to Leverage the Power of Snowflake! With Snowflake’s near-infinitely scalable compute, you’ll want to perform the heavy lifting of your query operations in Snowflake when possible (rather than the Power BI service). Custom SQL allows you to write queries that will be executed directly in Snowflake.
Streamlined Migrations. If you are currently using a data source that supports Custom SQL, you can simply migrate that SQL to the Snowflake Connector, significantly reducing the time and effort required to migrate to Snowflake.
Query Folding. Query folding is the ability for a Power Query to generate a single query statement to retrieve and transform source data. I could write an entire post on the benefits of query folding, but for now the important item to note is that Custom SQL enables query folding for Snowflake, which could result in more-efficient operations in Power BI. Query folding results in SQL execution being pushed back to the database (Snowflake) for execution.
Simplify Complex Transformations. Power Query is a very powerful tool for transforming data sets in Power BI; however, some complex SQL operations are difficult or impossible to perform in Power Query. Custom SQL enables the power of Snowflake SQL directly in Power BI.
Greater Flexibility for Analysts and Business Users. Users with limited access to database objects can now use Custom SQL to perform joins, unions, and so on, while (as mentioned above) leveraging the power of Snowflake to perform the operation rather than the Power BI service.
Greater Flexibility Adding Custom Columns. Adding custom columns using Custom SQL is particularly important to overcome limitations of DAX in DirectQuery mode and also removes potential overhead of calculations performed in the Power BI service.
Getting Started with Custom SQL in the Snowflake Connector for Power BI
Solution Overview
To implement this solution, you’ll complete the following high-level steps:
- Confirm you’re using the latest version of Power BI (or download the latest June 2021 release).
- Connect to Snowflake using Snowflake Connector for Power BI.
Architecture
Prerequisites
Before starting this walkthrough, you must have the following:
- The latest version of Power BI (June 2021 release). You may download the latest version here.
- A Snowflake account. If you do not have one, please register for a free 30-day trial account at trial.snowflake.com.
Confirm You’re Using the Latest Version of Power BI
To confirm you’re using the latest version of Power BI Desktop, click the Help menu item and then the About option. You’ll see the version name, which should indicate June 2021 (or later).
Connect to Snowflake Using Snowflake Connector for Power BI
In this release, the connector currently requires a fully qualified table name (DATABASENAME.SCHEMANAME.TABLENAME) in the query, and the database name field is required (even though the UI describes the field as optional). The Microsoft Power BI Team is working on continuing to improve and streamline the user experience.
To configure Power BI, complete the following steps:
1. Select Get Data from the menu, and then find the Snowflake Connector.
Tip: Type “Snowflake” in the search box to quickly find the Snowflake Connector.
2. Enter your server (Snowflake account URL), the Snowflake Warehouse, the Snowflake Role, and the Snowflake Database. Please note, the text currently says “(optional),” but the database name is currently required. Finally, add your SQL statement in the text box and hit OK.
3. If this is the first time you’re connecting to Snowflake in the report, you’ll see an authentication prompt; otherwise, you’ll receive the Connection Settings prompt to select Import or DirectQuery.
4. Once the connection is established, the result data is returned in the Power Query Editor for you to load and/or transform in Power Query. Once the data is loaded, you’re ready to build your reports and visualizations.
Summary
Power BI enables your team to work together easily on the same data, collaborate on reports, and share insights across the organization—empowering everyone in your organization to quickly make data-driven decisions that drive strategic actions. The Snowflake Data Cloud is a network that connects customers, partners, data providers, and data service providers, generally without actual data copied or transferred between providers and consumers. It enables them to derive value from sharing rapidly growing data sets with each other in secure, governed, compliant, and seamless ways. By leveraging Power BI and Snowflake, customers can accelerate their ability to generate insights, create powerful visualizations, and drive business decisions powered by Snowflake.