Table of Contents

Data Warehousing - 34 Kimball Subsytems

About

This page takes back the Kimball Datawarehouse 34 Subsystem as a table of content and links them to a page on this website.

For Kimball, the “ETL” process has four major components:

Each of these components and all 34 subsystems contained therein are explained below.

Components

Extracting

The initial subsystems interface to the source systems to access the required data. The extract-related ETL subsystems include:

Cleaning and conforming data

These critical steps are where the ETL system adds value to the data. The other activities, extracting and delivering data, are obviously important, but they simply move and load the data. The cleaning and conforming subsystems change data and enhance its value to the organization. In addition, these subsystems should be architected to create metadata used to diagnose source-system problems. Such diagnoses can eventually lead to business process reengineering initiatives to address the root causes of dirty data and to improve data quality over time.

The ETL data cleaning process is often expected to fix dirty data, yet at the same time the data warehouse is expected to provide an accurate picture of the data as it was captured by the organization's production systems (see related article, “Data Stewardship 101: First Step to Quality and Consistency). It's essential to strike the proper balance between these conflicting goals. The key is to develop an ETL system capable of correcting, rejecting or loading data as is, and then highlighting, with easy-to-use structures, the modifications, standardizations, rules and assumptions of the underlying cleaning apparatus so the system is self-documenting.

The five major subsystems in the cleaning and conforming step include:

Delivering: Prepare for presentation

The primary mission of the ETL system is the handoff of the dimension and fact tables in the delivery step.

There is considerable variation in source data structures and cleaning and conforming logic, but the delivery processing techniques are more defined and disciplined. Careful and consistent use of these techniques is critical to building a successful dimensional data warehouse that is reliable, scalable and maintainable.

Many of these subsystems focus on dimension table processing. Dimension tables are the heart of the data warehouse. They provide the context for the fact tables and hence for all the measurements. For many dimensions, the basic load plan is relatively simple: perform basic transformations to the data to build dimension rows to be loaded into the target presentation table.

Preparing fact tables is certainly important as they hold the key measurements of the business that users want to see. Fact tables can be very large and time consuming to load. However, preparing fact tables for presentation is typically more straightforward.

The delivery systems in the ETL architecture consist of:

Managing the ETL environment

A data warehouse will not be a success until it can be relied upon as a dependable source for business decision making. To achieve this goal, the ETL system must constantly work toward fulfilling three criteria:

The ETL management subsystems are the key architectural components that help achieve the goals of reliability, availability and manageability. Operating and maintaining a data warehouse in a professional manner is not much different than other systems operations: follow standard best practices, plan for disaster and practice (see related article, “Don't Forget the Owner's Manual”). Many of you will be very familiar with the following requisite management subsystems: