Analyze data load failed.Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK'. Cannot insert duplicate key in object 'dbo.DIM_N_SYSTEM$'.

Analyze data load failed.Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK'. Cannot insert duplicate key in object 'dbo.DIM_N_SYSTEM$'.

Summary

Normalize Job Fails During Load Analyze phase with the error Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK'. The job completes the first time and then fails each time the Normalize job is rerun , for all subsequent attempts.

Synopsis

Normalize Job Fails During Load Analyze phase with the error Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK'.
The job completes the first time and then fails each time the Normalize job is rerun , for all subsequent attempts.

In the Data Platform/Log/bdna.log we see the error :

ERROR System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Analyze data load failed. ETL: [Inventory ID] Message: [ERROR : Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK'. Cannot insert duplicate key in object 'dbo.DIM_N_SYSTEM$'. The duplicate key value is (Value from customer data).Failed in DM_NM_LOAD_TABLE_SP for DIM_N_SYSTEM$]


Discussion

The KEY_SYS attribute is the primary key in DIM_N_SYSTEM$. This field is a combination of HOST_ID, OPERATINGSYSTEM_ID,DATASRC,USER_DOMAIN,HOSTNAME,SERIALNUMBER.

If the serial number has any non-visible characters , they key_sys value generated causes an issue. For the first run the key_sys(also includes the serial number) is added to database. For second/subsequent runs , the process tries to remove this data and add it back from the data source. However, the deletion fails due to the non-visible characters in serial number and hence the process tries to attempt another insert with the same key_sys and fails with the error : Violation of PRIMARY KEY constraint 'DIM_N_SYSTEM_PK' .

The discovery tool should be checked and validated for any non-visible characters in serial number. After these characters are removed, the process completes without errors.

NOTE: You need to use an advanced text editor to see these invisible characters. They may not be visible via notepad,csv,notepad++. You can search for the value [^\x00-\x7F] in Notepad ++ using the reg ex search mode to search for any non-ascii values.
Labels (2)
Was this article helpful? Yes No
0% helpful (0/1)
Comments

how do i solve this issue

Version history
Revision #:
1 of 1
Last update:
‎Oct 17, 2018 09:18 PM
Updated by: