Export Choice field labels from DataVerse

Question

We were previously using Data Export Services to export tables from Dataverse to a Azure SQL database for reporting purposes. Since this tool has been depreciated, we are looking into Data Factory. The limitation we have come across is exporting choice fields and the related text labels over to Azure SQL database. Do you have any recommendations? If not, we will need to consider using a third party tool which we are hoping to avoid.

Answer 1

The Option Set metadata is exported when synchronizing Dataverse to a Data Lake. There is open source code available that will resolve the Option Sets / Choice integers to text.

https://github.com/cooptimize/Dataverse

Edit (Feedback received to add additional context)

When tables are synced to Dataverse, the metadata is stored in Data Lake files in the structure "{table}-Metadata.json". You can parse the json to get the choice values.

Microsoft also has CSV versions of these files now in the Data Lake and Lake Tables created in the Synapse Workspace.

The complexity is there are four types of metadata - Option Sets, Global Option Sets, Status Code, and State Code. In my opinion it's better to UNION the four types together into a single view that's used to join to the raw tables, instead of doing four different types of joins.

The Github code referenced has 1. UNIONed metadata logic and 2. T-SQL and Power Query code that combines the metadata with the raw table. It also uses the same logic as the Dataverse TDS/SQL connector - it appends "name" to the end of every choice field name ("name" suffix that matches an existing field is not allowed in Dataverse development).