OLTP vs OLAP Modeling: Dimensional Models, Star Schema and Slowly Changing Dimensions

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10).

Guess First: Do the Two Queries Give the Same Report?

Management wants revenue by month and parent category for the second quarter of 2026. One developer writes the report straight from the OLTP (Online Transaction Processing) schema: orders, order lines, books, and categories twice. Another writes the same report from the star schema we build in this post: one fact table and two dimensions. Both queries leave out cancelled orders, and both are fed by the same data. Guess: do the two reports give the same rows and the same totals?

They don't. The OLTP query quietly loses 670.50 lira of sales, and the reason is not a bug but the nature of writing reports on a normalized schema. In this post we look at why a normalized OLTP schema struggles with reporting, at the dimensional model of the OLAP (Online Analytical Processing) side, at the star schema, and at how past reports are protected from today's changes, that is, at slowly changing dimensions. The examples run in the dm_10 database, which holds the Bookstore schema of the SQL Temelleri series.

Why Does a Normalized Schema Struggle with Reporting?

In the first seven posts of the series we optimized the schema for writing: every fact in one place, every change in one row, no anomalies. Reporting wants the opposite: reading and grouping many rows, asking the same question with a different breakdown every time. In a normalized schema this means rebuilding the same join chain in every report. The revenue report has to go from line to order, from order to date, from line to book, from book to category and from category to parent category; the "don't count cancelled orders" rule is written again in every query too.

The second problem is dates. The OLTP schema has no "quarter", "month name" or "is it a weekday?" column; every report computes them with DATEPART, and every developer computes them slightly differently. The third problem is history: in OLTP, when a book's category changes the old value is gone, because a normalized schema holds "the current truth". Last year's report gives a different result when run today. The fourth problem is load: a heavy report query competes for the same tables and the same buffer pool as the transactions writing orders.

The dimensional model tackles all four with a single idea: for reporting, copy the data into a separate structure shaped for reading. It is the most extreme and most systematic form of the denormalization from the 8th post; the mechanism that keeps the copy current is usually an ETL (Extract, Transform, Load) job.

Facts, Dimensions and the Grain First

A dimensional model has two kinds of tables. A fact table holds measured events: sales, payments, stock movements. It has many rows, and its columns are mostly numbers (quantity, amount) and keys pointing to dimensions. A dimension table holds the context of the event: when, which book, which customer. It has few rows, its columns are descriptive text, and a report's filters, groupings and labels come from it.

The first design decision, however, is neither a fact nor a dimension; it is the grain, what one row of the fact table represents. In the Kimball Group's dimensional modeling techniques, declaring the grain counts as the most important step of the design, and dimensions and measures are chosen only after that decision; starting at the most detailed level possible (atomic grain) is recommended, because detailed data can also answer questions nobody anticipated. For Bookstore sales the grain is "one book in a non-cancelled order", that is, one order line. This decision settles everything: the date is the order's date, the customer is the order's customer, the measures are the line's quantity and amount. The order number stays in the fact as a dimension without its own table; this is called a degenerate dimension.

Building the Bookstore Star Schema

fact_sales in the middle, three dimensions around it. The dimensions have their own surrogate keys (book_key, customer_key); the OLTP book_id stays as an ordinary column. The reason is the history tracking we will see shortly: a book may get more than one row in the dimension.

CREATE TABLE dbo.dim_date (
    date_key     INT         NOT NULL,
    full_date    DATE        NOT NULL,
    year         SMALLINT    NOT NULL,
    quarter      TINYINT     NOT NULL,
    month        TINYINT     NOT NULL,
    month_name   VARCHAR(10) NOT NULL,
    day_of_week  VARCHAR(10) NOT NULL,
    CONSTRAINT PK_dim_date PRIMARY KEY (date_key)
);
CREATE TABLE dbo.dim_book (
    book_key        INT IDENTITY(1,1) NOT NULL,
    book_id         INT               NOT NULL,
    isbn            CHAR(13)          NOT NULL,
    title           NVARCHAR(200)     NOT NULL,
    first_author    NVARCHAR(201)     NULL,
    category        NVARCHAR(100)     NOT NULL,
    parent_category NVARCHAR(100)     NOT NULL,
    valid_from      DATE              NOT NULL,
    valid_to        DATE              NOT NULL,
    is_current      BIT               NOT NULL,
    CONSTRAINT PK_dim_book PRIMARY KEY (book_key)
);
CREATE TABLE dbo.dim_customer (
    customer_key INT IDENTITY(1,1) NOT NULL,
    customer_id  INT               NOT NULL,
    city         NVARCHAR(100)     NOT NULL,
    country_code CHAR(2)           NOT NULL,
    CONSTRAINT PK_dim_customer PRIMARY KEY (customer_key)
);
CREATE TABLE dbo.fact_sales (
    date_key     INT           NOT NULL REFERENCES dbo.dim_date (date_key),
    book_key     INT           NOT NULL REFERENCES dbo.dim_book (book_key),
    customer_key INT           NOT NULL REFERENCES dbo.dim_customer (customer_key),
    order_id     INT           NOT NULL,
    quantity     INT           NOT NULL,
    sales_amount DECIMAL(12,2) NOT NULL
);

dim_book is deliberately denormalized: the category and parent category names and the first author's name are on the same row. In OLTP that meant three tables and two joins; here it is a single column. Since dimensions are small and rarely change, the anomaly cost of this repetition is low, while the gain shows up in every report. Normalizing the category into a separate dim_category table would make it a snowflake schema. Snowflaking saves a little storage but adds a join to every report; in most cases a star is preferred. The load script:

INSERT INTO dbo.dim_date (date_key, full_date, year, quarter, month, month_name, day_of_week)
SELECT CONVERT(INT, CONVERT(CHAR(8), d, 112)), d, YEAR(d), DATEPART(QUARTER, d), MONTH(d),
       DATENAME(MONTH, d), DATENAME(WEEKDAY, d)
FROM (SELECT DATEADD(DAY, value, CAST('2025-01-01' AS DATE)) AS d
      FROM GENERATE_SERIES(0, 729)) AS days;

INSERT INTO dbo.dim_book (book_id, isbn, title, first_author, category, parent_category,
                          valid_from, valid_to, is_current)
SELECT b.book_id, b.isbn, b.title, a.first_name + N' ' + a.last_name,
       ISNULL(c.name, N'Uncategorized'), ISNULL(p.name, ISNULL(c.name, N'Uncategorized')),
       '2025-01-01', '9999-12-31', 1
FROM dbo.books AS b
LEFT JOIN dbo.categories AS c ON c.category_id = b.category_id
LEFT JOIN dbo.categories AS p ON p.category_id = c.parent_category_id
LEFT JOIN dbo.book_authors AS ba ON ba.book_id = b.book_id AND ba.author_order = 1
LEFT JOIN dbo.authors AS a ON a.author_id = ba.author_id;

INSERT INTO dbo.dim_customer (customer_id, city, country_code)
SELECT customer_id, ISNULL(city, N'Unknown'), country_code
FROM dbo.customers;

INSERT INTO dbo.fact_sales (date_key, book_key, customer_key, order_id, quantity, sales_amount)
SELECT CONVERT(INT, CONVERT(CHAR(8), o.ordered_at, 112)), db.book_key, dc.customer_key,
       o.order_id, oi.quantity, oi.quantity * oi.unit_price
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
JOIN dbo.dim_book AS db ON db.book_id = oi.book_id
     AND CAST(o.ordered_at AS DATE) >= db.valid_from AND CAST(o.ordered_at AS DATE) < db.valid_to
JOIN dbo.dim_customer AS dc ON dc.customer_id = o.customer_id
WHERE o.status <> 'Cancelled';

(730 rows affected)

(77 rows affected)

(40 rows affected)

(641 rows affected)

Note two details. Books without a category go into dim_book as 'Uncategorized': keeping an explicitly named "unknown" value in a dimension instead of NULL is an established habit of dimensional modeling. And the fact load matches a book not just by book_id but by the validity range that covers the order date. That line looks unnecessary for now; it pays off in the history tracking section. 641 rows is what remains of the 680 order lines once the lines of cancelled orders are removed.

The Same Report in Two Models

The two queries from the guess question. First from the OLTP schema:

SELECT YEAR(o.ordered_at) AS year, MONTH(o.ordered_at) AS month,
       COALESCE(p.name, c.name) AS parent_category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
JOIN dbo.books AS b ON b.book_id = oi.book_id
JOIN dbo.categories AS c ON c.category_id = b.category_id
LEFT JOIN dbo.categories AS p ON p.category_id = c.parent_category_id
WHERE o.status <> 'Cancelled'
  AND o.ordered_at >= '2026-04-01' AND o.ordered_at < '2026-07-01'
GROUP BY YEAR(o.ordered_at), MONTH(o.ordered_at), COALESCE(p.name, c.name)
ORDER BY year, month, parent_category;

