SCSM Data Warehouse Cube / Dimensions do not process - Event 33573 / 35574

Recently I received an alert from our SQL server "OLE DB error: OLE DB or ODBC Error: Operation Cancelled HY008.  

Around that same time, I found a few errors in our Operations Manager log on our SCSM 2012 R2 DataWarehouse Management Server.  Specifically Events 33573 and 33574.

Event 33573 Warning
Message : An Exception was encountered while trying during cube processing.  Message=  Processing warning encountered - Location: , Source: Microsoft SQL Server 2012 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'WorkItemDim', Column: 'WorkItemDimKey', Value: '18553'. The attribute is 'WorkItemDimKey'..    

Well, that appears that a overnight job did not run for our SCSM data warehouse.  However, when I looked at the Data Warehouse Jobs in the SCSM Console, all of the status were either set to Not Started, or success.

Going back to the logs on the SCSM DW management server.

Event 33574 Warning
Message : Cube Processing workitem has failed. 
This is most likely caused by the DWDataMart (primary datamart) being out of sync from other marts. 
This is an intermittent problem and will resolve on its own as the Load jobs complete their runs. 

However, to work around this issue, administrators can manually start the Load.Common load job, wait for it to complete and then start the Cube processing job. 

Well, this seems to indicate that this will just auto-resolve on its own, once the load jobs complete their run.  Running Load.Common through PowerShell did not seem to resolve the issue.

Looking at Event 33573 Critical, we see a lot more information.

Message : An Exception was encountered while trying during cube processing.  Message=  Processing error encountered - Location: , Source: Microsoft SQL Server 2012 Analysis Services Code: -1054932986, Description: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation..       

Processing error encountered - Location: , 
Source: Microsoft SQL Server 2012 Analysis Services Code: -1054932978, 

Description: Errors in the OLAP storage engine: An error occurred while processing the 'WorkItemDim' partition of the 'ActivityRelatesToChangeRequest_WorkItemDim' measure group for the 'SystemCenterChangeAndActivityManagementCube' cube from the SM_DWASDataBase database..   

The event continues to list several other measure groups.  I made note of them all.

Some searching found a few articles.

Source links: TechNet and Alex's post at the TechNet Forum

This was more like it.  

Please understand what you are running before you run it.  Use at your own risk.

To resolve, I ran the following on the SCSM 2012 R2 DataWarehouse Management Server, in Windows Powershell.  Replace SERVERNAME for your SQL Analysis Server Instance that has the DWASDatabase.  

Run Script:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $NULL
$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect("SERVERNAME")
$Databases = $Server.Databases
$DWASDB = $Databases["DWASDatabase"]
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension
$Dimensions = $DWASDB.Dimensions
foreach ($Dimension in $Dimensions){$Dimension.Process("ProcessFull")}

Once that completed, I then processed each measure group, that Critical Event 33573 listed.

Open SQL Management Studio, Connect to the Analysis Server Instance.  Expand out the Database, and cube that is mentioned.  Then expand Measure groups, find the measure, right click and choose process.  

Ahh great the home stretch... but boom.  Processing failed.

So at this point, lets turn off all of the SCSM DW jobs, and start the processing again.  We can do this by running the following on the SCSM 2012 R2 Data Warehouse Management Server.

Import-Module 'E:\Microsoft System Center 2012 R2\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1'
Disable-SCDWJob –JobName MPSyncJob –Computer SCSMDWSERVER.URBANSCIENCE.NET

Disable-SCDWJob "Process.SystemCenterConfigItemCube"
Disable-SCDWJob "Process.SystemCenterWorkItemsCube"
Disable-SCDWJob "Process.SystemCenterChangeAndActivityManagementCube"
Disable-SCDWJob "Process.SystemCenterServiceCatalogCube"
Disable-SCDWJob "Process.SystemCenterPowerManagementCube"
Disable-SCDWJob "Process.SystemCenterSoftwareUpdateCube"

Disable-SCDWJob "Load.SM_CMDWDataMart"
Disable-SCDWJob "Load.SM_OMDWDataMart"
Disable-SCDWJob "Extract_DW_SCSM_USAI"
Disable-SCDWJob "Extract_UrbanScience"
Disable-SCDWJob "Transform.Common"
Disable-SCDWJob "Load.Common"


Now lets run that script to process all of the dimensions again.

Run Script:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $NULL
$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect("SERVERNAME")
$Databases = $Server.Databases
$DWASDB = $Databases["DWASDatabase"]
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension
$Dimensions = $DWASDB.Dimensions
foreach ($Dimension in $Dimensions){$Dimension.Process("ProcessFull")}

Process all of the measures that were indicated in the event.

Now they complete with success!

Now Move a Level up and process the cube.

Great!

Now don't forget the turn the jobs back on.  Open PowerShell and Run this.

Enable-SCDWJob "Process.SystemCenterConfigItemCube"
Enable-SCDWJob "Process.SystemCenterWorkItemsCube"
Enable-SCDWJob "Process.SystemCenterChangeAndActivityManagementCube"
Enable-SCDWJob "Process.SystemCenterServiceCatalogCube"
Enable-SCDWJob "Process.SystemCenterPowerManagementCube"
Enable-SCDWJob "Process.SystemCenterSoftwareUpdateCube"

Enable-SCDWJob "Load.SM_CMDWDataMart"
Enable-SCDWJob "Load.SM_OMDWDataMart"
Enable-SCDWJob "Extract_DW_SCSM_USAI"
Enable-SCDWJob "Extract_UrbanScience"
Enable-SCDWJob "Transform.Common"
Enable-SCDWJob "Load.Common"

Enable-SCDWJob "MPSyncJob"


So why did this happen?

Well at our company we had an instance where we had two line item approvers on a single Change Request.  This caused an issue with a 3rd party cache building service (Cireson).  The solution was to remove the duplicate approver.

This is my theory.

The SCSM admin then removed the entire review activity instead of the approver.   This became an issue as the change and associated activities were already added into the cube.  So when it began to process again it could not find that review activity to mark, so it failed the processing and any related items to that review activity.