If you’ve tried reporting on your Dataverse / Dynamics 365 data using Power BI, you’ll probably have noticed you don’t get the labels associated with any optionset (now “Choice”) fields in your data. There are ways around this such as using the PowerBI OptionSet Assistant solution. In this post I’ll Continue Reading
TDS Endpoint Time Zone Change
A change has been rolling out recently to the Dataverse TDS Endpoint that affects how date/time values are returned, which should give your queries better performance but may mean you’ve got to do a bit more work to ensure the values are shown as your users expect.
Efficient Bulk Dataverse Updates with SQL
If you need to make bulk updates to your Dataverse or Dynamics 365 data, maybe to fix data quality problems or restructure your data, you may be exporting your data to Excel, making your changes and re-importing it. Or you may be considering setting up a more complex data pipeline Continue Reading
Using Dates from TDS Endpoint in Power BI Charts
As part of migrating some dashboards to Power BI using the TDS Endpoint I hit a problem getting a chart to show correctly. I needed to show invoices with the values grouped by month. This is simple in a standard Dynamics chart, and when importing the data into Power BI. Continue Reading
TDS Endpoint Queries with Time Zone and User details
A common issue I used to encounter with SQL-based reports for on-premise Dynamics CRM instances was correctly & efficiently filtering on dates. In the underlying SQL tables, Dataverse stores dates & times in UTC time zone, and this is how they’re indexed. In the views that the TDS Endpoint exposes Continue Reading
CDS T-SQL Endpoint Vulnerability
I received a notification today that the preview TDS endpoint had been disabled on my environment because of a security vulnerability.
Multi-select Picklist Filtering
I’ve been doing some work today on filtering multi-select optionset fields in SQL 4 CDS and using the T-SQL endpoint, and I came across a few interesting quirks.
CDS T-SQL Performance
I wrote a post a while ago looking at the performance of the T-SQL endpoint compared to using FetchXML. Now I’ve got a better understanding of how it works, I wanted to check if the same performance considerations I’ve previously encountered when working with the filtered views on-premise still apply.
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!
SQL 4 CDS 2.1.0 – the T-SQL edition
Since I’ve been doing some investigation into the new preview T-SQL endpoint for CDS recently, I’ve decided to integrate it into SQL 4 CDS. Update to 2.1.0 to get: