MSDyn365 Internals: T-SQL Endpoint

Disclaimer: the details in this post are based on the best guesses I’ve been able to make about how this works, which are derived from the behaviour I’ve seen while using it. I don’t have any official information on any of this, so use at your own risk!

As you might have noticed from my earlier posts, and my D365UG session on the topic, I’ve been a bit excited recently about the preview of the T-SQL endpoint in CDS Dataflex Pro CDS.

I’ve done my best to poke and prod at it, and I think I’ve got a pretty good idea of how it works. I’ve outlined this below, as the more I understand how something works internally, the better I can decide how to use it. If you work the same way, great!

Overview

This is my understanding of the overall process of executing a SQL query.

1. The user authenticates against the endpoint and executes the query
2. The SQL endpoint connects to Dataflex Pro, passing through the OAuth token provided by the user for authentication. It then executes an ExecutePowerBISql request, supplying the SQL text from the user
3. Dataflex Pro processes the request and passes it to the database to execute
4. The database returns the result of the query to Dataflex Pro
5. Dataflex Pro stores the result in the ExecutePowerBISql response and passes it back to the SQL endpoint
6. The SQL endpoint unpacks the data from the response and passes it back to the client

While the SQL endpoint itself is fascinating, I’m particularly interested in what’s going on inside Dataflex Pro. How does it process the ExecutePowerBISql request? How is the query ultimately executed by SQL Server?

By understanding those two systems we’ll be better able to craft performant queries and anticipate and resolve errors.

SQL Database and Filtered Views

Starting from the bottom layer, my understanding is that the query is ultimately executed against filtered views. These may be familiar to those with memories of writing reports for on-premise deployments, but a quick history lesson for those more fortunate:

In on-premise Dynamics 365, you are able to write SSRS reports that can execute directly against the SQL database, rather than going via FetchXML. Dynamics 365 automatically maintains a filtered view, e.g. FilteredAccount, FilteredContact etc., for each entity type. These views provide an abstraction over the top of the raw tables:

• row- and field-level security is automatically applied based on the current user
• optionset attributes have both the underlying integer value and the corresponding label available as separate fields
• date attributes have the value available in both UTC and the user-local timezone
• lookup attributes have the GUID and display name of the related record available

If you have access to an on-premise deployment, you can look at the definition for these views and see just how much work SQL Server is doing to provide all this value. In our test instance for example, the view definition for FilteredAccount stretches over 1,938 lines!

Query Rewriting

So, if the SQL Database has a view called FilteredAccount, how can I write the query SELECT * FROM account and expect it to work? In fact, the on-premise database (and presumably the online version too) has another view called account that does not have all the extra functionality I’ve outlined above.

I therefore believe the Dataflex Pro step of executing the ExecutePowerBISql request does some amount of rewriting of the query, so what gets executed by SQL Server isn’t exactly what you wrote. You can see this in some edge cases where the process fails in some unexpected ways.

This step will also likely do some sanitisation of the query to ensure you aren’t trying to do anything naughty. This is currently a read-only system, so it may well be this step that ensures you aren’t writing an INSERT statement for example.

So how does that query rewriting look, and why is it important? Take a look at some examples:

Original	Rewritten
SELECT * FROM account	SELECT * FROM FilteredAccount
SELECT name, fullname FROM account INNER JOIN contact ON accountid = parentcustomerid	SELECT name, fullname FROM FilteredAccount INNER JOIN FilteredContact ON accountid = parentcustomerid
SELECT account.name, contact.fullname FROM account INNER JOIN contact ON account.accountid = contact.parentcustomerid	SELECT account.name, contact.fullname FROM FilteredAccount INNER JOIN FilteredContact ON account.accountid = contact.parentcustomerid

Looks simple enough, but notice the last query. The original query was fine, but after rewriting the name account and contact are now invalid and the query results in an error. Hopefully in future the rewriting will take care of this and automatically apply an alias to match the original name. In the meantime though, you need to do this manually:

SELECT account.name,
       contact.fullname
FROM   account AS account
       INNER JOIN
       contact AS contact
       ON account.accountid = contact.parentcustomerid;

You can also see how the rewriting works by running the query:

SELECT FilteredAccount.name,
       FilteredContact.fullname
FROM   account
       INNER JOIN
       contact
       ON FilteredAccount.accountid = FilteredContact.parentcustomerid;

This query works correctly, indicating that the account and contact table names have been changed to FilteredAccount and FilteredContact respectively. However, the sanitisation step prevents you from running the final query directly:

SELECT FilteredAccount.name,
       FilteredContact.fullname
FROM   FilterdAccount
       INNER JOIN
       FilteredContact
       ON FilteredAccount.accountid = FilteredContact.parentcustomerid;

This generates the error Invalid table name FilteredAccount. This appears to be done to ensure you can only access tables that correspond to standard Dataflex Pro entities, and not any other tables that are maintained in the database for internal use and do not have the same automatic security restrictions in place as the filtered views.

ExecutePowerBISql Request

As for all other Dataflex Pro functionality, the SQL execution is exposed as a pair of request and response messages. The SQL endpoint appears to be acting as a proxy between the TDS protocol, used by SQL Server, and the Dataflex Pro SDK to execute this message.

However, there is nothing to prevent any other SDK client, including your own code, from executing this request. Take the following code for example:

var request = new OrganizationRequest("ExecutePowerBISql")
{
  Parameters = new ParameterCollection
  {
    ["QueryText"] = "select name from account"
  }
}

var response = svc.Execute(request);
var dataset = (DataSet) response.Results["Records"];

From this point the data is available in a standard ADO.NET DataSet object, which your code can display in a grid, iterate over etc.

For this to work, the TDS Endpoint must still be enabled or you will receive an error. It appears that this feature switch is applied at the Dataflex Pro level rather than within the SQL Endpoint proxy software itself.