Duplicate Requests Issue in Power BI Custom Connector Development

[Anonymous]

Dear Power BI Development Team,

I am currently developing a custom Power BI connector and have encountered an issue with duplicate requests during data retrieval.

I have thoroughly studied the official documentation (Paging, Schema) as well as the corresponding GitHub samples (Paging Example, Schema Example). Despite following the recommended practices for pagination, as outlined in these examples, I continue to experience duplicate requests.

Even when running the provided “Schema” example (Example 6) from GitHub and disabling preview in Power BI, the queries are still executed twice. If I do not disable preview, the same queries are executed both for preview and for the final load.

This behavior is problematic for large datasets, as it results in unnecessary duplicate loads, significantly increasing processing time. My questions are:

• Is there a recommended solution to prevent duplicate requests?
• Is it possible to implement caching or to differentiate between preview and load requests to reduce the amount of data retrieved during preview?

Any guidance or recommendations you could provide would be greatly appreciated, as this issue is critical for optimizing the performance of our connector.

Thank you for your time and support.

[cpwebb]

Can you test it in the Service too please? As I said, that will help determine what the problem is in Desktop.

[Anonymous]

Locally, I can test this in Power BI Desktop using Fiddler to track the requests. You can also test it the same way using the example I shared with you earlier.

Additionally, I’ll try to monitor the requests from the Power BI Service in Azure Application Insights to gather more data on how it behaves there.

[cpwebb]

I mean publishing the semantic model to the Power BI Service and then refreshing it - using the custom connector - from there.

[Anonymous]

Well, I’m not entirely sure about Power BI Service. It seems like the same issue exists there, but I can’t say with 100% certainty. However, this issue is definitely present in Power BI Desktop.

[cpwebb]

But does it also occur in the Service? Knowing if it does or doesn't will also help to determine the cause, and therefore help finding a solution for Desktop.

[Anonymous]

When you mention using Power BI Service, do you mean creating a report in Power BI Desktop, publishing it, and then manipulating it in Power BI Service? Or are you referring to directly using the custom Power BI connector within Power BI Service itself?

[cpwebb]

The important question here is this: do you get duplicate requests in both the Power BI Service and in Power BI Desktop, or only in Desktop? If it's the latter then it's less of a problem because the Power BI Service is where good performance really matters.

[Anonymous]

The issue occurs in Power BI Desktop, but it is still significant for our customers because they use Desktop to build reports before publishing them to the Power BI Service. In Desktop, re-requesting large datasets causes performance issues, slowing down the process and leading to frustration for customers. Additionally, generating this data involves running SQL queries in Azure, which incurs unnecessary costs when duplicate requests are made. Although performance in the Power BI Service is critical, addressing this issue in Desktop is also important for improving customer satisfaction and reducing operational costs.

[lbendlin]

Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice?

Quote:"The solution is the same as the blog post I just mentioned too: use the Table.View M function to hard-code the schema returned by the query and implement query folding manually."

[Anonymous]

Yes, that's exactly what Microsoft's example does. But when I run that example, I still have the same problem I described above.

https://learn.microsoft.com/en-us/power-query/samples/trippin/6-schema/readme

[lbendlin]

@cwebb would you have an opinion on this?