Data Warehousing, Multidimensional OLAP Models & Star/Snowflake Schemas

Operational database systems (OLTP — On-Line Transaction Processing) are engineered for high-concurrency, short write transactions that insert, update, and delete individual records (e.g., ATM withdrawals, e-commerce checkouts). OLTP databases prioritize ACID transactions and 3NF/BCNF normalization to eliminate write update anomalies.

In contrast, a Data Warehouse (OLAP — On-Line Analytical Processing) is an enterprise repository constructed for consolidated decision-support queries scanning millions of historical records (e.g., 'What was the year-over-year revenue growth across electronics in the Southern region?').

The Three-Tier Architecture:

1. Bottom Tier (Data Sources & Staging): Heterogeneous operational databases (SQL, ERP, flat files) undergo ETL (Extract, Transform, Load) to clean, reconcile, and format data into persistent staging areas.

2. Middle Tier (OLAP Server): An analytical engine organizing data into multidimensional cubes (ROLAP, MOLAP, or HOLAP).

3. Top Tier (Front-End Clients): Business intelligence dashboards, report generators, and data mining algorithms.

A Multidimensional Data Model views data in the form of a data cube defined by Dimensions and Measures.

Fact Table: A central table storing quantitative numerical metrics of a business process (e.g., quantity sold, revenue, shipping cost). Its primary key is a composite key consisting of foreign keys referencing all connected dimension tables.

Dimension Tables: Surrounding tables containing descriptive attributes and hierarchical categorization levels (e.g., Time: day -> month -> quarter -> year; Location: street -> city -> state -> country; Product: item -> brand -> category).

Taxonomy of Measures:

1. Additive Measures: Can be summed meaningfully across all dimensions (e.g., sales_amount, units_sold).

2. Semi-Additive Measures: Can be summed across some dimensions but not others (e.g., account_balance can be summed across accounts or branches, but NOT across time).

3. Non-Additive Measures: Cannot be summed across any dimension (e.g., unit_price, percentage_discount, temperature). Must be aggregated using ratios of sums rather than sum of ratios.

OLAP engines provide five core interactive operations to navigate multidimensional data cubes:

1. Roll-up (Drill-Up / Aggregation): Performs data aggregation either by climbing up a dimension hierarchy (e.g., aggregating daily sales into monthly totals) or by dimension reduction (dropping a dimension entirely from the cube).

2. Drill-down (Roll-Down): The reverse of roll-up: introduces finer detail by stepping down a dimension hierarchy (e.g., expanding quarterly revenue into monthly figures) or by adding a new dimension.

3. Slice: Performs a selection on one dimension of the cube, resulting in a sub-cube of lower dimensionality (e.g., selecting Time = 'Q1' produces a 2D slice across Product and Location).

4. Dice: Defines a sub-cube by applying selection conditions on two or more dimensions (e.g., Location in ('CA', 'NY') AND Time in ('Q1', 'Q2') AND Item in ('Laptop')).

5. Pivot (Rotate): Visual rotation of axes to view the data from different perspectives (e.g., swapping row and column dimensions in a cross-tabulated spreadsheet).