Incorporate Data Warehouse QA Checks into MOM 2005

Traveling the potholed road of software requires the right monitoring tools. If you’ve installed a tool such as Microsoft Operations Manager 2005 (MOM), rest assured; your tool chest is well stocked. Sometimes, though, MOM needs a little help.

Take, for example, data warehouse quality assurance (QA) checks (which Ralph Kimball calls flash totals in his book The Data Warehouse Toolkit). If you’ve added QA checks such as flash total calculations to your SQL Server Integration Services package, you probably want to check the totals every time your Integration Services package or other Extract, Transact, and Load (ETL) process completes.

Although a database administrator or application administrator can view daily flash total reports, he or she often prefers automated monitoring. No administrator wants to view the auditing daily when all processes likely will function normally. However, when that one-in-a-hundred instance does occur, an administrator must know immediately.

MOM seems like the perfect solution, but how do you get flash totals and other data warehouse QA measures into MOM? This tutorial shows you how, so you can use MOM’s rules and notification capabilities to run automated checks.

Quality Assurance and Data Warehousing

Before dissecting the coding solution, an overview of data warehouse QA and a brief description of MOM capabilities is useful for the solution.

As previously stated, flash totals and other aggregate value checks are a component of the data warehouse QA process.

An ETL process copies data from a transaction-processing system to a data warehouse. During the process, record counts (flash totals) or aggregated key measures of the source (transaction-processing system) and the destination (data warehouse) are generated for analysis. Part of the data warehouse QA process compares the totals and performs appropriate actions if the source and destination totals do not match.

Non-matching QA values can occur for many reasons. For example, a change to a business process may require changes to the underlying assumptions of an ETL process. As a result, the ETL process may omit records or include more records than needed.

To perform automatic checking, you need a tool to automate the QA checking and generate notifications should the ETL loading fail QA tests. You could build your own application, but if you’re using MOM, a custom application isn’t necessary.

MOM Primer

MOM serves as a sentinel for your Windows applications and servers. MOM monitoring capabilities range from scanning errors in event logs to posting notifications based on codified rules. A discussion of all MOM capabilities is beyond the scope of this article. (For more information on MOM’s capabilities, check out “Monitor Your .NET Applications with Microsoft Operations Manager 2005“.) Instead, it covers only the features that are relevant to the QA solution.

MOM is a Windows service you load on a server. It allows an administrator to generate notifications and run scripts based on things such as Windows events and Windows Management Instrumentation (WMI) values (more on WMI in a moment). You can use MOM custom rules to write data to WMI and then program MOM to examine values in WMI and generate an e-mail notification based on its findings.

Windows Management Instrumentation Primer

WMI exposes data in the form of WMI classes. WMI classes resemble property-only classes you would implement in the .NET Framework. Class property data types are similar to other .NET primitive data types, ints, strings, and so forth. (For a complete overview of WMI, check out the WMI chapter in Mark Russinovich’s and David Solomon’s Microsoft Windows Internals.)

For the QA solution, you would use the .NET Framework Instrumentation classes to build a WMI dynamic provider. WMI dynamic providers work differently than other providers. When querying values from a dynamic provider class, WMI communicates directly with the provider rather than gathering data that was generated by the provider and stored in the repository. Thus, when you use the .NET Framework to create a provider, you need a running application to supply WMI values.

Among the items MOM monitors in WMI are WMI events and data (instances). Events are similar to what you add to the event log, but they are much richer. Instances allow you to add data to WMI. As such, the solution in this article will use WMI instances.

WMI Instance Class

You can implement a WMI instance using the .NET Framework in the following two ways:

  1. Inherit from an instance class
  2. Decorate a .NET class with some attributes

WMI attributes and classes are located in the System.Management.Instrumentation namespace. (For more details on this namespace, see the MSDN article “System.Management Lets You Take Advantage of WMI APIs within Managed Code“.

Whether you implement a WMI instance using a class or attributes depends on your application requirements. This tutorial uses attributes because most samples I reviewed used attributes. The following snippet defines the instance class:

public class AggregateValueWMIInstance

All public properties exposed by the class will be visible in WMI unless you add explicit attributes to exclude the property, as in this example:

public Guid InternalIdentifier

A directive to locate the class in a particular WMI namespace is the only other statement recommended to complete the WMI class definition:

[assembly: Instrumented("Root/CroweV2")]    //Place in CIM under crowe

The .NET Framework documentation shows how you can use the installer.exe utility to make the class available in WMI. The following code sample automatically installs the class in WMI using the RegisterAssembly static function in the Instrumentation class whenever the Publish function is first executed:

private void RegisterWMIClass()
   Assembly curAssm;

   curAssm = Assembly.GetAssembly(this.GetType());

   if (System.Management.Instrumentation.Instrumentation.
      //Cool; it's already registered in WMI
   else    //Well then, register it


Now, all you need are the code to write the instance to WMI and the MOM configuration to read the information.

More by Author

Must Read