Power BI Semantic Model Best Practices (PIMP) Script!

[Music] hey guys welcome and I'm excited to actually bring on a new guest for a little recorded tutorial today uh Alexander actually has some interesting descriptions and designs for some best practices and kind of how to um use some scripts to basically pimp out the powerbi data model and improve it in a lot of different ways so excited to always bring new people on and also just see some of the the ways that we can um do increase our efficiency and automate some of this stuff for um for anything around best practices uh with that being said I'll hand it off to you for a little bit of an introduction and then to talk about what we will be getting into for the demo thanks a lot Reed I'm super excited today to show you my powerbi pimp script I'm sure it will help a lot of people I hope and but before I start let's go into a short about me so my name is is Alexander Korn and I'm part of the hio group um shoot out to my employer who allowed me to put actually put in some quite some time into the development of this free and open source tool for you for the community uh by the way my employer share a passion with me there's first of all obviously powerbi and secondly ivcs uh which is for me the most complete best practices for conveying insights um if you want to know more about that word to ibcs or actionable reporting.com that's my blog um yeah but before we can convey insights obviously we need a b as a basis a robust data model and that's why we're talking today um and how to optimize and automate the whole thing so if you have worked with powerbi and you know what you're doing um you also know that setting up a create data model obviously takes quite some time and and maybe you have caught yourself or at least myself I have caught myself that I have left out some of those best practices even though I I knew I should imple Implement them but I just didn't had enough time uh to implement them uh always in all of them and I thought hey this needs to be a thing of the past and there must be some way to automate it that way we're not seeing those bad data models anymore um and that's why I cre this pimp script in home to automate the process so what are those best practices um available with the script most obvious one probably is you're all aware of is a calendar table um so every fact table has a date column therefore we all so should have a calendar date Dimension um and also the script marks it then as a date uh as a calendar table secondly if you have a date Dimension calendar Dimension then you can also Implement a calculation group for time intelligence so why is this important for insights because deviations are key and if you don't have a plan or a Target value at least have the comparison to a previous period and there's also kind of calculation in this time intelligence calculation Group which you can which you can also reuse uh as as a as a guideline for your other measures which you might Implement if we're talking about measures then there's also a last refresh every table probably has at least if it's import mode um then put on the bottom right or wherever you have that located maybe not as big as in my example but put a time stamp and uh for the last refresh and that includes also the naming last refresh most proberly therefore you will have not only the table with a time stamp maybe also a measure talking about measures there is an empty measure table how I like to call it it's a container for your measures and a table by itself contains um not that much there's no data in the Mt in in the table um for me there's not really a point of having fact tables and so in fact tables we have either uh keys or IDs and then we have also the value columns and um then if if there's still attributes in it then it's kind of a dimension so that's why I say there you can hide all of your um fact tables and have all the measures as explicit measures in your empty measure table if you want you can even have multiple measure tables um but best practice this is also to structure your kpis your your measures in subfolders or display folders yep agree cool and then there is um also as part of the script you obviously need to add explicit measures um and I reused a script from Tom Tom Martin um as a basis and he actually uses the aggregation defined already so you if you for example for unit price that might be average or not not some so that's that's what you manually need to modify and but there's also other value columns like ID or key columns so the script also searches for ID or key and then already marks the summarized by to nonone so that's why the script does not add the explicit measures for those columns and the others are probably probably if they are not IDs um then they are at least text and then they will also not be added as explicit measures then if you have an existing model or if you have um that many new measures then um you want to format also your measures in a way that it's re readable and be aware that if you apply it in the script that's it's going to uh format potentially a lot of measures if you choose that option I'm using here a calculated column by the way those should probably not exist either way U um and should be done in power query the calculated columns I know I also do those but maybe it's sometimes better to move it to the back end if we have that many um measures I also added a calculation group for unit so then you can change all your visualizations uh in million or thousand formatting or just the number and one last thing there's definitely lots of best practices missing so that's why I also added a best practice analyzer I think that's super cool and there is a script where you get the best practice analyze into your tablea editor and and if you have not done that already script will also do that for you yeah so I would head over to My Demo now and to show it actually how that that's in practice so I'm opening power be desktop and here I have just one order table and then I also have a tablea editor MH and as a first step I need to connect to um the powerbi desktop model and here I just have the table and then I need to add the C sh J script or the macro how it's how it's called and you can go to my GI up and copy paste it from there and then if you already have saved it you can even um load it in and here are some variables which you also can Define but the majority of the variables are is within the script so um U don't worry about that too much um so just run the script and if I run now the script it will check that adding tables is not successful because currently that's one limitation that WR operations are not possible against the local instance of uh powerb desktop that's why it's telling me hey table adding will not be successful now just will be able to add uh like the time in intelligence calculation group that's why we're doing now uh stopping this process um and then saving it locally okay and then reopening the file and then we apply the changes against the local Bim model and then okay it will ask a series of questions all the things we just discussed so empty measure table and uh setting the aggregation to non for the key columns and then adding the explicit measures add the last refresh table add the date Dimension and then also that's how it's able to add all those in okay so just a couple steps but then it's able to add those um into the model Itself by merging basically okay yeah you can actually say okay I don't want to add this step um and then it will just run fine but uh all the steps here uh we we discussed previously um and then it will summarize in the end what you just decided on uh all the things which were added and then um we press okay and then immediately it's added bam so that's super cool for me to finalize the process um we need to save and then go back to uh the Pia desktop file and then save it as pbip as project file and then go back to the model. Bim and then replace the data set file here um yes and then reopen follow at desktop the file we just changed the pbip and then in the end you can then save it back as PBX um but all the changes are now applied even the change of the tables and adding the tables so that's currently a workaround uh but I have an idea um already which I can share where you can vote for and then Ru Romano already stated hey that's on the road map to also be able to change tables and add tables but there is no timing yet so uh once that actually is possible the whole process will be even smoother and you don't have to go to work around with the Bim locally and um also with yeah the uh the pbip and then we just need to refresh also that the last refresh table is added so and then everything is optimized that's very cool um yeah so the process here very quickly was new file and then connect with tablea editor and then save and reopen locally execute the script and then the work around with the project folder and then lastly reopen the whole file so like I said firstly firstly please vote for the idea that we get some track on that secondly please ping me via LinkedIn if you have any questions or Rito or whoever if you have any ideas for next version I would be more than happy to add those best practices and let's make it better together Reed what do you think about the script and the whole process and best practices I mean it's it does a lot of what I already do today I mean I've you know for for years I've used a a metrics I call it a metrics folder um just because I I find that not a lot of report um self-service report Builders don't know what Dax is but they understand metrics or calculation sadly we can't use measures because that's a reserve name but whatever you call it it's good to have a top level folder to organize your calculation separate logic from physical columns um having the date fields so it's you honestly anything that you just need to do over and over again as a pattern um is really useful and you know in in I'd say in a in a perfect world The the thing that I typically will give to clients is never start from a brand new powerb desktop file have a template file it that already has your calendars your hierarchies everything in it it's it's it has the theme file and it's pre-built um and if you can start net new with one of those that's great but you also often have a lot of Legacy reports that haven't been built with all of that so scripts like this come in handy because yes you can't start from a brand new file but you at least can take something that has been built by somebody else and you can very quickly upgraded to add a lot of these typical um best practices um but also the you know the starter kit aggregations you know any of the the items that you just showed with the the time intelligence tables um and all that so I think it just goes a long way to save time um which end of the days any developer uh would appreciate being able to do so uh I really like this and um I'll flip back over here to us so we can oh go ahead if you had a few other read I have one at a at a bonus so definitely you mentioned also the theme and there I definitely recommend to add the background also to the theme that way you don't have to add the background to each page H you just can go into your powerbi desktop and use a SVG image um and then it will go super fast also adding those um in so that that's my tip uh for for the design theme beside having all the standards and everything and then whenever you or have additional pages that will be picked up automatically um yeah very cool yeah I mean same thing I've uh it's good to see this also done in the theme files that you do I know that Mike Carlo over at powerbi tips with him and Seth they have that template theme generator that also can put those in but it it's just all timesaving right like anything that just lets you create more outof the-box solutions um especially that fits to to a standardization of either modeling practices or reporting practices that help helps the company all kind of match on how they they build their reports and models so very happy to to see more things like this and encouraging people to use tabular editor which you know is it's the best modeling tool that we have either with tabular editor 2 which this can still be done with a free version um or people you know want the to use tabular editor 3 with all the extra bells and whistles for a few dollars a month um that version is available as well too but U I think this has been great let me me flip over to our main screen here as we close up but uh um yeah happy to be able to bring you on and get give you a chance to share this with the community and give them one more tool in their tool build I was so happy to be here thanks a lot Reed for letting me show the script U I hope that some of you guys out there are going to take this script further develop your own ver maybe your own power query script and then let me know that you did even further steps I think there's lots of things how you can improve that even further and be even faster Al together absolutely yeah and um hopefully get you back on sometime for a recording or even a live stream so um until next time and everyone yeah thanks for tuning in thank you so much for watching please don't forget to like comment or share this video plus if you have any comments for a future video go ahead and add that to the comment section down below now this is your first time to my Channel or you want to see more of these awesome videos please click that subscribe and notification button also feel free to show your support by becoming a channel member

Channel: Havens Consulting

Views: 2,260

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, PowerPivot, Power Pivot, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power BI DAX, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Drill Through, Power BI Service, Fabric, Semantic Model

Id: -9YaxArn3TM

Length: 16min 25sec (985 seconds)

Published: Tue Feb 20 2024