Service Manager Data Warehouse and Reporting

Service Manager Data Warehouse and Reporting

  • Comments 7
  • Likes

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.


Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • This post is a continuation of the series started with The System Center Platform in Service Manager

  •   In my last post I gave an overview of some of the components that make up Data Warehouse and Reporting.

  • All I want is a report where the end user in SCSM can select a bunch queries to get the desired data they want. For example, to search for change requests... I want a report where the user can select the following queriable data:

    Change Request Area

    Change Request Priority

    Change Request Impact

    Change Request Title (Contains Text)

    Scheduled End Date (Range)

    Actual End Date (Range)

    etc etc etc

    Why is this so impossible? My SQL guy wants to kill me right now beause the databases with this tool look like spaghetti. Why is this so complex and hard to do?????

  • How do I give a manager access to the Reports?

  • Am I blind?  For the life of me, on these tech net blogs, I see no useful navigation.  Some bloggers mention in the first sentence a previous or future post and link to it.  There are no 'Previous' or 'Next' links, or even just a list of titles... some have a 'previous posts' lists, but not all, and if the previous sequential posts go back beyond the five or so links in the previous posts list, your out of luck.  So, for instance, I landed on this page by clicking the "In my previous post..." links.  But now that I'm here on this page, all I see is "in my next post..." but absolutely no navigation to get to the next post...  I know it exists, since I landed here from it, but if I got here via a search, well, my only assumption is that the blogger has not yet created the next post.

  • If you add fields to existing classes such as Activity or Problem should they come through in the DW tables automatically?  Mine aren't but I cannot find what to do to update the DW Schema.  Any ideas?

  • @Rob -

    Both the ActivityDim and ProblemDim are defined as HierarchySupport="IncludeExtendedClassProperties" so any properties that you add to the Activity or Problem class should be included in the ActivityDim and ProblemDim.  Make sure that you seal your class extension management pack before you import it into SCSM.