Power BI Performance Tuning for Databricks SQL

0:05hi today we have a special topics with two amazing guests we're GNA be discussing best practices when it comes to using 4vi and data break sequel my guests are ysh and Andre hello so can you please tell us more uh

0:27about you verti and then Andre sure hi um I'm a solution architect with digital native unit in ter brakes as a part of this uh role I basically help the customers in their uh datab brakes adoption datab lak house platform adoption and I'm also part of the DB SQL and power BME group in in in datab

0:50brakes and wherein I talk about you know the best practices about datab break SQL powerbi and how the customers can leverage uh you know the both powerbi and data break SQL in in their environment and what about you Andre thanks uh everyone my name is Andre mki I'm a specialist Solutions architect in in Germany based out of

1:13Munich um yeah I uh work with customers across whole IM region helping uh with adopting data break SQL uh helping them to get the most out of Databricks data intelligence platform and as I have experience with powerbi and powerbi is one of the biggest and the widest

1:37adopted bi bi and business Analytics tool on the market uh it's one of my passions and I help customers to adopt power bi on data breaks data intelligence platform more efficiently and I've been told that you have uh a repo where you're sharing all those uh best practices can you tell us

1:59more about uh this one for sure so I'm I'm really glad you asked this question uh yourself because uh we you know we talking about the best practices it's really important for all the organizations and that's the reason me and Andre have actually created a g repo which where we wherein we have

2:21created all these uh these sample templates uh with the three main principles we have created with an intent that it should be open it should be simple and it should be multicloud right which is what as you would know datab BRX is like really famous for now all these uh and I will talk about you know uh about and I will showcase the git repo in depth in some time but

2:44the main intent is of this repository is to just talk about the best practices of integration between tabic SQL and with powerbi and just make it easy for anyone to Plug and Play these sample templates by using all the pbit template files that we have attached with in these repository that sounds that sounds good

3:08looking forward to uh have a look at the at the repo and I think I have the first question for you guys um I know for sure that analysts try often to connect to different data SQL environment which requires manual tuning so how can we uh streamline the process sure um stre lining the process

3:30obviously is really important right uh and uh to answer to your question about streamlining let's say when the developers have to move from uh a Dev environment to a QA environment right that's where uh the dynamic powerbi parameter connection data source parameterization really comes in in

3:52handy right what what what this means is in in powerbi you can create these variables or these parameters and because of this you can actually now create connections to your data sources dynamically you don't have to do any manual work of opening those reports and you know changing the connection sources so as a matter of fact let me now I

4:14think this is a good liveway for me to dive into and showcase to you in a step-by-step instructions in our G reposit and also show you our git repo that sounds good all right so as you could see here this is this is the gate repository that we

4:36have uh and I'm going to quickly give a quick introduction about what what the git repository basically contains now this gate repository as you could see it talks about powerbi and data SQL quick start sample all these all these sample templates that you see here are uh certain best practices or

4:58recommendations of when you're using powerbi and datab SQL so you have uh talk we here we talk about query paralyzation we talk about different storage modes and what you would see here is every implementation has a prerequisite has a step-by-step instruction if you want to follow along and at the same time we also have uh

5:21powerb template files for each and every sample templates that you see here the intent here being uh you should be able to quickly run and showcase and test out all these features right out of the box and one of these implementations that we have here as what you see is using the using parameters which is what we were just referring to now for using

5:45parameters it's to be honest it is just three-step process right but before I talk about those three steps uh I want to make sure that I covered what are the prerequisites for it so essentially what you need here uh is a a databas account and a SQL Warehouse which is up and running and then you also need a powerb desktop where you would be creating uh

6:07your parameters now to talk about step-by-step instructions um the first step of the process is uh you know creating those parameters when how you can create your parameters is in powerb desktop just click on manage uh click on the button called manage parameters and then you can name your variable uh based on

6:29your require now in this case we are talking about integration of powerbi and datab SQL for that we essentially need two variables one which talks about the server host name and the other one which talks about your databas SQL HTTP path and you can easily get this uh all this this information from your databas SQL

6:52Warehouse as you could see in the in the screenshot here so if you go to your connection details of your SQL Warehouse you would be able to get your server poost name and you'll be able to get your HTTP path so these two information these two variable information is what you need to uh to create a parameter once you have this from your Warehouse all you have to do is create two

7:14variables in powerbi desktop with uh with with the with the server host name that you have copied and the HTTP path that you have copied in the previous step as as you could see uh in in the second screenshot highlighted in my on my screen once you have and to be very honest with you that's pretty much it now you have these two variables which

