The last article in this series covered customization of System Center 2012 Service Manager through Management Packs, the Cloud Services Process Pack and the IT GRC pack, this sixth and last part will dive into the Data Warehouse in SCSM 2012 and why it’s the central location of all IT related information in your business, not just from SM 2012.
In the previous generation of System Center there was an optional component called System Center Reporting Manager; apparently Microsoft had only about 80 customers who actually implemented this solution. The goal was right; have ONE central database of ALL IT related information gathered through all the different System Center and third party management tools and build a data warehouse for long term reporting but apparently the implementation wasn’t right. In the SC 2012 wave SCSM is this central database, through the Data Warehouse server and associated database store.
The data warehouse is built on Kimball’s approach for a data mart with facts tables that contain numbers of incidents or numbers of SLA breaches for instance and dimension tables that describe user accounts, geography or computer accounts as examples. The benefits of having the DW as a separate database for long term storage is that running reports doesn’t impact production servers.
Up to five service manager instances can aggregate their data in one data warehouse. The data in the DW is also optimized for querying through a step by step process which runs every 24 hours. First the MP Sync job replicates the MPs installed on the SCSM servers to the DW server, and then it enumerates any reports contained in these MPs. This is followed by a standard ETL process which Extracts the relevant data from the SM database and moves it into temporary staging and configuration storage followed by Transformation which converts the data to the dimensional model. An example of this transformation is that different incident types are tracked in the CMDB, whereas in the DW they’re all flattened into one type. The last stage is the Load process which moves the information into the DW database.
Grooming is the process of moving data from the CMDB to the Data Warehouse SQL databases, there are different defaults for data retention (incidents are kept for 90 days, most other data for 365 days) but the data is also synchronized to the DW to facilitate reporting. Note that it can take up to an hour for new data to show up in the reports.
Anyone who was involved with Business Intelligence many years ago when it was hard and required expert skills to massage the data will be amazed by how easy it is now.
The true power of the DW comes from the built in cubes that allows you to use any Business Intelligence (BI) tools in the Microsoft stack, most notably Excel, to slice and dice the data to create exactly the reports you need. There are cubes for software updates, power management, and service manager configuration / work items and so forth. Most importantly you can publish these Excel spreadsheets in SP or to file shares and allow end users to manipulate the cube data to suit their own reporting needs. Spreadsheets will update the data from the DW server when they’re opened. You can also use Performance Point, a part of SP Enterprise to interact with the cubes in the DW.
For those with developer skills creating your own cubes isn’t that hard; Travis Wright held an excellent session at MMS 2012, called SD-B312 in the Service Delivery and Automation track covering this and other DW topics. There’s also a Visual Studio (VS) Authoring Extensions for System Center 2012 – Operations Manager pack that adds intellisense and templates for VS for SCSM development (even though it says it’s for OM).
The power of Service Manager – All your IT data in one place.
Not all data has to pass through the CMDB before it ends up in the DW, it makes sense for certain data sources to deposit data directly into the DW. Examples include compliance data, alerts in OM and power management settings, things that SCSM doesn’t care about but is good to have included in your overall IT reporting.
Here’s a tip, if you’ve got no data in your reports; disable all the DW jobs except for the MP Sync job, then run this job manually followed by running the other sync jobs manually as well. This should populate your reports with data.
SCSM is certainly the hardest component in the SC 2012 suite to get to grips with. Unless you’re a process junkie who drinks ITIL for breakfast it’s hard to get excited about a really good way to track incidents and service requests. There’s no doubt however that a properly implemented service catalog and automated processes can free up IT Professional’s time tremendously, perhaps to get on with learning the cooler features of VMM, OM and Orchestrator instead. SCSM has a lot of potential to help improve the efficiency and standardization of an IT department, and thus improve the entire business if implemented well.
For planning a SCSM 2012 implementation the recently released Infrastructure Planning and Design (IPD) document is invaluable.