What Is a Query Folding in Power BI and Why should You Care?

a question folding?” “Does your question fold?”… Perhaps somebody requested you these questions, however you had been like: “Question…Whaaaat?!”

Or, perhaps you’ve heard about question folding in Energy BI, however didn’t know the best way to benefit from it in real-life situations.

In the event you acknowledged your self in (no less than) one of many two conditions specified above, then please proceed studying this text.

Fantastic, you might be curious to seek out out what a Question folding is. However, first issues first…Earlier than you proceed, we have to set up some theoretical foundations, which can put the Question folding function within the correct context.

Knowledge Shaping

 and why it is one of the key concepts in the data preparation phase. Now, I would like to expand on that in a (maybe) unusual way:

Source Database — This is the most obvious choice and in most cases the most desirable scenario. It is based on traditional data warehousing principles of Extracting-Transforming-Loading (ETL) data. In this scenario, you define what data you want to extract (not all data from the database is needed, and it’s usually not a good idea to import all the data). Then, you determine in case your knowledge must be reworked alongside the best way, to fit your reporting wants higher — for instance, do you need to carry out foreign money conversion, or do it is advisable conform nation and metropolis names?

Do you acknowledge the town within the following picture?

Sure, it’s New York. Or, is it NYC? Or, is it New York Metropolis? Which one in every of these three names is appropriate? Sure, all of them are appropriate — however if you happen to import the info into your knowledge mannequin like this, you’ll get incorrect outcomes — as every New York, NYC, and New York Metropolis can be handled as a separate entity. This, and lots of extra potential caveats, must be solved through the Knowledge Shaping section, and that’s why it’s essential to spend a while massaging your knowledge.

Energy Question

In the event you don’t carry out knowledge transformations on the supply facet, the following station is Energy Question — it’s the built-in device inside Energy BI, that enables you to perform all kinds of transformations to your data. In line with Microsoft’s official documentation, you’ll be able to apply greater than 300 totally different transformations!

The important thing benefit of Energy Question is which you could carry out advanced knowledge transformations with little or no coding abilities! Moreover, all steps you’ve utilized through the knowledge transformation course of are being saved, so each time you refresh your dataset, these steps can be routinely utilized to form your knowledge and put together it for consumption by way of studies.

Below the hood of Energy Question is a Mashup engine, that allows your knowledge shaping to run easily. Energy Question makes use of a really highly effective M language for knowledge manipulation. And, now you might be most likely asking yourselves, what does all this story about knowledge shaping, Energy Question, Mashup engine, M language, and so forth. need to do with Question folding? I don’t blame you, it’s a good query, however we are going to come again quickly to reply it.

What’s a Question folding?

For some knowledge sources, equivalent to relational databases, but additionally non-relational knowledge sources, for instance, OData, AD, or Trade, the Mashup engine is ready to “translate” M language to a language that the underlying knowledge supply will “perceive” — usually, it’s SQL.

By pushing advanced calculations and transformations on to a supply, Energy Question leverages the capabilities of the sturdy relational database engines, which might be constructed to deal with massive volumes of information in essentially the most environment friendly manner.

That means of Energy Question’s Mashup engine to create a single SQL assertion combining all M statements behind your transformations is what we name Question folding.

Or, let`s make it easy: if the Mashup engine is ready to generate a single SQL question that’s going to be executed on the info supply facet, we are saying that the question folds.

Knowledge sources that assist Question folding

As already talked about, the obvious beneficiary of question folding is relational database sources, equivalent to SQL Server, Oracle, or MySQL. Nevertheless, it`s not simply that SQL databases benefit from the question folding idea. Basically, any knowledge supply that helps some type of querying language can probably benefit from question folding. These different knowledge sources are OData, SSAS, SharePoint lists, Trade, and Entra ID.

Alternatively, whenever you use knowledge sources equivalent to Excel recordsdata, BLOB storage recordsdata, flat recordsdata, and so forth. in your Energy BI datasets, the question can’t fold.

Knowledge Transformations that assist Question folding

Nevertheless, in the case of knowledge sources that assist question folding generally, it’s essential to needless to say not all transformations could be folded and pushed to a knowledge supply. So, simply to be clear, the truth that a SQL database helps question folding doesn’t essentially imply that your question will fold! There are some Energy Question transformations that merely can`t be pushed to a SQL database engine.

Fairly often, some delicate variations within the Energy Question transformations could be decisive within the closing final result, and whether or not your question will fold or not. I’ll present you a couple of of these delicate variations within the following sections.

Typically talking, the next transformations, when utilized in Energy Question, could be “translated” to a single SQL assertion:

• Eradicating columns
• Renaming columns
• Filtering rows, with static values or Energy Question parameters, as they’re handled as WHERE clause predicates in SQL
• Grouping and summarizing, that are equal to SQL’s Group by clause
• Merging of foldable queries based mostly on the identical supply, as this operation could be translated to JOIN in SQL. After I stated, merging of foldable queries — meaning it’ll work in case you are becoming a member of two SQL server tables, nevertheless it won’t work in case you are making an attempt to hitch a SQL desk and an Excel file
• Appending foldable queries based mostly on the identical supply — this transformation pertains to the UNION ALL operator in SQL
• Including customized columns with easy logic. What does easy logic imply? Utilizing M features which have equivalents in SQL language, for instance, mathematical features, or textual content manipulation features
• Pivot and Unpivot transformations

Alternatively, some transformations that may stop the question from folding are:

• Merging queries based mostly on totally different sources, as defined beforehand
• Appending (union-ing) queries based mostly on totally different sources — comparable logic as within the earlier case
• Including customized columns with advanced logic or utilizing some M features that don’t have a counterpart in SQL
• Including index columns
• Altering a column knowledge sort. This one is a typical “it relies upon” case. I’ll present you quickly what it is dependent upon, however simply needless to say altering a column knowledge sort could be each a foldable and a non-foldable transformation

Now, let’s study why you will need to obtain this habits — or, perhaps it’s higher to say, why do you have to care if the question folds or not?

Why do you have to care about Question folding?

Whenever you’re utilizing Import mode in Energy BI, the info refresh course of will work extra effectively when the question folds, each by way of refresh pace and useful resource consumption.

In case you are working with DirectQuery or Twin storage mode, as you might be concentrating on the SQL database immediately, all of your transformations MUST fold — or your answer won’t work.

Lastly, question folding can also be of key significance for Incremental refresh — it’s so essential that Energy BI will warn you as soon as it determines that question folding can’t be achieved. It won’t break your incremental refresh “per-se”, however with out question folding in place, an incremental refresh wouldn’t serve its primary goal — to cut back the quantity of information that must be refreshed in your knowledge mannequin — as with out question folding, Mashup engine must retrieve all knowledge from the supply after which apply subsequent steps to filter the info.

With all these in thoughts, it’s best to have a tendency to realize question folding every time potential.

Gradual report — don’t blame Question folding!

One essential disclaimer right here, and this is among the key takeaways from this collection of weblog posts: in case your report is sluggish, or your visuals need a lot of time to render, or your data model size is large, question folding has nothing to do with it!

Provided that your knowledge refresh or incremental refresh is sluggish and inefficient, it’s best to examine your Energy Question steps in additional depth.

All or nothing?

Just a few extra issues to bear in mind relating to question folding. It’s not an all-or-nothing course of. Meaning when you have, let’s say, 10 transformation steps inside Energy Question, and your question folds till the sixth step, you’ll nonetheless get some profit from partial question folding. Nevertheless, as soon as the question folding is damaged, it will possibly’t be achieved anymore.

To simplify, when you have 10 transformation steps, and your question folding is damaged within the fifth step, all earlier steps will fold, however as soon as the folding is damaged, it will possibly’t be achieved once more, even when you have transformations that assist question folding by default in steps 6 to 10 — like in our instance the place filtering needs to be a foldable step, these steps won’t fold. Maintain that in thoughts, and attempt to push all non-foldable steps down the pipeline as a lot as potential.

How are you aware if the question folds?

Okay, now we’re not rookies anymore. We all know what question folding is, why we must always try to realize it, and a few delicate tips that may make an enormous distinction.

Now, it’s time to discover ways to verify if the particular question folds or not. The primary and most evident manner is to right-click on the step and verify what the View Native Question possibility seems like.

If it’s greyed out, this step most likely doesn’t fold. Alternatively, if you’ll be able to click on on this selection, that signifies that your question will fold. I assume you might be perhaps confused with the phrase: PROBABLY!

However, that’s the right phrase, as you’ll be able to’t be 100% certain that if the View Native Question possibility is disabled, your question doesn’t fold. I’ll present you later how this selection can trick us into considering that the question folding was damaged, though, in actuality, folding truly happens.

As an alternative, whenever you need to make certain in case your question folds or not, you should use the Question Diagnostics function inside Energy Question Editor, or SQL Server Profiler, like a great previous and dependable technique to verify the queries despatched to a database by the Energy BI engine.

Moreover, there’s a cool function in Energy Question On-line, the place every step is marked with the icon that reveals if that step folds, doesn’t fold, or is unknown. As I stated, this function is out there solely in Energy Question On-line at this second, so let’s hope that the Energy BI group will implement it within the Desktop model quickly.

The satan is within the particulars…

Fantastic…You’ve most likely heard in regards to the saying that the satan is within the particulars. Now, it’s time to know how little nuances could make a giant distinction in our knowledge transformation course of.

Let’s begin with one of the curious instances in Energy Question editor…

Satan #1 — Merge Be a part of

This one could be very fascinating, as you’ll hardly assume what is going on within the background. Let’s say that I need to mix two of my queries into one. I’ll use the Journey Works pattern database, and I must merge the FactInternet Gross sales and DimCustomer tables.

I’ll take away a few of the columns from my reality desk, and maintain solely the CustomerKey column, as it is a overseas key to a DimCustomer desk, and the Gross sales Quantity column. I’ll be a part of the DimCustomer desk as it’s, with none further steps earlier than merging.

Merging tables is equal to JOIN operation in SQL. Basically, we select the column on which we need to carry out MERGE operation, and the kind of be a part of (left, outer, or inside).

The issue is that by default, whenever you’re merging two queries, Energy Question will generate a nested be a part of assertion, which might’t be correctly translated in SQL.

If I am going to the Instruments tab and click on on Diagnose Step, I can see that the Mashup engine fired two separate queries to my underlying SQL Server database — in different phrases, these two queries couldn’t be executed as a single SQL assertion, and that signifies that question didn’t fold!

How can we clear up this? Let’s simply select a clean question and write our M code by hand to realize precisely the identical consequence.

The important thing factor is that we’ll use the same, however nonetheless totally different M operate: Desk.Be a part of.

All operate arguments are precisely the identical as beforehand, and let’s now verify the result.

You keep in mind as soon as I instructed you that when the View Native Question is greyed out, your question most likely doesn’t fold, nevertheless it’s not 100% appropriate. And, it is a good instance. In the event you check out View Native Question, it nonetheless reveals that our question doesn’t fold…

…however let’s go to Diagnostics and verify if that’s true.

Oh, boy, we had been tricked — this step certainly folded! As you’ll be able to see within the illustration above, we’ve a single SQL question generated and despatched to a SQL Server supply database to be executed.

So, we discovered two devils on this instance — the primary one was a be a part of sort, which we had been capable of clear up by tweaking the routinely generated M code. And, the opposite one was the inaccurate habits of the View Native Question possibility. I’ll present you within the subsequent a part of the collection another instance when View Native Question lies.

Question folding in Energy BI — tips, lies & final efficiency take a look at

I assume you are actually acquainted with the idea of question folding in Energy BI, and particularly with its significance for knowledge refresh and incremental refresh processes. We’ve additionally began to scratch some fascinating behaviors of Energy Question transformations, and on this closing a part of the article, I’ll present you a couple of extra fascinating findings.

Lastly, we are going to wrap it up with the last word efficiency take a look at — I’ll present you the precise numbers behind two an identical queries — one folds, and the opposite doesn’t!

Altering Knowledge sorts

Some of the frequent transformations in Energy Question is altering knowledge sort. It’s a widely known best practice to use proper data types in your knowledge mannequin — for instance, if you happen to don’t want hours, minutes, and seconds degree of granularity in your studies, try to be higher off eliminating them and altering the info sort of that column from Date/Time to Date solely.

Nevertheless, the highway to hell is paved with good intentions:)…So, let me present you one delicate distinction that may trigger your question to grow to be rattling sluggish, though you’ve caught with the advice to make use of a correct knowledge sort!

As you’ll be able to spot within the illustration above, my OrderDate column is of Date/Time knowledge sort. And, I need to change it to Date solely. There are (no less than) two potential choices to do that — the primary one is to right-click on the column, develop the drop-down for the Change Sort possibility (like I did within the illustration), and choose Date sort (just under the Date/Time):

Just a few essential issues occurred right here, so let me clarify every of these:

1. Within the Utilized Steps pane, you’ll be able to discover that our transformation step had been recorded
2. Within the column itself, you’ll be able to see that the time portion disappeared
3. After I’ve opened the View Native Question dialog field, you’ll be able to see that the Mashup engine properly translated our transformation to a T-SQL CONVERT() operate
4. The M method utilized to this transformation step is: Desk.TransformColumnTypes()

Let’s now study the opposite possibility to vary knowledge sort of our column:

Slightly below our earlier Change Sort possibility, there’s a Remodel possibility. When you develop the drop-down, you’ll be able to see the Date Solely transformation. Let’s click on on it and verify what occurs:

Appears fairly comparable, does it? However, let’s stroll by way of all of the issues that occurred now:

1. As an alternative of the Modified Sort step, we now have a step known as Extracted Date
2. The column itself seems precisely the identical as within the earlier instance — no time half in there
3. Ooops, the question doesn’t fold anymore! As you’ll be able to see, the View Native Question possibility is greyed out!
4. This time, M method utilized is: Desk.TransformColumns()

So, one single totally different phrase within the M method (Desk.TransformColumnTypes vs Desk.TransformColumns) affected our question so arduous that it couldn’t be translated to SQL!

Takeover from this story: watch out, and be careful whenever you’re selecting choices for altering knowledge sorts!

Liar, Liar…

I’ve promised within the earlier a part of the article that I’ll present you another instance when the View Native Question possibility can idiot you into considering that question folding was damaged, even when in actuality it’s not true…

Let’s say that we need to maintain solely the highest X rows from our desk. In my case, I need to protect the highest 2000 rows from my reality desk:

As soon as I’ve utilized this step and checked the View Native Question, I can notice that my question folds, as my transformation was translated to a TOP clause in SQL:

Now, let’s say that I need to apply Absolute worth transformation on my Gross sales Quantity column. Usually, this transformation simply folds, as there may be an ABS operate in T-SQL:

Nevertheless, if I right-click on this step, I’ll see that the View Native Question possibility is greyed out, so I might assume that this step broke my question folding!

Let’s verify this in our Question Diagnostics device:

Oh, my God! This step folded certainly! So, we had been tricked by the View Native Question possibility once more!

The important thing takeover right here is: everytime you’re assuming {that a} particular transformation step could be folded (like on this instance, after we knew that SQL has an ABS operate to assist our transformation), double-check what actually occurs below the hood!

The last word efficiency take a look at

Okay, if I didn’t handle to persuade you up to now, why it’s best to try to realize question folding, let me now pull my final ace up my sleeve!

I need to present you the distinction in knowledge refresh efficiency between the queries that return precisely the identical outcomes — one in every of them folds, and the opposite doesn’t!

Take a look at #1 Question folding ON

For this testing, I’ll use the FactOnlineSales desk from the Contoso pattern database. This desk has round 12.6 million rows, and it’s good to display the magnitude of significance of the question folding idea.

Within the first instance, I’ve utilized 9 totally different transformation steps, and all of them are foldable, as you’ll be able to see within the following illustration:

Don’t take note of the SQL code that the Mashup engine generated: in case you are a SQL skilled, in fact, you would write way more optimum SQL code — nonetheless, needless to say with auto-generated scripts by the Mashup engine, you aren’t getting the most optimum SQL — you might be simply getting appropriate SQL!

I’ll hit Shut & Apply and activate my stopwatch to measure how a lot time my knowledge refresh lasts.

This question took 32 seconds to load 2.8 million information in my Energy BI report. Knowledge was loaded in batches of 100.000–150.000 information, which is an efficient indicator that the question folding is in place.

Take a look at #2 Question folding OFF

Now, I’ll return to Energy Question Editor, and deliberately break question folding on the third step (keep in mind the instance above with altering Date/Time sort to Date), utilizing the transformation for which I do know that isn’t foldable:

Reality to be stated, I’ll obtain a partial folding right here, as first two steps will fold, however all subsequent steps after the Extracted Date transformation won’t fold!

Let’s activate the stopwatch once more and verify what occurs:

The very first thing to note: this question took 4 minutes and 41 seconds to load into our Energy BI report, which is roughly 10 occasions extra than in our earlier case when the question folded. This time, batches of loaded knowledge had been between 10.000 and 20.000 information.

However, what’s much more regarding — you’ll be able to see that the overall variety of information loaded was nearly 11 million!!! As an alternative of two.8 million within the earlier instance! Why is it occurring? Properly, within the earlier sections, I defined that when the Mashup engine can’t translate M language to SQL, it wants to tug ALL the info (from the second when the question folding was damaged), and THEN apply transformations on the entire chunk of imported knowledge!

The ultimate result’s precisely the identical — we’ve 2.830.017 information in our Energy BI report — however, with question folding in place, all vital transformations had been carried out on the SQL database facet, and the Mashup engine acquired an already ready knowledge set. Whereas within the second state of affairs, after we broke the question folding, the Mashup engine pulled the entire remaining rows (approx. 11 million), and solely after that was it capable of apply different transformation steps.

And, this was only a fundamental instance, with one single desk, and never so large by way of knowledge quantity! Merely think about the magnitude of implications on a bigger dataset, with a number of tables in it.

Conclusion

Properly, we lined loads on this article. We discovered in regards to the knowledge shaping idea, we launched Energy Question fundamentals, and we additionally discovered what question folding is and why we must always do our greatest to realize it.

I’ve additionally shared with you some fundamental examples and neat tips on the best way to obtain question folding in some frequent use instances.

In the long run, please remember that the question folding is a piece in progress, and people from the Energy BI group are continuously enhancing this function. So, it will possibly occur that a few of the points with question folding I’ve proven you listed below are resolved within the meantime. Subsequently, you should definitely keep updated with the newest enhancements.

Thanks for studying!