Kimball's DW/BI Architecture

Developed by Ralph Kimball, this architecture takes a bottom-up approach focused entirely on dimensional modeling and delivering immediate business value.

The architecture consists of four distinct and logically separated components:

Operational Source Systems

These are the core systems capturing the day-to-day transactions of the business (e.g., ERP, CRM, legacy systems).

• Purpose: Designed to run operations, highly normalized and optimized for fast inserts/updates (OLTP).

ETL System

The "kitchen" of the data warehouse. It is responsible for extracting source data, transforming it (cleansing, standardizing, resolving quality issues, generating surrogate keys) and loading it into the presentation area.

Data Presentation Area

Data is organized, stored and made available for direct querying by users and BI applications. This is the core deliverable.

• Dimensional Modeling: Data is structured into Fact tables (metrics) and Dimension tables (context), forming star schemas.
• Enterprise Data Warehouse Bus Architecture: Kimball builds the warehouse incrementally through business-process-oriented dimensional data marts. These marts are integrated through conformed dimensions, allowing them to function as a unified enterprise-wide architecture rather than isolated departmental silos. The Bus Matrix is the primary planning tool for identifying these integration points.
• Characteristics: Designed to simplify analytical querying and provide fast performance, the data is denormalized, contains deep history, and is strictly read-only for users.

Bus Matrix

The Bus Matrix is the foundational planning tool for Kimball's Enterprise Data Warehouse Bus Architecture. It maps business processes (rows) to the conformed dimensions (columns), showing which dimensions are shared across processes.

Business Process	Date	Product	Customer	Store	Employee
Retail Sales	X	X	X	X	X
Inventory	X	X		X
Customer Support	X	X	X		X

This helps teams identify integration points, build conformed dimensions once, and reuse them across multiple business processes.

A detailed implementation Bus Matrix extends this planning by documenting the grain, facts, dimensions, and implementation status for each business process.

An Opportunity/Stakeholder Matrix complements the Bus Matrix by mapping business opportunities or processes to their key stakeholders, helping prioritize development based on business value and stakeholder needs.

Drilling Across

Drilling across is a technique for analyzing multiple business processes by querying their fact tables separately and then combining the aggregated results using shared conformed dimensions.

For example, suppose an organization has separate Shipment and Return fact tables. Both share the Customer and Product dimensions. To compare shipments and returns, each fact table is first aggregated independently by the common dimensions. The resulting datasets can then be combined safely.

Multipass SQL

Multipass SQL is the technique used to implement drilling across. Instead of joining two fact tables directly, the BI application issues separate queries against each fact table and then combines their aggregated result sets.

Directly joining fact tables can produce row multiplication when multiple rows from one fact match multiple rows from another. This can cause measures to be duplicated and produce incorrect totals.

For example, suppose the shipment and return facts contain:

Shipments Fact Table:

customer_key	product_key	shipped_qty
Alice	Laptop	10

Returns Fact Table:

customer_key	product_key	returned_qty
Alice	Laptop	2

Each fact table is queried independently:

Result Set A - Shipments:

customer_key	product_key	total_shipped
Alice	Laptop	10

Result Set B - Returns:

customer_key	product_key	total_returned
Alice	Laptop	2

The two result sets are then combined using their common dimensional attributes:

Combined Result:

customer_key	product_key	total_shipped	total_returned
Alice	Laptop	10	2

This approach ensures that each fact is aggregated at its own grain before the results are combined, preventing incorrect results caused by fact-to-fact joins.

Business Intelligence (BI) Applications

The tools (dashboards, ad-hoc query tools) that business users interact with. They primarily query the dimensional models in the Data Presentation Area.