Design Decisions

Design Patterns for the Enterprise Data Warehouse

Design Patterns are design decisions, or patterns, that describe the ‘how-to’ of the Enterprise Data Warehouse (and Business Intelligence) architecture. They specify the rules the architecture has to play by, and they set the stage for (future) solution development. I refer to this as the ‘playing field’ or ‘the rules’.

Every layer in the architecture specifies which concepts and steps are to be carried out in that particular layer, but how to exactly define this is specified in the Design Patterns. The Design Patterns are therefore both the starting point for the solution design as the main tool of the Data Warehouse architect to maintain the system.

Design Patterns are fundamental concepts and contain (and explain) the design decisions and considerations made. They therefore have a huge impact on the technical implementation of the Data Warehouse.

A Design Pattern is usually not tool specific but can it can be; depending on the nature of the specific concept explained. It is advised however to keep the Design Patterns as detailed as possible without including tool dependencies. This way they will remain true even when the specific software solution (implementation) changes. The (tool)specific implementation or solution design can be added one level down in the documentation as Solution Patterns.

The following Design Patterns have been posted so far:

Design Pattern 001 – Essential ETL Process Requirements
Basic requirements for all ETL processes (mappings) in the Data Warehouse environment to ensure that the ETL architecture matches the overall architecture.

Design Pattern 004 – Handling Logical Deletes
Reasoning and examples to implement logical deletes in tables that track history.

Design Pattern 005 – Placeholders
Placeholders used to handle NULL business keys are documented in this Design Pattern. One of the examples of implementing the ‘hard’ business rules.

Design Pattern 020 – Loading Flat Files
Flat files which are loaded into a datawarehouse batch-wise are common, this design pattern shows the basic guidelines when dealing with flat files.

Design Pattern 021 – Definitions of History
One of the core functionalities of the Data Warehouse (presenting and integration being the others) is correctly tracking changes in data over time, even when the data is long gone from the original systems. This pattern describes how this is done in a future-proof and consistent manner.