Edit

Access Dataverse choices (option sets) with serverless SQL pool

  • Article

For columns that use Microsoft Dataverse Choices, choice values are written as an integer label and not a text label to maintain consistency during edits. The integer-to-text label mapping is stored in the Microsoft.Athena.TrickleFeedService/table-EntityMetadata.json file. This article covers how to access the integer-to-text label mapping using serverless SQL pool.

Access option set.

Note

Azure Synapse Link for Microsoft Dataverse was formerly known as Export to data lake. The service was renamed effective May 2021 and will continue to export data to Azure Data Lake as well as Azure Synapse Analytics.

Prerequisites

This section describes the prerequisites necessary to consume Dataverse data with serverless SQL pool after using the Azure Synapse Link for Dataverse service.

  • Azure Synapse Link for Dataverse: This guide assumes that you have already exported data from Dataverse by using the Azure Synapse Link for Dataverse with and Azure Synapse Analytics workspace.

  • Storage Account Access. You must be granted one of the following roles for the storage account: Storage Blob Data Reader, Storage Blob Data Contributor, or Storage Blob Data Owner.

Consuming Dataverse choices with serverless SQL pool

  1. Navigate to your Azure Synapse Analytics workspace.

  2. Select Develop from the left side panel, then select + > SQL script.

  3. Paste the following SQL query and replace <STORAGE_ACCOUNT> with the storage account name and <CONTAINER_NAME> with the name of the container.

    SELECT [EntityName], [OptionSetName], [Option], [IsUserLocalizedLabel], [LocalizedLabelLanguageCode], [LocalizedLabel]
    FROM OPENROWSET (
        BULK 'https://<STORAGE_ACCOUNT>.dfs.core.windows.net/<CONTAINER_NAME>/Microsoft.Athena.TrickleFeedService/*-EntityMetadata.json', 
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc nvarchar(max)) AS rows
    CROSS APPLY OPENJSON(doc, '$.OptionSetMetadata')
    WITH (
            [EntityName] nvarchar(4000) '$.EntityName',
            [OptionSetName] nvarchar(4000) '$.OptionSetName',
            [Option] int '$.Option',
            [IsUserLocalizedLabel] nvarchar(4000) '$.IsUserLocalizedLabel',
            [LocalizedLabelLanguageCode] int '$.LocalizedLabelLanguageCode',
            [LocalizedLabel] nvarchar(4000) '$.LocalizedLabel'
    )

  1. Run the query. A table containing the Dataverse choices is displayed.

  2. Join the Dataverse choices with your Dataverse table and store the view in a new database using a three-part naming convention - [database-name].[schema-name].[table-name].

See also

Azure Synapse Link for Dataverse

Quickstart: Use serverless SQL pool