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.

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

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

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.

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

Separately we set required Outriggers (aka Enumerations).

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.

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

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.

Select Star Rating class, than go further.

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

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

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

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

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

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.

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

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.

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

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:

https://www.youtube.com/watch?v=TyW52VNMJZc

https://www.youtube.com/watch?v=QJStkNkY_qI

https://www.youtube.com/watch?v=bo0MGh13QLY

https://www.youtube.com/watch?v=ni_EzPajrOw

https://www.youtube.com/watch?v=50otXZY4o6I