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:
- Reuse data downloaded from cloud platform, reducing data volume exchanged with cloud platform: Differentiate OData4 for Power BI Cache Behavior
- Share the same data across reports using Power BI datasets: Reduce OData Download Size by Sharing Data Sets in Power BI
- Reduce number of rows retrieved through OData by filtering: Reduce data volume and improve performance of your Power BI report by Filtering
- Reduce number of rows for platform consisting of partitions such as financial companies: Improved Very Large Company Volume Performance on Exact Online
- Reduce data volume by filtering, not only on Azure Data Factory: How to filter data from OData query on Azure Data Factory? - 3 van forums
- Analyze which Power BI reports and data sets run slow: More easily analyze OData downloads with Bridge Online Monitoring
- Improve OData filters: Optimize OData Filters to Improve Performance
- Reduce download size by selecting solely needed columns: Reduce download size for Power BI reports by selecting solely the needed columns over OData
- Make the report easy to distribute to get help from a consultant: Use Power BI parameters with Invantive Bridge Online
- Pre-load data sets into cache: Pre-load data in OData-cache to avoid Power BI timeouts
- Give the data downloads more time as described on Avoid timeout error on Power BI OData download.
- Share datasets to reduce data downloadsas described here: Share access to a Power BI dataset
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.