In this, and in the next series of posts, I will be exploring the 34 subsystems of ETL Data Integration (note: there used to be 38) as defined by the Kimball Group. I introduce the subsystems in this post, and then I will discuss how each fits (or does not fit) into both a SQL Server environment and in a hand-coded environment using VFP9.

The subsystem concept is a best-practice initiative formulated by The Kimball Group to help organizations design effective and efficient Data Integration environments for Data Warehousing using the Dimensional Model. As you’ll see, the subsystems are well-defined, battle-tested, and represent a relatively complete picture of what is needed for ETL Integration.

The Kimball Group categorizes the subsystems into 4 distinct groups: Data Extraction, Cleansing and Conforming Tasks, Data Delivery, and Management. Management is an often forgotten aspect of ETL (If the acronym was ETLM, we’d be OK!). You’ll notice that there are quite a few management-specific subsystems.

    Data Extraction

  1. Data Profiling
  2. Change Data Capture (CDC)
  3. Extraction

    4. Cleansing and Conforming Tasks

  4. Data Cleansing Subsystem
  5. Error Event Management
  6. Auditing
  7. Removing Duplicates
  8. Data Conformance

    9. Data Delivery

  9. Slowly Changing Dimensions (SCD)
  10. Surrogate Key Generator
  11. Hierarchy Manager
  12. Special Dimensions Manager
  13. Fact Table Builders
  14. Surrogate Key Management
  15. Bridge Table Builder
  16. Late Arriving Data Handler
  17. Dimension Manager
  18. Fact Table Provider
  19. Aggregate Generation
  20. OLAP Cube Builder
  21. Data Propagation Manager

    22. Management

  22. Scheduler
  23. Backup System
  24. Recovery and Restart
  25. Version Control
  26. Version Migration
  27. Work flow Monitor
  28. Sorting
  29. Data Lineage and Dependency
  30. Problem Escalation
  31. Paralleling and Pipelining
  32. Security
  33. Compliance Manager
  34. Metadata Repository

Data Integration architects have a lot to be thankful for the work and effort put forth by the Kimball Group. As you can probably guess, to do ETL right, you’ll need adequate time and resources. It is no wonder that data integration is by far the most time-consuming aspect of data warehousing.

In subsequent articles I will define each of these systems, provide examples, and discuss how they can be accomplished in SQL Server as well as VFP9. I have a lot of work to do, so stay tuned!