Reporting Database Schema

The MOM 2005 Reporting Database features a highly normalized schema optimized for reporting and analysis.

It is recommended that all queries against the Reporting Database are restricted to the documented presentation area views. These views are easily identified by their names which follow the pattern: SC_*_View except for the views with names of type SC_Rel_*_View. Broadly speaking, there are four types of documented presentation area views:

• Fact Views: represent facts stored in the Reporting Database. Fact view names follow the pattern: SC_[Fact Name]Fact_View, where Fact Name is the name of the fact represented by the view.

• Dimension Views: represent common concepts that are shared by multiple facts and fact types. Dimension view names follow the pattern: SC_[Dimension Name]Dimension_View, where Dimension Name is the name of the dimension represented by the view.

• Class views: specialized views that are created based on classes that are defined as part of Management Packs. Each class defined by a Management Pack corresponds to a single class view. Class view names follow the pattern: SC_Class_[Class Name]_View, where Class Name is the name of the class represented by the view.

• Relationship views: specialized views that are created based on class relationships that are defined as part of Management Packs. Each relationship, defined by a Management Pack, corresponds to a single relationship view. Relationship view names follow the pattern: SC_Class_Rel_[Source Class]-[Target Class]_View where Source Class and Target Class are the name of the source and target classes related by the view. Each relationship view represented the association between a single instance of the source class with a single instance of the target class.

Property names follow the following conventions:

• Properties that refer to external dimensions are appended with the suffix _FK (e.g. Computer_FK). In this case, the value of the property is the same as the value of the SMC_Instance_ID property in the corresponding row of the dimension table.

• Properties that form the primary key of a dimension view are appended with the suffix _PK (e.g. ComputerName_PK)

• Each view contains a property called SMC_Instance_ID. This property is a unique identifier for each row in the view. This property also serves as the primary key for fact views.

Example - Alert Properties

This example shows how to query the Reporting Database to return a list of alerts, with the properties of the alerts similar to the view in the MOM Operator console. Specifically, it returns a list of alerts, and the following properties:

• Severity

• Time

• Computer

• Name

• Repeat Count

Each row in the Alert view contains information about a single alert. Data that is unique to individual alerts, such as the Alert ID and Repeat Count is represented as a column in the Alert view. Data that can be shared between alerts, such as information about the computer on which the alert was raised, is stored in the Related dimension views. The Alert view contains references to these related tables. For example, the property Computer_FK contains the instance ID of the row in the Computer view, which contains the information about the computer on which that alert was raised. Multiple alerts can refer to the same computer.

To query the Reporting Database to return the properties, the Alert view must be joined with the other related views that contain the properties that should be returned.

Table 8.1 Relationship of various parts of a report

Therefore, the query needs to join the Alert fact view with the Alert level dimension view and the Computer dimension view. The SQL query to do this is::

SELECT ALD.AlertLevelName AS Severity,
 AF.LocalDateTimeRaised AS Time, CD.FullComputerName AS Computer, AF.AlertName as Name, AF.RepeatCount AS RepeatCount
FROM SC_AlertFact_View AF
 INNER JOIN SC_AlertLevelDimension_View ALD ON AF.AlertLevel_FK = ALD.SMC_InstanceID
 INNER JOIN SC_ComputerDimension_View CD ON AF.Computer_FK = CD.SMC_InstanceID

Example - Classes and Relationships

This example shows authoring of a query against the operating system and the Installed Software classes, to return the operating system version, application name and installation date for all software installed on a computer. Specifically, this data is returned in the following form:

• Server Name

• Operating System Name

• Application Name

• Installed Date

Inspecting the Reporting Database schema diagram, yields the following information:

Table 8.2 Relationship of various parts of a report

Therefore, the query needed to join the three views is the following:

SELECT OS.[Server Name], OS.[Operating System Name], ISw.[Application Name], ISw.[Installation Date]
FROM SC_Class_OS_View OS
 INNER JOIN [SC_Class_Rel_OS-Installed Software_View] OSIS ON OS.ClassInstanceID = OSIS.SourceClassInstanceID
 INNER JOIN [SC_Class_Installed Software_View] ISw ON OSIS.TargetClassInstanceID = ISw.ClassInstanceID