Recently, we’ve had a few issues in which the BI Cube is red, meaning the BI Increment Load failed for some reason. If the reason for failure is the following error, we have a common data fix to correct the issue.
Error: The DELETE statement conflicted with the REFERENCE constraint "FK_fact_item_warranties_fact_items". The conflict occurred in database "AsteaBIProduction_DW", table "dbo.fact_item_warranties", column 'dim_item_key'.
To fix this issue, first run the following select query on the Data Warehouse database to check if there are any records where the referenced item ID doesn’t exist:
select * from fact_item_warranties where fact_item_warranties.dim_item_key in (select item_id from item_Deleted);
If the above query returns any results, please run the following query against the same Data Warehouse database:
update fact_item_warranties set dim_item_key = -1 where fact_item_warranties.dim_item_key in (select item_id from item_Deleted);
If the above mentioned error is the only error in the log, after the data is corrected, the next scheduled incremental load should run successfully.