The Dashboard Effect

A Speed-Trust Equation: Fast Power BI Reports Increase End User Confidence

• Brick Thompson, Jon Thompson, Caleb Ochs • Episode 129

Share episode

Brick and Caleb share the essentials of performant Power BI reports. They discuss the ideal loading performance benchmarks, the implications of load times on user trust, and helpful strategies for debugging and optimizing data models and DAX measures.

Watch this episode on our YouTube channel.

Blue Margin increases enterprise value for PE-backed, mid-market companies by serving as their fractional data team. We advise on, build, and manage data platforms. Our strategy, proven with over 300 companies to-date, expands multiples through data transformation, as presented in our book, The Dashboard Effect.

Subscribe here to get more episodes of The Dashboard Effect podcast on your favorite podcast app.

Visit Blue Margin's library of additional BI resources.

Brick Thompson:

Welcome back to The Dashboard Effect Podcast. I'm Brick Thompson.

Caleb Ochs:

I'm Caleb Ochs.

Brick Thompson:

Caleb, in our last episode, we talked about data models behind Power BI reports and how they can affect how a report performs - among other things. I'd like to just dig into what is good performance for a report. What should developer expect of themselves and the report they produce? What should users expect?

Caleb Ochs:

Yeah, it's really good question. Our benchmark, which I think is a really good one, is three to five seconds for a page or visual to load.

Brick Thompson:

Okay.

Caleb Ochs:

So when you get to a report, you first open up, there might be multiple visuals on the page, but those all should return in three to five seconds. So five seconds really should be your max. But you know, obviously, the faster the better. It takes a little bit of time, obviously, for the engineer to render any visual.

Brick Thompson:

Like, it doesn't count if some visualizations have popped up and you're still waiting, right?

Caleb Ochs:

Yeah. Well, I'm talking about the full load. If you've got every visual, except for one and one takes 30 seconds. That's bad. You don't want that.

Brick Thompson:

Right.

Caleb Ochs:

And the reason for that is not only just for user experience - for it to be faster. But what's really interesting is that people tend to trust the numbers if it comes back fast.

Brick Thompson:

Oh, that's interesting. I didn't know that. It makes sense intuitively

Caleb Ochs:

If it takes a while, this doubt starts to creep in. Like what's going on here?

Brick Thompson:

Is this accurate? That's interesting,

Caleb Ochs:

Which really, in reality, it really has nothing to do with each other, but it's an interesting perception.

Brick Thompson:

Oh, I didn't know that. That's a good one. Are there edge cases where it's okay, for report to take longer to load?

Caleb Ochs:

There can be. I would like to say no - you should always be fast. Sometimes you have to provide data in a way that it just is inherently slow to do it. So one example might be you have to provide a big wide table of measures and columns, because it's going to be exported and sent to somebody or something.

Brick Thompson:

So a table or matrix visualization could slow you down.

Caleb Ochs:

Yeah, if it has a lot of stuff. So just the more metrics that you have on a page, the slower it's going to be. And, you know, tables are the most susceptible to having a lot of measures in it.

Brick Thompson:

And then does that time limit that standard, do you apply that to things like cross filtering, too? I mean, those usually perform pretty well, but...

Caleb Ochs:

Yeah, for sure. You should be able to click around and it should be pretty snappy. It should feel really seamless and quick.

Brick Thompson:

Okay. So what do you do if you let's say, you've produced a report, and it's just running slowly? I'm assuming based on our last episode, you start to suspect your data model. But how do you go at that? How do you fix it?

Caleb Ochs:

Yeah. So me, the way that I've always debugged performance issues is I always narrow it to the visual that's causing a problem. Well, first of all, you definitely need to look at your model. If you've got a complex model, you've got a lot of relationships, and you're - back to the last episode - you're having to make a lot of steps between tables to pull values all together, that's a problem. As long as you got a clean data model, then you can start into the actual visual performance, and then measure performance optimization. And the way to do that is to narrow it down to what visual is giving you the problem, then really just isolate that. And start eliminating things until you find the metric that's giving you the problem. And then you can start digging into okay, this metric is the problem. Is it DAX? Am I performing something that could be improved in the model? Could I add a column in the model that will make this calculation easier, for example? Yeah. But that's kind of how you go about it, you just kind of step through to the lowest denominator.

Brick Thompson:

