TDS Endpoint Revisited

I’ve been doing some more work recently with the Dataverse TDS Endpoint, and there’s been a few updates to the service since the last time I last really dug into it.

The Documented

The documentation around the limitations of the TDS Endpoint has had a few updates over the course of this year so it’s worth having a read over those. The key points for me were:

No Result Size Limit

The previous limit of 84MB of each query result has now gone! The service now uses a streaming model so it doesn’t have to load the entire result set internally before sending it back to you.

Updated Time Limit

Queries now have 5 minutes to complete rather than the previous 2 minutes. However, the 2 minute timeout will still be applied for more complex queries if it detects your query using SELECT * or subqueries which can harm performance.

Per-User Access Control

Rather than just turning the TDS Endpoint on for everyone, you can choose to have it require a new privilege which you can manage via security roles.

And The Undocumented

I’ve previously done some investigation into how the TDS Endpoint works under the hood to understand how we can use it best, and there’s a few interesting changes there too.

⚠️ Everything from this point is undocumented, and therefore unsupported. It could change at any time, so don’t rely on it in production applications.

New Internal Message

For those that are interested in such things, the TDS Endpoint used to invoke the ExecutePowerBISql message which returned a DataSet in the response parameters. It now uses the ExecutePowerBISqlWithStreaming message which replaces this with an IPSqlTdsResponsePipe which wraps the internal TDS data stream.

The new message probably isn’t that useful unless you want to write your own wrapper around the TDS protocol, but the original message is still there as well.

Now Usable From Plugins 🎉

Calling the ExecutePowerBISql message from a plugin used to be explicitly blocked, but it looks like this restriction has now gone. You can now include code like this in your plugin:

var req = new OrganizationRequest("ExecutePowerBISql")
{
  Parameters = new ParameterCollection
  {
    ["QueryText"] = "SELECT TOP 10 name FROM account WHERE ownerid = @userId AND createdon >= @startDate",
    ["QueryParameters"] = new ParameterCollection
    {
      ["@startDate"] = DateTime.UtcNow.AddDays(-1)
    }
  }
};
var resp = svc.Execute(req);
var dataset = (DataSet)resp["Records"];

You can’t use the @@CURRENT_USER variable in queries like this, but you can use the @userId parameter which appears to be supplied by the platform for you. You can also add extra parameters via the QueryParameter collection to avoid SQL injection vulnerabilities.