Share
Report
5I am using Dataverse connector to ingest CRM tables into SQL MI .
I noticed the "Optionset/Choice" and "Lookup" values were not retrievable via the "Source" query.
My workaround is as follows.
1) To get the Optionset values (eg. statecodename in the Account entity) I download the "Stringmap" entity and use T-SQL to join to the entity and retrieve the value.
2) To get the Lookup values (eg. createdbyname in the Account entity) I also use T-SQL and perform a left join to the appropriate lookup table (Systemuser, etc). I was able to find the lookup definitions through this URL: https://xxx.crm6.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='account')/Attributes and searching for the "LookupAttributeMetadata" tags.
My questions:
A) Is there an option in FetchXML that will automatically retrieve the lookup values (#2 above) to avoid having to perform SQL joins?
B) Would it be faster to perform the joins for #2 from inside Synapse? I am not sure how to do this. Our Account table has over 6 million rows and the existing T-SQL "Update" script takes over 15 minutes.
All responses (
Answers (
