1. Unregister corrupt DW from SCSM
  2. On DW MS server, disable the three services:
    • Microsoft Monitoring Agent
    • System Center Data Access Service
    • System Center Management Configuration
  3. Back on SCSM, export and then remove all "Cube" management packs
    1. Note:  The installed SCSM Authoring Tool contains the base MS cubes
      • Cubes.mpb
      •  ServiceCatalogCubes.mpb
    2. Remember to also remove any cube altering MPs as well.  Ex: "AddCubeDateSlicers"
  4. Using SSMS, connect with Analysis Services to the DW SQL server
    • Expand the connection > DWASDataBase > Cubes à Delete all Cubes present
    • Optionally you could restore an earlier DWASDataBase then delete all Cubes
  5. Using SSMS, connect with Database Engine to the DW SQL server
    • Restore previously working databases when no cube jobs were running:
      •   CMDWDataMart
      •   OMDWDataMart
      •   DWDataMart
      •   DWRespository
      •   DWStagingAndConfig
  6.  Using SSMS, go into the restored DWStagingAndConfig, right click and create New Query
    • Copy, Paste and Execute the following command:
    • UPDATE [DWStagingAndConfig].[Infra].[Process] SET [IsEnabled] = 0 WHERE [ProcessName] LIKE '%Process.%'
  7. This ensures no cube jobs will try to run during the next step.
  8. Restart the DW MS server, this will also ensure the three services we stopped earlier are started again
  9. Once DW MS server is back up and running, log on it, and execute the following PowerShell command
    • Get-SCDWSource
    • Using the <SCSM> DataSourceName, we must execute another command
    • Note: NEVER unregister the "DW_" version, it's the actual data.
    • Unregister-SCDWSource -DataSourceTypeName "ServiceManager" -DataSourceName "<SCSM>"
  10. On the main MS server, open the SCSM Console and register the DW
  11. After about 25 minutes from successfully registering the restored active DW, view the MPSyncJob
  12. At this point we play the waiting game; we must wait for MPSyncJob to complete.
  13. Now we'll notice the two Extract jobs will run; we must wait for those to complete.
    • You'll notice now that some of the Process Cube jobs are gone after the Extract_<SCSM> job finishes.
  14. If the Extract Jobs are running multiple times, let them; we must wait for those to complete.
  15. Now that you jobs look similar to the following:
    •  
  16. On the DW MS server, execute the following PowerShell command:
    • Get-SCDWJob | ForEach-Object { Disable-SCDWJobSchedule -JobName $_.Name }
  17. Now ensure all jobs are "Not Started" by going into the SCSM Console and refreshing the DW Jobs Window
    • It's a good idea to make a separate backup of the DW Databases at this point before proceeding below
  18. In the SCSM Console, import "Cubes.mpb" which imports the following base MS Cube MPs

      

  19. In the SCSM Console, manually start the MPSyncJob, a few minutes later you should see the following:

  20. You'll notice under the Cubes section it is creating the corresponding cubes for us.
  21. Once MPSyncJob is complete, import the "ServiceCatalogCubes.mpb" and run MPSyncJob once more.
  22. Once MPSyncJob is complete again, enable and run each process cube job one at a time.  Example:
    • Enable-SCDWJob -JobName Process.SystemCenterWorkItemsCube
    • Start-SCDWJob -JobName Process.SystemCenterWorkItemsCube
    • Note: It took about ½ hour for the work item cube to process, be patient.
    • You may want to manually process all dimensions using SSMS or PowerShell before proceeding, though I didn't.
  23. The order was as follows:
    • Process.SystemCenterWorkItemsCube
    • Process.SystemCenterConfigItemCube
    • Process.SystemCenterChangeAndActivityManagementCube
    • Process.SystemCenterServiceCatalogCube
  24. Once they run and complete, then you may re-enable the job schedules using PowerShell:
    • Get-SCDWJob | ForEach-Object { Enable-SCDWJobSchedule -JobName $_.Name }
  25. If a job module is stuck in running:
    • Stop the cube job, disable the job using PowerShell
    • On DW MS, use PowerShell command:  Get-SCDWJobModule -JobName "<ProcessJob>"
      1.   Write down the ModuleID for later querying that has a status of Running.
    • Using SSMS, in DWStagingAndConfig you must find the job module and set it to a status of 2
      1. UPDATE [DWStagingAndConfig].[Infra].[WorkItem] SET StatusId = 2 WHERE ProcessModuleId IN
      2.         (       SELECT [ProcessModuleId] FROM [DWStagingAndConfig].[Infra].[ProcessModule]
      3.                 WHERE ModuleId = <ModuleID>
      4.         )
    • Restart the DW SQL and DW MS server
      1. Using PowerShell:  Start-SCDWJob -JobName DWMaintenance
      2. Once DWMaintenance finishes, enable the cube job using PowerShell
      3. Start the cube job in SCSM again, this way both the job and job module restart the "Running"
  26.  Once completed and everything looks fine for both data warehouse jobs and cube windows

  27. Import any remaining Cube MPs as well as any cube altering MPs.  Ex: "AddCubeDateSlicers" and obviously any post setup scripts like you'd normally do after importing something as per instructions for specified MP.
  28. Once all MPs are synced and all cubes created, use SSMS, connect with Analysis Services to the DW SQL server

    Expand the connection > DWASDataBase > Roles

  29. For each role not marked with Full Control, ensure the Cubes section has Access set to Read on each Cube!

  30. Temporarily disable job schedules, allow all jobs to finish, and make a separate backup for possible future use:

    • Note:  This is assuming you've already modified the DW SQL SSAS config file "msmdsrv.ini" located under the install directory:  "~\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" and are still having issues with the cube jobs.
    • 1)       ConfigurationSettings -> ThreadPool -> Process -> MaxThreads set to something like 200 (default == 64)
    • 2)       CoordinatorExecutionMode -> Set to 1 (default == -4)