A Better Way: Tips for Making Service Manager’s Data Warehouse Work Better for You

I love Service Manager. I love nearly everything about it. And if you are reading this, there’s a fair chance you do too, or at least like it a normal amount. Now, I did say I love nearly everything about it. What’s the thing I ‘don’t love’ (hate seems a bit too strong) the most…Data Warehouse. And I don’t think I’m alone in my dislike of Data Warehouse. Most Service Manager Admins I talk to – and I talk to a lot of them – agree with me.

So what’s so painful about it? Firstly, you need two fairly high performance servers to run it (one for the Data Warehouse Management Server and a second for the SQL server). This is going to cost you $$$ if you are running IaaS eg in Azure or take valuable resources away from other business systems in a on premise private cloud. Further, to work properly, you need to give it a SQL Enterprise licence and these do not come cheap. Thirdly, it’s just a pain to look after. Even in nice tidy environments, Data Warehouse will go wrong at some point. Its overly complex, 5 step process to take data from one database to another can fail easily. Troubleshooting it involves stopping all the jobs, restarting them one by one, allowing each one to fully complete before the next is started (all via PowerShell). Finally, assuming you can get your data into Data Warehouse, getting it back out again, is not a simple task. To build you own SQL Server reports or Excel PowerPivot sheets, you need to fully understand the super convoluted database structure and write multi-line SQL queries joining many of the tables to get anything useful.

(Thinking about it, hate is not too strong a word after all, I do hate Data Warehouse).

Why do we do this to ourselves, why do we use it at all? Most people I ask this question to give me the obvious answer: ‘Because that’s what you do, it’s part of Service Manager’. And they are right, it is what we do because it’s part of Service Manager and all the documentation, guides and blogs from Microsoft and even the Administration Overview screen in the Service Manager console (where you first land after installation) tells us to install it. Rarely do we actually think about the goals of the organisation and what they are trying to achieve with Service Manager and evaluate if they even need Data Warehouse. Just because its free, included, part of the product, in the project plan etc. does not mean its required. And as we’ve seen above, it’s not really free.

The other reason we install it, is because it claims to deliver one of the organisation’s goals: Work Item reporting over a long period of time. Service Manager is not designed to keep all Work Item data indefinitely, and in a reasonable size organisation, databases will get big and the system will get slow if you try. Instead, it has a built-in grooming process where older Work Items get removed from the database once they reach the required age. This is great for system performance but not so great for Management who want to be able to report on key metrics, such as total quantity of Work Items of each type, in order to track KPI over long periods of time. Data Warehouse gets over this by keeping data for longer periods of time in a separate database but it’s so complex and expensive. So do we really need it, or is there a better way?

What if there was another solution to provide the long term reporting functionality with a fraction of the cost and effort. Be able to provide those key metrics to management without all that pain and hassle? The answer is Cireson Analytics.

Cireson Analytics has existed as a solution for quite a while now but not everyone fully understands its true potential.  Most people know it as Cireson’s built in Business Intelligence (or Dashboarding) tool. It is often referred to as ‘Dashboard Designer’. But Cireson Analytics doesn’t just do that. In order to support the easy making of dashboards, Cireson Analytics also provides a database for long term retention of Work Item data which is far simpler than Data Warehouse.

There are a number of ways in which is it simpler. The database structure is as simple as you can get. One table for each Work Item type and another for relationships. The tables themselves are fully flattened with GUIDs and Display Names (in English) for relationships and enumerations. This means that you don’t have to join with other tables to get meaningful data so the process of writing SQL queries is so much quicker. Also, the way the data gets into the tables is simple too. Rather than the convoluted process Data Warehouse has to use due to its complexity, Cireson Analytics has 2 SQL jobs for each Work Item type (one for new items and one for updating), that run daily to copy the data over. These rarely go wrong but are easy to monitor and troubleshoot if they do. Finally, from an infrastructure point of view, because the database is simple, it is significantly smaller than Data Warehouse databases would be, and because the processing data into it is so much simpler (the SQL jobs) all of it can exist on your existing Service Manager SQL database with little impact. Data Warehouse needs a separate server for each of these – a separate SQL server for the databases and a Data Warehouse Management Server to process the data.

However, simpler isn’t always better. There are some limitations to Cireson Analytics as a long term retention option. The first is that it is non-extensible. One of the reasons I love Service Manager is its extensibility; it’s so easy to add custom properties to Work Item classes to suit organisational needs. However, the data in these custom properties will not get copied to the  Cireson Analytics database and once the Work Item is groomed from Service Manager, this data will be lost. The second limitation compared to Data Warehouse is the lack of history of a Work Item. The beautiful simplicity of the Cireson Analytics tables means that you just get a single row in a table for each Work Item. This means that you just see the Work Item in its current state or last state before it was groomed. You cannot see the history. Data Warehouse will keep a record of all of the changes that were made to a Work Item over it lifetime. Although, it is worth nothing that with no user interface for Data Warehouse it is extremely hard to get at this data and present it.

Whether Cireson Analytics can work for you in place of Data Warehouse depends on what you need it to do. Both of the above limitations boil down to the fact that Cireson Analytics is built for reporting of key metrics, and for that is works so well and so simply. For example the count of incidents of each priority over the last 12 months can be pulled out with a simple SQL query, selecting from just a single table. Use that SQL query inside the Cireson Service Manager Portal in a dashboard and you have a way to present that data to management in a clean web interface with so little effort and less infrastructure compared to a traditional Service Manager console report pulling data from Data Warehouse using SQL Server Reporting Services. However, Cireson Analytics is not built for audit purposes. If you need to be able to recall the full detail of a Work Item after it has been groomed, you will need Data Warehouse.

In summary, if you just want to track key business metrics over long periods of time, seriously consider using Cireson Analytics and not using or removing Data Warehouse. You can save yourself two servers, a SQL licence and a whole lot of pain looking after it. If you need more, then you may still need Data Warehouse. Nonetheless, you can always have the best of both worlds and use Cireson Analytics for easy metric reporting and Data Warehouse for audit.

Happy reporting everyone.

Experience Teams Ticketing Today

Start your 14-day free trial of Tikit. No credit card required.