Design of Service Manager 2010 data warehouse differs from Operations Manager 2007 architecture
In Service Manager 2010, the data warehouse is implemented a bit differently than in Operations Manager 2007. Let’s take a look at some of the differences and potential implications.
Service Manager 2010 uses the data warehouse for long-term storage of data, such as incidents. This makes it the data source for reports, since old data is groomed from the Service Manager database on a recurring interval – helping to maintain performance and keep the database at a manageable size.
The Service Manager data warehouse is in a separate management group from the other Service Manager components. Because it is implemented using the same Windows services (the System Center Data Access Service and the System Center Management Service), it architecturally cannot be in the same management group. However, the question really would be why was it implemented this way, which is different from the Operations Manager data warehouse. The reason most commonly given is that the Service Manager data warehouse will eventually become the System Center data warehouse, so it needs to be in its own management group.
The Operations Manager data warehouse is a single physical database. The Service Manager data warehouse is a logical database, consisting of three databases with different roles:
- DWStagingAndConfig
- DWRepository
- DWDataMart
These three databases enable organizations to optimize their hardware environments, as high-volume environments will place DWStagingAndConfig and DWRepository on hardware optimized for read and write I/O, and optimize the DWDataMart for read I/O only.
When data is moved from the Service Manager database to the data warehouse, the Extract job writes it to the DWStagingAndConfig database. The Transform job takes that data and does required manipulations to get it into the proper format for reporting, writing it to the DWRepository. Finally, the Load job queries data from the DWRepository and inserts it into the DWDataMart. The DWDataMart is the database used for all end-user reporting needs.
Service Manager uses the Kimball approach to dimensional modeling to optimize the data warehouse for performance and ease of use. The tables in the DWDataMart database are logically grouped into subject matter areas that resemble a star when laid out in a diagram; these are often called star schemas:
- In the center of the start is a Fact table. Fact tables represent relationships, measures, and key performance indicators. These are normally long and skinny as they have few columns but many transactions.
- The Fact table joins to Dimension tables, which represent classes, properties, and enumerations. These tables usually have fewer rows than fact tables but are wider as they have those attributes by which users slice and dice reports.
- A special type of dimension table is an Outrigger, which hangs off another dimension table for performance and usability reasons.
In addition, management packs must be synchronized from the Service Manager CMDB database to the data warehouse using the MPSync job.
The more advanced design and implementation of the Service Manager data warehouse position it to become a data warehouse for System Center moving forward.
