• Home
  • Blog
  • About Me
  • Home
  • Blog
  • About Me
Power Platform  ·  SQL  ·  Synapse Link for Dataverse

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.

Synapse Link for Dataverse - Metadata tables

Dataverse Choice fields are columns with a fixed list of options consisting of an integer value and text describing what the option value represents.

Dataverse choice field

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.

Synapse Link for Dataverse - SQL join to metadata

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.

Synapse Link for Dataverse - serverless pool view

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

Script to create view for Synapse Link for Dataverse


extract table from pdf and write as csv file using Power Automate Desktop
Extract Tables from a PDF using Power Automate Desktop
Previous Article
Power Fx Formula Columns in Dataverse
Next Article