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:

InstrumentationClass(InstrumentationType.Instance)]
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:

[IgnoreMember()]
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.
       IsAssemblyRegistered(curAssm))
   {
      //Cool; it's already registered in WMI
   }
   else    //Well then, register it
   {
      System.Management.Instrumentation.Instrumentation.
         RegisterAssembly(curAssm);
   }

}

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

Incorporate Data Warehouse QA Checks into MOM 2005

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.Remove(measureKey);
      _mySortedList.Add(measureKey, inst);

   }
   else
   {
      _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.
  2. [NumProviderProps.JPG]

    Figure 1: Configuration of MOM Numeric Provider

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

  3. 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).
  4. [PerfRuleProp1.JPG]

    Figure 2: MOM Performance Rule #1

    [PerfRuleProp2.JPG]

    Figure 3: MOM Performance Rule #2

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

    [StartingMOM.JPG]

    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.

Acknowledgements

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



About the Author

Jeffrey Juday

Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security. Jeff is a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

  • When it comes to desktops – physical or virtual – it's all about the applications. Cloud-hosted virtual desktops are growing fast because you get local data center-class security and 24x7 access with the complete personalization and flexibility of your own desktop. Organizations make five common mistakes when it comes to planning and implementing their application management strategy. This eBook tells you what they are and how to avoid them, and offers real-life case studies on customers who didn't let …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds