My first Publication Agile-Data-Warehouse-Design-eBook | Page 302

282
Chapter 9
Why and How
Solution
An audit dimension
provides summary
ETL metadata in a
dimensional format
Figure 9-19 shows an AUDIT dimension that presents ETL statistics and data
quality descriptions in a dimensional form—tied directly to the facts—where they
can be queried by BI users, and used to provide additional context within the body
of reports or as header or footer information. The AUDIT dimension surrogate
key—AUDIT KEY—represents each execution of an ETL process. For example, if
there are five different ETL modules that support the nightly refresh of the data
warehouse, there would be at least five new rows added to the AUDIT Dimension
each night. Each of these rows would have a unique AUDIT KEY, which would
appear in the fact table (and dimension) rows that were created or updated by the
given ETL execution—providing basic data lineage information on each fact (and
dimension): where it came from, and how it was extracted and loaded or last
updated.
Figure 9-19
AUDIT dimension
Audit dimensions
can be expanded to
provide basic data
quality indicators
Audit dimensions
turns metadata into
normal data that can
be used to query the
Figure 9-19 also shows additional indicator attributes (in bold) that describe data
quality and completeness. The Audit dimension would contain additional rows for
each ETL module so that unusual facts records can be explicitly flagged if they
contain out of bounds (defined by example data, data profiling, or historical
norms), missing, adjusted or allocated values.
Audit dimensions leverage the value of ETL metadata. By making it available
within each star schema they elevate metadata to the position of “real” data—
another how or why dimension that BI users can use to group or filter their reports
to help explain the figures they see.
facts
You can find additional information on creating and populating Audit dimensions
in The Data Warehouse ETL Toolkit, by Ralph Kimball and Joe Caserta (Wiley,
2004) pages 128–131