year  month  parent_category  revenue
----  -----  ---------------  --------
2026  4      Children         880.50
2026  4      Computing        3550.00
2026  4      Fiction          11620.00
2026  5      Children         200.00
2026  5      Computing        3250.00
2026  5      Fiction          7528.10
2026  5      Non-Fiction      601.50
2026  6      Children         510.00
2026  6      Computing        1150.00
2026  6      Fiction          10190.70
2026  6      Non-Fiction      401.00

(11 rows affected)

Then from the star schema:

SELECT d.year, d.month, b.parent_category, SUM(f.sales_amount) AS revenue
FROM dbo.fact_sales AS f
JOIN dbo.dim_date AS d ON d.date_key = f.date_key
JOIN dbo.dim_book AS b ON b.book_key = f.book_key
WHERE d.year = 2026 AND d.quarter = 2
GROUP BY d.year, d.month, b.parent_category
ORDER BY d.year, d.month, b.parent_category;

year  month  parent_category  revenue
----  -----  ---------------  --------
2026  4      Children         880.50
2026  4      Computing        3550.00
2026  4      Fiction          11620.00
2026  5      Children         200.00
2026  5      Computing        3250.00
2026  5      Fiction          7528.10
2026  5      Non-Fiction      601.50
2026  5      Uncategorized    670.50
2026  6      Children         510.00
2026  6      Computing        1150.00
2026  6      Fiction          10190.70
2026  6      Non-Fiction      401.00

(12 rows affected)

The star query has one more row: 670.50 lira of sales in May from books without a category (The Elements of Style or Meditations). Because JOIN dbo.categories in the OLTP query is an inner join, the lines of books whose category is NULL fell out of the report. Fixing this is easy (a LEFT JOIN and a COALESCE), but the point is not how easy the fix is: the same decision has to be made again in every report, by every developer. In the star schema this decision was made once, at load time, and every report inherited it.

The difference in the structure of the queries is instructive too. The star query has no business rules: the cancellation filter, the date calculation and the category hierarchy were resolved during loading. The query only says "filter the fact, group by the dimension, sum the measure". It is exactly the structure reporting and dashboard tools expect.

Slowly Changing Dimensions: Type 1, 2 and 3

On 1 July 2026 the bookshop decides to move The Time Machine and The War of the Worlds from the science fiction shelf to the classics shelf. In OLTP this is a single UPDATE; the next day a new order for The Time Machine arrives:

UPDATE dbo.books SET category_id = 2 WHERE book_id IN (31, 32);
INSERT INTO dbo.orders (customer_id, ordered_at, status) VALUES (1, '2026-07-02 10:15:00', 'Paid');
INSERT INTO dbo.order_items (order_id, book_id, quantity, unit_price)
SELECT MAX(order_id), 31, 1, 310.00 FROM dbo.orders;

Before the change, the revenue of the two categories in the first half of the year looks like this:

SELECT b.category, SUM(f.sales_amount) AS revenue_2026_h1
FROM dbo.fact_sales AS f
JOIN dbo.dim_date AS d ON d.date_key = f.date_key
JOIN dbo.dim_book AS b ON b.book_key = f.book_key
WHERE d.year = 2026 AND d.month <= 6
  AND b.category IN (N'Classics', N'Science Fiction')
GROUP BY b.category;

category         revenue_2026_h1
---------------  ---------------
Classics         31330.43
Science Fiction  22262.54

(2 rows affected)

How you reflect this change on the dimension side is a business decision, and these decisions are called slowly changing dimension (SCD) types. Type 1 is the simplest: overwrite the old value. To see its effect, I try it inside a transaction and roll it back:

BEGIN TRANSACTION;
    UPDATE dbo.dim_book SET category = N'Classics' WHERE book_id IN (31, 32);

    SELECT b.category, SUM(f.sales_amount) AS revenue_2026_h1
    FROM dbo.fact_sales AS f
    JOIN dbo.dim_date AS d ON d.date_key = f.date_key
    JOIN dbo.dim_book AS b ON b.book_key = f.book_key
    WHERE d.year = 2026 AND d.month <= 6
      AND b.category IN (N'Classics', N'Science Fiction')
    GROUP BY b.category;
ROLLBACK;

(2 rows affected)
category         revenue_2026_h1
---------------  ---------------
Classics         33841.85
Science Fiction  19751.12

(2 rows affected)

The January–June report changed although its dates did not: 2,511.42 lira of sales moved from science fiction to classics. In January these books were not classics; Type 1 rewrites history with today's classification. That is the right choice for cases where history should change too, such as fixing a typo. If the answer to "what did we sell in January?" must not change, it is wrong.

