Share
Report
8We 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?
All responses (
Answers (
