SCSM 2012 SP1 Data Warehouse Jobs failed with error message of <xxxx>vw is not updatable because a partitioning column was not found.

Question

I found 4 data warehouse jobs have a Failed status. After further checking, the error messages mentioned "UNION ALL view '<xxxx>vw' is not updatable because a partitioning column was not found." How can I resolve the issue ?

Load.Common
Transform.Common
Load.CMDWDataMart
Load.OMDWDataMart

Answer 1

steps provided by MS Support...I did these with all the job schedules disabled...

on the datawarehouse SQL server...

select max(datekey) from DWRepository..DateDim --should be 20501230
select max(datekey) from DwDataMart..DateDim --should be 20501230
select max(datekey) from CMDwDataMart..DateDim --should be 20501230
select max(datekey) from OMDwDataMart..DateDim --should be 20501230

If your errors are caused by this issue, the above queries will return 20201230

If date keys are from Dec 2020, please use the below steps:

1. Backup all DW DBs
2. Add additional date keys until 2050
   Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
   Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
   Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
   Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
3. rebuild the constraints on all 2021 fact tables by using the following script Run the following on DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart

declare @objName sysname, @newChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'_2020_Dec_','_2021_Jan_')
set @objName = replace(@objName,'_Chk','')
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
exec sp_executesql @newChk
end; close c; deallocate c;

4. Restart Microsoft Monitoring Agent

After this I was able to manually start each of the failed jobs, waited for completion, then enabled my job schedules.

Answer 2

I'm cleaning up my notes and will provide the sql queries to extend dates and add missing constraints...soon...

Answer 3

I think, MS Support solution forgot to change upper limit for *_2020_Dec_Chk from 20201230 -> 20201231

!! USE IT ON YOUR OWN RISK !!

declare @objNameD sysname, @newChkD as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objNameD if @@Fetch _STATUS<>0 break;
set @objNameD = replace(@objNameD,'_Chk','')
set @newChkD=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] DROP CONSTRAINT [XYZFact_2021_Jan_Chk];ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20201201) AND [DateKey]<=(20201231)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objNameD)
print @newChkD
--exec sp_executesql @newChkD
end; close c; deallocate c;

!! USE IT ON YOUR OWN RISK !!

To do real changes you must uncomment "--exec sp_executesql @newChkD" and comment "print @newChkD" and run it on DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart

Answer 4

I have written a simple powershell script (rename resetfailedscdwjob.txt to resetfailedscdwjob.ps1) to reset the failed SCSM Data Warehouse jobs and then send e-mail notification to supervisor and the support staff. I have created a Windows scheduled task to execute the powershell script every 15 minutes.

Alternatively, it is possible to reset a specific failed SCSM Data Warehouse job as a command line argument. For example, to reset the failed Process.SystemCenterWorkItemsCube, type the following command:

.\ResetFailedSCDWJob.ps1 Process.SystemCenterWorkItemsCube

Feel free to modify it to suit for your own needs.

Answer 5

The same thing has just started happening to me two days ago for no apparent reason. I've tried restoring the databases from backup, but that isn't working either. I hope that somebody will have a solution soon.