Custom Connector: notification when power bi desktop interrupts data load

[kdevroede]

Hi

I'm developing a custom connector for a data source that can be accessed through a SOAP interface.

This interface requires a session token to be retrieved first as part of the authentication and used in subsequent data or metadata requests.

I've managed to get the session handling working (chosen to opt for a session per dataset) that results in a session being created and for a specific query being executed and closed down again when the data is received (or if the request failed after x retries)

The interface requires pagination so the session closure logic takes this into account as well.

I've also implemented basic query folding (inspired by the Trippin tutorial from Microsoft) with an OnTake handler because I have noticed that otherwise Power BI desktop seems to interrupt the data load for previews and the session is not closed properly.

For example imagine that I would launch a query that returns 2000 records, the pagination and SOAP interface allows to detect when all data has been received and then close the session properly. But in case of previews, Power BI desktop only needs 1000 records, I assumed that all data would be fetched anyway and then filtered at PBI side but this does not seem to be the case (fiddler tracing) and so the data load is somehow "interrupted" and therefore the session remains open.

With the query folding in place, this situation is tackled nicely as well.

However, there are other scenarios where Power BI desktop interrupts the data load:

• If the user interrupts the "evaluating" or "data loading" whilst doing a refresh in the power BI mode
• When the user is in power query edit mode and jumps around between queries, steps in queries,...

Obviously sessions on server side will time out after a while but we would like to avoid stale sessions as much as possible and only reserve these for exceptional cases (network loss, sudden kill of power bi desktop,..).

Is there a mechanism through which Power BI actually can notify the connector that the query has been aborted/terminated so that I can implement some logic to properly close the session in such cases?

Tx for help

[V-lianl-msft]

You could refer to this:

https://docs.microsoft.com/en-us/power-query/handlingerrors 

[kdevroede]

Thank you for your suggestion but it seems this does not allow me to solve my problem or at least not in the most basic problem scenario where a user cancels the query execution through the UI in PBI.

I will try to explain with some pseudo code and some very strong simplifications to bypass all the other connector complexities

Let's assume that my connector has a shared function called GetData which takes a simple number as argment and would, based on that argument, decide which data to request to the source.

Let's also make abstraction from pagination etc. so we assume we perform one call and wait for the data to be returned as an xml payload.

The GetData function would then do the following things:

1. Get the current credentials from the Extension (assume username/password)
2. Authenticate against the source application and get a session id back through SOAP web protocol
3. Request the data, passing along the session ID, again through SOAP protocol
4. Parse the returned XML so we can turn it into a dataset
5. Close the session

Steps 3,4,5 are embedded in a collection of functions and the data fetching is done with List.Generate with conditions to validate that data is returned and that session closure can be triggered.

Now, in order to capture that somehow actually decides to cancel the query execution, I added a try block around steps 3->5 and when I get such an cancellation error, I wanted to close the session.

But accessing the session ID triggers an error "Objec Reference not set to an instance of an object".

So pressing the cancellation triggers an "Evaluation ended with a terminated container" exception.

And it seems that as a result of terminating the container, the entire context in which the query was executing is lost and so beyond reach for a proper winding down.

I am wondering how this tackled with an odbc based connector (not that I have the option to convert the custom connector to an odbc based solution) for example as odbc also provides SQLCancelHandle but of course that would imply that there is a hook/call exposed in Power BI that gives us the ability to invoke such logic.

Maybe I am doing something wrong. All help, feedback is appreciated.

Koen