STOP publishing your Power BI report until you do these 5 things!

yo what's up it's patrick from guyanacu and in this video i'm going to talk about the five things you should do my top five recommendation of the things you should do before you publish a power bi report to your production environment stay tuned [Music] if you're finding this for the very first time be sure to hit that subscribe button to stay up to date on all the videos from both adam and myself all right patrick i already do a lot of stuff before i put a report in production you're telling me and you want me to do more you may already be doing some of the steps that i'm about to outline but you maybe you're not um and so i just want to give you the top five things that i recommend you do before you put a report in production okay so let's get into this number one the very first thing you should do is split the data model and the report into two pbi x files so you have this nice lean report and a separate environment for developing your data model so you can create reports modify reports and publish the report without affecting the data set and you can also do some local development of your data model you can publish the data model into a development environment and people kick the tires on so you have these two separate things and now they can be managed develop designed and maintained completely separately and you may be thinking to yourself patrick how do i separate them ah so you guys know i like to do instead of all this talking let me give you a couple of pointers and show you how to do this let's do what let's head over to my laptop let's say you have this data model you're thinking how do i do this well if you go out to right havens consultant out to their youtube channel reid has a video where he explains how to split the report and the data model how you divide that one single ppix file into two one for the data set and one to a nice lean report and no code is required and basically what reed does he goes into the query editor deletes all of the queries now before you do this make sure you have a copy of this report with except with only the data model all right only the data model and publish that up to the service so you can see we go here that's what i've done right here i've just published the data model and then go back to your power bi report once you've deleted everything yep you'll have all these little x's like reed talks about in this video and then just get connect power bi data set as long as the scheme is the same everything will work and boom you'll have a disconnected environment with one lean report and one nice data model and you can do separate development now i know what you guys are thinking especially people that have gone down this path patrick that's a lot of work because anytime i change the data model i gotta publish it man open a report refresh your report while i got something for you got something for you steve campbell over at power bi tips created this nice little external tool where it can hot swap between a local data model and live connection so you can swap that report from the live connection to the local data model to all the uh modeling locally and then change it back and publish it up so you don't have to go through that iterative process publish refresh publish to free fresh thank you steve thank you reid for those great contributions to our community they're gonna help a lot get rid of all these crazy data silos number two guess what you gotta do with number two you gotta go talk to some people i know as i.t we kind of live in our little bubbles we talk to our i.t people we create our stuff and we usually the only time we talk to end users people that are using the stuff we create is after we publish it out and they start complaining in this case you want to be a little more proactive so after you initially design the report in the data model go talk to the people that's using the report go talk to the people that's using the data model and they may tell you your baby is ugly it's all right it's all right the baby's up it's okay right take that advice write write it down open up one note take some notes they may say hey i need a new measure i need some columns i don't need these columns what is it why is it filtering this way i really don't need that bi-directional relationship that's bananas what's the point of that you know take all that feedback hold on to it and we're going to talk about step number three take all that information that they gave you and go fix your reports right go fix your data models change the relationships remove columns add columns basically you're optimizing that schema you're optimizing that data model not talking about optimizing dax or performance anything like that this optimization will help performance but we're not into that yet we're just talking about changing the data model adding things removing things maybe you need to remove some rolls right but guess what after you finish step number three you may need to go back to step number two to talk to those report consumers and data analysts again let them give you the feedback don't stop that iterative process until they say this baby's kind of cute i like this baby this baby's cute once you get to that point and they're telling your baby is cute it's time to go to step number four and you guys are probably thinking patrick what about performance well now we're talking about performance because we want things to go we want to be as fast as possible when they open up a report we don't want to wait we don't want to go to get some coffee into the water cooler and stand and wait for five minutes until someone until that because they know it's going to take a long time for that report refreshes they won't wait that long they join click it within milliseconds or seconds at least the report renders okay so what you want to do well set up all this talking let me show you let's head over where to my laptop okay so what you want to do with your report go to view and you know where i'm going performance analyzer start recording but before you click refresh go over to that studio go to external tools fire up dax studio and clear the cache clear the cache go ahead and refresh your visuals do this because we're just looking at dax right now sort this descendant and sorted by my dax query go find the longest running query copy that query hit back over the dax studio turn on server timings paste that in there and select clear cache and run run this go take a peek at those server timings once it renders and start optimizing the dax work all the way through it until everything is maybe less than two seconds or as fast as you can get it right it depends on what your sla is with the report consumers but try to get it within those acceptable ranges once you do that right you're getting closer to what that cute little baby what's that number five patrick step number five is really interesting and most people don't do this in fact i didn't do this until maybe about a year ago maybe a little longer than that but not until recently we always talk about making sure a report is accessible or making sure we follow visualization best practices don't use pie charts make sure the best thing is in the upper left corner use a pie chart anyway right don't do this don't not too many colors make sure you have the right color template all these different things use the right visual for the right you know story you're trying to tell those are all great but you also need to optimize the visuals for performance okay and when you're using power bi probably i'll give you a good indication if you're not doing that you want me to show you how let's do it let's head over to my laptop so the best way to do this there's two two caches in your report there's the data cache and the visual cache we can clear the data cache by using docs studio or ssms the visual cache we can't clear the only way we can clear this by closing the report and open it and so marco russo has a really good video about you know a slow report where he focuses on dax but in this video i'm going to focus not only on dax like marco did but i want to talk about another option from visual perspective so you create this blank page save the report close it and reopen it with that blank page open go to view click on performance analyzer to start recording then flip over to a page right when you flip over to a page you'll see things start spinning and you may be astounded and go whoa wait a minute wait a minute don't expand anything yet sorted descending right and then let's sort this by total time there we go and so we see this is the longest one if we expand this out well the docs query fast the visual display is fast there's this other what is this other power bi has to do some things to prepare the visuals and only a certain number of visuals will render at the same time the more visuals you have on the page the longer that other will be the best way to do it is to optimize the elements on the visuals elements on that page my buddy chris hamill has a great blog post actually talking about that for improved performance right like taking a make you instead of having multiple cards use a matrix and put those elements in the matrix if there's things on the report like visuals like you saw i had like these little icons on it if you don't need those icons remove them you don't need them get rid of them you know what i mean all right so clean up your reports optimize them for performance and then guess what you may have to go all the way back to step two you go through step two walk through the steps again and then maybe you get to a point where your report consumers and the data analysts go this is a beautiful baby i can use this i can use this once they say that it's time to publish that report where to production all right what do you guys think are you using these steps in the process to get reports to reproduction are you using other steps i'm sure you're using tons of other steps to get your reports into production i'd love to know you guys know how i like to do let's continue the conversation we're in the comments below it's your first time visiting the guy in thecube channel hit that subscribe button if you like my video give me a big thumbs up as always from adam and myself thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 96,712

Keywords: power bi, power bi dashboard, power bi desktop, power bi desktop tutorial, power bi for beginners, power bi performance, power bi performance analyzer, power bi performance tuning, power bi publish, power bi reports, power bi service, power bi training, power bi tutorial, power bi tutorial for beginners, publish to power bi

Id: rsS7E5tOtDk

Length: 10min 14sec (614 seconds)

Published: Wed Sep 23 2020