Many people seems to have trouble getting their cube processing to work properly. Commonly, the processing works fine for a week or two after an upgrade/installation of SCSM 2012 but then suddenly fails. Different workarounds has been discussed; some people mention that increasing the ammount of RAM in the server hosting SSAS solved this issue while other have increased their RAM to as much as 48 GB without any luck. This error has been a hot topic on the Service Manager forums on TechNet and now recently on the MyITforum SCSM maillist.
Shaun Ericson at Cireson had this issue at a customer and opened a MS Support Case to get this issue investegated. Fortunately they found a solution that Shaun shared with the rest of the people on the MyITforum maillist today.
I asked Shaun if he was ok with me sharing the solution with all of you here on the blog, which he was.
Thanks for sharing Shaun!
Disclaimer: This solution was developed for a specific customer by MS Support after extensive troubleshooting. It might not apply to your environment. Use it at your own risk. If you are experiencing these issues it’s still best to contact MS Support to get it properly investigated.
Step 1 – Disable the Cube Processing jobs
On your Service Manager Data Warehouse management server, run this powershell script to disable the Cube Processing jobs:
Import-Module '%ProgramFiles%\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1' Disable-SCDWJob "Process.SystemCenterConfigItemCube" Disable-SCDWJob "Process.SystemCenterWorkItemsCube" Disable-SCDWJob "ProGetcess.SystemCenterChangeAndActivityManagementCube" Disable-SCDWJob "Process.SystemCenterServiceCatalogCube" Disable-SCDWJob "Process.SystemCenterPowerManagementCube" Disable-SCDWJob "Process.SystemCenterSoftwareUpdateCube"
(You might have to change the path to the Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1 to fit your environment)
Step 2 – Manual processing of the cubes
On your SSAS server, run this powershell script:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $NULL
$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect("FQDNofServer")
$Databases = $Server.Databases
$DWASDB = $Databases["DWASDatabase"]
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension
$Dimensions = $DWASDB.Dimensions
foreach ($Dimension in $Dimensions){$Dimension.Process("ProcessFull")}
Make sure to enter the FQDN of your SSAS on line 3. If using anything else than the default instance, make sure to include the instance name (FQDN\Instance)
Step 3 – Re-enable the Cube Processing jobs in SCSM
After the script in Step 2 has, run this powershell script on your Service Manager Data Warehouse management server to re-enable the Cube Processing jobs:
Import-Module '%ProgramFiles%\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1' Enable-SCDWJob "Process.SystemCenterConfigItemCube" Enable-SCDWJob "Process.SystemCenterWorkItemsCube" Enable-SCDWJob "Process.SystemCenterChangeAndActivityManagementCube" Enable-SCDWJob "Process.SystemCenterServiceCatalogCube" Enable-SCDWJob "Process.SystemCenterPowerManagementCube" Enable-SCDWJob "Process.SystemCenterSoftwareUpdateCube"
(You might have to change the path to the Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1 to fit your environment)
–
If you are having trouble with the cube processing and tried these scripts, please drop a comment below and tell us how it worked!
Great! Thanks! I’ve seen this issue before and in that case customer do all this manually.
Great post, Anders. But I think this is only temporary solution, because this trouble will repeat again and again. I do this procedure manually several times(stop DW jobs, process all cube dimensions manually, process cube and start jobs again), but after some time cube processing jobs is freezed again. So maybe problem in cube structure, гnfortunately, I can not understand it.
Hi SigmuS,
Yeah, this might not be a permanent solutions, but I’ve heard several cases where this has been enough to get it all going for quite a while again. The best way to prevent these kind of errors seems to be loads of RAM and SQL Enterprise unfortunately :/
Regards
//Anders
Heads up Anders,
In the Enable and Disable code, there appears to be a C&P error (Note the GET in the middle of “ProGetcess..)
Disable-SCDWJob “ProGetcess.SystemCenterChangeAndActivityManagementCube”
Enable-SCDWJob “ProGetcess.SystemCenterChangeAndActivityManagementCube”
The process won’t be 100% successful unless you correct the syntax.
Thanks,
Doug
Thanks Doug! It’s corrected now!
Regards
//Anders
Line 5 of step 1 still shows ProGETcess
Awesome post, this helped!
Thanks
I ran this and it seems to be working fine – awesome post!
Has any had this happen to them as well though?
If i look at the Cubes section in the console – they will say processed and give a date and time
But under the Data Warehouse Jobs, one job (WorkItems) keeps showing as failed.
When I run this command
$Dimensions = $DWASDB.Dimensions
foreach ($Dimension in $Dimensions){$Dimension.Process(“ProcessFull”)}
I am getting the following error. What am I doing wrong?
You cannot call a method on a null-valued expression.
At line:1 char:56
+ foreach ($Dimension in $Dimensions){$Dimension.Process <<<< ("ProcessFull")}
+ CategoryInfo : InvalidOperation: (Process:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Did you manage to solve this Raphael? Do you get that error when running the script as a whole?
Hi,
I’m having exactly the same problem as Raphael. It happens whether I run the script as a whole or line by line. Any ideas? Did you solve your problem Raphael?
After a little fiddling I resolved my problem. I’d changed the name of the Analysis Services DB when I installed the SQL feature. So when I modified the script from:
$DWASDB = $Databases[“DWASDatabase”]
to
$DWASDB = $Databases[“DWASDatabase_CDS”]
The script started to run properly.
Pingback: SCSM Data Warehouse Jobs Stuck in “Running” Status | Netivia Consulting
How to run the powershell script in step 2 in SSAS server ? Does SQL Server 2008 R2 Analysis Service support executing powershell script ?
Simply start a PowerShell prompt on the SSAS server 🙂
Hi,
I have tried this soulution because one cube processing was failed always.
But when i run the script on the SSIS Server I get follwoing error message:
————————–
Exception calling “Process” with “1” argument(s): “OLE DB error: OLE DB or ODBC error: Invalid column name ‘NewSapAutho
rizationUserLastName’.; 42S22.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘ServiceRequestDim’, Name of ‘
Service Request Dim’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘NewSapAuthorizationUserLastName’ attribute of the ‘Serv
iceRequestDim’ dimension from the ‘DWASDataBase’ database was being processed.
Internal error: The operation terminated unsuccessfully.
Server: The current operation was cancelled because another operation in the transaction failed.
OLE DB error: OLE DB or ODBC error: Invalid column name ‘NewSapAuthorizationUserFirstName’.; 42S22.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘ServiceRequestDim’, Name of ‘
Service Request Dim’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘NewSapAuthorizationUserFirstName’ attribute of the ‘Ser
viceRequestDim’ dimension from the ‘DWASDataBase’ database was being processed.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘ServiceRequestDim’, Name of ‘
Service Request Dim’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘NewSapAuthorizationTransactionName2’ attribute of the ‘
ServiceRequestDim’ dimension from the ‘DWASDataBase’ database was being processed.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘ServiceRequestDim’, Name of ‘
Service Request Dim’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘NewSapAuthorizationClient’ attribute of the ‘ServiceReq
uestDim’ dimension from the ‘DWASDataBase’ database was being processed.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘ServiceRequestDim’, Name of ‘
Service Request Dim’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Notes’ attribute of the ‘ServiceRequestDim’ dimension f
rom the ‘DWASDataBase’ database was being processed.
”
At line:1 char:55
+ foreach ($Dimension in $Dimensions){$Dimension.Process <<<< ("ProcessFull")}
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
—————
Any Idea How I can fix this?
Thanks
Markus
Hey Markus,
Did you ever figure this out?
Thanks!
I am having an issue were the cube processing jobs were stuck at running for over a day. I stopped and disabled the jobs. However when I run script 2 on our DW sql server it has been running for 2 hours now and nothing? any ideas? thanks
I ran across an issue a few days ago where all cubes would process and update with the exception of the WorkItems cube. The cube stayed in this state for several days. There were basically 3 suggestions I came across which were to re-run the Load.CMDWData/OMDWDataMart jobs. I did those separately. Another suggestion from another blog was to restart the SSAS on the SQL Server. After roughly 20 minutes, voila! It worked. Not sure if the first or the second suggestion was the cure but thought I would pass this along.
Everything is going well with this script, I’m on step two and after the ‘foreach’ statement, it sits there. I’m assuming this step can take awhile? I don’t want to wait around after work for 2 hours like one poster suggested, so I will go home. I hope I can come back in the morning and enable my cube processing. We’ll see.
Hi
After running the process the cubes SystemCenterPowerManagementCube SystemCenterSoftwareUpdateCube and continue the process generating an error , any ideas?
Thank you for your help
This worked very well for me few months. However, I noticed few days ago that cubes running forever again. I run exactly the same script , but it looks nothing is happening. It starts processing one dimension and than nothing. Nothing gets updated. I confirmed I stopped and disabled the Cube Processing jobs. I am running the script from SSAS server. Any thoughts?