OnCommand Insight Data Warehouse ETL job fails at "DatamartsPreparation" caused by "Duplicate entry '#-12345-#' for key'PRIMARY

 Clicking the 'FAILED' red hyperlink will show an error message similar to the following:

com.netapp.sanscreen.dwh.capacity.CapacityException: Failed running pre capacity job Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry '#-12345-#-#-#' for key 'PRIMARY'

will contain the following error message:

2018-08-08 08:10:09,953 ERROR [Thread-65 (ActiveMQ-client-global-threads)] SqlShell(SqlShell.java:1303) - Failed to insert row into dwh_capacity_staging.storage_and_storage_pool_capacity_fact due to unique keyPRIMARY[storagePoolTk, storageTk, tierTk, backend, isVirtual] and values 0-88217-0-0-0[0, 88217, 0,0, 0]. Query: INSERT INTO dwh_capacity_staging.storage_and_storage_pool_capacity_fact

Showing the duplicate records: storagePoolTk,storageTk,tierTk,backend,isVirtual,capacityMB,rawCapacityMB,usedCapacityMB,usedRawCapacityMB,snapshotUsedCapacityMB,snapshotUsedRawCapacityMB,softLimitCapacityMB,unconfiguredRawCapacityMB,spareRawCapacityMB,failedRawCapacityMB,volumeCapacityMB,unusedVolumeCapacityMB,volumeConsumedCapacityMB,mappedVolumeCapacityMB,maskedVolumeCapacityMB,internalVolumeAllocatedCapacityMB,internalVolumeUsedCapacityMB,internalVolumeConsumedCapacityMB,dedupeRatio,compressionRatio

0,88217,0,0,0,0,247281042,0,0,0,0,null,247 281042,0,0,0,0,0,0,0,0,0,0,null,null

0,88217,0,0,0,0,7922254,0,7922254,0,0,null,0,0,0,0,0,0,0,0,0,0,0,null,null

2018-08-08 08:10:14,540 ERROR [Thread-65 (ActiveMQ-client-global-threads)] JobDispatcherBean(JobDispatcherBean.java:106) - Job{name='Datamarts Preparation', id=29970} failed com.netapp.sanscreen.dwh.capacity.CapacityException: Failed running pre capacity job

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry '#-12345-#-#-#' for key 'PRIMARY'

Checking the database on the DWH server shows that 12345 is associated with the IPaddress of your StorageGRID datasource:

1. On the DWH server, open a command prompt with elevated privileges (Run AsAdministrator)
2. Run cd InstallationDrive: Program Files\SANscreen\mysql\bin
3. Run mysql.exe -uroot -p
4. Enter the password for your DWH database
5. Run select * from dwh_dimensions.storage_dimension where tk = 12345