Troubleshoot PowerBI Report, which take too long to refresh

[dc7669]

Hi all,

I have a PowerBI report that take too long to refresh and would like know how to troubleshoot it, i.e. finding the steps that uses the most time and finding ways to reduce it. 

The table in the PowerBI Report took a long time to process. 

Two queries were appended together in the "Appended Query" step.  Each query had about 200k rows.  I will first filter each query with Year-To-Date entries only before appending them together.  From the messages of the refresh message screen, within 5 minutes of the refresh, all those records were retrieved.  

Then, the next two steps are getting "previous 5 year average" of 2 numbers.

In the refresh message screen, I could see the row counter increase by about 10 in 1 minute.

After these two steps, there should be about 7000 rows in the tables.  Thus, the update took a LONG time. 

From the above, it suggests to me that the "previous 5 year average" were the steps that took a long time, right? 

However, when I took out the "Year to Date" filter in the two base queries, the report could be finished within an hour in PowerBI Cloud!!  

This seems to tell me that the "Year to Date" filter is the culprit.  

"Year to Date" filtering:  I had a 3-step process at the beginning.

1. Add "MMDD" from the data on the record.

2. Add "MMDD" from today's date.

3. Filter out the dates are the beyond today's date.

Then, I tried to shorten to 2-step.

1. Test whether the date in the record should be included in YTD.

2. Filter out the false ones. 

But, this does not seem to have any impact on the overall execution time at all. 

Any idea on how to audit the execution time of each step in PowerBI report? 

Any suggestion on how to speed up this report? 

Thanks. 

dchan1

[dc7669 · Accepted solution]

FYI, I solved this issue.  I was able to get the PowerBI report to run in about an hour. 

It seems to be that the culprit was the was I did the YTD comparison.

I was using this at the beginning: 

= Table.SelectRows(#"Added Custom1 - MMDD", each if Date.Month(DateTime.LocalNow())>=7 then [MMDD] >="0701" and [MMDD] <=DateTime.ToText(DateTime.LocalNow(),"MMdd") else [MMDD]>="0701" or [MMDD]<=DateTime.ToText(DateTime.LocalNow(),"MMdd") )

This is essentially comparing Text, which takes a long time to execute.

Then, I added 10000 to [MMDD] to make it an integer field and use it to filter. 

= Table.SelectRows(#"Changed Type", each [MMDD] <= Number.FromText(DateTime.ToText(DateTime.LocalNow(),"MMdd"))+10000)

This seems to drastically reduce the run time to an hour.  

dc7669

[Anonymous]

Hi @dc7669 ,
To audit the execution time of each step in a Power BI report, especially during the data refresh phase, I recommend using the Performance Analyzer in Power BI Desktop. This tool can help you determine which steps or visuals are taking the longest to load. For more detailed instructions on using the Performance Analyzer, see this document
Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...
Based on the dates you mentioned, you may want to try using incremental refresh to refresh the most recent data
Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn
This allows Power BI to refresh only changed or new data, rather than the entire dataset. As part of the incremental refresh strategy, you can greatly reduce the amount of data that is processed with each refresh by applying a "year to date" filter.
If, after applying the above suggestions, you are still experiencing problems with long refresh times, it may be helpful to check the complexity of the calculations used to obtain the "average of the previous 5 years". Complex calculations on large datasets can be resource intensive. If possible, consider optimizing these calculations or pre-aggregating the data.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[dc7669]

Hi @Anonymous  Albert,

Performance Analyzer:  It is used for testing how long it takes for graphics on a report to show/refresh, right? 

For my case, the graphics are displayed pretty quickly.  When I run Performance Analyzer, all things were returned under 1000ms.

For Incremental Refresh,  I have following the instructions here: 

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

to add RangeStart and RangeEnd parameters in the early steps of the data table.   

But, I did away the DateTime field in the few steps I have and the ending data table did not have a DateTime field.  I used group some numbers by years under "Transform" - "Group".  The data refresh seems to be faster than before in Power BI Desktop. 

I uploaded the report to Power BI Service.  I will see if it will run there. 

I will report back in a day or two. 

dc7669