Debugging / Tracing opportunities for incremental refresh

[FilipK]

We're trying to improve our report update performance by using incremental refresh according the following description.

Incremental refresh in Power BI - Power BI | Microsoft Docs

Problem

After having switched to incremental refresh the performance is worse than before (refresh time 30 minutes against 3 minutes before)

Our setup

Dataset

The current dataset is still quite small but to is to be expected 100 times bigger than today.

The biggest table:

The first refresh after switching to incremental refresh took very long and the others were already faster but still far above our expectations.

Between each refresh only 70 table rows are added, so I don't understand why pulling 70 lines from DB should take that long.

Additional info

• We're accessing some SQL views. The execution of each view is only taking some seconds.
• Also in Power BI Desktop the data refresh takes more than 20 minutes

Do you have any ideas where the bottleneck is or how to narrow down to the root cause of the slow update?

 

[Anonymous]

Hi @FilipK ，

First check whether the data source used in your report supports query folding. If the data source used does not support query folding, which may require retrieving the full dataset from the data source. This can cause incremental refresh to be very slow, and the process can run out of resources either in the Power BI service or in the on-premises data gateway if used. Also please check if configure incremental refresh correctly in Power BI. 

In addition, please review the content of the following links and check whether you have same situation with them.

Incremental Refresh didn't save time

Power BI Incremental Refresh for SQL Sources

Best Regards

[FilipK]

Dear @Anonymous ,

thanks for your reply.

I'm using Azure SQL S3 Standard and according to the MS incremental refresh documentation it shall be supported.

My point is if the incremental refresh is done correctly. I think so, but who knows.

The native query appears to me sensible. No weird things we do.

The SQL profiler cannot be used for Azure SQL doesn't support that, so I'm currently reading into the other opportunities to trace the server requests and responses (link1 and link2) If you already have a good idea how to see immediately on the Azure SQL if folding is done with the dataset or not, I'd be thankful.

If I refresh data in PBI Desktop twice I assume the whole dataset is updated for it takes almost identically and I see the row counter counting to the same figure.