Having trouble with your cube processing?

August 21, 2012 Posted by Anders Asp

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!

21 Responses to Having trouble with your cube processing?

  1. FreemanRU says:

    Great! Thanks! I’ve seen this issue before and in that case customer do all this manually.

  2. SigmuS says:

    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.

  3. Anders Asp says:

    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

  4. Doug says:

    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

  5. Stephanie says:

    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.

    • Raphael says:

      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

  6. Tim says:

    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?

    • Tim says:

      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.

  7. Pingback: SCSM Data Warehouse Jobs Stuck in “Running” Status | Netivia Consulting

  8. Wyatt Wong says:

    How to run the powershell script in step 2 in SSAS server ? Does SQL Server 2008 R2 Analysis Service support executing powershell script ?

  9. Markus says:

    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

  10. Jeremy Smith says:

    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

  11. Judy Kline Olsen says:

    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.

  12. Mike C says:

    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.

  13. leogeo80 says:

    Hi

    After running the process the cubes SystemCenterPowerManagementCube SystemCenterSoftwareUpdateCube and continue the process generating an error , any ideas?
    Thank you for your help

  14. Veljko says:

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *


*