The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

6.4. Tying It All Together

Throughout this chapter we've been focusing on the details of data flows and Control Flows. We've continually deferred discussion about over-arching issues like the audit system, and communication between master packages and child packages. It's finally time to tackle these topics.

6.4.1. The Audit System

A decade and more ago, our DW/BI systems had primitive or, more often, no auditing systems. In the current regulatory environment, we need to do better. You could spend almost as much time and energy developing an auditing system as building the core system itself. The auditing system that we present here is better than most systems we've seen, but by no means is it as extensive as we can envision. It strikes a balance between ease of implementation and robustness, and will be good enough for most DW/BI systems.

NOTE

KIMBALL METHOD ETL SUBSYSTEM

This section describes techniques relevant to Kimball Method ETL Subsystem #6 Audit Dimension Assembler: Assembly of metadata context surrounding each fact table load in such a way that the metadata context can be attached to the fact table as a normal dimension.

We have several design goals for the audit system. We want to be able to answer the following questions:

• How did each row initially enter the DW/BI system?

• What process most recently updated each fact row?

• How can you locate all the fact rows loaded today? How would you back out a load?

• Was this row loaded through the standard process, or through an ...