Synapse Link for Dataverse – Metadata
By Joe Gill
Published On 5th July 2022
I previously wrote a couple of blog posts (post1 and post2) suggesting two different ways to access your Dataverse metadata in Synapse. Both of these techniques are now redundant as Microsoft announced in May 2022 that the Dataverse metadata is now available directly in Synapse. Your Synapse serverless pool now contains additional external tables with the metadata. Your queries can join to these tables to get the text values for choice fields.
Dataverse Choice fields are columns with a fixed list of options consisting of an integer value and text describing what the option value represents.
Only the integer value of a choice is stored on the record in the SQL database behind Dataverse and this is what is exported to Synapse.
If you want to return the text value of choice fields when you query Synapse then you can get this from the new metadata tables. There are five new external tables containing the Dataverse metadata. The text values for global and local choice fields can be found in the GlobalOptionsetMetadata andOptionsetMetadata tables. You need to join to the correct table depending on you type of choice field you are querying. There are also metadata tables for the state and status choice fields.
Here is a sample query that joins to the GlobalOptionsetMetadata table to get the text value of a choice field on the account table.
As per my previous blog post if you have lots of queries or metadata joins then you are better off creating views that you can reuse. You cannot create views in the build-in default serverless pool so you will need to create another serverless pool and create a cross database view in that serverless pool.
If you want to create metadata views it is worth looking at a script written by Slava Trofimov that generates the scripts to create views. The script is available on github and Scott Sewell has written a post on how to use it. This will save you lots of work. The scripts generated contain joins for all your choice fields and you should remove any joins not required to improve performance. Especially for the oob entities like account and contact which as you can see have tons of joins
