Optimizing performance: Tables or measures?

[PaulDBrown]

Hello everyone!

I am hoping for some advice as to how to improve performance in a model I'm building. The dataset is actually small: a main fact table with under 5K rows, a couple more fact tables with even less rows and various lookup tables built from these. The  issue is I have a matrix with a number of measures (some within the visual itself, and many of these stemming from underlying measures.) and the refresh time seems very high (a number of seconds, probably around 10 seconds) given the small dataset involved, and I'm worried that when I actually deploy the model to a full dataset involving potentially hundreds of thousands, if not millions, of rows, the matrix will take forever to refresh. 

I have a suspicion that the reason behind the delay is that a number of the measures use the SUMMARIZE function. And I was wondering if it might not be much more effecient actually to create tables using SUMMARIZE instead of using the function in the measure itself. Before I go about testing this, which would involve re-building a considerable amount of measures from scratch, I was hoping the experts onboard might be able to provide some insight.

Does anyone have expertise in this?

Thanks in advance,

Paul.

[MattAllington]

What do you mean by refresh time?  Refreshing is when you reload the data, but is sounds like you might be referring to the time it takes to render the matrix. How many values are displayed in the matrix?  Can you share a few of the measures?

[PaulDBrown]

@AnonymousThank you for the suggestions and the link. I did have a look at it and will try to work my way through it.

@MattAllingtonSorry, yes, I meant the render time.

This is the matrix:

Takes 8 - 10 seconds to render

I have tried breaking down the matrix into 9 individual visuals, each visual with a single measure for testing purposes. Keeping the original matrix on the same page (8 or 9 seconds to render), the indivual matrices all take under 3 seconds (with the exception of one (last column on the right) which takes 5 seconds to render. These are the measures in the measure (column) with the poorest performance:

Render approx 5 seconds

And here are two of the more complex columns and measures:

Render approx 2 secondsrender approx 1 second

What I find surprising is that individually these 9 measures render in under 3 seconds, (except for 1, which takes approx 5 seconds) and the complete matrix, which is on the same page as the individual measures (for testing purposes: the real page report only contains the complete matrix.), takes approximately 8 or 9 seconds.

Thank you for any further ideas.

Regards,

Paul.

[Anonymous]

HI @PaulDBrown,

After finish looking at your description, I think your formula will cause the performance issue.

I found some measures are invoke multiple measures who contains specific filter and calculations.

For these measures, they will caused to filters conflict and looping calculation.(current row count * invoke measures calculation amount)

Some reference links:

Optimizing DAX expressions involving multiple measures.

Nested Measure Performance

Normally, I often use simple calculated measures in other measure.(e.g, sum, max, average or static value...)

They can work as original calculation formula and work with current filter effect without any performance issue.

Regards,

Xiaoxin Sheng

[stretcharm]

@AlbertoFerrarihas a greate video that explains dax optimisation.

https://www.sqlbi.com/tv/dax-optimization-examples/

[Anonymous]

HI @PaulDBrown,

>>I have a suspicion that the reason behind the delay is that a number of the measures use the SUMMARIZE function. And I was wondering if it might not be much more effecient actually to create tables using SUMMARIZE instead of using the function in the measure itself.

As you said, if you have lots of measures need to create variable summary table to calculate, it will spend more resource than use summarize function to create static calculated table.

In addition, if you use some functions who has filter as their parameters(e.g. sumx, countx...), please not use complex expression or add loop check of other tables in expression position.

BTW, power bi will stored data model in local SSAS instance, you can also use SSAS monitor to check its performance.

CONNECT TO POWER BI DESKTOP MODEL FROM EXCEL AND SSMS

Regards,

Xiaoxin Sheng