So you watch it load, and if a particular visual is taking a long time to load, you suspect that one?

Caleb Ochs:

Yeah, really from the development process, in general, you should be doing by testing of your visuals. A common mistake is that someone might load a subset of the data, build everything, and then load everything and then find out that everything you've written performs really terribly.

Brick Thompson:

Yeah.

Caleb Ochs:

The other thing that is common is you may load all the data, but then you're doing all of your development with a filter set the whole time. So just "last month".

Brick Thompson:

It's not having to work that hard. Yeah.

Caleb Ochs:

And then you deploy it to your users and they open the thing wide open and it doesn't even load or something. So you definitely want to develop against everything and make sure your measures are fast from the ground up.

Brick Thompson:

So you're developing against everything. You wouldn't take the shortcut of like, Alright, let me filter this down just to make it work faster on developing. Develop against everything. If it's not working fast enough for your development there's a problem.

Caleb Ochs:

Exactly right.

Brick Thompson:

Oh, I like that. That's good. Okay. So is there a particular...are there certain things to look out for in DAX? Like, are summarized statements typically bad? Or are there

Caleb Ochs:

There are. You really have to know DAX pretty things... well to be able to find all of them. But yeah, "summarize" can get you. Anything that really goes row by row, I think one of the biggest ones to look out for is an IF statement. So if you have, like, "IF this column is blank", or "IF this measure is blank", then do this other thing. Basically, what it will have to do is it has just run that IF statement. So as to calculate whatever metric you're running the IF over. And then if it's true, each time it does that calculation, then it has to do another one. The IF conditions can just kill you, it really can turn into like, three measures in one.

Brick Thompson:

is there a trick to overcoming that?

Caleb Ochs:

Yeah, You should just not have IF's.

Brick Thompson:

Oh, really? Just don't use them, so put a flag in a column or something.

Caleb Ochs:

Yeah, most of the time, when you need an IF, it's to do something for the UI. Like to say if this is blank, then return a zero or something. It's probably a bad example. But yeah, something to make the report look maybe a little bit better. In those cases, you just got to push back. I just had a request this week about wanting to add a zero, instead of a blank, in a table. And I had to tell him, "This is gonna sound weird. But doing that is actually really difficult, and it can slow the report way down." Because as soon as you could like, do a plus zero, but then you get every single value, whether or not there should be a value there or not. It would just be showing up with a zero. So it's not super intuitive that way, but you just have to really push back, like, "That's gonna kill us. So we're not we're not going to do that." That makes sense. Does a statement like a "SUMX"? Is that also one that's also having to iterate through? So those are gonna hurt you? They're super valuable. I don't know how you do some of those calculations without it. Yeah, "SUMX" is great. However, nesting is when you get into trouble. So let's say you've got one measure that is doing a SUMX, and then you have another one that is like another SUMX that's using that measure.

Brick Thompson:

Oh, okay.

Caleb Ochs:

So as soon as they start referencing each other, that can be really slow.

Brick Thompson:

Okay. Lots of pitfalls in there. I think the big takeaway for me, if I were developing is the advice you have about be testing as you're developing. Don't use a limited data set, if you can help it. Don't filter the data down so that you're getting a false sense that things are performing well, and they might not want you to get into production.

Caleb Ochs:

Right. Yeah. And then froman end user perspective, if your reports are slow, past that three to five second threshold, you might really want to look at 1) what's the composition of the report? Is it just a big table of data? Do you really need that? Or would it be smarter to kind of break it up? Make it a little bit more actionable. A big table of data is not very awesome to look at and actually understand things?

Brick Thompson:

A simple solution to that is, if you need that big table of data, in some cases, let's say you need to do the export to Excel, I think was your example. We were talking about. Just have that on a separate page. When you need it, go to it, but don't have it part of the main report.

Caleb Ochs:

Exactly. Right. Yeah. And then, you know, second is really, you should have high standards for how fast your Power BI reports perform because that's what it's meant to do, right? It's why it's so interactive, and it's built that way. So if you're having big problems, you probably need to either look at data model or you need some DAX help.

Brick Thompson:

Don't just accept it.

Caleb Ochs:

Yeah. Don't accept it.

Brick Thompson:

Don't say well, this one just takes 20 seconds. Got it. All right. Good discussion. Thanks.

Caleb Ochs:

Yep.