Type 2 preserves history: it closes the current row and adds a new row with the new value. Every row has a validity range and an "is it current?" flag:

DECLARE @change_date DATE = '2026-07-01';

UPDATE dbo.dim_book
SET valid_to = @change_date, is_current = 0
WHERE book_id IN (31, 32) AND is_current = 1;

INSERT INTO dbo.dim_book (book_id, isbn, title, first_author, category, parent_category,
                          valid_from, valid_to, is_current)
SELECT book_id, isbn, title, first_author, N'Classics', N'Fiction',
       @change_date, '9999-12-31', 1
FROM dbo.dim_book
WHERE book_id IN (31, 32) AND valid_to = @change_date;

SELECT book_key, book_id, title, category, valid_from, valid_to, is_current
FROM dbo.dim_book
WHERE book_id = 31
ORDER BY valid_from;

(2 rows affected)

(2 rows affected)
book_key  book_id  title             category         valid_from  valid_to    is_current
--------  -------  ----------------  ---------------  ----------  ----------  ----------
31        31       The Time Machine  Science Fiction  2025-01-01  2026-07-01  0
78        31       The Time Machine  Classics         2026-07-01  9999-12-31  1

(2 rows affected)

The Time Machine now has two rows in the dimension: key 31 represents the science fiction period, key 78 the classics period. Old fact rows keep pointing to 31; new sales are linked to 78 thanks to the date range match during loading. Let's load the July sale and report both periods together:

INSERT INTO dbo.fact_sales (date_key, book_key, customer_key, order_id, quantity, sales_amount)
SELECT CONVERT(INT, CONVERT(CHAR(8), o.ordered_at, 112)), db.book_key, dc.customer_key,
       o.order_id, oi.quantity, oi.quantity * oi.unit_price
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
JOIN dbo.dim_book AS db ON db.book_id = oi.book_id
     AND CAST(o.ordered_at AS DATE) >= db.valid_from AND CAST(o.ordered_at AS DATE) < db.valid_to
JOIN dbo.dim_customer AS dc ON dc.customer_id = o.customer_id
WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2026-07-01';

SELECT IIF(d.month <= 6, 'H1', 'July') AS period, b.category, SUM(f.sales_amount) AS revenue
FROM dbo.fact_sales AS f
JOIN dbo.dim_date AS d ON d.date_key = f.date_key
JOIN dbo.dim_book AS b ON b.book_key = f.book_key
WHERE d.year = 2026 AND d.month <= 7
  AND b.category IN (N'Classics', N'Science Fiction')
GROUP BY IIF(d.month <= 6, 'H1', 'July'), b.category
ORDER BY period, b.category;

(1 rows affected)
period  category         revenue
------  ---------------  --------
H1      Classics         31330.43
H1      Science Fiction  22262.54
July    Classics         310.00

(3 rows affected)

The figures for the first half did not change; the July sale went to classics. Type 2 has one more advantage: if you want, you can also see history with today's classification. It is enough to go from the historical row the fact points to, to the current row of the same book:

SELECT cur.category AS current_category, SUM(f.sales_amount) AS revenue_2026_h1
FROM dbo.fact_sales AS f
JOIN dbo.dim_date AS d ON d.date_key = f.date_key
JOIN dbo.dim_book AS hist ON hist.book_key = f.book_key
JOIN dbo.dim_book AS cur ON cur.book_id = hist.book_id AND cur.is_current = 1
WHERE d.year = 2026 AND d.month <= 6
  AND cur.category IN (N'Classics', N'Science Fiction')
GROUP BY cur.category;

current_category  revenue_2026_h1
----------------  ---------------
Classics          33841.85
Science Fiction   19751.12

(2 rows affected)

This is the same result Type 1 gave, but without erasing history. Type 3 is a middle way: it keeps the previous value in a separate column. It only remembers one previous value and is usually used for transition-period reports such as "compare before and after the reorganization":

ALTER TABLE dbo.dim_book ADD previous_category NVARCHAR(100) NULL;
GO
UPDATE dbo.dim_book
SET previous_category = N'Science Fiction'
WHERE book_id IN (31, 32) AND is_current = 1;

SELECT book_key, title, category, previous_category
FROM dbo.dim_book
WHERE book_id IN (31, 32) AND is_current = 1;

(2 rows affected)
book_key  title                  category  previous_category
--------  ---------------------  --------  -----------------
78        The Time Machine       Classics  Science Fiction
79        The War of the Worlds  Classics  Science Fiction

(2 rows affected)

