Log in  \/ 
Register  \/ 


How to create System Center 2012 Service Manager Knowledge Base report

In this post, I will show how to add information about an existing class to the SCSM Data Warehouse and quickly generate a report without developing skills. In the same way, you can add the information about your own custom class.

I will use a knowledge article’s class as an example. Out-of-the-box SCSM does not provide any report for knowledge articles. Moreover, the knowledge article’s data is not presented in the Data Warehouse’s databases.

See also SCSM 2012 Knowledge Management report on classic SQL Reporting Services

First, we have to extend the Data Warehouse for the knowledge article’s class. To do this we will use a free utility SCUtils DWMPCreator. Let’s run the tool using the SCSM administrative account.

How to create SCSM report

In the first window, we see all existing classes with descriptions and corresponding management packs.

SCSM 2012 custom report

We want the knowledge article’s class so we pick it up.

SCSM 2012 custom cube

Next, we specify the unique name for a new Dimension that will also serve as a name of a table in the Data Warehouse’s database.

Create SCSM 2012 custom cube

Then we select fields that we want to see in the Data Warehouse and Reporting.

SCSM 2012 cube

Separately we set required Outriggers (aka Enumerations).

SCSM 2012 report

The knowledge article’s class has relationships that connect the class’s instances with instances of other classes (like Users, Incidents, Services, etc.) so we have to specify at least one to use in the future reports. I select the ‘Links To Knowledge Documents’ relationship. Actually you may select many relationships id required.

System Center 2012 Service Manager

On the next screen you can edit the relationship fact’s name and choose a Target Dimension.

Microsoft Service Manager report

On the final screen of the wizard, you can change management pack’s name, the location of the digital certificate’s file or add additional dimensions and relationships.

We will use this opportunity to add an information about knowledge article’s ratings. After clicking on ‘Add Another Dimension’ button, we will meet the first page of the wizard.

Remark: In System Center 2012 Service Manager only the console users can set a rating for a knowledge article. However, if you are so lucky to have SCUtils Knowledge Base solution, your end users have the same opportunity as well as an ability see knowledge article in HTML on the SCSM self-service portal.

System Center 2012 Service Manager create Custom report

Select Star Rating class, than go further.

System Center 2012 Service Manager report

On the next page, we select all required fields for the Data Warehouse and Reporting.

System Center 2012 Service Manager Custom report

There’re no outriggers for Star Rating class so we proceed for the next step.

System Center 2012 Service Manager custom report

Here we add the second relationship ‘Has Average Rating’.

System Center 2012 Service Manager Custom cube

On the next window we check Relationship Fact’s name and a Target Dimension.

SCSM 2012 Custom cube

On the next step, we specify the management pack’s name and then click on ‘Create’ button.

SCSM 2012 create Custom cube

SCSM 2012 Custom report


As a result we have 4 files. The XML files consist of the source XML and are usually used for manual editing if required. The sealed files (with *.MP extension) we will import to SCSM.

The management pack named ‘KnowledgeBase.DW.Library.mp’ will be used to create dimensions and outriggers for knowledge articles and Star Rating’s classes in Data Warehouse’s databases.

The management pack named ‘KnoweledgeBase.DW.Library.CubeMP.mp’ is intended to add new dimensions in the SQL Analysis Services (OLAP).

Our next action is importing KnowledgeBase.DW.Library.mp and KnoweledgeBase.DW.Library.CubeMP.mp.

SCSM 2012 reports

We have to make sure all management pack we imported are successfully associated.

SCSM 2012 reports

After that, we should let the system runs a few full cycles of the ETL jobs. Finally, the information about the knowledge articles are transferred in our Data Warehouse’s databases.

It’s time to start with the reporting. For instance, we will buid the following reports:

  • Knowledge Base Statistics Report;
  • Knowledge Article by category;
  • Knowledge Article by Owner;
  • Knowledge Base Article Avg. Rating.

As we stated in the beginning, we will escape coding. To do that we will engage the SCUtils CubeDesigner tool.

Let’s start the tool from the SCSM console.

SCSM 2012 reports

We select ‘Create New’ to build our custom cube in Analysis Services.

SCSM 2012 reports

We know what required for our report so let’s add new Dimensions by clicking on ‘Add Dimension’.

Select ‘Knowledge Article’.

Than we add ‘Domain User or Group’.

The third dimensions will be ‘Star Rating’.

To filter by date we need a Time Dimension. Click on ‘Add new’ in the Time Dimensions’ zone.

We will sort by ‘Created Date’.

Than we connect UserDim and KBArticleDim using the corresponding relationship.

Let’s rename UserDim to OwnedByUser to reflect the dimension’s role.

Than we have link StarRatingDim to KBArticleDim with the required relationship.

We have finished with the source dimensions and it’s time to create measures for our reports. Click on ‘Add new’ in the MeasureGroup’s zone.

We will add a measure that counts all published knowledge articles. First, we select DistinctCount for ArticleId (a key field).

Than we create a new measure that will count all published articles. We will repeat all previous steps for our first measure and filter all published artilces. To do that click on ‘Define Criteria for this Measure’ and select Status equals to ‘Published’.

Let’s create another measure to calculate the average rating for our articles. We select Total Rating Stars in StarRatingDim with Average Aggregate Function.

We will also filter all non-zero values.

Using the similar approach, we will count articles with Draft and Archived status.

Now we save our cube’s definition and specify a name for our cube.

After that, we will deploy the cube to Analysis Services.

After the deployment process is over we will be asked for processing the cube. Let’s go with a Full Process.

Wow, our custom cube is finally deployed and processed. Now we can start using Excel to design our reports.

In this post we presented the way how you can add missing information to the Data Warehouse with the SCUtils DWMPCreator utility. And how you can build beautiful reports in Excel with custom cubes created with the SCUtils CubeDesigner tool.

Below is the list of the recorded videos that show how to use our above-mentioned tools:






Categories: System Center 2012

Comments (2)

  • jose Fehse

    jose Fehse

    13 November 2014 at 02:37 |
    Hi, wondering how to create the cube MP when you are throwing an extension of a class (i.e. Service Request). The program won't generate the Cube file in this case. Ideas?


  • Marat Kuanyshev

    Marat Kuanyshev

    13 November 2014 at 07:08 |
    Can you email to support@scutils.com screenshots of the issue and detailed description?


Leave a comment

You are commenting as guest.

This is the default dialog which is useful for displaying information. The dialog window can be moved, resized and closed with the 'x' icon.