Understanding different cache types in Power BI | by Nikola Ilic | Mar, 2023

Do you know that Energy BI depends on two totally different cache varieties? On this article, we’ll demystify how every of them works in actual life

What number of occasions did you end up within the following scenario? Whenever you open the report for the primary time, it takes some time to render, however when you shuttle from different report pages, that very same web page renders considerably quicker!

Yeah, I do know, we’ve all been there a number of occasions. That’s taking place as a result of Energy BI caches the info and might reply a lot quicker after that first preliminary run.

Sounds straightforward, proper? Nicely, it’s not simply easy as that and this text will attempt to demystify totally different cache varieties in Energy BI.

Really helpful studying earlier than the beginning: Since I’ll be referring to a number of the Energy BI inside structure elements, particularly Storage Engine and System Engine, I recommend you first read this article to know the distinction between the 2. You also needs to perceive the different roles that these two engines carry out within the course of of information retrieval. That is of paramount significance, as a result of the remainder of this text will assume that you’re conscious of the important thing traits of each Storage and System engine.

Let’s kick it off by explaining two important cache varieties from a high-level perspective, after which we’ll dig deeper to clarify nuances for every of those varieties.

Let’s begin with a quite simple instance. I’ll be utilizing a pattern Contoso database for the entire demos.

I’ve one clustered column chart visible, displaying the overall gross sales quantity for every model that exists within the Contoso database. There may be additionally a slicer for a model title. Let’s activate Efficiency Analyzer in Energy BI Desktop, and select one of many values within the slicer:

As it’s possible you’ll discover, a System Engine generated a DAX question to retrieve the info about Contoso model gross sales, and Storage Engine wanted 14ms to bodily return that information. Since we’re utilizing an Import storage mode and I’m within the Energy BI Desktop, the info is saved within the native occasion of the Evaluation Companies.

Let’s now change the slicer worth to Litware:

Once more, the identical workflow occurred as within the earlier case. Now, what occurs if I change again to Contoso in my slicer?

Issues turn out to be attention-grabbing now! There isn’t a DAX question in any respect and Copy question possibility, that allows us to seize the question and analyze it in more detail in, let’s say, DAX Studio — is greyed out! Meaning, there was no question generated by the System engine, and the info for this visible was served from the cache. On this case, we’re speaking in regards to the visible cache.

The identical will occur if I once more choose Litware within the slicer. Nevertheless, as soon as I click on on the Refresh visuals possibility on the prime…

Regardless of retrieving the info for Contoso once more, on this case, the visible cache was cleared and the System engine generated a DAX question as soon as extra.

Clearly, on this tremendous fundamental instance, it’s not straightforward to identify a major distinction in efficiency between the 2 situations. However, in actuality, we often apply extra complicated logic, and retrieving question outcomes from the cache is often dramatically quicker than working the identical question again and again.

If I now hook up with my Evaluation Companies native occasion from DAX Studio, and activate All Queries, as soon as I hit Refresh visuals, all of the queries might be captured by DAX Studio:

From right here, I’ll double-click the primary question and execute it throughout the DAX Studio:

This desk, which comprises question outcomes, might be cached by the report. And, at any time when our visible asks for a similar consequence, the info will be served from the cache.

Okay, within the instance above, we defined how Energy BI will cache question outcomes when working with Energy BI Desktop, within the native occasion of Evaluation Companies. The reputable query can be: what occurs as soon as we transfer to Energy BI Service? Does this cache “thingy” nonetheless work?

The reply is — YES! On this case, it’s executed utilizing your internet browser. Nevertheless, remember that the visible cache has a scope of the precise Energy BI session. We’ll come later to clarify in additional element how this works.

The information cache is one other cache sort in Energy BI. Not like within the earlier state of affairs, the place caching happens on the extent of the person report person, information cache operates on a extra generic degree — the extent of the Evaluation Companies tabular mannequin.

When you did your homework and browse the articles I advised firstly, you’re most likely conscious that VertiPaq shops our Contoso information in-memory, in a compressed manner.

So, what precisely occurs after we requested Energy BI to calculate the overall gross sales quantity for the Contoso model? System Engine generates and executes a DAX question, however then Storage Engine interprets DAX to a particular SQL-like language, referred to as xmSQL, to bodily pull the info from the tabular mannequin.

For each xmSQL question, there’s a particular information construction, referred to as datacache that’s being saved in-memory.

If I activate Server Timings in DAX Studio and run the DAX question captured within the earlier instance:

As it’s possible you’ll discover, the question outcomes had been retrieved from the cache (we now have one Storage Engine question, and that one Storage Engine question was utilizing a cache). This implies, in actuality, we didn’t actually question the Evaluation Companies mannequin. To substantiate that that is taking place, I’ll activate the Cache tab within the DAX Studio, and also you’ll see on line 1 that this question was really served from the cache, and never from the inner information buildings of the Evaluation Companies.

Now, there are no less than two vital issues to remember concerning the info cache.

First, it MAY trick you to assume that your question is working quick, though it won’t be true. Let’s say that you’re troubleshooting a poorly performing question and also you leverage DAX Studio to get extra perception into what is going on within the background. You run the question for the primary time, and it required 2000 ms to return the outcomes.

You then apply some minor modifications and also you run the question once more — now it renders in 100 ms. Yaaay! You’ve already began considering: “Nicely, why everybody says that DAX is difficult? I’ve simply reordered the traces in my code and it really works 20x quicker…”

Yeah, proper! The subsequent morning, a report person complains once more that the identical report visible renders gradual.

You most likely forgot to clear the info cache BEFORE working the “improved” model of the DAX calculation.

Even on this immensely easy calculation, the state of affairs with the consequence set coming from the cache was nearly 4x quicker than the one querying inside information buildings of the Evaluation Companies (please, take note of line 1 which comprises Inner as a subclass).

Second, as the info cache resides in-memory, because of this it does have restricted assets. To place it easy, not all queries will be cached throughout the Evaluation Companies! Relying on the quantity of information retrieved by the question, it might occur that solely a portion (or none) of Storage Engine queries will be retrieved from the cache.

Let me present you ways this seems to be in actuality. I’ve added some extra elements to my Energy BI report. Let’s say that I need to calculate what number of distinct orders we had in every of the years. I’ve created a easy DAX measure to calculate this:

Distinct Orders = DISTINCTCOUNT(FactOnlineSales[SalesOrderNumber])

Subsequent, I need to evaluate this worth with the worth from the earlier 12 months, so I’ll go and create one other measure to calculate the variety of distinct orders from the earlier 12 months:

Distinct Orders PY = CALCULATE(
[Distinct Orders],
SAMEPERIODLASTYEAR(DimDate[Datekey])
)

Let’s change to DAX Studio and verify the question generated to populate the info for this visible:

Since I didn’t clear the info cache earlier than working this question, all 10 Storage Engine queries had been retrieved from the cache! In whole, outcomes had been returned in 7ms.

I’ll now clear the cache and re-run the identical question:

As a substitute of 7ms, we now have greater than 1 second! That’s why I instructed you that it’s of key significance to clear the info cache earlier than working the identical question.

Let’s now verify what occurs if we embrace particular person dates within the scope, as an alternative of years:

With out clearing the cache, this question took greater than 16 seconds to return outcomes!

To briefly clarify what is going on right here: when the question executes, Storage Engine retrieves the info and materializes intermediate question ends in a particular construction referred to as datacache. This datacache is lastly consumed by the System Engine earlier than the ultimate consequence set goes again to the report. Now, relying on many alternative issues, typically all the required information will be materialized inside one datacache, however typically it might occur that there’s merely an excessive amount of information to be scanned and materialized, so Storage Engine creates a number of datacaches.

In our instance, we will see that every of those queries may be very quick — just a few milliseconds — however there are a variety of them. To be completely exact: 2195 queries!

Now, what occurs if there’s not a lot information to be materialized. Can we “assist” the engine to leverage the info cache function once more?

I’ll add a date slicer to my report and embrace solely dates after January 1st 2010:

Let’s see what DAX Studio Server Timings shows now:

Clearly, the question runs quicker, because the engine has to cope with a decrease variety of datacaches. However, we’re nonetheless not in a position to leverage the info cache.

Let’s now embrace solely the dates after October 1st 2010 and verify the question in DAX Studio:

This time, we’re hitting the cache and the distinction is huuuuuge! This question took solely 134ms to return outcomes.

To conclude, from the angle of the info cache, it’s extraordinarily vital how a lot information your queries scan and materialize.

Caching question outcomes is likely one of the key efficiency optimization ideas, not completely associated to Energy BI and tabular mannequin, however typically.

Once we’re analyzing Energy BI cache varieties, you have to be conscious of two totally different caches:

• Visible cache (or report cache) — information is cached within the scope of the precise Energy BI session, however, if we’re speaking in regards to the session within the Energy BI Desktop on the native machine, or session in Energy BI Service
• Knowledge cache — information is cached within the scope of the Evaluation Companies occasion, however of the variety of opened Energy BI periods

Thanks for studying!