Missing columns in CDM entities with Dataverse D365 Synapse Link to ADLS

Question

We have a synapse link setup to load data from Dataverse D365 into Data lake gen2. It has a table 'Contact' and it has around 780+ columns and the synapse link loads this table as Common Data Model and the dataflow reads the CDM.

There were columns missing in the target table and when checked the model.json file, it has only 740+ columns. Around 40+ columns were missing in the CDM model.json file in Storage Account loaded from Dataverse.

Could this be because of any limitation like row size limit because contact tables through synapse link loads only 740+ columns. Contact is just one example and some columns were missing in other tables as well.

Using SSIS KingswaySoft all the columns were being loaded from contact tables and in the new architecture implemented with Synapse Link, Columns were missing.

Could someone please comment on this and what the potential issues could be and the best workarounds using ADF.

Answer 1

Hi @Siva B
Welcome to Microsoft Q&A platform and thanks for posting your question here.

When exporting data from Dataverse D365 to ADLS using Synapse Link, some columns are missing in the CDM model.json file. This can be due to several factors, such as change tracking not being enabled for certain columns, entity configuration issues, or limitations within the model.json file.

One potential solution is to enable change tracking for all necessary columns. Change tracking allows the Synapse Link to track all changes made to the columns and include them in the exports. To enable change tracking, go to the entity settings in Dataverse and select the columns to track. This ensures that all changes made to the columns are included in the Synapse Link exports.

Another solution is to manually edit the model.json file to include the missing columns. The model.json file defines the schema of the CDM entities and is used by the Synapse Link to export data. By manually adding the missing columns to the model.json file, ensure that the columns are included in the exports. It's important to ensure that the columns are correctly formatted and match the source schema to avoid any errors.

Reviewing the entity configuration in Dataverse can also help identify any configuration issues that may be causing the missing columns. Check the entity settings in Dataverse to ensure that all columns are selected for export.

It's also recommended to review any known limitations with finance and operations tables that may affect the Synapse Link. By following these steps, the issue of missing columns in CDM entities when exporting data from Dataverse D365 to ADLS using Synapse Link can be resolved.

Reference

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data

https://learn.microsoft.com/en-us/azure/data-factory/format-common-data-model#import-schema

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data#known-limitations-with-finance-and-operations-tables

https://learn.microsoft.com/en-us/common-data-model/model-json#entity-level-properties

I hope this information helps you. Let me know if you have any further questions or concerns.