• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Synapse Link for Dataverse – Metadata

5th July 2022 by Joe Gill

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 - Joe Gill

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

Synapse Link for Dataverse - Metadata - Joe Gill

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.

Synapse Link for Dataverse - Metadata - Joe Gill

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 - Metadata - Joe Gill

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 - Metadata - Joe Gill

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

Synapse Link for Dataverse - Metadata - Joe Gill

Share This On Social:
  • Tweet
  • Synapse Link for Dataverse - Metadata - Joe Gill

Filed Under: Azure, Dataverse, Power Platform, SQL

Primary Sidebar

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

Synapse Link for Dataverse Security

16th December 2022 By Joe Gill

Power Fx -Power Virtual Agents

28th November 2022 By Joe Gill

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

Synapse – SQL Admin

Synapse Link for Dataverse Security

Power Fx -Power Virtual Agents

Synapse Link for Dataverse – Append-Only

© 2023 ยท Joe Gill