7:36are now pointing to your uh to your Warehouse databas SQL Warehouse uh let's say this is let's say pointing to your Dev environment right now okay once you have these variables created the next step is to add these parameters and and essentially update your M query and it's as simple as just opening your query editor uh opening your data SQL data

8:00source connection and instead of uh having the hardcode VAR hardcode connections uh you know your uh hardcode connection parameters defined there all you have to do is replace it with the two variables that we created in the previous step and that's it now with this and as and as you would see like you know in in the essential three steps

8:23I have I'm able to now create a dynamic report and a dynamic data source connection now once once this is done and you publish this report your developers will never or no longer have to open the powerb desktop to change your connections manually because uh when you publish these reports on powerbi service these variables would automatically uh appear

8:45on the powerb service and let's say you want to change your connection from Dev to QA or even to prod all the developers have to do is just change the variable name uh the connection name from your Dev connection to your uh production connection or QA Connection in the powerb service and they will never have to manually open or change anything on the powerb desktop

9:09that's that's very easy and I think you're GNA improve the productivity of many many analysts rather than having each time to go and change this manually now is everything is streamlined and simplified and I think this is just something very very easy you can you you you have just showcased and I think we have more more uh to come in in

9:32few minutes so I guess we did discuss about the parameters but what's the best approach to optimize performance because I know this is a very sensitive topic what would you recommend to use like the import mode the import mode the Dual mode um which one is the best because I've seen some customers using only import mode other direct mode

9:57others like the Dual which one is well suited for each case use case thank you sir for for the question indeed powerbi is famous for delivering you know super performance when using import mode when data is cached in memory of powerbi there is also direct query mode uh which enables you to query

10:21unlimited data volumes uh in Databricks data intelligence platform and we have customers who car literally terabytes of data RBI also offers composite models when developers can combine both import storage mode and direct query storage mod so while some data is cached in memory uh the rest is uh retrieved

10:43directly from uh Databricks Lakehouse using datab break SQL and often times we see that our customers use these storage modes and composite models inefficiently that's why we created uh this sample comparing this example comparing behavior and efficiency of imports direct query and dual modes for

11:05typical star schema data models yeah so here we so here we analyze uh the powerbi behavior when using different storage mods for typical

11:27star schema data model so we have uh uh step-by-step instructions how to uh reproduce uh our experiment and our uh you know conclusions in the end so uh here we have a sample data model using uh three flavors of the same Dimension tables using direct quy mode import mode

11:51dual mode and we have a fact table which is configured to use direct qu mode uh quite simple example but it demonstrates how um how different these three storage modes behave and surprisingly when customers switch uh Dimension tables to import mode it leads to worse performance

12:15actually not not an improvement which is fair to expect because data is imported into into memory but essentially it leads to you know performance Decline and that's uh we have you know step-by-step instructions you can see the results of our test how the report behaves when using purely directquery

12:37mode how it behaves when we switch Dimension tables to use import mode and you can see that here for example the performance of one of the visuals in the report is much worse even comparing to you know direct query mode Dimension tables and eventually we test dual mode performance

13:00results in the best performance across the three experiments and here you can also find our recommendations uh for Dimension tables for fact tables and for aggregation tables which is you know the next topic so uh this example uh demonstrates this example show cases that uh you know

13:24proper combination of storage modes will result in the best possible performance and not always import mode wins not always direct query mode wins uh there is also a third storage mode which is dual which is not uh you know well known uh across the community but proper usage of these three modes will result in the

13:46best possible performance and the best possible user experience and of course as yti mentioned for every uh best practice for every example including this one we have a powerbi template which um you know our users can download and use in their own environments using their data Bri

14:08workspaces uh we use standard samples catalic and tpch schema so users don't need to create any test data sets they can reproduce end to endend in their environments yeah I think you've simplified the way to uh give them more opportunity to test uh the template so to summarize like large tables they

14:32should use uh the direct mode for fact tables they should use the Dual mode but what if I want to uh further optimize the performance what what should I do like should I is there any partition in PP I'm not that familiar with the uh with the tool I can I can take that um sure yes

14:56powerbi does have uh the concept of partitioning right and in fact it's actually one of the most uh one of the most powerful techniques uh in powerbi especially when you are dealing with as you mentioned like you know large data set in the in the import mode right so how it works is in in powerbi they create when we create these uh these

15:19custom partitions it creates like small manageable segments which are called obviously partitions and with these partitions you can actually now process your data parallell like each partition would have its own set of data and it would process the data uh parall when you're doing a data set refresh it is basically processing its own set of data

15:41itself right what how it helps is in turn you now have these these segments which are processing the data parall and at the same time your query performance would also go up and your data set refresh would also be faster right actually let me just show you uh a quick example of uh of an experiment that we

