The Service Manager Data Warehouse is a powerful tool but can be complex to work with and comes with a big footprint. We’ve worked with customers that wanted to drop their Data Warehouse but run into major reporting challenges retaining and reporting on data from work item class extensions. There are several ways you could approach this, and the solution we came up with was to create a new database for these extended properties to compliment Cireson Analytics. This example is high level, but hopefully gives you some ideas on how SCSM and Cireson products can be extended to suit your organization’s unique needs.
High Level Overview
The first step in this process is to create a new database (AnalyticsExtension in this case). To achieve this, you can either:
A) SQL Query > CREATE DATABASE AnalyticsExtension;
B) Open SQL Server Management Studio(SSMS) > Right Click Databases > New Database
Give the Database a name and then leave everything else as default.
The Second Step is to create a table in the new database. As mentioned previously there are two methods for achieving this.
A) SQL Query >
B) Using SSMS > Expand AnalyticsExtension > RC Tables > New > Table
Now that you have the new Database (AnalyticsExtension) and Table (WorkItem) the next step in this process is to fill the database with data from the ServiceManager DB. Before we populate the data, lets talk about what the goal is with our next two steps. We are planning to create an SQL Server Agent job per class. So, Incidents will have a dedicated job that performs two steps. Step 1 will bring in new Incidents from the ServiceManager database, and the Step 2 will update existing Incidents in our WorkItem table based on their last modified date.
A) In SSMS > SQL Server Agent > RC Jobs > New Job
B) Give you new Job a title such as “Company Incident Job”
C) Select the steps tab
D) Give your Step 1 a name “Insert New Incidents”
E) Insert a query you have written to Copy all Incidents from ServiceManager DB to AnalyticsExtension DB
F) Select the Advanced Tab and make sure to select the proper “On Success Action”
G) Next, repeat the process of d through f to create Step 2 for “Update Incidents”
Reporting
Now if you were to do a very quick query joining the CiresonAnalytics DB against the newly created AnalyticsExtension DB you would have all the class extensions displaying.
You are now ready to create a Cireson Dashboard or PowerBI report to display your specific fields.
Conclusion
If you have ever thought about breaking up with the Service Manager Data Warehouse for one reason or another, consider adding Cireson Analytics instead! The roadblock tends to be critical data points and finding a way to surface those in alternative solutions. The above was a simple real-world solution to use in tandem with the Cireson Analytics Database. If your organization wants to move to alternative reporting solutions but have one or two requirements holding you back, getting there might be easier than you think!
Feel free to reach out to us at team@cireson.com if you have any questions!
