Solution: BI Incremental Load Failure (fact_inventory_daily)

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.