Skip to main content

Notifications

Announcements

No record found.

Supply Chain Management forum
Suggested answer

BYOD Incremental push & T-SQL deletes

Posted on by 8
We run D365 FinOp on premise with exports to BYOD to support feeds to a data warehouse and analytical reporting. All of my data entities are basically a 1:1 match with the physical SQL tables in AxDB with the entity key defined to match the primary key of the source table. I have developed about 350 entities for the key Dynamics tables needed for reporting. For several years now this has worked pretty well as 99% of these entities are exported incrementally at various frequencies throughout the day. My organization has many legal entities (companies) so rather than using the /export across all companies/ option in BYOD export, I removed the primary company context from the entities whose source table is stored by company and mapped DataAreaId to an entity field with an alternate name, CompanyId, for example. I don't want to use the export across all companies because that causes a separate query on the database for each entity export, in sequence not parallel, for every legal entity. This doesn't make much sense to me in terms of efficiency, resource usage, etc. 
 
Using the entities without company context has worked great until I recently discovered a problem. Let's consider ProdTable as an example. The source table has primary index ProdId. Since the table is not a shared table, the primary key on the SQL dB includes DataAreaId. When I enable change tracking on the corresponding entity, SQL Server is tracking changes on primary key DataAreaId and ProdId, just as we would expect. My entity has an entity key of ProdId and CompanyId (mapped to ProdTable.DataAreaId) and this is reflected in the primary key on the published table in BYOD. Incremental inserts to BYOD work fine. If I have a ProdId of ABC in both company 1 and 2, I can see both in BYOD. Now, let's say company 1 deletes ProdId ABC. When the incremental export runs, it will issue a delete statement to BYOD that is /DELETE FROM ProdTableStaging WHERE ProdId = 'ABC' /. There is no predicate on the company so it deletes both records. 
 
I found where this is happening from the EntityBase class where it is matching the columns of the primary index of the source table to the index columns of the entity staging table. It is looking specifically to match DataAreaId with exact name. The only way a column with exact name DataAreaId can exist on an entity staging table is if the entity has company context. If the changes are being tracked with DataAreaId included and DataAreaID is mapped to a field in my entity, it shouldn't matter if the entity has company context. It is still part of the entity key and, in my eyes, the condition should be included when syncing the data incrementally to keep uniqueness in tact across the process. Otherwise, you end up with data that has gone missing. What am I missing?
 
I know of only two ways to work around this, neither of which is particularly attractive. 1) Use full export with every execution 2) Set the company context field on the entity and incrementally export /across all companies/. Both have major performance implications considering the number of entities times 40 companies. 
 
This was found on 10.0.26 (yes, we are several versions behind).
 
Any suggestions?
  • RPMcCoy Profile Picture
    RPMcCoy 8 on at
    BYOD Incremental push & T-SQL deletes
    We have upgraded to 10.0.37 and the behavior is unchanged (still problematic). Still issuing deletes based on the primary index of the entity's primary table without DataAreaId in the predicate.
  • RPMcCoy Profile Picture
    RPMcCoy 8 on at
    BYOD Incremental push & T-SQL deletes
    I skipped ahead 10 versions and downloaded the service update for 10.0.36 to review the changes in the DMFEntityBase class. It looks like there is enhanced logic for deletes in BYOD synchronization that are likely to fix this very issue. We definitely need to get up to date!
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,584 Super User on at
    BYOD Incremental push & T-SQL deletes
    Hi Ryan,
     
    I would suggest to testing it in a current version to see if it is working or not. Anyway, if it is a bug, Microsoft is not supporting 10.0.26 anymore. You will need to stay current.
  • RPMcCoy Profile Picture
    RPMcCoy 8 on at
    BYOD Incremental push & T-SQL deletes
    I don't see how to accomplish this with an appropriate/supported extension point and CoC.
  • Suggested answer
    Kevin Xia Profile Picture
    Kevin Xia Microsoft Employee on at
    BYOD Incremental push & T-SQL deletes
    Hi,
    Based on the detailed description provided, it seems that the issue with incremental deletes in the BYOD export is related to the handling of primary key columns and company context within the data entities. It appears that the current behavior of the system does not account for the company context when processing the incremental deletes, leading to unintended deletions across legal entities.
    addressing this issue will require careful consideration and potentially custom modifications to the synchronization logic. Consider implementing custom logic to handle incremental deletes based on the entity key and company context. This may involve extending the default behavior of the synchronization process to ensure that deletions are scoped correctly across legal entities.
    Best regards,
    Kevin

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans