When I’m designing a Microsoft System Center Service Manager Solution (SCSM) for customers and we get to the topic of reporting, questions inevitably arise about data warehouse. Is it needed?
The main concern that customers have is the number of resources a data warehouse consumes. Microsoft recommends at least two but more often three servers to support:
- Management server
- SQL database
- Reporting Services
And this is for just for data warehouse alone. If this seems a bit excessive, then you would be right.
Microsoft Reporting Out-of-the Box Challenges
Microsoft’s solution for reporting from SCSM looks like this:
- Use a rationalized database (data warehouse).
- Use Extract, Transform and Load jobs (ETL) to maintain 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. The ETL jobs are traditionally done in SQL and scheduled as SQL jobs. Not so in the case of SCSM.
Within SCSM architecture, the ETL jobs are handled by the data warehouse management server and the ETL jobs are services that run outside SQL. As such, there is some application overhead that must be worked into the code. This is preferable to leveraging SQL jobs in the SQL implementation housing the data, which introduces additional computation time and effort requiring more resources.
The reason this solution may feel obtuse is that it was originally designed to consolidate all three into a single data warehouse for reporting:
- Microsoft System Center Service Manager (SCSM)
- Microsoft System Center Operations Manager (SCOM)
- Microsoft System Center Configuration Manager (SCCM)
However, most organisations tend to report out of the respective solutions rather than the single one. The result, regardless of path chosen, is that the SCSM data warehouse has a very large administrative overhead for producing reports.
Cireson addressed most customer reporting requirements and SCSM gaps with the release of Cireson Dashboards and Analytics in the Cireson SCSM Analyst Portal. For some time now, Cireson has been developing for the Analytics offering integrated into Cireson’s Analyst Portal and the Team product suite.
Introducing the Data Warehouse Replacement: Cireson Analyst Portal
Starting with Cireson Analyst Portal V11.2.0, the action log for Work Items is now synchronised with the Analytics database, and it can be leveraged via the Cireson Analyst Portal using simple SQL queries, such as these. Now report authors have the full range of data expected in the original data warehouse solution without the need for the extra servers or SSRS Report Builder.
In addition, any class extension added to a class is automatically synced into the Analytics database for easy reporting, leaving no data behind. This addresses one of the longest standing administrative pain points: losing data if you drop a class extension. You won’t: analytics are retained.
As the ETL jobs used by the Cireson Analytics app are built within SQL as SQL Agent Jobs, the processing effort is significantly less. Fewer resources are needed to provide the same solution as the out-of-the box data warehouse.
So Why Would You Stay with the Out-of-the Box Solution?
At this stage, there are no OLAP cubes supported in the Cireson solution, and some organisations like to be able to slice this data in Excel Pivot Tables.
I’ve heard organisations say that they do not wish to lose historical data by turning off the data warehouse solution. However, when the data warehouse and ETL services are turned off and the management server is removed, the data contained in the data warehouse does not disappear. It can be joined with Cireson Analytics data via Power BI or simple SQ and reported across both databases. This way, organisations get the best of both worlds.
Finally, I think it is important to mention that the only other piece of data that could be derived from data warehouse alone is the Entity Change Log (ECL). It 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 the duration between changed values, etc.
Conclusion
Unless you have very specific reporting requirements that only the SCSM Data Warehouse can provide, or if you want to aggregate all SCSM data into a single data warehouse, then it’s time to make the shift and regain some computing resources—and money—for your organisation.
Schedule a demo to see Cireson Analyst Portal in action.
