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

Wyatt Wong 116

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

Song, Michael 6 Reputation points

2020-12-03T00:49:49.313+00:00

I am also having the same issue with SCSM2016 1807
Georges Mouawad 11 Reputation points

2020-12-03T11:03:55.883+00:00

we are facing the same issue with SCSM 2012 R2 CU9

I tried to restore databases, even restore the VMs with no success.

Accepted answer

D B 111 Reputation points

2020-12-03T23:38:36.76+00:00
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:

Backup all DW DBs

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'

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;

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.
Please sign in to rate this answer.

6 people found this answer helpful.
Andrew Gordon 11 Reputation points

2020-12-04T01:00:54.287+00:00

This looks like it is working for me. A million, billion thank yous.

Andrew Gordon 11 Reputation points

2020-12-04T03:05:00.183+00:00

Yes, that means that you have to do the steps listed above. I got the same results that you did, performed the SQL scripts and then processed. Everything is going through perfectly again. Yea!!!

Zakaria Muhammad 161 Reputation points

2020-12-04T03:36:27.463+00:00

what does this XYZFact means on this line, "set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan]"?

i don't have any table like XYZFact_2021...

Wyatt Wong 116 Reputation points

2020-12-04T03:42:58.363+00:00

I also want to know that. I have executed the above script for 4 databases DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart and got no errors.

Wyatt Wong 116 Reputation points

2020-12-04T04:05:44.193+00:00

I have executed the steps provided by DB-7386 and now checking the result.

D B 111 Reputation points

2020-12-04T05:10:36.517+00:00

I'm no SQL expert, but it appears to be a variable for each job name that needs updated. If you add these lines before the exec sp_executesql @newChk line you will see...if you are concerned, remove the sp_executesql line...

Print (@objName)
Print (@newchk)

first example from mine:
ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] WITH CHECK ADD CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] CHECK CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk];

Wyatt Wong 116 Reputation points

2020-12-04T05:28:00.697+00:00

Here is the result before execution of the above SQL statements:

Here is the result after execution of the above above SQL statements and re-run of the SCDW Jobs. The BatchId of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart have successfully changed to a new value, which means the the previous BatchId have successfully completed.

Wyatt Wong 116 Reputation points

2020-12-04T08:04:42.463+00:00

I believed ZakariaMuhammad-8648 was asking a different thing. He mentioned that he could not find any table called XYZFact_2021_Jan. I'll check if I can find out more information.

ST1285 11 Reputation points

2020-12-04T12:06:46.817+00:00

Thanks for sharing, but we're getting absolutely no luck with this. We run the above in SQL, get 'Command Completed Successfully', but still get the 'partitioning column was not found' error on the transform.common and the three Load.x jobs.

However, there are 31 days in Dec - should the top bits not be 20501231?

Pablo Schlegel 6 Reputation points

2020-12-04T15:52:11.11+00:00

Solve!, Thanks

mohammad saad 21 Reputation points

2020-12-07T10:14:19.473+00:00

Thanks, It worked for us as well, we have been facing this issue since 01-DEC-2020, and now it is resolved.

Thank you!

Wyatt Wong 116 Reputation points

2020-12-08T04:05:34.16+00:00

I modified the above SQL script as follows:

USE [DWRepository]

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)
print @newChk
end; close c; deallocate c;

Please find a sample output of the @newChk variable as follows:

Jaime Esquivel 6 Reputation points

2020-12-09T22:09:31.303+00:00

This just worked for us! Thank you!!

Wyatt Wong 116 Reputation points

2020-12-10T00:52:25.523+00:00

Have you run the following SQL statements

Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

and then execute the SQL script ?

According to @D B , you need to run the SQL script in each of the DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart databases. You can add the USE [database name] command first before executing the SQL script

You also need to restart the System Center Health Service in the SCSM Data Warehouse management server and re-run each SCDW Job of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart

Camilo Andres Vanegas Ramirez 11 Reputation points

2020-12-30T21:52:11.837+00:00

Muchas gracias, me funciono, la ejecución de los job's se ejecutaron de forma correcta, de nuevo muchas gracias

Salim Assaf 6 Reputation points

2020-12-31T19:14:20.06+00:00

To everyone looking at this, you may want to double check your dates... Dec 31, 2020 does not exist in the DateDim tables therefore the below should likely be adjusted as it will cause you problems.

Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-04T13:50:25.597+00:00

