Update strategy to reduce refresh times in PBI Desktop

[FilipK]

The last days I figured out some issues regarding refresh times in PBI Desktop.

Situation

• PBI Desktop with SQL DB as data source
• One table gets data from an SQL view that calculation costs 30 minutes
• The view unfortunately cannot be easily optimized to be faster (not in my hands

What happened

• A column in the view was added
• To prepare the refresh you need to go to Transform data, select the table in query editor and in the navigation pane of the applied steps I clicked on Navigation and then refresh preview in order to get the new column updated in my local data model  

• With that preview a sql query Top 1000 .... was called but due to my non optimal view it still took 30 minutes.

Beside optimizing the view, what are possible workarounds to reduce time for such adaptions? I mean I was pretty sure how to filter that new column with the advanced editor, so preview was not really necessary for me. 

[v-janeyg-msft]

Hi, @FilipK 

Have you tried to use sql statement to filter part of the data from the beginning of connecting to the data source? Maybe it will help.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[FilipK]

Hi @v-janeyg-msft ,

you mean using a e.g. a where clause that makes the query itself very fast, don't you?

I think that will work to speed up the preview, right. But still before pushing the report to my PBI workspace I need to remove the sql statement again, since I need the whole dataset. So it will ask again for refresh time. 

[v-janeyg-msft]

Hi,  @FilipK 

I found a feature to turn off preview directly, but it may cause the refresh frequency to increase. If you don’t mind closing it directly.

Reference: Disable Power Query background refresh - Power BI | Microsoft Docs

Best Regards

Janey Guo

[v-janeyg-msft · Accepted solution]

Hi, @FilipK 

Existing features don't help the previewed data. Since you just want to speed up the preview, you can use this method to filter the data first, and then when it is loaded into the PQ, you can still modify the sql statement in the Advanced Editor. It should be noted that if the refresh speed is always slow, it is likely that the performance of the source data is too poor.

Best Regards

Janey Guo

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

[amitchandak]

@FilipK , If the top 1000 from view is taking 30 Mins, I really doubt the view is optimized for that. explore the option to work tables.

Another option is to pass some date filters or another filter that is better optimized on view.

[FilipK]

@amitchandak , thanks for your prompt answer. 

You're right, but do I have any chance to change the way PBI is querring the refresh? Before the top1000 step is performed the view is created which costs the time. That is the problem.

select top 1000      [$Table].[epoch_time] as [epoch_time],      [$Table].[msgtime_datetime2] as [msgtime_datetime2],      [$Table].[ans] as [ans],      ...]  from [dbo].[View_TD2] as [$Table]

You proposed using some other date filters. But to what I understood, there is no way to influence how the preview query looks like. 

As you see in my picture above I created a filter for incremental refresh. With that I'm super happy. In PBI Desktop I can change RangeStart and RangeEnd so the query costs 10 seconds. But for refresh I don't see the solution.