I recently ran into the following error in the Transform-job in Data Warehouse:
An error countered while attempting to execute ETL Module:
ETL process type: Transform
Batch ID: 5601
Module name: TransformIncidentDim
Message: ErrorNumber=”2601″ Message=”Cannot insert duplicate key row in object ‘dbo.IncidentDim’ with unique index ‘UniqueIndex’.” Severity=”14″ State=”1″ ProcedureName=”TransformIncidentDimProc” LineNumber=”163″ Task=”Inserting into Dimension”
The error occures when the transform job was trying to instart a specific Incident into the dbo.IncidentDim table in the DWRepository database.
The source of the error was that the specific category in this incident, existed 2 times in the IncidentClassification table, but I guess the same thing could happen to the support groups or any other enum in the incident class.
Solution:
- Open the IncidentClassification table in the DWRepository database, and look for enums with duplicate rows (but with different classificationID) with this query:
SELECT count(enumTypeId) as ‘Count’ ,enumTypeId FROM IncidentClassification GROUP BY enumTypeId - Look for a row where the count number is greater than 1. When found, copy the enumTypeId value and run this query:
SELECT * FROM IncidentClassification WHERE enumTypeID = ‘<enumtypeID value>’ - To decide which duplicate row to remove, run a query against DWDataMart to see which one of the rows are used.
Run this query against DWDataMart for each IncidentClassificationID above:
SELECT * FROM IncidentDimvw WHERE Classification_IncidentClassificationID = ‘<IncidentclassificationID value>’ - For the row/rows in #2 that are not used in DWDataMart, delete them from DWRepository by using each the of IncidentClassificationIDs so only one row remains:
DELETE FROM IncidentClassification WHERE IncidentClassificationID = ‘<IncidentclassificationID value>’ - Rerun the Transform.Common job, and it should complete successfully.