realice el procedimiento el día 31 de diciembre y fue satisfactorio, sin embargo validando el día de hoy nuevamente se presentan las mismas fallas, agradezco su colaboración si hace falta ejecutar algo adicional, gracias

C Sharp Conner 1 Reputation point

2021-01-07T00:19:35.577+00:00

Still having issue on SCSM 2012 R2 UR9. Error slightly different from yours, believed to have happened Dec 30th. Error 4457.

Operation Manager Error 4457 - The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.

Eventually the TransformEntityManagedTypeFact module fails as a result with same 4457 error…. Details say On Step 5 Inserting Facts (Which are relationships) … the others say "Inserting into destination fact"

I think it stands to reason the "value" is bad data like a NULL being entered for a relationship.

Hypothesis: The LIVE Database has bad data in the dbo.relationship table and when it is given to the ARCHIVE, the ARCHIVE is unable to insert this bad data (like NULLs) for Primary Keys or something like that...

Wyatt Wong 116 Reputation points

2021-01-07T05:35:03.747+00:00

Can you post the screen output of "Get-SCDWJob" and share to us ?

Wyatt Wong 116 Reputation points

2021-01-07T05:37:39.04+00:00

I wrote a SQL script to list out all SCSM DW Jobs but excluding the Completed Jobs. Here it is:

USE [DWStagingAndConfig]

SELECT p.ProcessName, w.WorkItemId, w.ProcessModuleId, pm.VertexName AS [ModuleName], w.BatchId, w.StatusId, s.StatusName, w.RetryCount, w.ErrorCount, w.ErrorSummary, w.InsertTime, w.TakenTime, w.UpdateTime, w.NotToBePickedBefore, w.CustomInfo
FROM Infra.[Process] p INNER JOIN Infra.[Batch] b ON p.ProcessId = b.[ProcessId]
INNER JOIN Infra.WorkItem w ON b.BatchId = w.[BatchId]
INNER JOIN Infra.[Status] s ON w.StatusId = s.StatusId
INNER JOIN Infra.[ProcessModule] pm ON w.ProcessModuleId = pm.ProcessModuleId
WHERE b.StatusId <> 6 AND
w.StatusId <> 6
ORDER BY StatusId DESC, p.ProcessName ASC

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

Wyatt Wong 116 Reputation points

2021-01-07T05:42:29.533+00:00

An enhanced version include a list of commented items.

To check the failed SCSM DW Jobs, simply comment out the "w.StatusId <> 6' and uncomment "w.StatusId = 2"

To look for a specific SCSM DW Jobs, simply uncomment one of the "-- AND ProcessName = 'XXXXXXXXXX'" entry

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

mohammad saad 21 Reputation points

2021-01-07T06:13:07.113+00:00

great!!!

Thank you

Wyatt Wong 116 Reputation points

2021-01-07T10:33:14.44+00:00

The script listed out all the SCSM DW Jobs (including their modules) with BatchId as well as the StatusId and the Status Description. I created the script so that I can find out the BatchId of each SCSM DW Jobs more quickly than using the Powershell command Get-SCDWJob

Daniel Luck 1 Reputation point

2021-01-09T21:23:08.703+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

Daniel Luck 1 Reputation point

2021-01-09T21:24:02.46+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

James Johnson 1 Reputation point

2021-02-03T19:01:13.19+00:00

Is this supposed to create the xyzFact_2021_JAN tables? I've tried running your modified script (thanks for posting it) but I still don't have any Jan tables and I keep getting the partitioning column error on the Transform.Common job.

Wyatt Wong 116 Reputation points

2021-02-04T03:11:31.74+00:00

There is no need to create any tables. You can send e-mail to me at <wyattwong @ outlook . com> for further assistance.
Sign in to comment

38 additional answers

Gromov, Vyacheslav 1 Reputation point

2021-01-12T08:25:28.427+00:00

Please help. I completed all recomendations but no result. I have errors for some modules
Module name: TransformEntityManagedTypeFact
Message: UNION ALL view 'DWRepository.dbo.EntityManagedTypeFactvw' is not updatable because a partitioning column was not found.
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2021-01-13T02:50:37.247+00:00

If you follow what I posted, you should be able to resolve the issue.

Wyatt Wong 116 Reputation points

2021-01-13T02:54:58.443+00:00

Please run the script in the following URL and look for missing constraints:

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

You need to run the script on all 4 databases below:

[DWRepository]
[DWDatamart]
[CMDwDatamart]
[OMDwDatamart]

Gromov, Vyacheslav 1 Reputation point

