Explore Dynamics 365 FO data entities with SQL syntax

In a previous edition of the newsletter, we discussed the Dynamics 365 FO table browser for exploring data directly from tables. However, in some scenarios, we may need to explore data from entities instead. It's important to remember that direct access to the SQL database is not possible, at least in production environments.

You can use the same browser extension to obtain a list of available data entities, filter them, and also check certain entity properties, such as whether the entity is exposed via OData or if it's available on the Data Management Framework (DMF).

By clicking on one entity, the browser opens the OData URL for that entity and displays the data in JSON format.

Alternatively, you can directly access a data entity using the following URL:

https://<environment>/data/<entityPublicName>        

The browser isn’t the best tool for working with JSON data, so we can use tools like Power BI to connect through an OData service and then, use Power Query to filter the data, apply transformations, and more.

But what if you want to create SQL queries with this data? The solution lies in Dataverse, which could expose data entities as virtual entities and then use Tabular Data Stream endpoint that emulates a SQL connection.

Moreover, thanks to community-developed tools, we can use a tool in XrmToolBox called SQL 4 CDS created by Mark Carrington to write queries that connect directly to Dataverse using SQL syntax.

This tool is also integrated into the Finance and Operations Virtual Entity Manager for XrmToolBox tool that we explored in a previous post: