OLAP cubes in Service Manager 2012

What is Service Desk without a reporting? In Microsoft System Center 2012 Service Manager the vendor has presented a new functionality for creating reports – OLAP cubes. In the beginning, the default cubes lacked a support for time dimensions but later a product team released some guidelines how to fix the issue. Now the solution seems solid but not entirely. The out-of-the-box cubes have many dimensions, measures and you can add time dimensions. You can even extend the standard cubes (CubeExtension elements) with some limitations. For instance, you could add new measures, KPIs, dimensions, named calculations, but cannot add new measure groups and substitutions. And in any case that means that your extension will make the cube even more bulkier.

From a practical point of view, when you create or change an Excel report linked to a standard Service Manager cube, you have tons of dimensions, measure groups that look very similar to each other. You have to be especially careful working with calculated fields to be able to filter them properly. And you are still very limited with time dimensions. For example, if you want using OLAP cubes to find out how many incident were closed last month, you have to return to “old-school” reports based on SQL Server Reporting Services. If you want to accomplish the same task for service requests, you have no choice at all with default Service Manager 2012. Some critics can say that you always can create a “simple” T-SQL statement that lets to calculate the required statistics. However, how many of you are very familiar with T-SQL language? And then, if you want to compare numbers with the same period of last year, you have to create a new T-SQL request. Each time?

Despite of those speeches, the idea of OLAP cubes is very expressive. OLAP technologies are widely used in Business Intelligence (BI) and many other areas to analyze massive data. And definitely, you have many facts in Service Manager to analyze as well. With tens and hundreds of thousands of incidents it is not easy to find uprising trends of weakening services or personal performance issues of end users, etc. What is required is a way to create new cubes and modify existing ones without a deep knowledge of T-SQL or MDX languages. During our first consulting projects with Service Manager 2012 we spent lots of time to create custom cubes or extend the standard ones and all that time we wanted to have a tool that allowed to simplify the process of the work with OLAP cubes in Service Manager. Finally, we have developed such a tool and used it for internal purposes. Of course, for a successful use of the tool you have to have some knowledge of Service Manager classes and some basic concepts of relationships in Service Manager. To be frank, you are not able to create a very simply report without that knowledge. This tool is actually for people who know a Service Manager architecture and prefer to achieve goals fast and completely without any coding.

Here  a few real life questions for reports that can be answered with a custom cube:

  1. How many active incidents are older than 30 days?
  2. What is an average duration of a resolution for problems by classifications?
  3. What is an average count of incidents related to change request by month?

An era of hard-coded reports is over. In our current transient time no one wants to be limited by dated technologies and a flexibility and readiness for changes are in a high demand. It is a time to create your own custom OLAP cube, friends. Come and try SCUtils CubeDesigner for Service Manager 2012.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.