Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

pws_flick — Fri, 31 Jan 2025 02:48:39 GMT

Hi All,

I have created a Dataflow Gen2 which is picking up data from the 'account' entity in Dynamics 365.

The M-Code for this query is as follows:

let Source = CommonDataService.Database( "xxxxxxxxx.crm6.dynamics.com", [CreateNavigationProperties = null] ), // Grab the "account" table #"Navigation 1" = Source{[Schema = "dbo", Item = "account"]}[Data], // Keep desired columns #"Selected Columns" = Table.SelectColumns( #"Navigation 1", { "accountid", "primarycontactid", "primarycontactidname", "name", "customertypecode", "description", "emailaddress1", "emailaddress2", "emailaddress3", "telephone1", "telephone2", "telephone3", "createdon", "createdby", "createdbyname", "modifiedon", "modifiedby", "modifiedbyname", "ownerid", "owneridname", "parentaccountid", "parentaccountidname", "address1_addresstypecode", "address1_name", "address1_primarycontactname", "address1_composite", "address1_line1", "address1_line2", "address1_line3", "address1_city", "address1_stateorprovince", "address1_postalcode", "address1_country", "address1_latitude", "address1_longitude", "industrycode", "bcbi_companyid", "bcbi_companyidname", "businesstypecode", "defaultpricelevelid", "defaultpricelevelidname", "exchangerate", "fax", "masteraccountidname", "masterid", "msa_managingpartnerid", "msa_managingpartneridname", "msa_managingpartneridyominame", "msdyn_billingaccount", "msdyn_billingaccountname", "msdyn_primarytimezone", "msdyn_salestaxcodename", "msdyn_serviceterritory", "msdyn_serviceterritoryname", "msdyn_workorderinstructions", "originatingleadid", "originatingleadidname", "paymenttermscode", "statecode", "statuscode", "territoryid", "territoryidname", "transactioncurrencyidname", "utcconversiontimezonecode", "vel_abn", "vel_accountmanagercrm", "vel_accountmanagercrmname", "vel_accountmanagercrmyominame", "vel_accountmanagersales", "vel_accountmanagersalesname", "vel_accountmanagersalesyominame", "vel_acn", "vel_acquisitioncode", "vel_allocatedportalusers", "vel_allocatedportalusers_date", "vel_allocatedportalusers_state", "vel_autonumber", "vel_bpaycrnfol", "vel_businesskey", "vel_certificateofcompletionexemption", "vel_chemicalregister", "vel_childaccountallocatedportalusers", "vel_consolidatedinvoice", "vel_customerdivision", "vel_customerponumber", "vel_default_paymentmethod", "vel_default_paymentmethodname", "vel_division", "vel_edicust", "vel_errordetails", "vel_folbillto", "vel_folbilltonumber", "vel_follocation", "vel_follocationnumber", "vel_foreign", "vel_lastservicedate", "vel_mastergrp", "vel_mastergrpname", "vel_oldcustomernumber", "vel_oldpropertynumber", "vel_paymentgateway_customerid", "vel_paymenttype", "vel_picategory", "vel_picategoryname", "vel_portalinvitationresult", "vel_portalinvitationsenton", "vel_portalinvitationstatus", "vel_portallink", "vel_registered", "vel_remainingportallicenseallowance", "vel_reprtgrp", "vel_reprtgrpname", "vel_showstripesyncbutton", "vel_specialtermsandconditions", "vel_spnumber", "vel_totalauthorizedportallicenses" } ) in #"Selected Columns"

I have setup incremental refresh as follows:

The data is being written to a table (dbo.account) in my Fabric Data Warehouse with the same columns as the query.

