Service Manager Data Warehouse and Reporting

Hi, my name is Chad Rowe and I am a Program Manager for the System Center Service Manager team working on the data warehouse and reporting features. I’ve been with the team for about 8 months and prior to that I worked within Microsoft’s IT organization for 9+ years managing operations teams that supported our line of business applications.

Service Manager data warehouse and reporting will give folks the ability to report on operational data in near-real time. It will also have historical and analytical functions to drive strategic service delivery and operations decision making.

There are 3 different elements that make up data warehouse and reporting in Service Manager:

• Data warehouse – the processes and databases that manage the data from it’s raw source form to it’s end form that is used for reporting
• Reporting infra – the framework that is used for in-console reporting that includes the custom controls used to support the interface between reports and the DW
• Reports – consumed by the end user for operational and analytical data

The data warehouse is optimized for reporting purposes and the data warehouse can be extended via management packs. As Travis pointed out in his common platform post, data warehouse and the reporting infrastructure are a couple of those components that will be used in other System Center products over time which drives the need for both to be highly extensible.

I plan on doing a series of posts going into detail about the data warehouse and reporting features, starting with data warehouse and working my way up the stack. As mentioned in John’s setup post there are a couple of different components that make up the data warehouse at a high level, the data warehouse databases and the data warehouse management server.

Data Warehouse Databases - We have 3 databases, DWStagingAndConfig, DWRepository, and DWDataMart.

• DWStagingAndConfig is where we store all of our management packs, ETL (extract, transform, load) configuration, and other configuration information. It is also the initial store for the source data coming from the Service Manager CMDB.
• DWRepository is where we transform the extracted source data into the reporting optimized structure.
• DWDataMart is the database for our published data that gets consumed by the reports.

Data Warehouse Management Server - The DW management server controls all the workflow processes associated with the data warehouse. There are three main processes:

• Management Pack synchronization
• Data warehouse schema and report deployment
• Extract, Transform, Load

These workflow processes are what make the data warehouse tick, from bringing the management packs in to deploying the reports out and all the stuff in between. In my next post, we will dig into the MP synchronization process and see what it does and why it does it. Come back soon to see the MP synchronization diagram (below) in full detail. In the meantime, please leave comments with any questions you may have or any requests for coverage on a particular topic in the data warehouse and reporting areas of Service Manager.