10 Points to Consider Building 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 as a prime choice across industries as a BI tool. Power BI’s inherent mobility, ease of accessibility, and moderate cost accelerate 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 quickly push the result back to the dashboard. Traditionally, if you need to perform advanced analytics, you must write R / Python code and make 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 using 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 greatly improve response time.
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 on a report page are interactive with each other. However, this feature is only sometimes desired. Apart from the fact that it leads to unnecessary changes to the charts, it also slows down the dashboard’s performance.
For example, when a user changes the currency of sales amount in a sales dashboard, it need not change the percentage sales by-product since these 2 charts may not be related functionally.
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. Using this checklist, our developers review every data element and determine whether an aggregated table can be created for those elements.
Create a feature-rich Dashboard with Custom Visuals using Javascript
You can build custom visuals using JavaScript for the Power BI dashboard. You can also combine the features of multiple visuals into one, which contributes to performance using JavaScript. Thus, if existing visuals in PowerBI or the ones in the marketplace do not meet your needs, you always have a choice of designing your own visuals.
We have designed custom visuals for sales and 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 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 not used in the dashboard’s final version must be removed.
Our code review process for PowerBI focuses on making sure that there are not redundant data elements in the PowerBI data model. Also, one of the essential tasks to make this a foolproof exercise is to maintain a source to target mapping documents and refer to those during the code review process.
Pack more visualizations with the Bookmark Feature
One of Power BI’s great features, Bookmark helps efficiently utilize the space available on the dashboard. BI developers can 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 to segment 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 with many ready-to-use custom visuals on offer across plenty of categories. Using already tested and available visuals on the dashboard is always a good idea if they fit your requirements. It reduces your efforts and accelerates the dashboard-building process. Make sure to check for visuals that are Power BI certified.
We extensively leveraged the marketplace to design the dashboard per customer requirements for one of the customers requiring complex charts with a combination of images or diagrams with an advanced blend of charts.
There are, of course, many other features that make Power BI the visualization tool it is. Consider this a checklist you want to look out for when building 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/
Saumitra: https://www.linkedin.com/in/saumitra-kulkarni-a67a38168/