Power BI Data Optimization

There's usually a clear desire for companies creating Power BI reports to build them in a way that offers the best user experience. It involves not only delivering excellent data analysis to satisfy monitoring requirements but also designing optimal output reports. Too often the responsibility of maximizing output is put exclusively on IT or Power BI managers when it should be a feature of anyone using the tool.

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

Performance Optimization

Performance optimisation may seem overwhelming, particularly to a self-service consumer or someone new to report growth, but there are plenty of actions to improve reporting performance without having to be a seasoned professional.

In this article post I'm going to walk through some practices that your company can now adopt to maximize report results, right in Power BI Desktop, no matter what level of technology experience your team has. This post will specifically discuss tactics in two areas where performance can be easily enhanced: reporting and visual design, and data transformation in the Power Query Editor.

Visual Design and Study

First, let's focus on the design and visualizations of reports. What many Power BI users don't know is that any visualization that shows data issues a query before rendering to fill the data. Visuals such as text boxes and photographs do not issue a query but it takes time to make the website. All of this can quickly add up as more is added to a website.

The best approach to design is to include only insightful, informative visuals on a report page, striking a balance between providing satisfactory feedback to users and overcrowding the website. If a page performs slowly and is filled with visuals, find places where visuals can be omitted or mixed, or transferred to another page or article.

Below, I'll demonstrate how, in a study, I reduced visuals without sacrificing perspective, thereby improving efficiency. As always, I love wordplay, so my study "Hospital Efficiency and Patient Satisfaction" is the one I have chosen to demonstrate maximizing Power BI output. The original pages are titled with "Original," and the optimized pages have "Optimized" in the title.

The discrepancy between the two first pages is a slight one. The metrics shown along the top in "Overview First" are all separate cards sitting within a sort of a rectangle. As a result, I have three query-issued images, and four that require time to make.

I took these different cards on my configured website, and assembled them into a multi-row deck. I formatted the card to closely suit the single card's visual impact, and placed it onto the border to remove the need to use a shape as a container. And I took four photos and made one of them. The combination of single cards into a multi-row card and the elimination of shapes by using borders or custom page backgrounds are quick ways to reduce the time to render. This trick may not always produce the exact same visual effect, but it's close enough in one instance when performance is involved.

More has been going on on the page Review of Patient Satisfaction. This page contains a lot of slicers, as you can see in the original, since users need to be able to analyze patient satisfaction in a variety of ways.

Power bi slicers

Slicers are the biggest offenders for adversely affecting the performance of the report page. Try using filters instead of slicers to give the users the same features with the new filter experience available in preview. Before the users communicate with them, filters don't issue a query, whereas slicers issue a query before rendering. I relocated some of the slicers to the filter panel on my configured website, and colored the filter panel to be a very light blue, so it only stands out enough to make users aware of it.

Take your career to new heights of success with Power BI online course

Optimisation of Power bi

The filter pane is not available in a publicly posted report to the web, so in this post you can not interact with the filter pane in the embedded version. However, as you can see in the screenshot, it can be used by users who access my organization's report in Power BI Service.

Not only does the use of filters reduce the load time, it also frees up precious canvas space on the report tab. You will also note I kept three of my slicers. I chose this design because my users like the simplicity of using the slider bar for certain metrics and these are the slicers that users often communicate with while browsing the website. The report is performing optimally, so I will keep those slicers as they are unless it becomes a problem. This is how I struck a balance between performance and visual design and gave my users the best possible experience.

Test Performance in Desktop Power BI

In Desktop, I used the Performance Analyzer to check how the basic visual improvements I made on my optimized pages reduced the load time. The Performance Analyzer records and displays how long the rendering on the page takes for each visual and breaks that time out into category. You will learn more about how to use the Performance Analyzer and interpret Microsoft's report of its tests.

Optimisation of Power bi pages

The difference in performance was only about 100 milliseconds, in the "Overview" pages instance. The difference was anywhere from 300-500 milliseconds across multiple test scenarios on the 'Patient Satisfaction Review' pages, where I transferred most of my slicers to the filter window. Keep in mind that in my case, none of the pages take very long to create, because I don't work with a large amount of data and my model behind the scenes is designed for Power BI. The improvements I made saved time, and in a report that is struggling, possibly with a larger amount of data, that makes a lot of difference.

Data Transformation and Power Query Editor

Now let's talk about the Power Query Editor and the transformation of data.

Power Query Editor is one of Power BI's most useful features. It allows users to perform complex data transformation and preparation in a simple interface, without learning a query language or using an external tool. Although Power Query facilitates data transformation, it also makes tanking performance easy. It's easy to start clicking around, transforming info, and end up with a one-mile long Applied Steps trail accompanied by a glacial rhythm chart. Fear not — I will clarify a few aspects of the Power Query Editor and put out two easy practices that will help you avoid getting into a situation like this.

Above all, do steps of transformation in the right order. Yes, there is a correct order, even though any order can bring about the desired transformation of the data. It is best to go in with a simple plan about what to do with the data before you begin to turn it.

Example of Power BI Data Transformation

For example, if you know that you need only a portion of your source data, filter it down at the first step. Operating with only the required data set during the rest of the transformations would not only speed things up, but there are also several cases where Power Query Editor will transfer data back to source, using the power of a relational source such as the SQL Server database to do transformations. It is a method called Query Folding, and while it might sound complicated, what you really need to understand is that often such data transformations can be done quicker in an environment such as SQL Server, and where possible, Power Query can submit such transforms to the data source. When you do measures such as front-filtering data, Power Query can recognize when it can be moved back to source, thereby improving reporting.

Conclusion

I hope you reach to a conclusion about Power Bi transformation. You can learn more through Power BI online training Hyderabad.