Tim's Wiki

Data Warehousing and Business Intelligence

Properties2
descriptionDesigning and operating data warehouses for analytics — Kimball lifecycle, OLAP cubes, the ETL back room, and BI delivery in the front room.
tagsdata, database, business

3 min read

Data Warehousing and Business Intelligence

Overview

A data warehouse (DW) is an analytics-oriented data store, distinct from the transactional (OLTP) databases that feed it. A warehouse generates information; layered on top, data mining generates knowledge. The classic definition (Inmon) gives a DW four properties:

  • Integrated — data unified from many sources (which may lose some source-specific content)
  • Subject-oriented — organized around business subjects, not applications
  • Time-variant — retains history rather than only the current state
  • Non-volatile — loaded and read, not continuously updated in place

OLTP databases are heavily normalized (1NF→3NF) so a single fact updates in one place; warehouses deliberately de-normalize for fast, simple analytical reads.

Core Areas

Kimball lifecycle

The Kimball approach designs a warehouse business-first, with a dimensional structure, developed iteratively. Technical architecture must weigh cost and data sensitivity. See Dimensional Modeling for the modeling mechanics.

OLAP and the cube

Analytical queries operate over a cube with standard operations:

  • Slice — fix one dimension to a single value
  • Dice — select a sub-cube across several dimensions
  • Drill down / roll up — move between finer and coarser granularity
  • Pivot — rotate the cube to view different dimensional faces

ETL — the “back room”

ETL prepares data before it reaches users. Key elements:

  • Staging area / landing zone — intermediate storage between source and target, used for transformation processing (implemented as tables, text, or XML).
  • Data profiling — up-front analysis of a source’s content (statistics, anomalies); the first step of improving data quality.
  • Slowly Changing Dimensions (SCD) — how dimension history is handled (see Dimensional Modeling for SCD types 0–6).
  • Staged data forms — immediate, snapshot (point-in-time copy), and archive.
  • Aggregate navigation & hot partitions — pre-aggregation and partitioning frequently-queried data for speed.
  • ETL testing — completeness and integrity checks. Note: null is a value (exists), not “empty”.
  • Tools — Oracle Warehouse Builder, SAP Data Services, SSIS, IBM InfoSphere Information Server, Singer.

BI — the “front room”

Where users access results:

  • Standard reports — complex, pre-built report queries vs direct ad-hoc queries.
  • Operational BI (OBI) — reviewing operational processes/data to make tactical and strategic decisions.
  • Instantaneous BI — real-time views (e.g. web traffic).
  • Write-back — feeding derived knowledge back to improve the source system.

Trade-offs / disadvantages

Data homogenization (losing uncommon data), data-ownership issues, underestimated load times, hidden source-system problems, un-captured required data, high maintenance, long project duration, and integration complexity.

Relationships

Graph View

Backlinks