With any customer that I am designing a SCSM solution for there is always a point in the design phase that we get to the topic of reporting and if there is a need for a Data Warehouse.
The main concern that customers have is the number of resources a Data Warehouse consumes as Microsoft recommends at least 2 servers (but more often 3) just for Data Warehouse alone. (One for the management server, one for the SQL database, and one for Reporting Services). If this seems excessive then you would be right.
Microsoft’s solution for reporting out of SCSM was to use a rationalised database (Data Warehouse) and use Extract, Transform and Load jobs (ETL) to maintain that data in the warehouse database. Then, using SQL Server Reporting Services (SSRS), or OLAP Data Cubes, allow administrators to retrieve reports. This is standard practice for Data Warehouse solutions however, the ETL jobs are traditionally done within SQL and scheduled as SQL jobs. Not so in the case of SCSM.
Within the SCSM architecture, the ETL jobs are handled by the Data Warehouse Management server and the ETL jobs are services that run outside SQL. A such there is application overhead that must be worked into the code rather than leveraging the SQL Jobs within the SQL implementation that houses the data. This introduces additional computation time and effort that requires more resources.
The reason this solution seems so large and clunky was it was originally designed to consolidate SCSM, SCOM and SCCM into a single data warehouse for reporting. However, most organisations do not use it this way, so it seems to have a very large admin overhead for producing reports out of SCSM and user were restricted to only using this method for reporting. That is until the release of Cireson Dashboards and then later Analytics in the Cireson Analyst web portal.
Cireson has been slowly developing for the Analytics offering, that is integrated in to the Cireson Analyst Portal and is part of the Team suite of products, for some time now and most reporting requirements have been able to be answered by this solution for many customers.
However, there were two key pieces of data that were missing from the analytics solution that was only available within the out of box Data Warehouse solution.
They were the Action log comments and any property extensions that organisations made to a class.
Many customers wanted to report on the number of comments per work item or the number of customer comments per work item or even to search for key words within the action log comments. This was not possible with the Cireson Analytics solution… Until now.
Introducing the Data Warehouse replacement.
Starting with version 11.2.0 of the Cireson Analyst portal the Action Log for work items is now synchronised to the Analytics database and can be leveraged via simple SQL queries (such as these.) via the Cireson Analyst Portal. This now gives report authors the full range of data that they could expect in the original Data Warehouse solution without the need for the extra servers or having to navigate the SSRS Report Builder to develop reports.
In addition, any class extension that an organisation may add to a class can now be synced into the Analytics database for easy reporting. Making sure no data is left behind.
As the ETL jobs that are used by the Cireson Analytics app are built within SQL as SQL Agent Jobs, the processing effort is significantly less and therefore fewer resources are needed to provide the same solution as the out of box Data Warehouse.
So why would you stay with the Out of Box solution?
At this stage there are no OLAP cubes supported within the Cireson solution and some organisations like the ability to be able to slice this data within Excel Pivot Tables.
I’ve heard organisations say that they have historical data that they do not wish to lose by “turning off” the data warehouse solution. However, when turning off the Data Warehouse ETL services and removing the Management server, the data contained within the Data Warehouse does not go away, but rather can be joined to the Cireson Analytics data via Power BI or simple SQL join queries to report across both databases, giving organisations the best of both worlds.
Finally, I think it is important to mention, the only other piece of data that could be derived from Data Warehouse alone is the Entity Change Log (ECL) data. This allows you to write reports about when items change value allowing you to gather metrics on how long an item remained in a given status, or how long between values changed etc.
Conclusion
Unless you have very specific requirements around your reporting that only the SCSM data warehouse can provide, or if you are wanting to aggregate all of your organisations System Centre data into a single data warehouse, then it’s time to make the shift to Cireson Analytics and regain some computing resources (and $) for your organisation.