Overview of Power BI Performance and Download Size Improvement Techniques

Go to Dutch version.

Invantive Cloud provides a uniform way to access cloud and traditional database platforms from a variety of Microsoft products:

  • Power BI Desktop,
  • Power BI Service,
  • Azure Data Factory,
  • Microsoft Integration Services and
  • Power Query for Excel.

Importing company data into for instance multiple Power BI Desktop reports can take a long time and consume significant amounts of network capacity and computational power. Invantive Cloud limits the network capacity and computational power to ensure proper operation across the whole user community and reduce costs.

In many scenarios the same reports can be achieved through various means, but the performance and download time can be multiple orders of magnitudes smaller. Even improvements with a factor 100 or more are not uncommon, while sometimes exceeding a factor 1.000.

As a guide, Invantive has collected various tips to help you optimize existing reports, data processing, query performance and dashboards:

Make sure you understand the structure of Invantive Cloud before starting to optimize the performance and reducing the download. Learn the structure on Invantive Cloud Structure.

Table Functions

The tips provided work for all Invantive Cloud-based data sources and cloud platforms. However, some data sources may show a better improvement using a technique than data sources. For instance, some platforms use table functions extensively and views based on table functions can be significantly slower than a normal table download would be. Table functions are commonly found on for instance Teamleader, Loket and NMBRS. Read more on table functions on What are table functions and table function parameters?.

Refresh Frequency and Incremental Downloads

The refresh frequency of reports and each Power BI dataset can be configured widely. Continuously downloading all the data from Invantive Cloud for Power BI reports, even for slowly changing large datasets, can consume available capacity very quickly. Power BI bundles technologies for an incremental data load, plus enables configuration of the refresh frequency.

For a typical 9-to-5 Monday-to-Friday business, it is a waste of resources to load the data every hour, 168 hours a week. By changing to a 40 times per week cycle, you can reduce resource usage by a factor 4.

Power BI still slow?

Follow these steps in case you still experience slow performance with your Power BI report after applying these techniques by requesting a free performance audit:

  • Create a topic on these forums.
  • Create a Power BI-template file (*.pbit) and add it to the topic.
  • Do not, repeat not, upload your pbix file. It probably contains company-confidential data.
  • Or export the query text from the Advanced Query Editor and add it to the topic.
  • State the platform of your data source: Visma.net, Yuki, AFAS Online, etc.
  • Explicitly state your data volume in number of rows per table.
  • Explicitly state your current Power BI report performance in terms of number of rows, download time and MB of data downloaded across the network as seen in the Invantive Bridge Online monitoring. Note that actual download size can be a factor 10-15 larger due to decompression.

For the near future we are working on an idea to daily refresh the databases in the background. For more information refer to Daily open and query databases to check consistency and rotate refresh tokens.