Data import to cost accounting module fails with duplicate key error on cost center table during scheduled ETL job

ERP Microsoft Dynamics 365
, , , , , , , ,
1

We’re migrating cost center data from our legacy ERP to D365 Cost Accounting module using Data Management Framework. The ETL job processes about 8,500 cost center records with associated dimensions, but it’s consistently failing with duplicate key violations on the cost center table.

The error message shows:


Violation of PRIMARY KEY constraint 'PK_CostCenter'
Cannot insert duplicate key in object 'dbo.CAMCostCenter'
The duplicate key value is (CC-1001, AREA-NORTH)

Our source data has been validated and contains no duplicates based on cost center ID. The ETL job is configured to use “Insert” mode, and we’ve verified the target table is empty before each run. However, the duplicate key error suggests records are somehow being inserted multiple times during the same job execution.

Has anyone encountered duplicate key errors during cost accounting data imports even when source data is clean? I’m trying to understand if this is an ETL framework issue or something specific to the cost center table structure.

2

I think I found part of the issue. We’re using CAMCostCenterEntity but our source data includes dimension attributes that should actually go through CAMCostCenterDimensionEntity separately. When we try to import everything through the master entity, it’s attempting to create dimension combinations that may already exist from a previous partial import attempt.

Should we split the migration into two separate jobs - one for cost center master data and another for dimension associations? Or is there a way to handle this in a single import with proper sequencing?

3

Yes, D365 cost accounting entities often have business logic that auto-generates related records. For example, when you insert a cost center with dimensions, the framework might automatically create dimension combination records or cost object entries. If your source data already includes these derived records, you’ll get duplicates when the system tries to auto-generate them again.

Review the data entity definition for CAMCostCenter and check if it has any computed fields or related child entities that get populated automatically. You might need to import to a different entity or disable certain auto-generation features during migration.

4

Absolutely split the migration into separate jobs - this is the correct approach for cost accounting data. Let me explain the complete solution covering your ETL job structure, duplicate key prevention, and cost center table handling.

ETL Job Restructuring:

Your duplicate key error stems from trying to import both master data and dimensional relationships in a single pass. Cost accounting in D365 requires a specific import sequence:

  1. Job 1 - Cost Center Master Data:

    • Entity: `CAMCostCenterEntity
    • Fields: CostCenterID, Name, Description, Type, Status
    • Mode: Insert or Upsert
    • This creates the base cost center records without dimensions

  2. Job 2 - Dimension Definitions:

    • Entity: `CAMDimensionEntity
    • Import dimension members (AREA-NORTH, AREA-SOUTH, etc.) if not already present
    • Must complete before Job 3

  3. Job 3 - Cost Center Dimension Associations:

    • Entity: `CAMCostCenterDimensionEntity
    • Fields: CostCenterID, DimensionName, DimensionValue
    • This creates the composite key combinations
    • Enable “Skip duplicate” option in execution settings

Duplicate Key Error Resolution:

The composite primary key (CostCenterID, DimensionName, DimensionValue) is causing your violations. Address this with:

  1. Pre-Import Validation Query:

SELECT CostCenterID, DimensionName, DimensionValue, COUNT(*)
FROM StagingTable
GROUP BY CostCenterID, DimensionName, DimensionValue
HAVING COUNT(*) > 1

This identifies duplicates in staging before import execution.

  1. Clear Target Table Strategy: Even if the table appears empty, dimension combination records might exist in related tables:

DELETE FROM CAMCostCenterDimensionCombination
WHERE CostCenterID IN (SELECT CostCenterID FROM CAMCostCenter)
DELETE FROM CAMCostCenter

Run this cleanup script before migration to ensure truly clean state.

  1. Parallel Processing Configuration: Disable parallel processing for cost center imports:
    • Data Management > Framework Parameters
    • Set “Maximum insert commit size” to 1 for cost accounting entities
    • Set “Threshold for parallel processing” to 0
    • This prevents race conditions during dimension combination creation

Cost Center Table Structure Handling:

The cost center table has specific constraints you must respect:

  1. Composite Key Structure:

    • Primary key is NOT just CostCenterID
    • It’s (CostCenterID + DimensionCombinationRecID)
    • The system generates DimensionCombinationRecID based on dimensional attributes
    • If you provide the same dimensional attributes twice, same RecID is generated = duplicate key

  2. Dimension Combination Logic: D365 automatically creates dimension combination records. When you import:

    
CC-1001, AREA-NORTH, DEPT-SALES

    The system:

    • Creates cost center CC-1001
    • Looks up or creates dimension combination for AREA-NORTH + DEPT-SALES
    • Links cost center to dimension combination via RecID
    • If this combination already exists, reuses the RecID

  3. Avoid These Patterns:

    • Don’t import cost centers with dimension values in the same entity
    • Don’t include derived/calculated dimension combinations from source
    • Don’t re-import without clearing dimension combination cache

Source Data Transformation:

Transform your source data to match the required structure:

Source Query for Job 1 (Master Data):


SELECT DISTINCT
  CostCenterID,
  CostCenterName,
  CostCenterType,
  IsActive
FROM LegacyERP.CostCenters

Source Query for Job 3 (Dimensions):


SELECT DISTINCT
  cc.CostCenterID,
  'AREA' as DimensionName,
  cc.AreaCode as DimensionValue
FROM LegacyERP.CostCenters cc
WHERE cc.AreaCode IS NOT NULL
UNION
SELECT DISTINCT
  cc.CostCenterID,
  'DEPARTMENT' as DimensionName,
  cc.DepartmentCode as DimensionValue
FROM LegacyERP.CostCenters cc
WHERE cc.DepartmentCode IS NOT NULL

This unpivots dimensions into separate rows, each creating one dimension association.

Execution Sequence:

  1. Clear target environment (run cleanup script)
  2. Execute Job 1 - Import 8,500 cost center master records
  3. Validate: Query CAMCostCenter table, verify 8,500 records present
  4. Execute Job 2 - Import dimension members (if needed)
  5. Execute Job 3 - Import dimension associations (likely 15,000-25,000 rows if multiple dimensions per cost center)
  6. Enable “Skip duplicate records” in Job 3 execution settings
  7. Validate: Query CAMCostCenterDimensionCombination for expected associations

Error Handling:

If duplicates still occur:

  • Check DMF execution logs for the specific duplicate key values
  • Query staging table to see if those combinations appear multiple times
  • Verify no custom code in CAMCostCenterEntity that generates additional records
  • Review data entity field mappings - ensure no many-to-many relationships in single entity

This approach has successfully migrated cost accounting data for implementations with 50,000+ cost centers and complex dimensional hierarchies. The key is separating master data from dimensional relationships and respecting the composite key structure of the cost center table.

5

Good points. I checked the source query and we are joining cost centers to multiple dimension tables (cost element, activity, department). It’s possible we’re creating duplicates through the joins. However, when I export the staging table before the import executes, I see 8,500 unique records with no duplicate key combinations.

The staging data looks clean, but the error occurs during the insert into the target table. Could there be triggers or business logic in D365 that’s creating additional records during insert? Or maybe the entity is splitting records somehow?

6

Another thing to check - are you using the right data entity? There are multiple entities for cost accounting: CAMCostCenterEntity (master data only) vs CAMCostCenterDimensionEntity (with dimension combinations). If you’re importing dimension combinations through the master entity, it might be trying to create the same dimensional breakdown multiple times.

Also, cost center hierarchies can cause issues. If your source data includes both parent and child cost centers with the same dimensional attributes, you might be violating uniqueness constraints that exist for the hierarchy structure.