Dataverse Synapse Link Choice Column Names

[joeparkinson]

Hey,

I've integrated dataverse into fabric using Synapse link to automatically bring in all the tables into Fabric. However, the choice columns in the dataverse only return the integer ID of the choice. Is there anyway to bring in the name? If I query using powerquery, the names also return. 

I wouldn't want to match all the codes up to the various choice tables as it would be a huge amount of work, but the choice tables dont come over with the tables either anyway, so currently there is no way to make this data usable. Hoping there is some switch I can turn on to bring this over?

[mscottsewell]

SELECT  [Base].accountid customerid
      , 1 AS [CustomerEntityType]
      , [Base].name [Customer Name]
      , [Base].parentaccountidname [Parent Account]
      , [Base].address1_city [City]
      , [Base].address1_stateorprovince [State or Province]
      , [Base].address1_country [Country]
      , [Base].ownerid ownerid
      , [Base].owneridname [Customer Owner]
      , ISNULL(account_industrycode.value,'N/A') [Industry]
      , [T].name AS [Territory]
      , [T].[parentterritoryidname] [Parent Territory]
      , ISNULL([T].[manageridname], 'No Territory Manager') [Territory Manager]
      , [T].territoryid
      , [Base].accountnumber AS [Customer ID]
      , account_businesstypecode.value [Business Type]
    FROM [account]              AS Base
    LEFT OUTER JOIN [territory] AS T
        ON  T.territoryid = Base.territoryid
    LEFT JOIN [stringmap] AS account_industrycode
        ON  account_industrycode.langid = 1033
        AND account_industrycode.objecttypecode = 'account'
        AND account_industrycode.attributename = 'industrycode'
        AND account_industrycode.attributevalue = [Base].industrycode
    LEFT JOIN [stringmap] AS account_businesstypecode
        ON  account_businesstypecode.langid = 1033
        AND account_businesstypecode.objecttypecode = 'account'
        AND account_businesstypecode.attributename = 'businesstypecode'
        AND account_businesstypecode.attributevalue = [Base].businesstypecode
    WHERE
        [Base].IsDelete IS NULL

This is the pattern I use - I only join on the values that I need at runtime since those labels can change independently of the record or I might want a different language represented in the report.

[joeparkinson]

Thanks - whats frustrating about this though, is I can pull all the tables in powerquery with their string straight into fabric. If I use the synapse link, I need to write SQL queries for each table and column (we have 100s of tables), so its going to be a huge amount of work vs using a dataflow.

[mscottsewell]

There is an approach to script the creation of views using this Script to Quickly Create Dataverse Views in Azure Synapse - with the Metadata Labels! | LinkedIn - 
The fabric shortcuts will be a significantly more performant and near-realtime than anything created with dataflows. - but if your actual datasize is small enough that you can manage it with dataflows, use whatever works for you.

[v-cboorla-msft]

Hi @joeparkinson 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @joeparkinson 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

[v-cboorla-msft]

Hi @joeparkinson 

Apologies for the inconvenience that you are facing here.

I would request you to open up a support ticket to get support from our engineering team for your query.

Please go ahead and raise a support ticket to reach our support team: support-ticket 

Please provide the ticket number here as we can keep an eye on it.

[mscottsewell]

Also - I wrote up an example for getting the label values for multi-select fields.
Unpacking a Dataverse Multi-Select field in Fabric. | LinkedIn

[tonyvuolo]

I wrote a table-valued function that translates an integer option set lookup value into its display value.  I am curious if there is an easier way to get at this without joining to the OptionSet table for each option.  

[joeparkinson]

Ah, i missed the optionset table! At least its possible, but also quite frustrating given the 1000s of columns we'd need to match back. Out of curiousity, what was the function you wrote?

[v-cboorla-msft]

Hi @joeparkinson 

Thanks for using Microsoft Fabric Community.

At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them.

Appreciate your patience.

Thanks

[joeparkinson]

Thank you, appreciate the support on this