Add Reporting to Service Manager 2012

September 12, 2012


The Service manager 2012 has some interesting reports available for getting detailed information about the work items. However now we can also use olap cubes so we can get al sorts of data out of the data warehouse db using Excel and PivotTables. Sound nice …

So let’s start with the installation of the data warehouse component.

Prereqs :

-> SSRS instance with the same collation settings as CMDB and datawarehouse databases

-> SQL Server Analysis Service

Service Manager or Data warehouse databases One of the following versions of Microsoft SQL Server Standard or Enterprise:
The 64-bit version of SQL Server 2008 with SP1

The 64-bit version of SQL Server 2008 with Service Pack 2 (SP2)

The 64-bit version of SQL Server 2008 R2

The 64-bit version of SQL Server 2008 R2 SP1

SQL Server Reporting Services (SSRS) in SQL Server 2008 with SP1, SQL Server 2008 with SP2, or SQL Server 2008 R2, or SQL Server 2008 R2 SP1

Microsoft .NET Framework 3.5 with SP1 is required on the computer hosting the data warehouse databases.*

The SQL Server collation settings must be the same for the computers hosting the Service Manager database, data warehouse database, and Reporting Services database.

Software Requirements for System Center 2012 – Service Manager

Remember after the default setup of the SSRS instance the url will not be active :

So open the Reporting service console and apply default Web service url values

Do the same for the report manager url

And create the reportserver database and test the url.

Now start the setup on the data warehouse server.

Select the correct location

Verify the prereqs

Use correct SQL server and instance

Check additional datamart db’s

Choose management group name and admin group. ( use different management group then service manager )

Select SSRS instance and service url

Fill in the service account. Rememer , this is the same account as the service manager services account and must be a local admin on the datawarehouse server. See link

Configure the reporting account

Configure Analysis Services Database

Configure Analysis account.

Check CEIP and update options , check review and install.

Sit back and wait …

Now let’s register the datawarehouse server. Log on to the management server with an account that is service manager and datawarehouse admin.

Fill in the FQDN of the datawarehouse server.

Use the proposed account or create a specific user.

Check the result.

Now in a few minutes time you should have an additional data warehouse button in the console.

You can check the deployment status by checking the data warehouse jobs.

All management packs should be associated or imported. This could take some time.

After this you should verify that 5 data warehouse jobs appear :

  • Extract_<Service Manager management group name>
  • Extract_<data warehouse management group name>
  • Load.Common
  • Transform.Common
  • MPSyncJob

If not you can resume the MPSync Job. You should see the reporting tab appear if all is executed correctly.

Next Post we’ll start hitting those cubes.


Gino D


Data Warehouse Object Health State Data Dedicated Maintenance Recovery State

August 1, 2012

A customer of mine had his SCOM 2007 R2 CU6 Root Management Server that was in a critical health state. When looking at the health explorer, we saw this:


SCOM performance its own database maintenance. Kevin Holman wrote a nice article about what maintenance is done automatically by SCOM and what maintenance you could configure additionally: .

When looking at the state change events, we saw the following description:

Failed to store data in the Data Warehouse. Exception ‘SqlException’: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. One or more workflows were affected by this. Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance Instance name: State data set Instance ID: {GUID} Management group: MG

 What does this mean? SCOM is trying to perform its maintenance on state data, but unfortunately, there is too much maintenance work to do, so the rule times out. Best would be to now check why this is timing out. Maybe you have corrupt tables? Or you had a sudden flow of a lot of data that was inserted in the datawarehouse? For this customer, we had issues with config churn which we just fixed. So this time-out could be easily explained.

How to fix this? First, disable the rule performing the maintenance so it will not interfere with our manual procedure. Open the SCOM console

  1. Go to the authoring pane
  2. Select Authoring => Management Pack Objects => Rules
  3. Change the scope to ‘Standard Data Set’
  4. Right-click The only rule there is: Standard Data Warehouse Data Set maintenance rule
  5. Select Override the rule for all objects of class: Standard Data Set
  6. Disable the rule by ticking the row with the parameter name Enabled and by changing the Override Value to false

  7. Select an appropriate override management pack (not the default management pack!) and click on apply.

Now we will be triggering the stored procedure used by this rule manually, and this with no timeout!

  1. Open SQL Management Studio
  2. Select the instance where your OperationsManagerDW database is residing
  3. Once opened click on new query and select you OperationsManagerDW database.
  4. Remember that our state change event description mentioned issues with state data, so we first need to get the ID of the state data set. To do this, click on new query, select your datawarehouse database and enter the following command:

    SELECT DatasetID FROM vDataset WHERE DatasetDefaultName = ‘State data set’

    Click on execute and Copy the resulting ID.

  5. Click on new query, select your datawarehouse database and enter the following command:

    EXEC StandardDataSetMaintenance “GUID”, with GUID the ID that resulted from the previous query.

  6. Wait until the command finishes successfully.

Go back to the authoring pane of the SCOM console to remove our previously defined override.

  1. Go to the authoring pane
  2. Select Authoring => Management Pack Objects => Rules
  3. Change the scope to ‘Standard Data Set’
  4. Right-click The only rule there is: Standard Data Warehouse Data Set maintenance rule
  5. Select Overrides summary
  6. Delete the previously defined override:

And the error disappeared! If this error comes back frequently, you should check for corrupt tables, config churn, database performance or other issues why the maintenance is timing out.