Power BI is a dashboard tool developed by Microsoft and forms part of the Microsoft Power Platform. Powerful, integrates almost perfectly within the Windows 365 environment and is very competitively priced. Primarily due to those low costs and simple integration, many organisations quickly opt for Microsoft Power BI. A downside: Power BI can be incredibly slow. That is why we offer a number of tips to work faster with Power BI. By Harmen, CTO & senior data scientist at Datalab

There are countless reasons why Power BI is slow. The primary one is simply that it is a bulky, large application. Microsoft has done its best to make Power BI user-friendly and connectable to countless sources. Consequence: the application is full of things you will probably never use. Unfortunately, the application is built in such a way that there is little you can do about this. What can be done to keep things running smoothly?

Tip #1: Power BI is particularly slow on systems with limited memory. In practice, we rarely encounter a situation where the computer's processor is too slow. Usually, the problem is working memory usage, especially when you use many other applications. Purchasing extra memory is the advice. This is almost always possible and relatively cheap: for less than £100 you already get a significant upgrade. If this is not possible, you have two other options. The first and most obvious: ensure you have few other applications open simultaneously. Browsers in particular have a habit of consuming a lot of memory. Simply closing your browser and restarting it works wonders.

Tip #2: Use a virtual machine to make Power BI faster. Also usable on a Mac or Linux computer. Examples of virtual machines include Microsoft Azure, Google Cloud Platform or Amazon AWS. Also within the Netherlands, there are countless providers including TransIP. This strategy - deploying virtual machines - we frequently apply for our clients. The advantages at a glance:

  1. Focus: the virtual machine is set up for a single task, namely working with Power BI. No other software gets in the way.
  2. Calculations can be performed 'close to where the data resides', provided you use a data warehouse in the cloud.
  3. More efficient simultaneous working: you can share the machine with multiple colleagues working in parallel. This means your IT department has less to manage and you save costs on purchasing expensive laptops.

Data processing in Power BI is slow (and notoriously difficult)

Power BI excels in the number of source types you can connect. Virtually all databases and data warehouses 'plug in effortlessly' to Power BI. And with connectors, other sources can also be easily connected. Of course that is fantastic, but simultaneously one of the main reasons people call on our help - because the connectors barely function due to the large volume of data. Conclusion?

Power BI is a wonderful visualisation and analysis tool, but a very poor data manager.

In Power BI you can cleanse data, link it, perform calculations and much more. For small datasets (a few hundred rows maximum), it works well. As soon as your datasets grow into the thousands of rows, many calculations take place, or you want to link different sources, it quickly becomes problematic. You notice it in the slowness of Power BI Desktop (where you build dashboards), but also in Power BI Apps (where you can publish dashboards).

Our tip: where possible, move complex calculations to where your data is stored. Cleansing, linking and filtering of data is better performed in the source system rather than importing your entire dataset into Power BI. This is usually possible when your source system is a database or data warehouse. It becomes harder when working via a connector, for example an API. In that case, it is worthwhile investing in a data warehouse. This need not be very expensive and offers many additional possibilities.

Another major advantage of performing data processing before it is loaded into Power BI: your dashboard is much more stable. Connectors often load entire datasets inefficiently (for example by copying everything from the source each time, whilst only a small portion has changed). A data warehouse is virtually always more efficiently arranged and continues working even when source systems are temporarily unavailable.

Data warehouse: the added value

A data warehouse is built to handle all the complexity of cleansing data, linking and filtering for you. You can also use the data warehouse for complex calculations. A data warehouse is virtually always better, faster and more reliable at working with data than any dashboard tool. This also applies to Tableau, Qlik and the countless other dashboard products, regardless of what they promise.

In practice:

  1. In your data warehouse, you use a SQL query to get your data in order. Complex calculations such as aggregating and filtering are done in your data warehouse. You save that query as a so-called 'view'. This resembles a table or dataset as in Power BI, but all calculations have already been done in the background. Handy and fast. An additional advantage is that colleagues can also use your view, so everyone looks at the same figures.
  2. You then import the view into Power BI - it appears in the list of data sources as you already use them. If needed, you can further aggregate or filter.
  3. Does something change in the source systems, or do you want to add extra data? Adjust your view and refresh the data in Power BI. You will automatically use the correct data everywhere.

The "D" in DAX Queries sometimes seems to stand for drama. SQL is much simpler. Daunted by SQL? At Datalab we developed query.ai so you can ask questions in plain language - including Dutch - and the data warehouse writes the SQL query for you.

The process of developing dashboards in Power BI is slow

A tip for pro users who develop many different dashboards, especially if you reuse (parts of) those dashboards. Ask yourself whether Power BI is still the right tool for you. We see many organisations performing acrobatics with dashboards that are actually complete analytical environments. Power BI was not made for that. It can, but it is not optimal. There are much better options available. Our three favourites:

  1. R Shiny: build the most complex dashboards custom-made down to the smallest detail. The learning curve is somewhat steep but the result is impressive. You can develop entire web apps that do exactly what you want and seamlessly fit your organisation's branding.
  2. Shiny for Python: fresh off the press. Do you prefer programming with Python? Then this option is interesting. New but very promising. Built on the same principles as R Shiny, but in Python.
  3. Dash by Plotly: do you have experience with HTML or JavaScript, or are willing to acquire this knowledge? Then this is a powerful tool with beautiful visualisations. Slightly less powerful than Shiny, but fully customisable.

The greatest advantage of all three tools above: they are 100% free. Admittedly, publishing dashboards is not quite as smooth as with Power BI, but the additional capabilities they offer - and the cost savings - are more than worth it.

Conclusion

Power BI is a fine tool, but not suited for data processing itself. If you want to use Power BI effectively, we recommend exploring the possibilities of a data warehouse. If you are looking for a broader solution, we advise looking at Shiny for R, Python, or Dash.

The problems you may experience with Power BI are not exclusive to Power BI alone. Tableau, Qlik, Cognos and countless other tools can also be slow. It is therefore pointless to 'just switch' to Qlik or Tableau. This transition will virtually always cost you more than it delivers. Also be critical of offers like 'Qlik with built-in data warehouse' and similar. Rarely is this the best solution for you - neither in terms of costs nor functionality.