How to bring through text readable fields (for lookup/choice fields in dataverse) when copying data using the azure synapse link or azure data factory

Question

I have two situations where I copy data from Dataverse into other storage locations:

1. using azure synapse link to copy data from dataverse into azure data lake storage
2. using azure data factory copy activity to copy data from dataverse into azure sql database

My data contains a number of lookup fields and choice fields, both of which do not copy with the readable text value. For records with lookup fields, only the GUID is brought through and for records with choice fields, only the numerical value is brought through.

Does anyone know of a way to bring through the text values?

Answer 1

Either you use a lookup activity to retrieve the text labels for the lookup and choice fields from Dataverse and for that you can use a derived column transformation to add the text labels to the output data.

Or you can use the OptionsetMetadata table. When you create an Azure Synapse Link to Dataverse, five tables are created in an OptionsetMetadata folder in Azure Data Lake Storage Gen2. One of these tables, OptionsetMetadata, stores the local choices label metadata for the imported Dataverse tables.

Quick steps for the 2nd alternative :

• Create a new derived column transformation in your Azure Synapse Pipeline or Azure Data Factory pipeline.
• Add a new column for each lookup and choice field.
• In the expression builder, write an expression to retrieve the text label for the lookup or choice field from the OptionsetMetadata table.
• Set the output dataset to the dataset that will contain the lookup and choice field text labels.
• Run the pipeline. You may use kind of expression :

SELECT OptionLabel
FROM [OptionsetMetadata]
WHERE EntityName = '<entity_name>'
AND AttributeName = '<lookup_field_name>'
AND Option = [lookup_field_value]

Or an expression that you can use to retrieve the text label for a choice field from the OptionsetMetadata table:

SELECT OptionLabel
FROM [OptionsetMetadata]
WHERE EntityName = '<entity_name>'
AND AttributeName = '<choice_field_name>'
AND Option = [choice_field_value]