2021-01-13T15:12:07.55+00:00

Can you please discribe steps in detail.
I run this https://dataedo.com/kb/query/sql-server/list-all-table-constraints script and chek constraints with errors in log
Message: UNION ALL view 'DWRepository.dbo.EntityManagedTypeFactvw' is not updatable because a partitioning column was not found.
I see all constraints with the start and end dates.

What I'm missing?

Wyatt Wong 116 Reputation points

2021-01-14T09:04:49.07+00:00

There is no need to check it this way.

Wyatt Wong 116 Reputation points

2021-01-14T09:08:24.287+00:00

You can execute the following USE statement and then execute the SQL script in the following URL, it can find all the constraints for you. You can add a WHERE clause to limit the output to '%Fact%' tables and check the constraints:

USE DWRepository

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

After that, repeat running the SQL script on each of the following 3 databases:

USE DwDataMart

USE CMDwDataMart

USE OMDwDataMart

@Andrew Gordon

Gromov, Vyacheslav 1 Reputation point

2021-01-14T13:50:23.367+00:00

I don't understand what I should find?
Error is for DWRepository.dbo.EntityManagedTypeFactvw

use DWRepository

USE DwDataMart

USE CMDwDataMart there is no EntityManagedTypeFact

USE OMDwDataMart there is no EntityManagedTypeFact

Wyatt Wong 116 Reputation points

2021-01-19T01:56:54.08+00:00

Sorry for the late reply, can you run the Get-SCDWJob powershell script in SCSM Data Warehouse Management Server to show which SCSM DW Jobs you got the failed result ?

I thought the steps are pretty straight forward as I already marked @D B 's reply as the Accepted Answer.

GromovVyacheslav 1 Reputation point

2021-01-19T10:19:35.753+00:00

Transform.Common Failed Transform 01.12.2020 3:36:00 13.01.2021 18:03:00

Wyatt Wong 116 Reputation points

2021-01-19T12:03:29.697+00:00

Please run the PowerShell script Get-SCDWJob in SCSM Data Warehouse Management Server.

Wyatt Wong 116 Reputation points

2021-01-19T12:13:31.813+00:00

Have you run the SQL scripts from the following URL and add the following where clause?

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

=======================

WHERE table_view like '%Fact%' and constraint_type like '%constraint%'

Please make sure you run it in the correct database below:

DWRepository
DwDataMart
CMDwDataMart
OMDwDataMart

GromovVyacheslav 1 Reputation point

2021-01-19T12:17:03.327+00:00

I try to stop all jobs and rerun, but the result the same.

Wyatt Wong 116 Reputation points

2021-01-20T02:30:30.907+00:00

Please run the above query @Gromov, Vyacheslav

GromovVyacheslav 1 Reputation point

2021-01-22T11:53:42.2+00:00

And what should I find?
59546-dwdatamart.txt
59586-dwrepository.txt
59558-cmdwdatamart.txt
59547-omdwdatamart.txt

Wyatt Wong 116 Reputation points

2021-01-25T04:43:45.677+00:00

A sample review from the output of dwdatamart.txt file, a couple of the Fact tables have an incorrect check constraints

dbo.ActivityStageDurationFact_2018_Jan Table Check constraint ActivityStageDurationFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ActivityStatusDurationFact_2018_Jan Table Check constraint ActivityStatusDurationFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.BillableTimeHasWorkingUserFact_2018_Jan Table Check constraint BillableTimeHasWorkingUserFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.CalendarHasHolidayFact_2018_Jan Table Check constraint CalendarHasHolidayFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.CalendarHasWorkDayFact_2018_Jan Table Check constraint CalendarHasWorkDayFact_2018_Jan_Chk ([DateKey]<=(20180131))

You could refer to the SQL script from @D B and update the constraint to "[DateKey]>=(20180101) AND [DateKey] <=(20180131)"

GromovVyacheslav 1 Reputation point

2021-01-25T17:02:39.743+00:00

I try to find all incorrect check constraints, but there are too many of them.
As I see in my errors, I try to check only this constraints and find that they don't have all month, as example Jan 2017. Maybe this is the problem, how can I restore missing month?

Wyatt Wong 116 Reputation points

2021-01-26T01:51:09.06+00:00

The constraints of these fact tables have no problems as you can see in the detail column. For example in the EntityRelatesToEntityFact_2016_Sep table, the constraint name is EntityRelatesToEntityFact_2016_Sep_Chk with the value of ([DateKey] >= (20160901) AND [DateKey] <= (20160930))

