Blog/AI & ML/Custom SQL Now Supported in the Snowflake Connector for Power BI!
SEP 02, 2021/5 min readAI & ML

Custom SQL Now Supported in the Snowflake Connector for Power BI!

Custom SQL Now Supported in the Snowflake Connector for Power BI!

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:

  1. Confirm you’re using the latest version of Power BI (or download the latest June 2021 release).
  2. Connect to Snowflake using Snowflake Connector for Power BI.

Architecture

Prerequisites

Before starting this walkthrough, you must have the following:

  • 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.

Snowflake 
mysnowflakeaccount.snowfakecomputing.com 
Warehouse 
PowerBl_WH 
Advanced options 
Specify a text value to use as Role name (optional) 
ANALYST 
Command timeout in seconds (optional) 
Connection timeout in seconds (optional)
SQL statement (optional, requires database) 
SELECT * 
FROremoving filters.","results_filtered_by_with_count":"{{count}} Result filtered by","results_filtered_by_with_count_one":"{{count}} Result filtered by","results_filtered_by_with_count_other":"{{count}} Results filtered by","search_field":"Search field","close_filter":"Close filter","other_content_in_this_stream":"Other Content in this Stream","view":"View","grid_view":"Grid view","list_view":"List view","industry":"Industry","location":"Location","product_cat_used":"Snowflake Product Categories Used","learn_more":"Learn More","read_more":"Read More","view_more":"View more","view_less":"View less","register_now":"Register Now","watch_now":"Watch Now","listen_now":"Listen Now","platform":"Platform","region":"Region","select_platform":"Select a platform","select_region":"Select a region","modal_window_collapsed_message":"The modal window (ID: {{id}}) is currently collapsed in the editor view. To reveal it, please ensure that the 'Collapse modal in AEM Editor' option is unchecked.","newest_oldest":"Newest - Oldest","oldest_newest":"Oldest - Newest","a_z":"A - Z","z_a":"Z - A","nearest":"Nearest","farthest":"Farthest","iframe_validation":"Code snippet should start and end with iframe tags.","min_read_with_count":"{{count}} min read","learn_more_about_authors":"Learn more about the authors","learn_more_about_authors_one":"Learn more about the author","learn_more_about_authors_other":"Learn more about the authors","back":"Back","just_for_you":"Just For You","share_article":"Share Article","more_blog_posts":"More blog posts","top_voices":"Top Voices","solution_areas":"Solution Areas","workload_specializations":"Workload Specializations","snowpro_core_certications_with_count":"Snowpro Core Certifications: {{count}}","snowpro_core_certications_with_count_one":"Snowpro Core Certification: {{count}}","snowpro_core_certications_with_count_other":"Snowpro Core Certifications: {{count}}","snowpro_advanced_certications_with_count":"Snowpro Advanced Certifications: {{count}}","snowpro_advanced_certications_with_count_one":"Snowpro Advanced Certification: {{count}}","snowpro_advanced_certications_with_count_other":"Snowpro Advanced Certifications: {{count}}","headquarters_with_value":"<0>Headquarters: {{value}}","skip_to_content":"Skip to content","version":"Version","file_name":"File Name","architecture":"Architecture","size":"Size","release_date":"Release Date","sha256_checksum":"SHA256 Checksum","client":"Client","download":"Download","documentation":"Documentation","plus_more":"+More","months_shorthand":{"jan":"jan","feb":"feb","mar":"mar","apr":"apr","may":"may","jun":"jun","jul":"jul","aug":"aug","sep":"sep","oct":"oct","nov":"nov","dec":"dec"},"share":"Share","share_event":"Share Event","twitter":"X","email":"Email","facebook":"Facebook","linkedin":"LinkedIn","password":"Password","password_error":"Incorrect password entered. Please try again.","password_enter":"Enter password to continue","password_content_protected":"This content is password protected. To access, please enter the password into the field below:","need_help":"Need help?","contact_representative":"Contact your Snowflake Account Representative","view_quickstart":"View Quickstart","fork_repo":"Fork Repo","watch_the_demo":"Watch the Demo ({{value}})","by":"By","published_with_date":"Published: {{val, shortDate(datetime)}}","updated_with_date":"Updated: {{val, shortDate(datetime)}}","watch_the_demo_modal_msg":"Please configure a modal for this button. \nModalId: {{modalId}} \nYoutube video ID: {{videoId}}","short_date":"{{val, shortDate(datetime)}}","narrow_date":"{{val, narrowDate(datetime)}}","search_by_address":"Search by an address, city, neighborhood or state","mapbox_address_sr_instructions_with_count":"Type at least {{count}} characters to search. Use arrow keys to navigate results. Press Enter to select. Press Escape to close.","mapbox_address_sr_instructions_with_count_one":"Type at least {{count}} character to search. Use arrow keys to navigate results. Press Enter to select. Press Escape to close.","mapbox_address_sr_instructions_with_count_other":"Type at least {{count}} characters to search. Use arrow keys to navigate results. Press Enter to select. Press Escape to close.","clear_search":"Clear search","finding_your_location":"Finding your location...","search_suggestions":"Search Suggestions","use_my_current_location":"Use my current location","loading":"Loading...","no_results":"No results found","no_results_location_description_reset":"Try expanding the radius, searching a different location, or <0>resetting your filters.","no_results_location_description":"Or, explore virtual events which can be attended from anywhere in the world!","results_available_with_count":"{{count}} results available","results_available_with_count_one":"{{count}} result available","results_available_with_count_other":"{{count}} results available","close_error_dialog":"Close error dialog","oops_location_error_title":"Oops, we can’t find your location","oops_location_error_description":"We couldn’t find you quickly enough! Try again later, or search near a city, place or an address instead.","clear_all":"Clear All","distance":"Distance","within_unit":"Within {{value}} {{unit}}","apply_filters":"Apply filters","sort_with_value":"Sort {{value}}","related_content_empty":"The cards won’t render in the author environment!","on_this_page":"On this page","ready_to_get_started":"Ready to get started?","youtube_video_thumbnail":"YouTube video thumbnail","featured":"Featured","more":"More","category":"Category","search":"Search","search_placeholder":"Search Snowflake.com","close_search":"Close search","submit_search":"Submit search","coveo_content_type":"Content Type","coveo_relevance":"Relevance","coveo_newest":"Newest","coveo_oldest":"Oldest","coveo_no_results_title":"No results available for <1>{{query}}.","coveo_no_results_subtitle":"Try adjusting the filters or start a new search.","coveo_no_results_cta":"Start a new search","coveo_no_results_image_alt":"","coveo_filters":"Filters","coveo_close_filters":"Close filters","coveo_view_results":"View results"}}},"usedSvgs":["SnowflakeLogoDesktop","SnowflakeLogoMobile","CaretDown","Language","CheckMarkImage","Search","Link","Cloud","ArrowDown","Code","Linkedin","Twitter","Facebook","ArrowRight"],"usedComponents":["snowflake-site/components/markup-editor","snowflake-site/components/mega-header","svg-SnowflakeLogoDesktop","svg-SnowflakeLogoMobile","snowflake-site/components/nav/nav-mega","svg-CaretDown","svg-Language","svg-CheckMarkImage","svg-Search","svg-Link","snowflake-site/components/blog/sub-navigation","svg-Cloud","svg-ArrowDown","svg-Code","snowflake-site/components/blog/breadcrumb","snowflake-site/components/flexible-column-container","snowflake-site/components/flexible-column-container/flexible-column-content-container","snowflake-site/components/blog/blog-hero","svg-Linkedin","svg-Twitter","svg-Facebook","snowflake-site/components/blog/blog-text","snowflake-site/components/blog/blog-title","snowflake-site/components/youtube","snowflake-site/components/premium-content-banner","snowflake-site/components/blog/author-chip","svg-ArrowRight","snowflake-site/components/blog/blog-table-of-content","snowflake-site/components/blog/related-content","snowflake-site/components/form/embedded/marketo"],"isInEditor":false,"deviceType":"desktop"}