The Biggest Problem With Power BI, And How You Can Fix It

One of The biggest problems with the Power BI desktop is its performance, not when consuming data, Power BI does great on that, but when actually developing the reports. 

A lot of people have a very sluggish experience in Power BI due to their computer’s performance, a lot of waiting time and frustration is involved, especially if you have low RAM and are running a lot of software in the background. 

In this article, we will present you with a few tips that can resolve Power BI performance issues and help you make Power BI more performant, so you can develop Faster.

If you can, Get Some more RAM, It really helps!

This is an obvious one, but it had to be on this article, additional ram definitely has the most observable impact on the performance of Power BI. Your queries will run faster, your visuals will render faster, and you’ll be able generally to develop faster.

After upgrading our Power BI workstations at DASH BI, to machines with 32 GB of RAM and Intel® Core(TM) i7-6820HQ CPU @ 2.70GHz  Processors, we noticed a huge spike in productivity, more than 60% increase in feature implementation speed.

Make sure the system is managing the page file size

The paging file is an area on your hard disk that Windows uses like memory. There’s a setting in Windows 10 that manages the page file size automatically, which can help improve your PC’s performance. 

To turn on automatic page file management:

• In the search box on the taskbar, type advanced system, and then select View advanced system settings, which has a Control panel listed underneath it.
• In System Properties, select the Advanced tab, then select Settings in the Performance area.
• In Performance Options, select the Advanced tab, then choose Change in the Virtual memory area.
• Make sure the Automatically manage paging file size for all drives check box is selected. If it is, continue to the next tip. If it’s not, select it and then restart your PC by selecting the Start button > Power  >Restart.

Use your PC and see if it’s running better. If it’s not, try the next tip.

Turning off Auto Date/Time

Auto date-time is a useful feature when you are dealing with quick and dirty analysis, but if you’re doing any serious report development work with a lot of tables and columns, especially dates, turning off auto date/time and using a Date table instead can provide a huge return on investment.

In addition to improving the performance and speed by a huge margin, turning it off will also help you gain a tremendous amount in model size. 

There was once a client we were working with, and he only had pro licenses, he got very close to the 1 GB pro license size limitation and wasn’t quite ready to upgrade to premium. So he asked us to review his model and optimize it for size. Just turning off auto date/time got the model down from 980 MB to 223 MB!

Start getting used to working with date tables rather than auto date/time.

Reduce the number of apps that open when you first boot up your computer

If your computer has little RAM, and you’re having crippling slow development time, it’s most likely because you have too many apps running at the same time.

This implies that several useless apps may be running in the background, slowing things down even after Windows has fully loaded. Here’s how to make it right :

1. Select Task Manager from the Start menu by right-clicking the Start button.
2. Select the Startup tab in Task Manager.
3. Right-click a program you don’t want to start at startup and select Disable.
4. Keep as few programs running, and try to run Power BI right after a fresh startup for optimal results.

Optimize performance by removing Windows special effects

Windows 10 contains a variety of special effects, some of which are extremely noticeable and others that are quite subtle. They make using your computer more exciting and pleasant, but they can also slow down your computer, especially if it’s outdated or has a sluggish processor.

Here’s how you can disable some of these effects:

1. Open the Control Panel by pressing the Start button.
2. Go to “System” and choose it.
3. Select “Advanced system settings” from the drop-down menu.
4. In the System Properties window, make sure the Advanced option is selected and then click “Settings.”
5. Select “Adjust for maximum performance” to turn off special effects manually, or “Adjust for best performance” to turn them off automatically.

Turn off window transparency

Windows has numerous minor transparency effects that you may not have noticed. For example, if you open the Start menu, you should see a faint, blurred image of whatever is hidden behind it.

You can turn it off completely if you don’t want to forfeit processing speed for such a minor effect (and probably not notice the aesthetic difference).

1. Select the Settings icon, which is shaped like a gear and is located directly above the Power icon, by clicking the Start button.
2. Select “Personalization” from the drop-down menu.
3. Select “Colors” from the drop-down menu.
4. Scroll down and slide the button to the left to turn off “Transparency effects.”

Run your computer at maximum Power Settings

Depending on how your power management controls are set, your computer may not be functioning at full speed.

Windows has many power plans that intentionally slow down your PC to save power, allowing your laptop battery to last longer. 

Here’s how to pick a more powerful plan:

1. Open the Control Panel by pressing the Start button.
2. Select “Power Options” from the drop-down menu.
3. Select “High performance” for the fastest overall processing speed. 
4. You won’t see these options unless you’re using a laptop.
5. Scroll down and slide the button to the left to turn off “Transparency effects.”

User fewer pieces to build your dashboards

There are always multiple ways of doing the same thing in Power BI, and if you care about optimizing your own development experience, always choose the one that takes the least number of “pieces”. 

Here are a few examples :

Microsoft has provided buttons where you may add custom icons/images in the Power BI September version. In this video, I show you how the old situation was and how I fixed it with just one button! The performance is even faster by 80%! Important!

There is also this article shows that the number of visuals in a report can degrade the performance by one order of magnitude, compared to the results obtained by using a smaller number of visuals. 

At the time of writing, Power BI does not have a generic “small multiples” feature to automatically create multiple copies of the same visual based on the value of an attribute, such as the Brand attribute used in this article. 

The AppSource marketplace already offers several custom visuals with a specialized “small multiples” option, like Small Multiple Line Chart and Sparkline. 

The latest version of Card with States by OKVIZ is the first visual providing the “small multiples” option to replace multiple instances of the Card visual, which is a very common source of performance issues in Power BI.

Optimize your DAX measures

We will not go too much into depth on this subject as it’s pretty profound and deserves its own article, we advise you to check out this video by Curable if you’d like to know more

Use a STAR Schema

Star schema is a well-known modeling technique used by relational data warehouses. It necessitates that modelers categorize their model tables as dimensions or facts. Adopting this kind of schema for your Power BI Files will ensure that your analysis runs as smoothly and as fast as possible.

Business entities—the items you model—are described in dimension tables. Products, people, places, and concepts, even time itself, are examples of entities. 

A date dimension table is the most consistent table in a star schema. 

A dimension table is made up of descriptive columns and a key column (or columns) that acts as a unique identifier.

Sales orders, stock balances, currency rates, temperatures, and other observations or events are stored in fact tables. 

Dimension key columns that link to dimension tables, as well as numeric measure columns, make up a fact table. 

The dimensionality of a fact table is determined by the dimension key columns, whereas the granularity is determined by the dimension key values. 

Consider a fact table with two dimension key columns: Date and Product Key, which is used to hold sales targets. The fact that the table has two dimensions is obvious. The granularity, on the other hand, cannot be determined without taking the dimension key values into account.

Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time.

Tables that are either dimension-type tables or fact-type tables should be included in a well-structured model architecture. For a single table, avoid mixing the two types. 

We also propose that you aim to give the appropriate amount of tables with the appropriate relationships. It’s also critical that data in fact-type tables are loaded at a consistent granularity.

Finally,

It’s critical to recognize that the optimal model creation is a combination of science and art. When it makes sense, you can sometimes deviate from solid advice.