Debugging a slow Power BI report with Phil Seamark

yo what's up I'm Adam palm filling welcome to guy in a cube and as you can see we've got a special guest Phil is gonna walk us through how do we debug a slow report stay tuned if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both Patrick and myself all right Phil I got a problem what's your problem I made this awesome report it's a little bit slow okay alright like it do you think you can help me out I can show you what I do okay yeah so we're gonna walk through like kind of your process to you know try and speed things up yeah that's right so so people often see me reports that run slow and what I want to do is show you today the technique I use to narrow down and find how to speed that up so hopefully I can teach you to fish and you don't need to ring me at 3:00 in the morning anymore I do that a lot alright so enough all this talking let's head over to laptop and see how this works all right Phil so I've got this report so I've got a blank page just up first cuz someone told me a while ago like you know have that start with that and then you can figure out where things are going so what would be the first thing that you would look at okay a recently introduced feature to power bi desktop is called the performance analyzer so under View tab go and turn on the performance analyzer and in the panel that comes up at the star record button now go to the page that you want to debug and speed up and this panel is going to give us some really good early information about what might be going wrong here all right so we're gonna go to the blazing fast report and if this particular page has more than one visual the performance analyzer will give you an metric for each one of those visuals because what you might find it's just one visual that is slowing down the page load so alright so my my table is up here and it's looking like it took about seven point three seconds to do its thing okay I think we can speed that up so if you expand the the visual yep and what we're gonna do is just copy the query there's a little copy query link if you click that that'll paste the query the visual users and to the clipboard yeah now let's jump across two decks to do alright I just happen to have that running because I knew you were gonna ask for that code so we'll go connect to our power bi desktop model hmm and we'll connect them only it's paste in that query all right well look at that cope now the first thing I do in Dex to do is to on the Run button I set the clearcase sitting so that every time I run this deck studio will clear the cache because they can trip us up if we don't clear the cache we might start getting super fast results and we think the problem solved but we don't want that to which is also the reason for that blank page in the beginning so it hadn't triggered the visual to render so there's no cache results from that exactly when you're debugging for performance it's really important to be aware what the case is doing okay so the second thing I always do is I click on the menu button the server timing button right here yeah that's going to give us some really interesting detail about what's going on under the covers to help us narrow in what might be making this particular query slope nice all right and then go and run it yep please do and we should see a number that is very similar to what we saw back in power bi dogs act but close enough yeah all right so completed let's go to the server timings tab okay all right so we can see it's saying total time was 3.6 seconds which is actually what if you go back to power bi desktop when we expanded that little thing it did say the DAC squares like 326 seconds so that's good all right so we've got a good baseline yep gonna move this up a little bit there we go okay so what are we looking at here okay apart from the total time what I want to understand is exactly what kind of work did the query do perform and we can see here the number of storage engine scans that took place 167 storage engine queries yeah and that's a lot yeah so what I would be trying to do is to reduce the number of storage engine queries so to help speed this up so would you say it's fair that if you see a higher number like what's a good number you would expect to see Oh less than 20 yeah it's higher than that it's not necesarily that's not necessarily the reason but it's usually a tell that there's room for optimization it's a strong indication but if you if you attack that then you probably are going to end up with a better performing report and if we have a look at the storage engine scans here we're seeing the same thing repeated over and over and over again so there's a bit of a clue I do yeah I see a lot of these deep count ok items now I'll tell you a little bit about the anatomy of a Dex query okay that power bi desktop generates so looking at the screen here we can see there are some variables that are effectively the filters this particular report has got a number of filters either in the filter pane or in the slicer and as we scroll down a little bit what we should do is look for a summarized columns so there we go that's the meat of the query and we can see there that there are four filters from line 25 to 28 and the measures of the query are on line 29 to 31 it's so perhaps coming to outline 28 31 and 30 yeah comment out two of those lines and just let's see what happens and it's a bit old-school debugging but just commenting outlines re running it see if it knows faster or slower can help you zero in on what might be yeah I agree like coming from support we did that a lot where it's like trial and error sometimes it's trial and error it's probably usually what I tell people is look if you've got a gut feeling for something like you want to do a what I call the shotgun approach yeah I'll try that once first and then if that's not giving me any results then I fall back on - we're just gonna go through one by one and figure out where it is that's right we can uncoming yeah baby steps in we're probably gonna get some useful feedback by taking this approach so all right so let's go and run this and we got our comma issues yeah be careful with commas every time every time there we go batch completed that was way faster we're down to two storage engine queries and we're at 58 milliseconds no son brought we're done so what about started three now seconds so so the problem is likely to be in one of those two measures right and looking at the title of the second measure it's distinct count Kant is often a it's a hard measure for any bi system to work with so I'm guessing that it's likely to be in that so let's let's uncomment this measure let's grab the measure name because what we're going to do is not go back to power bi desktop to make modifications medical modifications to the measure let's modify it here index to do it and so you click the search button yo and go over the search and we can just paste the name in paste it in right there that filters down the metadata to show the measure if you right click on that measure name in the context menu that comes up you can click on the define measure option yeah and that's gonna create a copy of the measure locally here index to do for you now we can play with that measure yeah and it's not destructive I'm not messing with anything in the model and I'm just gonna play around with it here and see what I can do to make it faster exactly because we might make it worse and then ultimately if we can get it running faster and when we're happy yeah that's when we can take it back to the model and I know when I do things usually always the first time I do something it's probably not going to work by right-clicking on define Misha it's gonna put a copy of the measure at the very top of the tick so if you scroll up to the top okay we can see that their model measures begin model measure 10 and we have a calculate distinct count let's write the distinct count a slightly different way awesome so if you comment out that line they're distinct count line six and what I'd like you to try is to use some X Open bracket yeah values of I can break it and in the column that we're performing a distinct count over which I believe is sales customer care key yeah close bracket comma one one close bracket comma cool so this is just an alternative way of generating a distinct count so we'll get exactly the same result we're just right we're just trying a different method this particular method works very well when you have low cardinality values and the results but hey give it a crack this yeah all right so I'll just run it yeah just just draw alright 351 milliseconds 20 storage engine queries and that's down from one hundred and 60 some-odd queries and 3.6 seconds cope that's pretty good alright so a couple things that I'm picking up here one is so the whole distinct count versus some X yep that's not a guarantee that that's always gonna make it faster in this case it did make it faster because our rows weren't exactly long yeah it might be the opposite right on a different data series you know so that's not necessarily the the key takeaway here crate I think the bigger thing here is the approach of how we're gonna actually troubleshoot this with limiting what these queries are doing limiting trying to narrow down which measure might be actually contributing to the problem and then going to adjust that measure that's right talk to me a little bit about the storage engine queries and why that's a key value in terms of performance row okay the analogy I like to use about the storage engine and when and how that works in the query is imagine you're baking a cake and you need a bunch of ingredients you need 10 ingredients now you wouldn't go to the shop at 10 different times to get the eggs and then another time to get the flour and there's sugar exactly what you want to do is make one trip to the shop collect all the ingredients and then bring them back so that's it's not that we're lazy we're efficient exactly yeah and and that's exactly what power bi desktop is doing in those storage inquiries it's going out to get the the data and bring it back so you can collect more ingredients with fewer trips then you're gonna get more efficient and it's gonna be faster it's gonna be cake time sooner oh yeah beautiful except we got to bake it and then but that's a whole other that's a different video that's right hopefully this gives you some clues of what you can do in terms of troubleshooting so first start with the performance analyzer inside a power bi desktop that can give you some key tells and allow you to narrow down on things that's right and then we take it into a tool of your choice it doesn't have to be Dax studio it doesn't have to be but there are lots of neat features index studio to give you this information front and center yes yes yes so but there are other ways to do it but basically just get the original Dax query and then we've just got to run it try it clear the those types of things and just go and do the hard work of troubleshooting narrowing it down now sometimes the the bottleneck will be in the Mesha now the times it might be a particular filter so if you just go on commented out in the query you might find that it's a very slow filter that is making the difference to the yeah so for example one time we had an item where they had a really big fact table and they were doing the filter on the fact table instead of on the dimension but we went through this kind of same process to figure that out like okay that was definitely causing that problem yeah that's right to comment out a line or three here it's very quick and easy to do run it make sure you clear the cache and you just keep your eye on the storage engine scans well in that example that I saw where we were filtering the fact table the end result it was a model issue right so they didn't actually have it out as a dimension they were you know it could be a model issue it could be a Dax issue this process will help you narrow in on exactly to take the detail across into decks to do it's much easier to flesh out then doing it back in power bi desktop yep so and quicker to tweak things try it again and and now we can go and grab that write the code from the measure that we made a copy of and put that back into our power bi beast is that up right because we haven't fixed anything refresh the the the the metric yeah and we will have a faster query what do you guys think I want to pass this off to you was this helpful does this help you maybe get a little more comfortable and confident in terms of troubleshooting performance inside a power bi desktop let us know down in the comments below there's a lot of other techniques as well but I think this is a great starting point that you can work with to take your game to the next level if you liked this video be sure to hit that big thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both Patrick myself and Phil thank you so much for watching keep being awesome and we'll see you in the next video

Channel: Guy in a Cube

Views: 40,185

Rating: 4.9754171 out of 5

Keywords: power bi dashboard, power bi desktop, power bi desktop tutorial, power bi for beginners, power bi report performance, power bi report performance analyzer, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners, power bi tutorial for beginners excel, power bi videos, dax studio power bi, dax studio power bi desktop, bi, dax studio, dax studio training

Id: eABg872TAJU

Length: 13min 0sec (780 seconds)

Published: Thu Oct 03 2019