Microsoft & .NET.NETIncorporate Data Warehouse QA Checks into MOM 2005

Incorporate Data Warehouse QA Checks into MOM 2005 content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Hosting the WMI Class

As previously stated, the WMI support built into .NET is dynamic. So, you need running code to host the WMI class. The sample solution uses a desktop application. Your implementation probably will create a Windows service. (For an introduction to building a Windows service, read “Creating a Windows Service in .NET“.)

In the sample solution, AggWMIInstWriter reads the measures from a SQL database, populates an instance of AggregateValueWMIInstance, and publishes the class to WMI. The following code publishes the instance to WMI and in doing so makes the data visible to MOM:

private void UpdateNewMeasure(string measureKey,
AggregateValueWMIInstance inst)
AggregateValueWMIInstance instCur = null;
if (_mySortedList.TryGetValue(measureKey, out instCur))
instCur.Revoke(); //Hide the data from WMI
//It’s there, so replace it
_mySortedList.Add(measureKey, inst);
_mySortedList.Add(measureKey, inst);
inst.Publish(); //Display it in WMI

One interesting thing to note in the code is the use of the new .NET Framework 2.0 Generic class collections for type safety.

Now that the data has been loaded into WMI, the only task remaining is creating MOM rules to read the data.

MOM Implementation

Once you have data in WMI, implementing with MOM is straightforward. Adding WMI instance monitoring to MOM is beyond the scope of this article; the following summations share some of the key configuration dialogs:

  1. You use the MOM WMI numeric provider function. Configure the MOM numeric provider as shown in Figure 1.

    Figure 1: Configuration of MOM Numeric Provider

    You can change the example to sample more or less frequently.

  2. The next key to configuring MOM is to create a rule and select the provider you created in Step 1 (see Figures 2 and 3).

    Figure 2: MOM Performance Rule #1

    Figure 3: MOM Performance Rule #2

  3. Finally, use MOM to start sampling the data.

    Figure 4: Starting MOM

The complexity of your rule is limited by the capabilities of MOM. As previously stated, when a MOM rule executes, MOM can respond to it by sending notifications or running applications.

Preserve Data Warehouse Integrity

Flash totals and other QA checks are critical for preserving the integrity of your data warehouse. By using WMI and the .NET Framework, you can incorporate QA checks into your Microsoft Operations Manager rules and notifications.

Download the Code

You can download the code that accompanies the article here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at


Special thanks to Dustin Hannifin, my company’s MOM administrator, for his expertise and assistance with this article.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories