Data Warehouse Automation with erwin

Data warehouse projects have traditionally struggled with long implementation times. This increases the chance that business requirements will change while the project is still under way, putting schedule and budget goals at risk. A highly systematic model such as Data Vault offers substantial potential for automation.

Benefits of the Data Vault Methodology

Dan Linstedt developed Data Vault to shorten implementation times and make data warehouses easier to adapt to change. Its central design principle separates business keys, contextual information, and relationships into dedicated table types:

• Hubs contain stable business keys.
• Satellites hold descriptive and historized context.
• Links represent relationships between business keys.

This separation offers several advantages:

• Models are easy to extend and therefore support an agile approach.
• The resulting structures scale well.
• Load processes can be parallelized extensively because there are few synchronization points.
• Changes and history are easy to audit.

The Trade-Off

By separating information types and adding load metadata, Data Vault creates a very large number of tables and columns. Modeling effort increases even though much of the work consists of straightforward, mechanical steps.

That systematic structure is also what makes generation practical. When derivation rules can be described unambiguously, large parts of the modeling process can be automated and projects accelerated substantially.

Automation Potential Across DWH Layers

A typical data warehouse architecture includes the following layers:

• Source systems: operational platforms such as ERP or CRM solutions.
• Staging area: receives data from operational systems. Its structure largely mirrors the source and is augmented with technical load information.
• Core warehouse: integrates data from multiple systems. Under Data Vault it is divided into a raw vault and business vault. Business rules reside in the business vault, while the raw vault uses the simplest possible transformations.
• Data marts: follow reporting requirements and are often modeled as star schemas.

The staging area and raw vault can be derived from their preceding layers using clear rules.

The staging area and raw vault are particularly suitable for automation because their structures can be generated from the preceding model through well-defined rules.

Standard Modeler or Specialized Automation Tool?

Specialized data warehouse automation products address this potential directly. A standard modeling tool such as erwin Data Modeler offers a different set of strengths:

• Existing source-system models can be reused.
• Mature features support model comparison and standardization.
• A broad range of databases is supported.
• Interfaces allow models to be imported from other tools.
• Source systems or other warehouses are often already modeled in the same product.
• The model portfolio can represent the wider enterprise architecture, not only the data warehouse.
• Existing semantic information can be integrated through business glossaries.

MODGEN for erwin Data Modeler

The MODGEN add-in was developed to provide model-based generation within erwin. It integrates into the erwin interface and uses a workflow closely aligned with Complete Compare.

MODGEN provides capabilities including:

• generating staging and raw-vault models from the preceding layer,
• controlling generation through metadata stored in user-defined properties (UDPs),
• permanently or interactively excluding individual objects,
• and applying templates for standardized metadata columns.

Round Trips, Mappings, and Lineage

An iterative modeling process needs to be repeatable. MODGEN therefore compares the source and target model during every generation run. It displays differences and lets users select which changes to apply. The process remains round-trip capable as models are extended repeatedly.

Generation covers not only tables and columns as horizontal model structure. For every target column, it also documents the relationship to its source column as a data source. This provides a basis for producing source-to-target mappings. When source and target models are integrated into the Web Portal, the same information becomes available for impact and lineage analysis.