SCSM 2012 Data Warehouse jobs keep failing

  • Article

I often run into the same problem with the SCSM 2012 Data Warehouse, that the cubes jobs all fail. When resuming one of the jobs the following errors appear in the log:

Message : An Exception was encountered while trying during cube processing. Message= Processing warning encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'ConfigItemDim', Column: 'ConfigItemDimKey', Value: '13760'. The attribute is 'ConfigItemDimKey'..  

And / Or

Message : An Exception was encountered while trying during cube processing. Message= Processing warning encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'ProcessorDim', Column: 'ProcessorDimKey', Value: '156'. The attribute is 'ProcessorDimKey'..      

And / Or

Message : An Exception was encountered while trying during cube processing.  Message=  Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1055129595, Description: Server: The operation has been cancelled due to memory pressure..       Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1055129598, Description: Server: The operation has been cancelled.. 

When the cube processing is started, the Analysis service slowly consumes all available memory and does not stop until it cancels the process due to memory pressure.

Connecting to the server hosting the analysis service and looking at the properties of the cubes, shows that the cubes haven’t been processed and looking in SCSM all the jobs are set to failed:

Looking at the errors that are logged, they often relate to errors in the dimensions and their attributes. Reading the SCSM 2012 Administration guide, there is a script that processes all the dimensions in one step.

The script is not entirely correct, so I've changed it to:

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect("serverdw.blog.com")
$Databases = $Server.Databases
$DWASDB = $Databases["DWASDataBase"]
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension
$Dimensions = $DWASDB.Dimensions

foreach ($Dimension in $Dimensions){
    Write-host "Processing: " $Dimension
$Dimension.Process("ProcessFull")
}

Login to the server hosting the Analysis service and run the powershell script, just remember to change the $Server.Connect and the $DWASDB with the correct server (being the name of the SQL Server) and database name. It usually runs for 5-15 minutes. When complete, you can verify by looking at the properties at one of the dimensions:

After all the dimensions have been processed, you can start the cube processing from the SCSM console and they should all complete with success. Note that the jobs will change from Failed to Running to Not Started.

Instead look at the Cubes tab in the Data Warehouse wunderbar to see the status

 

But remember, this will just get the cubes processed once which is because it gets your cubes up to the latest set of data, but if the cube processing jobs starts failing again right away, it could because it is doing a full
processing job instead of a less resource demanding job like incremental.

The only way to fix this is to:

1)      Have a look at the HW and see if you could relocate DB-files, log files, temp etc. on more spindles / more VHD-files, add more processors or even RAM to help the SSAS with completing.

And/Or

2)      Upgrade to SQL Enterprise (Enterprise has the feature that allows for incremental processing instead of a full)

Comments

  • Anonymous
    January 01, 2003
    Hi Doug I'm sorry but I'm not a SQL expert and don't know how to configure SQL to do the incremental updates instead of full. I do know that SQL Enterprise edition has that feature, compared to standard edition. The script above is a workaround to have the cubes fully processed. If you're looking for a way to have them processed incremental on a daily basis, you have to go for the SQL documentation :-) Thanks Thomas

  • Anonymous
    October 01, 2012
    Thomas, Great article but need to know how to set the cubes to only run the PROCESS UPDATE instead of PROCESS FULL (number 2).  How do I set the feature in SQL Enterprise? Thanks!

  • Anonymous
    October 03, 2012
    No worries Thomas. I reached out to Microsoft about it.  Looks like the SCSM 2012 code checks for the SQL version and if it's Enterprise, the process automatically runs the PROCESS INCREMENTAL. "1) Our code checks the version of SQL (Enterprise or Standard) and takes a different path based on the edition. In case of Enterprise it will automatically do Incremental processing. Nothing needs to be configured on the SQL server."

  • Anonymous
    November 27, 2015
    Thanks for the great info. I really loved this. I would like to apprentice at the same time as you amend your web site, how could i subscribe for a blog site?
    For more info on showbox please refer below sites:
    http://showboxandroids.com/showbox-apk/
    http://showboxappandroid.com/
    Latest version of Showbox App download for all android smart phones and tablets. http://movieboxappdownloads.com/ - It’s just 2 MB file you can easily get it on your android device without much trouble. Showbox app was well designed application for android to watch movies and TV shows, Cartoons and many more such things on your smartphone.
    For showbox on iOS (iPhone/iPad), please read below articles:
    http://showboxappk.com/showbox-for-ipad-download/
    http://showboxappk.com/showbox-for-iphone/
    Showbox for PC articles:
    http://showboxandroids.com/showbox-for-pc/
    http://showboxappandroid.com/showbox-for-pc-download/
    http://showboxforpcs.com/
    There are countless for PC clients as it is essentially easy to understand, simple to introduce, gives continuous administration, effectively reasonable. it is accessible at completely free of expense i.e., there will be no establishment charges and after establishment it doesn't charge cash for watching films and recordings. Not simply watching, it likewise offers alternative to download recordings and motion pictures. The accompanying are the strides that are to be taken after to introduce Showbox application on Android. The above all else thing to be done is, go to the Security Settings on your Android telephone, Scroll down and tap on 'Obscure sources'.