What Is a Question Folding in Energy BI and Why ought to You Care?

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

Or, possibly you’ve heard about question folding in Energy BI, however didn’t know tips on how to make the most of it in real-life eventualities.

For those who acknowledged your self in (not less than) one of many two conditions specified above, then please proceed studying this text.

Tremendous, you might be curious to search 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 among the key ideas within the information preparation part. Now, I wish to broaden on that in a (possibly) uncommon approach:

Supply Database — That is the obvious selection and usually probably the most fascinating state of affairs. It’s primarily based on conventional information warehousing rules of Extracting-Reworking-Loading (ETL) information. On this state of affairs, you outline what information you need to extract (not all information from the database is required, and it’s normally not a good suggestion to import all the information). Then, you determine in case your information must be reworked alongside the way in which, to fit your reporting wants higher — for instance, do you need to carry out foreign money conversion, or do you’ll want to conform nation and metropolis names?

Do you acknowledge town within the following picture?

Sure, it’s New York. Or, is it NYC? Or, is it New York Metropolis? Which one among these three names is appropriate? Sure, all of them are appropriate — however in case you import the information into your information mannequin like this, you’re going to get incorrect outcomes — as every New York, NYC, and New York Metropolis will probably be handled as a separate entity. This, and plenty of extra potential caveats, should be solved through the Knowledge Shaping part, and that’s why it’s vital to spend a while massaging your information.

Energy Question

For those who don’t carry out information transformations on the supply facet, the subsequent station is Energy Question — it’s the built-in software inside Energy BI, that lets you carry out every kind of transformations to your information. In accordance with Microsoft’s official documentation, you may apply greater than 300 completely different transformations!

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

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

What’s a Question folding?

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

By pushing complicated 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 giant volumes of information in probably the most environment friendly approach.

That potential 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 information supply facet, we are saying that the question folds.

Knowledge sources that help Question folding