column_name	data_type
accountid	uniqueidentifier
primarycontactid	uniqueidentifier
primarycontactidname	varchar
name	varchar
customertypecode	int
description	varchar
emailaddress1	varchar
emailaddress2	varchar
emailaddress3	varchar
telephone1	varchar
telephone2	varchar
telephone3	varchar
createdon	datetime2
createdby	uniqueidentifier
createdbyname	varchar
modifiedon	datetime2
modifiedby	uniqueidentifier
modifiedbyname	varchar
ownerid	uniqueidentifier
owneridname	varchar
parentaccountid	uniqueidentifier
parentaccountidname	varchar
address1_addresstypecode	int
address1_name	varchar
address1_primarycontactname	varchar
address1_composite	varchar
address1_line1	varchar
address1_line2	varchar
address1_line3	varchar
address1_city	varchar
address1_stateorprovince	varchar
address1_postalcode	varchar
address1_country	varchar
address1_latitude	float
address1_longitude	float
industrycode	int
bcbi_companyid	uniqueidentifier
bcbi_companyidname	varchar
businesstypecode	int
defaultpricelevelid	uniqueidentifier
defaultpricelevelidname	varchar
exchangerate	decimal
fax	varchar
masteraccountidname	varchar
masterid	uniqueidentifier
msa_managingpartnerid	uniqueidentifier
msa_managingpartneridname	varchar
msa_managingpartneridyominame	varchar
msdyn_billingaccount	uniqueidentifier
msdyn_billingaccountname	varchar
msdyn_primarytimezone	int
msdyn_salestaxcodename	varchar
msdyn_serviceterritory	uniqueidentifier
msdyn_serviceterritoryname	varchar
msdyn_workorderinstructions	varchar
originatingleadid	uniqueidentifier
originatingleadidname	varchar
paymenttermscode	int
statecode	int
statuscode	int
territoryid	uniqueidentifier
territoryidname	varchar
transactioncurrencyidname	varchar
utcconversiontimezonecode	int
vel_abn	varchar
vel_accountmanagercrm	uniqueidentifier
vel_accountmanagercrmname	varchar
vel_accountmanagercrmyominame	varchar
vel_accountmanagersales	uniqueidentifier
vel_accountmanagersalesname	varchar
vel_accountmanagersalesyominame	varchar
vel_acn	varchar
vel_acquisitioncode	varchar
vel_allocatedportalusers	int
vel_allocatedportalusers_date	datetime2
vel_allocatedportalusers_state	int
vel_autonumber	varchar
vel_bpaycrnfol	varchar
vel_businesskey	varchar
vel_certificateofcompletionexemption	bit
vel_chemicalregister	bit
vel_childaccountallocatedportalusers	int
vel_consolidatedinvoice	bit
vel_customerdivision	int
vel_customerponumber	varchar
vel_default_paymentmethod	uniqueidentifier
vel_default_paymentmethodname	varchar
vel_division	int
vel_edicust	int
vel_errordetails	varchar
vel_folbillto	varchar
vel_folbilltonumber	varchar
vel_follocation	varchar
vel_follocationnumber	varchar
vel_foreign	bit
vel_lastservicedate	datetime2
vel_mastergrp	uniqueidentifier
vel_mastergrpname	varchar
vel_oldcustomernumber	varchar
vel_oldpropertynumber	varchar
vel_paymentgateway_customerid	varchar
vel_paymenttype	int
vel_picategory	uniqueidentifier
vel_picategoryname	varchar
vel_portalinvitationresult	varchar
vel_portalinvitationsenton	datetime2
vel_portalinvitationstatus	int
vel_portallink	varchar
vel_registered	bit
vel_remainingportallicenseallowance	decimal
vel_reprtgrp	uniqueidentifier
vel_reprtgrpname	varchar
vel_showstripesyncbutton	bit
vel_specialtermsandconditions	varchar
vel_spnumber	varchar
vel_totalauthorizedportallicenses	int

However, whenever I try to publish this dataflow - it will generate this error when it tries to refresh (fails on the account_WriteToDataDestination step):

account_WriteToDataDestination: There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'Data Source Type' of the table wasn't found. Details: Reason = Expression.Error;ErrorCode = 10224;Detail = Data Source Type;Microsoft.Data.Mashup.Error.Context = User'. Error code: Mashup Exception Expression Error. (Request ID: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx).

This seems to indicate that it's looking for a column named 'data source type' ??? I'm thoroughly confused. There is no column named this in the source table or the destination table...

I have also tried setting the CreateNavigationProperties to 'true' / 'false' - but this gives the same error.

Any help / guidance is greatly appreciated!

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

lbendlin — Fri, 31 Jan 2025 12:54:19 GMT

Out of curiosity - why are you enabling incremental refresh on a dimension table?

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

pws_flick — Fri, 31 Jan 2025 23:50:04 GMT

In this business, new accounts are added on almost daily basis - thus, I would only like to ingest new and updated accounts each day, rather than the entire 2.3 million+ each day

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

lbendlin — Sat, 01 Feb 2025 01:18:35 GMT

thus, I would only like to ingest new and updated accounts each day

Thank you for the background information, that helps.

thus, I would only like to ingest new and updated accounts each day

Oh, you're doomed. It's called Incremental refresh for a reason. Data is expected to be immutable. If it isn't (like your "updated accounts" ) then Incremental refresh will not work for you, and you will end up with multiple versions of the same account across multiple partitions. Instead you would need to implement your own differential refresh process, CDC or SCD2.

topic Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table in Dataflow

Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table

Re: Dataflow Gen2 refresh issue - Dynamics 365 entity to warehouse table