Wyatt Wong 116 Reputation points

2021-01-26T01:52:44.987+00:00

Have you read my previous reply to you ? I sample checked the output of dwdatamart.txt file, a couple of the Fact tables have an incorrect check constraints as follows:

dbo.ActivityStageDurationFact_2018_Jan Table Check constraint ActivityStageDurationFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ActivityStatusDurationFact_2018_Jan Table Check constraint ActivityStatusDurationFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.BillableTimeHasWorkingUserFact_2018_Jan Table Check constraint BillableTimeHasWorkingUserFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.CalendarHasHolidayFact_2018_Jan Table Check constraint CalendarHasHolidayFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.CalendarHasWorkDayFact_2018_Jan Table Check constraint CalendarHasWorkDayFact_2018_Jan_Chk ([DateKey]<=(20180131))

Wyatt Wong 116 Reputation points

2021-01-26T01:54:24.603+00:00

The correct constraint should look like these:

dbo.ActivityStageDurationFact_2018_Jan Table Check constraint ActivityStageDurationFact_2018_Jan_Chk ([DateKey]>=(20180101) AND [DateKey]<=(20180131))
dbo.ActivityStatusDurationFact_2018_Jan Table Check constraint ActivityStatusDurationFact_2018_Jan_Chk ([DateKey]>=(20180101) AND [DateKey]<=(20180131))
dbo.BillableTimeHasWorkingUserFact_2018_Jan Table Check constraint BillableTimeHasWorkingUserFact_2018_Jan_Chk ([DateKey]>=(20180101) AND [DateKey]<=(20180131))
dbo.CalendarHasHolidayFact_2018_Jan Table Check constraint CalendarHasHolidayFact_2018_Jan_Chk ([DateKey]>=(20180101) AND [DateKey]<=(20180131))
dbo.CalendarHasWorkDayFact_2018_Jan Table Check constraint CalendarHasWorkDayFact_2018_Jan_Chk ([DateKey]>=(20180101) AND [DateKey]<=(20180131))

Wyatt Wong 116 Reputation points

2021-01-26T01:59:11.923+00:00

If you refer to the accepted answer, @D B have mentioned to execute the following SQL statement to check the maximum DateKey, please run it first to find out the value.

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

GromovVyacheslav 1 Reputation point

2021-01-26T10:43:48.877+00:00

Yes I check the dates, all dates are good. I ask about missing month for example Jan 2017 may this influence?
I correct constraint to which you pointed. I have a problem with one of them:

Wyatt Wong 116 Reputation points

2021-01-27T02:09:50.327+00:00

Dear Sir, I STRONGLY advise you to READ ALL (I mean ALL) the threads in this post which you maybe able to find the answer to fix your issue. Regarding the following queries:

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

We found that the returned value should be 20501231. I have include a screen capture to show how I fixed the DateKey to 20501231

Wyatt Wong 116 Reputation points

2021-01-27T02:17:01.437+00:00

Regarding your dbo.BillableTimeHasWorkingUserFact_2018_Jan table, there is no constraint associated, so you should be able to skip the "ALTER TABLE .... DROP CONSTRAINT ..." statement and execute the "ALTER TABLE ...... WITH CHECK ADD CONSTRAINT .... CHECK ....." command. However, I noticed SQL Server reported an error message of "The ALTER TABLE statement conflicted with the CHECK constraint ......."

So are you using an account with sufficient privilege to CREATE and DROP constraint ? Regarding the error message, I need to google search to see if I can find any insights.

Wyatt Wong 116 Reputation points

2021-01-27T02:21:11.943+00:00

I found a post below:

https://dba.stackexchange.com/questions/115637/the-alter-table-statement-conflicted-with-the-check-constraint

According to the answer, if you encounter error when you execute "ALTER TABLE ...... WITH CHECK ADD CONSTRAINT .... CHECK ....." command. That could mean at least 1 row in your dbo.BillableTimeHasWorkingUserFact_2018_Jan table have DateKey value that violate the constraint condition of:

([DateKey] >= (20180101) AND [DateKey] <= (20180131))

So could you do a SELECT statement on dbo.BillableTimeHasWorkingUserFact_2018_Jan table to see if there is any DateKey value which is outside the above date range ?

GromovVyacheslav 1 Reputation point

2021-01-27T13:06:56.02+00:00

Yes, I have in this table 601 row with outside date for 2 years. What should I do with them?

