Using Cubes in Service Manager 2012

November 8, 2012


Today we will demonstrate the basic use of excel cubes in service manager.

Open the service manager console -> Data warehouse -> Cubes

Select the desired cube and click Analyze in Excel.

Let’s start simple. Select IncidentDim -> IncidentdimCount

Ok now let’s add the classifications.

Nice, let’s add a graph.

Easy …

Now we can add the effected user display name.

You can switch labels as desired. Simply move the desired label upwards.

You can also move labels to the report filter section. This adds filtering to excel.

And you can move the label from row to column.

Remember if extensions are in a sealed mp, you can immeditately use them in the excel reports.

Now this is just basic use of the cubes but I’m sure you see that there are endless reporting capabilities in here.

Enjoy …

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