How to disable some preview LIMIT 1000 queries on a refresh?

[antfarol]

Hi!

I have a PowerBI report connected to the SQL Warehouse on Databricks. It is setup for incremental refresh, and locally I'm dealing only with a fraction of data limited by the RangeStart and RangeEnd parameters. When I publish the report online and do the first (full) refresh and PowerBI starts sending some limit 1000 queries, some kind of preview. I can see the queries on the Databricks side and they look like this

Is there a way to disable that?

I have disabled that bacjground preview load but that didn't help

Since I'm querying from a complex views on databricks those limit 1000 previews are very costly and incremental refresh doesn't make almost any sence.

How to disable that? So PowerBI sends only the very nessesary queries (pulling all historical data first and then only the incrementals?)

[azworker]

Hi,

Did you find a solution to this? Facing the same problem.

[antfarol]

Unfortunately not. I had a ticket with MS Support and they just suggested to replace my views with the materialised tables. At the end that is what we did for the most haviest cases. Which is not a very ideal solution in our situation.

If it turns out there is an option to get rid of those previews, and you will be lucky to find it, please ping me here.

Cheers!

[skygold16]

By the look of this issue, it looks like you have used native query while creating a connection with databricks. You need to check if you are filtering only 1000 rows when connecting. check you native query view. Or check your databricks query in the advance editor of Power Query. 

[antfarol]

Hi! I don't have anything in the native query

 

[Anonymous]

Hi, @antfarol 

In PowerBI, you can change the Preview All Rows option to Based on All Rows and refresh. For details, please refer to the post:

Solved: Limit 1000 values - Microsoft Fabric Community

Or try to set visual query limits in PowerBI Desktop, in option->current file->report setting, you can refer to the following documentation:

Set visual query limits in Power BI Desktop - Power BI | Microsoft Learn

It's also about making sure that the views in Databricks are as optimized as possible. This includes indexing, partitioning, and simplifying views where feasible. While this doesn't disable preview queries, it can help reduce their performance impact.

Use materialized views in Databricks SQL - Azure Databricks - Databricks SQL | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[antfarol]

Sorry, the post you linked is a completely different topic (though also has limit 1000 in the title 🙂 ). And the screenshot and article refers to filtering inside the report. While I need to disable some unnecessary queries from outside. And materialized views are something I'd like to avoid.

Anyway, thanks for your time.