Wyatt Wong 116 Reputation points

2021-01-28T04:14:55.72+00:00

You may have to check with @D B for further advices. However, you can run the following SQL statements to fix the DateDim first:

Exec DWRepository.dbo.PopulateDateDim '20210101', '20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101', '20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101', '20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101', '20501231'

After that, execute the following:

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

Wyatt Wong 116 Reputation points

2021-01-28T04:22:10.29+00:00

I checked my dbo.BillableTimeHasWorkingUserFact_2018_Jan and I got the same constraint conditions as you. So I guessed you don't need to fix it.

Please fix the DateDim tables to have the value updated to 20501231 first.

Wyatt Wong 116 Reputation points

2021-01-28T04:24:41.753+00:00

After that, you may focus to check the constraints for the "2020_Dec" and "2021_Jan" Fact tables. Please use the SQL script below

https://dataedo.com/kb/query/sql-server/list-all-table-constraints

Then add the following where clause to show the fact table for 2020 Dec.

=======================

WHERE table_view like '%Fact%' and constraint_type like '%constraint%' and details like '%202012%'

Note that you have to run the query for EACH of the following database:

[DWRepository]
[DWDatamart]
[CMDwDatamart]
[OMDwDatamart]

Repeat the checking for 2021 Jan with the following WHERE clause:

=======================

WHERE table_view like '%Fact%' and constraint_type like '%constraint%' and details like '%202101%'

Wyatt Wong 116 Reputation points

2021-02-02T04:15:31.11+00:00

Have you resolve the issue ?
Sign in to comment
SJM 1 Reputation point

2021-01-12T14:09:57.247+00:00

Microsoft have a fix for this internally but you will need to raise a support case with Microsoft.

I have not yet implemented or tested the fix, but I will try reporting back our results.

To summarise:

The cause of the issue is that the DateDim in Service Manager ends in December 2020
This issue was resolved in 2016 UR5 and should ONLY be present in versions earlier than that (including 1801)

Regards

SJM
Please sign in to rate this answer.
Anton Tolmachev 11 Reputation points

2021-01-13T11:47:03.653+00:00

@SJM i suppose it's fixed also at SCSM 1807 considering this point mentioned on the list to the release :

DateDim tables in DWDataMart are now extended until December 31, 2050.

(same as 2016 UR5)

will give it a try updating from 1801 to 1807 in a mean time.
To wing 2019 upgrade is more complex, though seems we had finally started to push things to it. we'll see how it goes.

SJM 1 Reputation point

2021-01-13T15:43:28.083+00:00

If that was indeed mentioned in the release notes for 1807 then upgrading from 1801 to 1807 is certainly an option for you to consider.
Sign in to comment
Abdellah 6 Reputation points

2021-01-15T11:31:38.69+00:00

Hi !

After 5 days of tests and checking all solutions here I resolved the problem of all jobs except the "Transform.Common", but at least reports are working now.

Here is how I did to resolve the incident, the procedure I followed is below :

(By the way! special thanks to @D B , @Wyatt Wong , @Salim Assaf , @C Sharp Conner , @mohammad saad , @Jim Finke for their efforts and for the good work :-) )

1- I restored a save snapshot of the DWH server before the first incident that Mr. @D B has put the first solution for, and I ran it on safe mode.
2- I disabled SCSM services.
3- I opened the SQL Server Management and I ran those commands :

Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

4- I used the three scripts attached on bottom to fix Dec 2020, Jan and Feb 2021 constraints (Note that the third one didn't work on DWRepository.dbo because there are no Feb tables):

5-After that I restarted the SCSM DWH and SCSM Servers ,now the reports are working .

I hope this can be helpful.
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2021-01-19T01:50:24.09+00:00

Actually there is no need to restore from any snapshot, run in safe mode and disable SCSM service. You just need to execute the scripts to fix the constraints and re-run the SCSM DW Jobs.
Sign in to comment
mohammad saad 21 Reputation points

2021-01-19T10:27:06.237+00:00

Hi,

We are encountering one issue, and it is out of constraint problem but anyone has any idea where to look at ?

In the webfront/vanaylst portal , support group is showing something else and in the report database it is showing different name.

which means report database support group column are not updating, anyone has any idea for this issue

?
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2021-01-20T01:33:59.143+00:00

Can you run the Get-SCDWJob powershell cmdlet in SCSM Data Warehouse server ? If it is a different issue, you may consider to start a new post of your own.
Sign in to comment