16:03did and how logical partitioning really helped in that so again going back to our git repo uh and here we have a section called uh which talks about logical partitioning what logical partitioning is and how you can do uh quickly again talking about the prerequisites uh logical

16:26partitioning or custom partition uh as as we call it of especially for the import mode um you there are few prerequisites and few things that you should that you should keep in mind it works only on powerbi premium workspace uh to for in our experiment we actually use D Studio which you would

16:48see in few minutes where I'm using to analyze the performance of and showcase like you know the difference between uh logical partition when it processes how it looks like as compared to a table which is like not partition and then uh for creating your these custom partition you could use either SS SMS or in in our case we used uh table editor and then finally you

17:11need SS SMS to deploy your xmla endpoint and process your uh xmla endpoint now as Andre mentioned earlier all our templates are based on samples catalog and TPC schema right and in this experiment uh we have used orders table uh which talks about uh you know and and

17:33then which has a fact table which has an orders fact table and that fact table is in is an import method is in import mode right so what what I have done here is uh I've created a report and published this in our premium workspace and what I'm showing here is by default in the screenshot as you would as you could see uh when you when you publish a report

17:55for the first time by default powerbi just creat creates uh it's a non-p partition table so what that essentially means is all your data is just processed as a single partition in a single threaded way so as you could see here on the screenshot here I'm processing like 7.5 million rows and um the number of

18:17partitions is just one so all my entire data set is getting refreshed on just that one partition what I have done is I've created in the Second Step In The Next Step what you see here is I've created custom partition like in this case what I want you to understand was I wanted to understand uh I wanted to process the data faster and I wanted to process it

18:40based on my different order priority now there could be orders which are U which are high priority then there are orders which are like medium and low priority as well right so what I have done I have created here is as you could see in the screenshot is I've created five different partitions based on the order priority like urgent low medium so on

19:04and so forth and now as you can clearly see instead of having um uh instead of having like one partition processing the entire 7.5 million rows I'm still processing the same amount of rows but it is multi-threaded and it is actually getting processed parall and each partition is processing its own set of

19:27Records and in terms of performance and in terms of implementation what you could see here is I in in the next step I have actually tried to show that like if in case this is the first time when I process my default report which is a single nonp partition table it finished in 8 and a half minutes uh processing the entire uh

19:50you know process refreshing the entire data set but when I when I process my partition table which is the five partition that I showed you earlier as you could see here it actually finished in less than 4 minutes so clearly as as you can see I'm and even processing a relatively smaller data set

20:12of just 7.5 million I'm actually able to see like 50% Improvement all because of partitions and all because of partitions these partitions processing the data parall in a multi-threaded way so as your data set actually incre increases you know and when you start dealing with bigger terabytes of data set on on import mode your logical partitioning is

20:36becomes really powerful because now you are able to see better data performance and your data set refreshes and uh your end users would be like you know would be would would get your data quicker so just an example an experiment of how you how you can use uh partitions on the import mode and how it helps in the performance

21:02perfect I think we discussed uh three important topics how to use the parameters to streamline uh the uh the connection to different SQL data seal uh environment we talked about the uh the best choice when it comes to choosing direct query import mode and the Dual dual mode or different use cases and we

21:26talked about how to uh optim how to optimize further more the performance on PBI any additional information want to share with us Andre I think you're on mute sure thank you uh thank you SEF for

21:51summarizing the best practices or templates which we published in the in the Repository um I would like to wrap up uh our you know uh short conversation with this uh summary Microsoft powerbi is the leader in the business analytics market and at data breaks uh we set the new category

22:15for data platforms which is data intelligence platform and we see more and more customers implementing powerb analytics on data briak data intelligence platform with this repository of reusable samples reusable best practices we aim to help our customers to implement and optimize their powerbi Solutions on datab break signal both

22:37performance and cost wise following best practices is the foundation for success in the long term our repository provides not only an overview of best practices but also step-by-step instructions and parbi templates which can be easily reused in any datab bricks workspace by the way not only Azure datab bricks but also datab breaks on AWS and gcp we are

23:01truly multicloud so if you working on uh if you're working with powerbi on datab break SQL we encourage you to check our repository uh we have more than uh we discuss today and we are not stopping there we will be adding more uh best practic more examples in the future to help our customers to

23:23succeed thank you Andre and just for your information you will find link of the GitHub report in in the description of the video and you can leave your comments there in case you have any questions or maybe suggestion to Andre and ysh and again thank you for your amazing contribution to the community and all your the work you're doing to

23:46improve the performance and the satisfaction of so many customers and uh data breaks and po V users thank you having us