The System Center Platform in Service Manager Part 6: The Data Warehouse

The System Center Platform in Service Manager Part 6: The Data Warehouse

  • Comments 3
  • Likes

This post is a continuation in the series which describes the System Center common platform components implemented in Service Manager. Previous posts:

The System Center Platform in Service Manager Part 1: Introduction

The System Center Platform in Service Manager Part 2: The Model-Based Database

The System Center Platform in Service Manager Part 2: The Model-Based Database - Try It!

The System Center Platform in Service Manager Part 3: The System Center Data Access Service

The System Center Platform in Service Manager Part 3: The System Center Data Access Service - Try It!

The System Center Platform in Service Manager Part 4: The System Center Management Service

The System Center Platform in Service Manager Part 4: The System Center Management Service – Try It!

The System Center Platform in Service Manager Part 5: The Management Configuration Service 

The data warehouse in System Center Service Manager provides three primary functions:

  1. Offload data from the main Service Manager database to improve performance of the Service Manager database
  2. Long-term data storage
  3. Provide data for reports

The data warehouse that ships with System Center Service Manager is actually its own management group.  It has essentially all the System Center common platform pieces that you would see in a System Center product (SCOM, SCE, SCSM) built on the common platform:

  • Model-based Database for storing configuration information about the data warehouse and for staging the data after it has been extracted from the ‘ServiceManager’ database.  In the data warehouse management group, this instance of the mode-based database is named ‘DWStagingAndConfig’.
  • Management Server
    • System Center Data Access Service
    • System Center Management Service
    • System Center Management Configuration Service

In addition to a complete stack of the System Center common platform, the data warehouse has two other databases:

  • DWRepository – this is where we store the transformed data which is optimized for reporting purposes
  • DWDataMart – we load transformed data into this database and ultimately this is the database that the reports query

This data warehouse is completely new.  We did evolve most of the common platform components in Service Manager from Operations Manager 2007, but the data warehouse is one area where we started from scratch.  We wanted to build a data warehouse which was:

  • fully extensible via management packs
  • designed as more of a true data true warehouse – star schema, facts, dimensions, etc.
  • designed for very large scale

At the same time, we wanted to preserve some of the concepts of the Operations Manager warehouse – for example the ability to send data from multiple management groups to a single central data warehouse installation.

The data warehouse portion of Service Manager is not yet used in any other System Center product, but it was built with the intention of being a redistributable platform component that other System Center teams could deliver as the data warehouse solution for their products.

The Automated Data Warehouse Infrastructure

Chad has already done a good job of describing providing a high level overview of the data warehouse and the various system processes of Management Pack Synchronization, Deployment and Extract/Transform/Load (ETL) that happen in the data warehouse so I’ll just link to those here:

Data Warehouse and Reporting Overview

Anatomy of Management Pack Synchronization

Anatomy of Data Warehouse/Reporting Deployment

Anatomy of Extract, Transform, Load (ETL)

A Few Important Note on Deploying the Data Warehouse

It’s important to note a couple of important things regarding the deployment of the data warehouse:

  • Because the data warehouse is really it’s own management group, the data warehouse management server cannot be installed on the same server as a Service Manager management server. 
  • You can however install all of the databases on a single SQL Server instance if you wanted to (this is not a good configuration for a production deployment). 

See this blog entry for more information on how you can install both Service Manager and the Data Warehouse on a single physical server for testing purposes using virtualization.

Brief Introduction to the Reporting Infrastructure

Lastly, I want to briefly introduce the Reporting Infrastructure.  The Reporting Infrastructure is built on SQL Server Reporting Services.  The SQL Server Reporting Services infrastructure provides for a lot of things including report level security, report subscriptions, browser-based access to reports, linked reports, and much more.  I wont go into a lot of detail here because I assume you are already familiar with SQL Server Reporting Services.

What is important to know about the implementation of the Reporting Infrastructure in Service Manager is that it is pretty similar to the experience you have in Operations Manager.  There is a Reports workspace which shows you the catalog of reports which users can run on demand.  Also like Operations Manager, the reports can be run in context.  For example, I could select a computer in a view in the console and run the Computer Details report about that computer.

So – here is what our updated architecture diagram looks like (in a “thumbnail” size view).  Since the architecture diagram is really getting too large to show in the blog itself, I’ve provided a Visio file with the architecture diagram in it.

image

Now, you can Try It! by reading the next post in this series.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Travis, "I’ve attached a Visio file with the architecture diagram in it": where is the link to this file?

    Thanks!

  • @Mike - not sure what happened to the Visio file.  I've uploaded it to our SkyDrive now though and provided a link to it above.

    cid-17faa48294add53f.office.live.com/.../ArchitectureDiagram.vsd

  • Can you share us the Visio dig associated to this blog, at present it is not available in SkyDrive