10 Points to consider to build high-performance dashboards in PowerBI
With the exponential growth in data volume across various business functions and increased accessibility to data, businesses can unravel insights like never before. Data visualization has become a vital part of this unraveling process. Hence, the need for highly efficient dashboards that encapsulate the required business metrics becomes inevitable.
Leading last year’s Gartner Magic Quadrant for Analytics and Business Intelligence Platforms, Power BI has emerged to be a prime choice across industries as a BI tool. Power BI’s inherent mobility, ease of accessibility, and moderate costing are accelerating its adoption across organizations.
Over the years, Ellicium has helped many organizations with analytics and enterprise business intelligence. Amalgamating our experience, in this article we have put together 10 critical points to get the best out of Power BI when it comes to building high-performance dashboards.
Benefit from Power BI’s Compatibility with Azure
If you need advanced predictive analytics beyond regression and segmentation, PowerBI is getting mature with it. Power BI can execute AI/ML programs using Azure service and can easily push the result back to the dashboard. Traditionally if you need to perform advanced analytics, you have to write R / Python code and push the results to the dashboard. PowerBI Azure compatibility takes away the effort of writing Python or R code.
At Ellicium we have leveraged our data science expertise and have developed multiple reusable ML programs in Azure services that can be used seamlessly in PowerBI.
Data Analysis Expression (DAX) – The Engine of Power BI!
DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. In crux, DAX is the engine of Power BI. DAX makes data manipulation easy. One of the key features if Power BI, DAX enables and boosts on the go analysis on the dashboard.
DAX is blazing fast at applying filters, whereas Power Query can be very slow at applying a join, especially if that join is not being pushed down to the SQL level.
Restrict the usage of image URL components
Often business users require images to be embedded in dashboards. The simplest example of this is indicators to show the status of KPIs. It is very easy to display images by using URLs of images. For every invocation of the dashboard, Power BI extracts the images from an external web-based source which takes a toll on response time.
This hampers the availability and responsiveness of the dashboards. By restricting the usage of image URL components, you can improve response time a lot.
Adjust and tune interactivity between components
Power BI visuals are interactive with each other. Selecting an item in a visual will affect the display of another chart. Sometimes this results in highlighting items in another chart, and sometimes filtering values in the other visuals. By default, all visuals in a report page are interactive with each other. However, this feature is not always desired. Apart from the fact that it leads to unnecessary changes to the charts, it also slows down the performance of the dashboard.
For example, in a sales dashboard, when a user changes currency of sales amount, it need not change percentage sales by-product since functionally these 2 charts may not be related.
You can disable interactivity between components that are not functionally related.
Create summary tables for data modeling
This is a common oversight where single events are taken as data points. This adds complexity to visualization on the dashboard along with unnecessary computation. Hence, always make sure to aggregate data tables whenever it is possible.
We have created a checklist to decide whether a data element can be aggregated or not. Using this checklist our developers go through each and every data element and decide whether an aggregated table can be created for those elements.
We have designed custom visuals specifically for sales analytics, production analytics that can be used across multiple industries. However one needs to be aware of the point that custom visuals may be slower in performance as compared to the native visuals.
Take only what is required!
OLAP databases, data warehouses, and data marts contain hundreds of columns with millions of records. Data models should contain only those elements that are required in the dashboard. During development, developers tend to add many elements to the data model purely for analysis or experimentation purposes. But these elements that are not in use in the final version of the dashboard need to be remove.
Our code review process for PowerBI has a lot of focus on making sure that there are not redundant data elements in the PowerBI data model. Also, one of the important tasks to make this a foolproof exercise is to maintain a source to target mapping documents and refer those during the code review process.
Pack more visualizations with the Bookmark feature
One of the great features of Power BI, Bookmark helps to efficiently utilize the space available on the dashboard. It allows BI developers to imbibe two or more visuals in the same region. Bookmarks act as a button to switch between the available options. We have effectively used this feature to save real estate on the dashboard by packing more analytics in the same space.
Leverage Power BI’s Exclusive Analytics Features
Unlike dashboards about a decade ago, reports and dashboards today need to integrate predictive analytics features. Recently Microsoft has introduced cohort and segmentation analysis features. We have used this feature multiple times for the segmentation of customers, products etc. Sales and marketing dashboards can very effectively leverage this feature.
Take advantage of the Power BI custom visual marketplace
Power BI has an amazing marketplace that has many ready to use custom visuals on offer across plenty of categories. It is always a good idea to use already tested and available visuals on the dashboard if they fit your requirements. It reduces your efforts and accelerates the dashboard building process. Make sure to check for visuals which are Power BI certified.
For one of the customers that required complex charts with a combination of images or charts with an advanced combination of charts, we leveraged the marketplace extensively to design the dashboard as per customer requirements.
There are of course many other features that make Power BI a visualization tool it is. Consider this as a checklist that you want to for sure look mark out when you want to build high-performance dashboards.
Nikhil Phadke and Saumitra Kulkarni are the co-authors of this article. You can get in touch with them here: Nikhil: https://www.linkedin.com/in/nikhil-phadke-256a39169/