At the start of the new year, a few customers reported that BI is not updating during the scheduled incremental load. We found that the reason is the new year dates are missing from the dim_dates table in the BI data warehouse. The issue can be identified with the following error in the ETL log:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dim_inv_versions_dim_date". The conflict occurred in database "AsteaBIAllvotec_Production_DW", table "dbo.dim_date", column 'dim_date_key'.
To populate the dates, the following stored procedure can be executed. The following example will populate the dates for the year 2022:
EXECUTE sp_dim_date_populate '2022';
GO
After the stored procedure is executed, the incremental load job can be reprocessed.