ETL, Dimensional Modelling and OLAP for Data Mining

Build the table before mining it

You will define fact-table grain, create dimensions and run a grouped analytical query. The database example uses standard SQL concepts that transfer to common relational systems.

• Separate extract, transform and load responsibilities.
• Define fact grain, measures, dimension keys and slowly changing attributes.
• Compare star, snowflake and fact-constellation designs.
• Explain roll-up, drill-down, slice, dice and pivot operations.

Grain is the promise made by a fact row

ETL extracts from source systems, transforms values under documented rules and loads a target designed for analysis. ELT loads first and transforms inside a capable analytical platform. The choice changes operations, not the need for lineage, validation and idempotency.

A fact table records events or measurements at one declared grain. ‘One row per completed order line’ is precise. Mixing order totals and line amounts in the same measure column causes double counting. Dimensions describe who, what, where and when. Surrogate keys allow a warehouse to preserve historical versions without changing source identifiers.

A star schema connects a central fact directly to denormalized dimensions. It is usually simple for analysts. A snowflake normalizes dimensions into more tables, reducing repetition but adding joins. A fact constellation shares dimensions across several facts, such as sales and returns. Choose clarity and integrity over diagram fashion.

OLAP operations are navigational views of aggregate data. Roll-up summarizes day to month. Drill-down moves toward detail. Slice fixes one dimension value. Dice selects a subcube across several dimensions. Pivot rotates dimensions for presentation. These operations do not create truth; they rely on correct grain and aggregation rules.

ETL tests should reconcile source and target row counts, sums and key coverage. A rerun should not duplicate facts. Late-arriving dimensions and corrected source records need an explicit policy.

Create a star and answer one business question

Run the SQL in PostgreSQL or adapt the data types for another relational database. The query calculates monthly revenue by region without joining through an operational schema.

Define dimensions, fact grain and roll-up

CREATE TABLE dim_date (
    date_key integer PRIMARY KEY,
    full_date date NOT NULL UNIQUE,
    month_start date NOT NULL
);

CREATE TABLE dim_customer (
    customer_key integer PRIMARY KEY,
    source_customer_id text NOT NULL,
    region text NOT NULL,
    valid_from date NOT NULL,
    valid_to date,
    UNIQUE (source_customer_id, valid_from)
);

CREATE TABLE fact_order_line (
    order_line_id bigint PRIMARY KEY,
    date_key integer NOT NULL REFERENCES dim_date(date_key),
    customer_key integer NOT NULL REFERENCES dim_customer(customer_key),
    quantity integer NOT NULL CHECK (quantity > 0),
    net_amount numeric(12,2) NOT NULL
);

SELECT d.month_start,
       c.region,
       SUM(f.net_amount) AS net_revenue
FROM fact_order_line AS f
JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_customer AS c ON c.customer_key = f.customer_key
GROUP BY d.month_start, c.region
ORDER BY d.month_start, c.region;

Expected result shape

month_start | region | net_revenue
------------+--------+------------
2026-01-01  | East   | 12540.30
2026-01-01  | West   | 10990.10
2026-02-01  | East   | 13120.00
... values depend on inserted facts ...

The primary key on order_line_id supports idempotent loading at the declared grain. A separate audit should compare warehouse sums with the source for each load window.

Warehouse errors that survive valid SQL

SQL can return a polished table from a flawed model. Add checks that target meaning, not just syntax.

• A one-to-many dimension join can multiply fact rows. Compare fact counts before and after each join.
• Summing account balances across days is usually invalid. Classify measures as additive, semi-additive or non-additive.
• Updating a customer region in place destroys historical reporting if history matters.
• Using local timestamps without a time-zone policy can assign events to the wrong date dimension.

Model a support warehouse

Design a star schema at the grain of one resolved support ticket. Include date, customer and issue dimensions plus measures for resolution time and reopen count.

• Write the grain above the fact table and test every column against it.
• Add primary, foreign, unique and check constraints.
• Write one roll-up and one drill-down query.
• Define how a changed customer tier and a late-arriving ticket will be handled.

Warehouse review package

• Entity-relationship diagram with fact grain.
• DDL and sample inserts that run in a transaction.
• Reconciliation queries and rerun test.

Official references and further reading

• PostgreSQL table constraints (Official integrity-constraint reference)
• PostgreSQL GROUPING SETS (Official multidimensional grouping reference)
• PostgreSQL transaction isolation (Official basis for safe analytical loads)

Review note for ETL, Dimensional Modelling and OLAP for Data Mining: recheck the linked documentation after a dependency changes the relevant API, metric or modelling assumption, then record the tested version beside your result.