How to disable preview queries running at the backend in Power BI desktop?

[Mohanasudharsan]

We are developing a custom connector via Power Query(.pq) file. We are handling multiple million rows for the dataset. Even if the preview option is disabled in the Power BI navigation window and load is clicked, the APIs are called at the backend twice(one for preview and the other for visualization). This takes much time for us to process such huge data. 

Can you please suggest a way to stop the query request via the pq file for time complexity handling?

If possible, the preview should be disabled at the global level(not only for viewing but also the query running at backend) for Power BI Desktop.

[rubayatyasmin]

Hi, @Mohanasudharsan 

options >> Security >> Native DB query 

[Mohanasudharsan]

Hi @rubayatyasmin ,

It doesn't work as expected. Still, the issue is retaining.

[rubayatyasmin]

Hi, @Mohanasudharsan 

However, if the "Allow Native Database Query" option did not solve your problem, you might want to try a different approach. You could potentially use a parameter in your custom connector to control when the query is executed.

Below is an outline of a possible solution:

1. Create a parameter in your custom connector that controls whether or not to return a preview dataset or the full dataset. This parameter can be Boolean (true/false) or you could use some kind of string indicator, whatever works best for your situation.

2. In your function that executes the query, check the parameter. If the parameter is set to return a preview dataset, return a small subset of the data or some dummy data. If the parameter is set to return the full dataset, run the full query.

3. In the Power Query editor, you can then control this parameter. When you're setting up your reports and need to see the data structure, set the parameter to return the preview dataset. When you're ready to load the full data, set the parameter to return the full dataset.

This is not a perfect solution as it requires manual intervention to switch between the preview dataset and the full dataset. However, it should allow you to avoid running the full query when you're just trying to preview the data structure in the Power Query editor.

This approach will require some additional development work on your custom connector, and it may not be suitable for all use cases. But it could be a workaround to help manage performance while you're working with large datasets.

refer to this doc. See if you find anything helpful. 

https://learn.microsoft.com/en-us/power-query/multiple-queries?source=recommendations

[rubayatyasmin]

Hi, @Mohanasudharsan 

To disable preview queries running at the backend in Power BI Desktop, you can follow these steps:

1. Open Power BI Desktop and go to "Options and settings" by clicking on "File" in the menu bar and selecting "Options and settings."

2. In the Options dialog box, select "Query Options" under the "Global" section on the left-hand side.

3. In the Query Options dialog box, go to the "Preview features" tab.

4. Uncheck the "Allow Native Database Query" option. This option allows Power BI to use database-specific optimizations for previewing data. Disabling it will prevent Power BI from sending preview queries to the backend.

5. Click "OK" to save the changes and close the dialog boxes.

By disabling the "Allow Native Database Query" option, Power BI will no longer execute preview queries in the backend, which can help improve performance when dealing with large datasets.

[Mohanasudharsan]

Hi @rubayatyasmin ,

Thanks for the response. Based on your previous response, when we look for "Query Options" or "Preview Features" in Power BI Desktop's latest Version: 2.119.666.0 64-bit (July 2023), there is no option for "Allow Native Database Query". 

Can you suggest some other ideas to solve our issue?