Processing Data Warehouse cubes in Service Manager fails with errors that contain "The attribute key cannot be found when processing" and "The attribute is 'DateKey'"

Assume that you process Data Warehouse cubes in System Center Service Manager. If you receive error messages that contain the text strings "The attribute key cannot be found when processing" and "The attribute is 'DateKey'", this issue applies.

Additionally, events that resemble the following are logged in the Operations Manager event log:

Note These error message text strings can occur with other Data Warehouse cubes and with other date fields such as ScheduledStartDate, ScheduledEndDate, ActualStartDate, ActualEndDate, ServiceRequest_ActualEndDate__DateKey, and so on.

This issue occurs because the DateDim Dimension is based on the dates that are listed in [DWDataMart].[dbo].[DateDim]. One or more workitems (SR, CR, and so on) that are being processed contain a date field that has a date that is not contained in the DateDim table. If the DateDim table has not been extended with new dates, dates after 12/30/2020, will result in the cube failing.

To resolve this issue, follow these steps:

1. In SQL Server Management Studio, open a query against DWDataMart.
2. Execute the following with the End Date range that you want. We recommend not extending the date range beyond what is needed.
    

   /********************************************************************************************/
   /********************************************************************************************/
   -- Update @EndDate below to extend DimDate Calendar rows
   DECLARE @EndDate DATETIME = '12/31/2030'        --End Value of Date Range
   --
   -- Update @EndDate above to extend DimDate Calendar rows.
   -- Note: like the built in stored procedure it sets all saturday and Sundays with isHoliday.
   -- Different countries / companies observe different days for holidays
   --
   /********************************************************************************************/
   /********************************************************************************************/
   DECLARE @CurrentDate DATETIME = (SELECT top 1 CalendarDate FROM [dbo].[DateDim] ORDER BY DateKey desc)
   SET @CurrentDate = DATEADD(DD, 1, @CurrentDate) 
   WHILE @CurrentDate <= @EndDate
   BEGIN
   /* Insert rows into existing DimDate table. */
    INSERT INTO [dbo].[DateDim]
    SELECT
     CONVERT (INT,CONVERT (char(8),@CurrentDate,112)) as DateKey,  -- ex   20201230
     CONVERT (smalldatetime, CONVERT (char(10),@CurrentDate,101)) as CalendarDate,  -- ex   2020-12-30 00:00:00
     DATENAME(DW, @CurrentDate) AS DayOfWeek,                      -- ex   Wednesday
     DATEPART(DD, @CurrentDate) AS DayNumberInMonth,               -- ex   30
     DATEPART(WW, @CurrentDate) AS WeekNumberInYear,               -- ex   53
     DATENAME(MM, @CurrentDate) AS MonthName,                      -- ex   December
     DATEPART(MM, @CurrentDate) AS MonthNumber,                    -- ex   12
     DATEPART(YY, @CurrentDate) AS YearNumber,                     -- ex   2020
     CASE DATEPART(QQ, @CurrentDate)
      WHEN 1 THEN 'Q1'
      WHEN 2 THEN 'Q2'
      WHEN 3 THEN 'Q3'
      WHEN 4 THEN 'Q4'
      END AS CalendarQuarter,                                     -- ex   Q4
     CASE DATEPART(QQ, @CurrentDate)
      WHEN 1 THEN 'Q3'
      WHEN 2 THEN 'Q4'
      WHEN 3 THEN 'Q1'
      WHEN 4 THEN 'Q2'
      END AS FiscalQuarter,                                        -- ex   Q2
     DATEPART(MM, DateAdd(mm,-6,@CurrentDate)) FiscalMonth,        -- ex   6
     DATEPART(YY, DateAdd(mm,-6,@CurrentDate)) FiscalYear,         -- ex   2019
                 -- Set every Saturday and Sunday as a Holiday.
                 CASE (DATEPART(dw, @CurrentDate) + @@DATEFIRST)%7 
                        WHEN 0 THEN 1
                        WHEN 1 THEN 1 
                        Else 0
                        END IsHoliday,                             -- ex  0
     CASE DATENAME(DW, @CurrentDate)
         WHEN 'Saturday' THEN 0
      WHEN 'Sunday' THEN 0
      ELSE 1
      END AS [IsWeekDay],                                          -- ex   1
                 CASE DATEPART(DD,EOMONTH ( @CurrentDate )) 
         WHEN DATEPART(DD, @CurrentDate) THEN 1
      ELSE 0
      END AS IsLastDayOfMonth                                      -- ex   0
    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
   END
   

3. After the script is finished, connect to DWRepository and execute the script again.

When a "Request Offering" is created, there is an option to "Limit available dates to a specific range" in the "Configure Prompt" window.

Request Offerings date fields should be restricted to the date range that is defined in the DWDataMart DateDim table so that the SQL Analysis cube does not fail.

Limit available dates to a specific range

• Limit earliest available date: 1/1/2000
• Limit latest available date: 12/31/2030