The Kimball Group's techniques go beyond these: an attribute that never changes (Type 0), a mini-dimension for frequently changing attributes (Type 4), and combinations of these (Type 5, 6, 7). In practice most dimensions are decided per attribute: a typo in a book's title is Type 1, its category Type 2. This asks the same question as the view of 6NF on temporal data from the 7th post of the series: whose history matters? The practical design of effective-dated records on the OLTP side is in the "Temporal Data" post of the Çözüm Desenleri series.

A Quick Pointer to Columnstore

Fact tables grow to millions of rows, and report queries read most of the rows but few of the columns. SQL Server's columnstore index was designed for exactly this access pattern: it stores data column by column in compressed row groups instead of row by row, and a query reads only the columns it needs. The typical choice for a fact table is a clustered columnstore index:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_sales ON dbo.fact_sales;

Measuring its effect on a 641-row table is meaningless; the benefit of columnstore shows at millions of rows and with batch mode execution. For the details, the columnstore index guide on Microsoft Learn is a good starting point. What matters for this post is that the dimensional model and columnstore complement each other: a long, narrow fact table is ideal for grouping queries that read few columns.

In Production

Don't put heavy reports directly on the OLTP database. The month-end report competes for locks and the buffer pool on the same tables as the transactions writing orders. At least route reports to a read replica; if there is a regular reporting need, build a separate dimensional model. The infrastructure side of replicas and scaling is in the "Veri Katmanını Ölçeklemek: Replication, Sharding ve SQL mi NoSQL mi?" post of the System Design series.

Make loading incremental and rerunnable. The fact load should work with a date window, and loading the same window twice must not produce duplicate rows; deleting and reloading the window, or matching on the source key (order_id, book_id), are two common ways. For late changes (refunds, cancellations) the window should cover the last few days.

Plan for the unknown dimension member. A fact row can point to a book or customer that is not in the dimension yet. Linking it to an "Unknown" dimension row and fixing it later, instead of dropping the row, keeps totals consistent with the source.

Share dimensions across processes. If sales and stock movement facts use the same dim_book and the same dim_date, the two processes can sit side by side in the same report. In Kimball terminology these are called conformed dimensions; building a separate book dimension for every fact loses that possibility.

Common Pitfalls

Mixing grains and double counting. Payments are at the order level, lines at the line level. Order 1 has two lines and two payments; joining them in the same query multiplies the payments by the number of lines:

SELECT 'items'               AS source, SUM(oi.quantity * oi.unit_price) AS amount
FROM dbo.order_items AS oi WHERE oi.order_id = 1
UNION ALL
SELECT 'payments', SUM(p.amount)
FROM dbo.payments AS p WHERE p.order_id = 1
UNION ALL
SELECT 'items x payments', SUM(p.amount)
FROM dbo.order_items AS oi
JOIN dbo.payments AS p ON p.order_id = oi.order_id
WHERE oi.order_id = 1;

source            amount
----------------  -------
items             1042.70
payments          1042.70
items x payments  2085.40

(3 rows affected)

The total payment doubled. Events at different grains belong in separate fact tables (fact_sales and a fact_payments), and if they are reported together, each should first be summed at its own grain by a shared dimension and only then combined.

Adding columns without declaring the grain. The suggestion "let's put the shipping fee in the fact too" looks innocent; but the shipping fee belongs to the order, not the line. Added to a fact at line grain, it repeats on every line row and the total swells.

Making every dimension Type 2. History tracking is not free: the dimension grows, and queries have to answer the question "which row?". If nobody asks about history, Type 1 is enough.

Treating the dimension as a copy of the OLTP table. dim_book is not the same as books; it is a table in the language of the report, flattened, with "unknown" values named and carrying history. Copying it one to one just moves OLTP's reporting problems into a new database.

A Small Experiment

★☆☆ Add an is_weekend column to dim_date and write the query that compares weekend and weekday revenue in the first half of 2026. How different is writing the same query on the OLTP schema?

★★☆ Design a fact_payments table for payments: what is the grain, which dimensions are needed, where does payments.method go? Then write a monthly "sales and collections" report without double counting either fact.

★★★ Which SCD type would you choose for dim_customer when a customer's city changes? Apply Type 2, change a customer's city and run the "revenue by city" report both by the city at the time of sale and by the current city.

We modeled the same data in two different ways: normalized for writing, dimensional for reading. In the next post we look at yet another shape: thinking about the same Bookstore requirements in a document model, keeping an order and its lines in a single document, the right place of a JSON (JavaScript Object Notation) column in a relational database, and the questions that decide between relational and document.