Forum Discussion
Slow updates - other options?
Hello,
I have several pbx reports over 100K KB with 20+ data sources (excel sheets or folders with excels sheet, in total 50+excel sheets) and the manual refresh of the report is sometimes slow and / or very time consuming when searching for potential an error in excel...
How to cope with reports of this size?
I have zero experience with databases, but would it be a possibility to load those excels to a database (SQL?) and pick then only columns I need for my measures?
Thank you for you advise!
Katerina
Hi KatkaS ,
According to your description about how to improve report performance and increase the efficiency of report update, you can try the following steps: first of all you can import only the columns you need, make sure that the type of columns in the model is as simple as possible, and you can do the aggregation operation in excel before you import it to power bi. Secondly, if your exel tables have the same structure, you can merge them. Thirdly, if your data is updated by date, you can use incremental refresh to make the refresh more efficient. Finally, use the Performance Analyzer in Power BI Desktop to learn which visual objects take the longest to load and why.
For optimizing power bi you can refer to this document
Optimization guide for Power BI - Power BI | Microsoft Learn
For information on how to configure incremental refresh you can refer to this document
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft Learn
You can refer to this document to learn how to use Performance Analyzer
Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft LearnBest regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hello KatkaS ,
yes, you can do the database thing and if it's not possible, you can do some transformations to tables to show only the columns you need and if there's fields that you can group by then you can minimize the data.
to remove columns in power bi https://learn.microsoft.com/en-us/power-query/choose-remove-columns
group by in power bi https://learn.microsoft.com/en-us/power-query/group-by
append queries in power bi https://youtu.be/1cPtk-8iK0k?si=Dv3ZnqolTn2VmJws
Hi KatkaS ,
According to your description about how to improve report performance and increase the efficiency of report update, you can try the following steps: first of all you can import only the columns you need, make sure that the type of columns in the model is as simple as possible, and you can do the aggregation operation in excel before you import it to power bi. Secondly, if your exel tables have the same structure, you can merge them. Thirdly, if your data is updated by date, you can use incremental refresh to make the refresh more efficient. Finally, use the Performance Analyzer in Power BI Desktop to learn which visual objects take the longest to load and why.
For optimizing power bi you can refer to this document
Optimization guide for Power BI - Power BI | Microsoft Learn
For information on how to configure incremental refresh you can refer to this document
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft Learn
You can refer to this document to learn how to use Performance Analyzer
Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft LearnBest regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
