Data & Databases
Business Intelligence: Dimensional Modelling, ETL and Dashboards
Beginner9 min readUpdated
Business intelligence turns operational records into numbers people can act on, and most of the difficulty is modelling rather than tooling. This guide explains why analytical schemas differ from transactional ones, how to design fact and dimension tables around a stated grain, how slowly changing dimensions preserve history, and what makes a dashboard and a metric definition trustworthy.
What you will learn
- Explain the differences between OLTP and OLAP workloads and why they need different schemas
- Design a star schema by declaring the grain, the facts and the dimensions
- Handle history with slowly changing dimension types 1, 2 and 3
- Compare ETL and ELT and describe the role of data marts and semantic layers
- Design KPIs and dashboards that answer a question rather than display data
Every organisation eventually asks the same kind of question: how many, how much, compared with when, and why is it moving. The data to answer it already exists in operational systems, scattered across schemas designed for something else entirely. Business intelligence is the discipline of reshaping that data so the questions can be answered quickly, consistently and by people who do not write SQL.
The tooling changes constantly; the modelling does not. A well-designed star schema from twenty years ago would work fine on a modern cloud warehouse, and a badly designed one will disappoint on any platform. This guide concentrates on that durable part, with SQL examples that run on PostgreSQL or Oracle.
Two different jobs: OLTP and OLAP
Transactional systems and analytical systems have opposite priorities, which is why one schema cannot serve both well.
| OLTP | OLAP | |
|---|---|---|
| Typical operation | Insert or update a few rows | Aggregate millions of rows |
| Query shape | Known, repetitive, indexed point lookups | Ad hoc, wide scans, group and filter |
| Schema | Normalised, many narrow tables | Dimensional, few wide tables |
| Data currency | Current state only | History retained deliberately |
| Users | Applications, many concurrent sessions | Analysts and dashboards, few heavy sessions |
| Success metric | Latency per transaction | Throughput per query, clarity of the model |
An operational schema stores each fact once and joins to reassemble it — the discipline covered in database design and normalization. That is exactly right when the goal is correct concurrent writes. For analysis it produces two problems: queries need ten joins that only a specialist can write correctly, and updates overwrite the past, so "what was this customer's segment when they placed that order" becomes unanswerable.
Analytical models therefore make a deliberate trade: accept redundancy, keep history, and optimise for comprehension.
Facts and dimensions
Dimensional modelling splits the world into measurements and the context of those measurements.
A fact table holds the measurements — quantities, amounts, durations, counts — plus foreign keys to the dimensions that describe them. It is long and narrow, and it is where nearly all the rows live.
A dimension table holds the descriptive attributes you filter, group and label by: customer, product, store, date, channel. It is short and wide, often heavily denormalised, and it is where nearly all the readability lives.
Everything starts with the grain: a plain sentence stating exactly what one row of the fact table represents. "One row per order line" is a grain. "Sales data" is not. Declare it, write it in the documentation, and reject any column that does not fit it.
CREATE TABLE dim_date (
date_key integer PRIMARY KEY, -- 20260907
full_date date NOT NULL,
day_of_week varchar(9) NOT NULL,
month_name varchar(9) NOT NULL,
quarter smallint NOT NULL,
year smallint NOT NULL,
is_weekend boolean NOT NULL,
is_holiday boolean NOT NULL
);
CREATE TABLE dim_product (
product_key bigint PRIMARY KEY, -- surrogate
sku varchar(20) NOT NULL, -- natural key from the source
title varchar(200) NOT NULL,
category varchar(60) NOT NULL,
brand varchar(60) NOT NULL,
valid_from date NOT NULL,
valid_to date,
is_current boolean NOT NULL
);
CREATE TABLE fact_sales_line (
date_key integer NOT NULL REFERENCES dim_date(date_key),
product_key bigint NOT NULL REFERENCES dim_product(product_key),
customer_key bigint NOT NULL REFERENCES dim_customer(customer_key),
store_key integer NOT NULL REFERENCES dim_store(store_key),
order_number varchar(20) NOT NULL, -- degenerate dimension
quantity integer NOT NULL,
net_amount numeric(12,2) NOT NULL,
tax_amount numeric(12,2) NOT NULL,
discount numeric(12,2) NOT NULL
) PARTITION BY RANGE (date_key);
Three details in that model are conventions worth adopting. Dimensions use surrogate keys rather than source system identifiers, so that history can be versioned and so that a source changing its key numbering does not corrupt the warehouse. A date dimension is a real table, not a computed expression, because it lets you filter on "public holiday" or "fiscal quarter" without encoding a calendar in every query. And an identifier with no attributes of its own, such as the order number, stays in the fact table as a degenerate dimension rather than getting a table nobody would join to.
Facts come in three flavours, and mixing them causes wrong numbers. Additive facts (net amount, quantity) can be summed across every dimension. Semi-additive facts (an account balance, a stock level) can be summed across some dimensions but not across time — you average or take the latest instead. Non-additive facts (a margin percentage, a unit price) cannot be summed at all; store the numerator and denominator and compute the ratio at query time.
Star and snowflake
A star schema puts the fact table at the centre with one join to each dimension. That is the whole design, and its virtues are practical: any question is one join per dimension, BI tools can browse it automatically, and the SQL is readable by non-specialists.
SELECT d.year, d.quarter, p.category,
SUM(f.net_amount) AS revenue,
SUM(f.quantity) AS units
FROM fact_sales_line f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2026 AND p.brand = 'Northwind'
GROUP BY d.year, d.quarter, p.category
ORDER BY revenue DESC;
A snowflake schema normalises the dimensions, splitting category and brand into their own tables. It saves a little space and adds joins to every query. Snowflake selectively — for a genuinely deep hierarchy or a lookup shared across dimensions — and leave the rest denormalised.
When several fact tables share the same dimension tables, those dimensions are conformed, and that is what makes cross-process analysis possible: sales and returns can be compared by product and month because both use the same product and date dimensions. Conformed dimensions are the backbone of a warehouse; without them you have a collection of unrelated data marts.
Because fact tables are large and almost always filtered by time, partition them by the date key and let the engine prune. Indexing is different from OLTP too: bitmap indexes on low-cardinality dimension keys work well in Oracle warehouses, and columnar storage makes many indexes unnecessary. The reasoning behind those choices is in the index and execution plan guide.
Keeping history: slowly changing dimensions
A customer moves city. A product changes category. What should last year's report show? The answer is a modelling decision, and the standard vocabulary is numbered.
- Type 0 — retain original. The attribute never changes after first load. Suitable for a date of birth or an original signup channel.
- Type 1 — overwrite. Update in place and lose the old value. Correct for fixing genuine errors, such as a misspelled name. Historic reports change retrospectively, which is sometimes right and often surprising.
- Type 2 — add a new row. Insert a new dimension row with a new surrogate key, close the previous row with
valid_toand flipis_current. Facts keep pointing at the row that was current when they occurred, so history is preserved exactly. This is the default for anything used in analysis. - Type 3 — add a column. Keep
current_regionandprevious_regionside by side. Useful only for a single reorganisation where people want to compare both views.
The type 2 update is the one to get right:
-- Close the outgoing version
UPDATE dim_product
SET valid_to = current_date - 1, is_current = false
WHERE sku = 'BK-1042' AND is_current;
-- Open the new one with a fresh surrogate key
INSERT INTO dim_product (product_key, sku, title, category, brand, valid_from, valid_to, is_current)
VALUES (nextval('dim_product_seq'), 'BK-1042', 'Field Guide to Estuaries',
'Natural history', 'Northwind', current_date, NULL, true);
Two practical notes. Load facts after dimensions in each run, so the key lookup finds the correct version. And add an "unknown" member (key 0 or -1) to every dimension so that a fact arriving with a missing reference can still be loaded and counted rather than silently dropped.
Getting the data in: ETL and ELT
ETL extracts from sources, transforms in a dedicated processing tier, and loads the result. ELT loads raw data into the target platform first and transforms with the platform's own compute. ELT has become the default where storage is cheap and the engine is elastic, because the raw copy is retained, transformations become version-controlled SQL, and no separate transformation tier needs scaling.
Either way, the same concerns recur:
- Incremental extraction. Re-reading a whole source every night stops scaling quickly. Use a watermark column, or change data capture from the source's transaction log.
- Idempotence. A job must produce the same result when re-run after a partial failure. Load into a staging table, then swap or merge atomically.
- Surrogate key lookup. Map each source natural key to the current dimension key, handling late-arriving and unknown members explicitly.
- Auditing. Record row counts in and out, rejected rows and run duration for every load. When a number is queried three weeks later, the load audit is what answers it.
- Scheduling and dependencies. Loads form a graph, not a list. Orchestrate them so a dimension never loads after the facts that reference it. For batch pipelines built in Java, Spring Batch provides the chunk processing, restartability and job repository this needs.
Where volumes or source variety exceed what a warehouse comfortably handles, the pipeline usually lands data in a lake first; the patterns for that are in the big data architecture guide.
Marts, cubes and semantic layers
A data mart is a subject-oriented subset of the warehouse — finance, supply chain, marketing — built from conformed dimensions. Built that way, marts are views onto one model. Built independently by each department, they become the classic problem where three teams present three different revenue figures in the same meeting.
OLAP cubes pre-aggregate measures across dimension hierarchies so that drilling from year to quarter to month is instant, and are queried with MDX rather than SQL. Classic multidimensional cubes have largely given way to columnar in-memory engines and materialised aggregates, but their vocabulary survives everywhere: slice, dice, drill down, roll up, pivot. In SQL, GROUPING SETS, ROLLUP and CUBE produce subtotal rows at several levels in one pass, and window functions handle running totals, moving averages and period-over-period comparisons without self-joins.
A semantic layer is where metric definitions live: revenue is net amount excluding cancelled lines, an active customer is one with an order in the trailing 90 days. Defining these once — in the BI tool's model, in a metrics layer, or in governed database views — is what produces a single version of the truth. When every dashboard author re-implements the definition, divergence is guaranteed. Materialised views are a useful implementation detail here, precomputing heavy aggregates and refreshing on a schedule.
Dashboards and KPIs that get used
Most dashboards fail for design reasons rather than data reasons.
- Start from a decision. Every chart should answer a question someone acts on. If nobody can name the action, delete the chart.
- Give a number context. "€1.4M" means nothing alone. Against target, against last year, against last month, with a trend — that is a KPI.
- Choose measures people can influence. A metric nobody can affect generates resignation, not improvement.
- Define it precisely and publish the definition. Include the filter conditions, the time zone and the refresh time on the dashboard itself.
- Show freshness and completeness. A dashboard that silently displays stale data is worse than one that is down.
- Keep it to one screen. Detail belongs one click away, not in a wall of tiles.
Self-service BI tools let analysts build their own views on the governed model, which is the right division of labour: the data team owns conformed dimensions, fact grain and metric definitions; analysts own questions and presentation. Without the governed model underneath, self-service produces a proliferation of contradictory extracts, and the query habits in the SQL best practices guide matter more, not less, when hundreds of ad-hoc queries hit the warehouse.
Finally, treat data quality as part of the product. Completeness, validity, consistency across sources, timeliness and uniqueness are all testable, and tests that run with each load catch problems before a director does.
Key takeaways
- OLTP schemas optimise correct concurrent writes; analytical models accept redundancy and keep history so that questions are easy to ask and answers stay stable.
- Declare the grain of a fact table in one sentence before choosing columns, and never mix rows of different meanings in one table.
- Prefer a star schema with denormalised, conformed dimensions and surrogate keys; snowflake only genuinely large or shared hierarchies.
- Know whether each fact is additive, semi-additive or non-additive, and store ratios as their components.
- Use slowly changing dimension type 2 for attributes used in analysis, type 1 only to correct errors, and always provide an unknown member.
- Define every metric once in a semantic layer, and design dashboards around decisions with context, freshness and a published definition.
Sources and further reading
Frequently asked questions
- Why not just run reports against the production database?
- Two reasons. Analytical queries scan far more rows than transactional ones, so they compete for cache and I/O with the customers using the application. And the operational schema is normalised for correct writes, not for readable aggregation, so every report becomes a fragile ten-table join that breaks when the application changes. Small teams can start with a read replica, but the modelling problem remains.
- Star schema or snowflake schema?
- Start with a star. Denormalised dimensions mean fewer joins, simpler SQL and easier browsing in BI tools, and the storage saved by snowflaking is negligible next to the fact table. Snowflake only the branches where a hierarchy is genuinely large, is shared between several dimensions, or changes on its own schedule — a product category tree, for instance.
- What is the grain of a fact table?
- The grain is the precise meaning of one row, stated in a sentence before any column is chosen: 'one row per order line per shipment' or 'one row per account per day'. Declaring it first prevents the most damaging modelling error, which is mixing rows of different meanings in one table so that every sum silently double-counts.
- Do I still need a data warehouse if we have a lake?
- You still need the modelling the warehouse represents. A lake stores files cheaply; it does not tell anyone what revenue means or which customer record is current. Lakehouse table formats let you build conformed dimensions and fact tables directly on lake storage, so the layer stays, even when the technology underneath it changes.