Is it feasible to employ T-SQL for querying Dynamics 365 FO virtual entities and executing advanced queries?

Could T-SQL queries be employed to access Dynamics 365 FO virtual entities? Although querying Dataverse tables is known, can Dynamics 365 FO virtual entities be queried would it be possible to perform joins across different Dynamics 365 FO virtual entities, along with aggregate functions ??

Initially, I attempted to utilize the Tabular Data Stream (TDS) endpoint (https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query ). While connected to my environment, I encountered an issue where none of my 'mserp_' virtual tables were accessible through the TDS endpoint.

Upon further investigation, I discovered that 'virtual tables' are not compatible with the Tabular Data Stream (TDS) endpoint. This information is outlined in the documentation here: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query#supported-operations-and-data-types.

Subsequently, I came across a tool called SQL 4 CDS, which quickly became my preferred solution. SQL 4 CDS is a plugin for XRMToolBox developed by Mark Carrington . For details on installation, introduction please refer to resources such as https://markcarrington.dev/sql-4-cds and https://www.xrmtoolbox.com/plugins/MarkMpn.SQL4CDS/.

Discovering SQL 4 CDS opened up a whole new realm of possibilities for me, much like the excitement I experienced when I first encountered FetchXML Builder by Jonas Rapp . Despite being aware of the drawbacks of using Odata calls in situations involving numerous transactions, such as performance issues and throttling concerns, I found SQL 4 CDS to be the optimal solution. As someone with a background in SQL Server, this tool stood out to me as the most efficient option. I hope that by sharing my experience, others who may not have been aware of this fantastic tool will also benefit.

I'll be working with SalesOrderHeaderV2Entity and SalesOrderLineV2Entity, both of which are published as Dataverse virtual entities from Dynamics 365 FO.

The relationship between entities is automatically established when both are published as virtual entities. For more details on virtual entity relationships, you can refer to the documentation here: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/entity-modeling#relations .

This publishing process creates both one-to-many relationships (from the salesorder) and many-to-one relationships (from the salesline).

Upon connecting to my Dataverse environment through SQL 4 CDS, I gained access to all the Dataverse tables along with my virtual entities. Interestingly, these virtual entities, which were previously inaccessible using SSMS and the TDS endpoint, are now visible.

Using SQL 4 CDS, I can thoroughly examine my entities, review all associated fields and relationships, and even query the entity metadata of my virtual tables, extracting various information.

While writing my T-SQL query, I benefit from IntelliSense assistance, which aids in constructing joins, as depicted below.

Below is an example query I created to assist a friend. I've simplified it for readability and reduced the number of joins, but it still demonstrates the capabilities of the tool. This query incorporates various T-SQL commands such as joins between 2 virtual tables , count(), STRING_AGG, Group by, and Having. Let your imagination and your T-SQL expertise guide you in extracting any information you need.

SQL 4 CDS supports most standard SQL syntax, enabling execution of SELECT, UPDATE, INSERT, and DELETE queries. However, if the Dynamics 365 FO entity is read-only, only SELECT queries will function. Additional details can be found here: (https://markcarrington.dev/sql-4-cds/ ).

One challenge I encountered while writing the query was determining the meaning of certain values, such as determining if the enum mserp_salesorderstatus = 200000003 represents an "Invoiced" status. In such cases, I found FetchXML Builder to be an invaluable and user-friendly tool, providing a graphical interface for constructing queries (https://fetchxmlbuilder.com/features/)

?In this scenario, I'm utilizing the SalesOrderHeaderV2Entity and filtering based on mserp_salesorderstatus, as indicated in the accompanying image. By examining the enumerator's value, I can discern relevant information.

I'm able to convert the FetchXML query to a SQL query, as demonstrated below. Additionally, I have the option to open and modify the query directly in SQL 4 CDS from FetchXML Builder. Prior to proceeding with my T-SQL query in SQL 4 CDS, I can verify the data's accuracy in FetchXML Builder.

The latest addition to the XRMToolBox for Dynamics 365 FO is the FinOps Virtual Entity Manager by David Rivard (https://itmustbecode.com/finance-and-operations-virtual-entity-manager-for-xrmtoolbox/). It's encouraging to see tools being developed for Dynamics 365 FO in XRMToolBox, and I anticipate further expansion in the future.

The upcoming functionality in Dynamics 365 FO, particularly the Auto Create/Auto Refresh feature, is poised to greatly streamline the process of publishing and updating Dynamics 365 FO data entities as virtual entities in Dataverse. Despite these advancements, I still believe that this tool remains an excellent choice due to its ease of use and convenient filtering options.

For me, using the FinOps Virtual Entity Manager is much simpler than navigating through the Advanced Find page, selecting Available finance and operations virtual table and wait.., and searching for the Dynamics 365 FO data entity I want to publish or refresh.

Attempting to enable change tracking for a virtual entity that doesn't meet the prerequisites will result in the following error: to read ?more about prerequisites ?https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/rowversion-change-track#enable-row-version-change-tracking-for-data-entities