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.