As already talked about, the obvious beneficiary of question folding is relational database sources, corresponding to SQL Server, Oracle, or MySQL. Nevertheless, it`s not simply that SQL databases make the most of the question folding idea. Primarily, any information supply that helps some sort of querying language can probably make the most of question folding. These different information sources are OData, SSAS, SharePoint lists, Change, and Entra ID.

Then again, whenever you use information sources corresponding to Excel recordsdata, BLOB storage recordsdata, flat recordsdata, and so on. in your Energy BI datasets, the question can’t fold.

Knowledge Transformations that help Question folding

Nevertheless, with regards to information sources that help question folding usually, it’s vital to take into account that not all transformations could be folded and pushed to an information 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 refined variations within the Energy Question transformations could be decisive within the last final result, and whether or not your question will fold or not. I’ll present you a number of of these refined variations within the following sections.

Usually 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 primarily based on the identical supply, as this operation could be translated to JOIN in SQL. Once I mentioned, merging of foldable queries — which means it is going to work in case you are becoming a member of two SQL server tables, but it surely won’t work in case you are attempting to affix a SQL desk and an Excel file
• Appending foldable queries primarily based 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

Then again, some transformations that can forestall the question from folding are:

• Merging queries primarily based on completely different sources, as defined beforehand
• Appending (union-ing) queries primarily based on completely different sources — related logic as within the earlier case
• Including customized columns with complicated logic or utilizing some M features that don’t have a counterpart in SQL
• Including index columns
• Altering a column information kind. This one is a typical “it relies upon” case. I’ll present you quickly what it is dependent upon, however simply take into account that altering a column information kind could be each a foldable and a non-foldable transformation

Now, let’s study why you will need to obtain this conduct — or, possibly 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?

While you’re utilizing Import mode in Energy BI, the information refresh course of will work extra effectively when the question folds, each when it comes to refresh pace and useful resource consumption.

If you’re working with DirectQuery or Twin storage mode, as you might be concentrating on the SQL database instantly, all of your transformations MUST fold — or your resolution won’t work.

Lastly, question folding can be of key significance for Incremental refresh — it’s so vital 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 important objective — to scale back the quantity of information that must be refreshed in your information mannequin — as with out question folding, Mashup engine must retrieve all information from the supply after which apply subsequent steps to filter the information.

With all these in thoughts, you need to have a tendency to attain question folding at any time when doable.

Sluggish report — don’t blame Question folding!

One vital disclaimer right here, and this is among the key takeaways from this collection of weblog posts: in case your report is gradual, or your visuals want lots of time to render, or your information mannequin dimension is giant, question folding has nothing to do with it!

Provided that your information refresh or incremental refresh is gradual and inefficient, you need to examine your Energy Question steps in additional depth.

All or nothing?

A number of extra issues to remember concerning question folding. It’s not an all-or-nothing course of. Which means in case 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, in case 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 in case you have transformations that help question folding by default in steps 6 to 10 — like in our instance the place filtering must 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 doable.

How are you aware if the question folds?

Okay, now we aren’t rookies anymore. We all know what question folding is, why we must always attempt to attain it, and a few refined methods that may make an enormous distinction.

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

If it’s greyed out, this step in all probability doesn’t fold. Then again, if you’ll be able to click on on this selection, that implies that your question will fold. I suppose you might be possibly confused with the phrase: PROBABLY!

However, that’s the right phrase, as you may’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 pondering that the question folding was damaged, regardless that, in actuality, folding truly happens.

As a substitute, whenever you need to make sure in case your question folds or not, you should utilize the Question Diagnostics function inside Energy Question Editor, or SQL Server Profiler, like an excellent outdated and dependable approach to examine 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 mentioned, this function is offered solely in Energy Question On-line at this second, so let’s hope that the Energy BI staff will implement it within the Desktop model quickly.

The satan is within the particulars…

Tremendous…You’ve in all probability heard in regards to the saying that the satan is within the particulars. Now, it’s time to know how little nuances could make an enormous distinction in our information transformation course of.

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

Satan #1 — Merge Be part of

This one could be very attention-grabbing, 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 number of the columns from my reality desk, and preserve solely the CustomerKey column, as this can be 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. Primarily, we select the column on which we need to carry out MERGE operation, and the kind of be a part of (left, outer, or interior).

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 implies 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 attain precisely the identical consequence.

The important thing factor is that we’ll use an identical, however nonetheless completely different M operate: Desk.Be part of.

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

You keep in mind as soon as I instructed you that when the View Native Question is greyed out, your question in all probability doesn’t fold, but it surely’s not 100% appropriate. And, this can be a good instance. For those who check out View Native Question, it nonetheless reveals that our question doesn’t fold…

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

Oh, boy, we had been tricked — this step certainly folded! As you may see within the illustration above, we have now 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 kind, which we had been in a position to clear up by tweaking the robotically generated M code. And, the opposite one was the inaccurate conduct of the View Native Question possibility. I’ll present you within the subsequent a part of the collection yet one more instance when View Native Question lies.

Question folding in Energy BI — methods, lies & final efficiency check

I assume you at the moment are acquainted with the idea of question folding in Energy BI, and particularly with its significance for information refresh and incremental refresh processes. We’ve additionally began to scratch some attention-grabbing behaviors of Energy Question transformations, and on this last a part of the article, I’ll present you a number of extra attention-grabbing findings.

Lastly, we’ll wrap it up with the final word efficiency check — I’ll present you the precise numbers behind two similar queries — one folds, and the opposite doesn’t!

Altering Knowledge varieties

One of the vital frequent transformations in Energy Question is altering information kind. It’s a widely known greatest follow to make use of correct information varieties in your information mannequin — for instance, in case you don’t want hours, minutes, and seconds degree of granularity in your reviews, you ought to be higher off eliminating them and altering the information kind 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 refined distinction that may trigger your question to turn into rattling gradual, regardless that you’ve caught with the advice to make use of a correct information kind!

As you may spot within the illustration above, my OrderDate column is of Date/Time information kind. And, I need to swap it to Date solely. There are (not less than) two doable choices to do that — the primary one is to right-click on the column, broaden the drop-down for the Change Sort possibility (like I did within the illustration), and choose Date kind (slightly below the Date/Time):

A number of vital issues occurred right here, so let me clarify every of these:

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

Let’s now study the opposite possibility to vary information kind of our column:

Slightly below our earlier Change Sort possibility, there’s a Remodel possibility. When you broaden the drop-down, you may see the Date Solely transformation. Let’s click on on it and examine what occurs:

Appears to be like fairly related, does it? However, let’s stroll by all of the issues that occurred now:

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

So, one single completely different phrase within the M components (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 information varieties!

Liar, Liar…

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

Let’s say that we need to preserve 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 understand 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’s 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’d assume that this step broke my question folding!

Let’s examine this in our Question Diagnostics software:

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 help our transformation), double-check what actually occurs underneath the hood!

The final word efficiency check

Okay, if I didn’t handle to persuade you to date, why you need to attempt to attain question folding, let me now pull my final ace up my sleeve!

I need to present you the distinction in information refresh efficiency between the queries that return precisely the identical outcomes — one among 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 reveal the magnitude of significance of the question folding idea.

Within the first instance, I’ve utilized 9 completely different transformation steps, and all of them are foldable, as you may 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, after all, you may write rather more optimum SQL code — nevertheless, take into account that 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 information refresh lasts.

This question took 32 seconds to load 2.8 million data in my Energy BI report. Knowledge was loaded in batches of 100.000–150.000 data, which is an effective 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 kind to Date), utilizing the transformation for which I do know that’s not foldable:

Reality to be mentioned, 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 examine 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 information had been between 10.000 and 20.000 data.

However, what’s much more regarding — you may see that the whole variety of data loaded was virtually 11 million!!! As a substitute of two.8 million within the earlier instance! Why is it taking place? Effectively, within the earlier sections, I defined that when the Mashup engine can’t translate M language to SQL, it wants to drag ALL the information (from the second when the question folding was damaged), and THEN apply transformations on the entire chunk of imported information!

The ultimate result’s precisely the identical — we have now 2.830.017 data in our Energy BI report — however, with question folding in place, all essential transformations had been carried out on the SQL database facet, and the Mashup engine obtained an already ready information 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 in a position to apply different transformation steps.

And, this was only a fundamental instance, with one single desk, and never so massive when it comes to information quantity! Merely think about the magnitude of implications on a bigger dataset, with a number of tables in it.

Conclusion

Effectively, we coated quite a bit on this article. We discovered in regards to the information shaping idea, we launched Energy Question fundamentals, and we additionally discovered what question folding is and why we must always do our greatest to attain it.

I’ve additionally shared with you some fundamental examples and neat methods on tips on how to obtain question folding in some frequent use circumstances.

Ultimately, please bear in mind that the question folding is a piece in progress, and people from the Energy BI staff are consistently bettering this function. So, it will possibly occur that a number of the points with question folding I’ve proven you listed below are resolved within the meantime. Due to this fact, you’ll want to keep updated with the newest enhancements.

Thanks for studying!