In this tutorial, we’ll learn how to connect to SQL server. We’ll also discuss how query folding works in Power Query and how we can run SQL commands in Power BI. Inside Power Query, we can extract and manipulate data from various sources. With query folding, we can offload data transformations to the source instead of doing them in Power BI.
Query folding is very effective with large databases to ensure performance improvements for your reports.
Connecting To A SQL Server Database For Query Folding
We’ll be doing this tutorial in a Power Query editor. First, click SQL Server under New Source.
If you’ve already installed a SQL server machine and working independently, the Server name will be localhost. However, if you are working in an organization, they will provide you the server name and access to their database. We also need to provide the database name. In this example, I’ll use the AdventureWorksDW2012. You can learn how to download this sample database from this tutorial.
For the Data Connectivity Mode, all the data that we’ll select will be loaded in the model if we select Import. But if we choose DirectQuery, nothing will be loaded in the data model but everything will be in the database. Whenever we apply a filter, the query will be sent back to the SQL Server.
But this is not efficient because it will take more time to update. Therefore, we’ll choose Import as the Data Connectivity Mode.
Then, if we click the Advanced options, it’ll provide us a section where we can write a SQL statement. We’ll learn how to do this later.
In this example, we only want one table at a time so we won’t bring in relationship columns or tables. In that case, we have to untick the option for that.
Lastly, click OK.
After that, we’ll be able to see the tables that are available and from there, we can have some information. From this example, we need to get the data from fact internet sales. Hence, let’s search and select FactInternetSales then click Ok.
As a result, we’ll now have the data on our Power Query editor.
Understanding Query Folding In Power Query
Within the Source panel, right-click Navigation then select View Native Query.
With that, we’ll be able to see the command that was executed by this machine. Power Query engine created this command to run in SQL Server. Let’s now click Ok.
As an example, I randomly created a filter in this table just to show you that once we create those, we’ll be able to see them in the APPLIED STEPS panel.
When we right-click one of the filters, we’ll see that the View Native Query option is still available.
This SQL Query from our filter has been run in the SQL Server. When we haven’t applied the filter, we’re getting 5 million rows. Now that we’ve applied a filter, we’re only getting 4 million rows.
This means that Power BI is now extracting 4 million rows instead of 5 million rows from SQL Server. With this, the number of rows has reduced and the number of loads from our network is reduced as well.
Keep in mind that as long as we see the Native Query, it means that query folding is working. Therefore, all the processing is being done within the source system. This is the most efficient way to process data especially if you have a large volume of data.
We’ve added another step where we removed one column from our table. If we right-click on it, we’ll see the View Native Query option which means that it’s still working.
Identifying And Fixing A Broken Query Folding
With some transformations like changing a column’s data type, the Query Folding will break. For example, we’ll change the TaxAmt column data type to Whole Number.
This will add a Change Type step under the APPLIED STEPS. If we right-click on it, we’ll see that the View Native Query is now disabled, which means that the Query Folding is broken.
When a Query Folding is broken, any other transformation that we’ll do will be done in the Power BI Power Query but not in the source system anymore.
For example, if we’re getting 3 million rows, all of them will come in Power Query. We can still reduce those records through filtering. However, those 3 million rows will now come across the network, which is not very efficient.
For another example, let’s say we want to filter the OrderDate to only display the dates after January 1, 2012.
If we view that filter on the APPLIED STEPS, the View Native Query option will not be visible.
Again, that’s because the Query Folding was broken due to the previous transformation that we’ve created. What we can do is to move all the filtering steps that we’ll do above the transformation step that broke the Query Folding.
For this example, we’ll just right-click on the recent filtering step that we’ve created and click Move before, or just drag it at the top of the Change Type transformation.
If we right-click on that filter again, we’ll see that the View Native Query option is now visible, which means that the Query Folding is working again.
Benefits Of Connecting SQL Server And Running SQL Language
Let’s say for example that we want to display data with a total sales by country format as shown in the image.
On our FactInternetSales table, we have the SalesAmount column but we don’t have country information on it.
We can still get country information because we have the SalesTerritoryKey column.
What we need to do is to bring the DimSales table here so we could merge it with our FactInternetSales. Then, we need to bring the country column and group them by country column which is very complex and might take a long time. So, instead of doing all of that in Power Query which is not efficient, we should do it on SQL instead.
To do that, just click New Source > SQL Server.
Let’s connect again to our server named localhost, and AdventureWorksDW2012 as our database.
This time, we want to do an advanced option because we want to write a command under the SQL statement field. For this example, we’ve already written a command that we’ll enter on the SQL statement. You can learn about SQL commands in our other tutorials.
We don’t want to include relationship columns, so we’ll uncheck this option here. Then, click the OK button to run this command.
After executing the command, we’ll see this preview window showing a Total Sales by Sales Territory Region.
So, we’ve been able to get a similar output of SalesByCountry table by using a simple SQL command instead of manipulating different tables and columns on our Power Query.
Another benefit is that we can transform all of our data in SQL and only bring the data needed or required to our data model. With this, we can build a very efficient data model according to our plan without any difficulty or problems.
***** Related Links *****
SQL Server Download And Installation
SQL For Power BI Users – New Enterprise DNA Course
Querying Data From Multiple Data Sources
Conclusion
In this tutorial, we’ve learned what a Query Folding is and discovered its benefits. We’ve also discussed the steps on how to connect Power Query to SQL Server.
Moreover, we talked about the benefits of connecting to SQL Server and creating transformations on the SQL Server instead of doing them on Power Query.
Hopefully, you were able to see how doing all the transformations in SQL is more efficient and faster compared to doing it on Power Query.
All the best,
Hafiz
