OLTP and OLAP
Transactions touch a few whole rows; analysis reads a few columns
OLTP reads and writes a few whole rows, fast and often; OLAP reads a few columns across millions of rows.
The same orders table serves two very different kinds of work. The application, the online transaction processing side, changes one order at a time: mark order 101 as shipped. It touches one whole row, and does that thousands of times a second for thousands of users. The analyst, the online analytical processing side, asks: what did we sell since October? That reads just two columns, the date and the total, but of every row in the table. Same data, opposite shapes. And the shape decides how the data should sit on disk.
Columnar storage
A column store keeps each column together, so queries read less
A row store keeps whole rows together; a column store keeps each column together, so a query reads only the columns it names.
Here are three orders as they sit on disk. In a row store, each row is written whole, then the next. That is perfect for the application: one write, one place. But to add up the totals, the disk must hand over every column of every row, notes and statuses included. A column store writes all the ids together, then all the customers, then all the dates, then all the totals. A query that names two columns reads those two runs and nothing else.
Columnar storage · Going deeper
Parquet: the column file format of the data world
Parquet files are split into row groups, each holding one column chunk per column, with statistics in the footer.
Parquet files are split into row groups, each holding one column chunk per column, with statistics in the footer. Inside a Parquet file. Row groups the file is cut into horizontal slices of rows (here 122,880 rows each). Engines can read different row groups in parallel. Column chunks and pages within a row group, each column is stored on its own, encoded and compressed (Snappy, gzip or zstd). Footer metadata the schema, and for every column chunk its min, max and null count. A reader opens the footer first and skips the chunks a filter rules out; this is called predicate pushdown or data skipping. Sorting helps skipping works when values are clustered: these orders are sorted by date, so each row group covers about six weeks. Shuffle the rows and every group spans the whole year and none can be skipped. Other formats you will meet.
Inside a Parquet file
- Row groups the file is cut into horizontal slices of rows (here 122,880 rows each). Engines can read different row groups in parallel.
- Column chunks and pages within a row group, each column is stored on its own, encoded and compressed (Snappy, gzip or zstd).
- Footer metadata the schema, and for every column chunk its min, max and null count. A reader opens the footer first and skips the chunks a filter rules out; this is called predicate pushdown or data skipping.
- Sorting helps skipping works when values are clustered: these orders are sorted by date, so each row group covers about six weeks. Shuffle the rows and every group spans the whole year and none can be skipped.
Other formats you will meet
Bytes read
For one sum over a million orders, the column store read 161× fewer bytes
Measured: a row store read 108.3 MB; the column store read 2.6 MB of two columns, and 673 KB after data skipping.
We measured this. A million orders, ten columns, and one question: the sum of totals since October. PostgreSQL, a row store, read every one of its 13,216 pages, 108.3 MB. The same rows as a Parquet file, a column format: the two columns the query names come to 2.6 MB, compressed. And each block of rows stores the smallest and largest date it holds, so the six blocks that end before October are skipped without being opened. 673 KB. The same answer, from 161× fewer bytes.
Column compression
Columns compress well because the values in a column are alike
Dictionary encoding replaces repeated values with small codes; run-length encoding stores a sorted run once with its count.
A column holds one kind of value, and that makes it very compressible. The status column has only four values, so store each once and keep a small code per row: two bits instead of a word. The date column is sorted, so it is long runs of the same day. Store each day once with its count. Here are the real results. Dates shrank 4,505×, statuses 50.1×, cities 12.8×. Totals, almost all different, shrank only 2.1×. Fewer distinct values, smaller column.
Star schema
Numbers go in one fact table; descriptions go in dimensions around it
A star schema has a fact table of keys and measures at a stated grain, joined to dimension tables.
Analytical data is usually modelled as a star. In the centre, the fact table: one row per event at a stated grain, here one product on one receipt. It holds keys, and the numbers you add up: quantity, amount, discount. Around it, the dimensions: date, store, product, customer. They hold the words you filter and group by: month, region, category, segment. Each dimension row describes many fact rows, so every link is one to many.
Fact and dimension types · Going deeper
Three kinds of fact table, and four dimension words to know
A transaction fact records events; a periodic snapshot records levels; a conformed dimension is shared by several stars.
Facts and dimensions
Questions join facts to dimensions, group, and add up the measures
Additive measures sum every way; semi-additive ones, like balances, do not sum over time; ratios never sum.
Nearly every analytical question has this shape: join the facts to a few dimensions, group by their attributes, add up a measure. Sales by month and category. But be careful what you add. Amounts and quantities are additive: sum them across stores, across days, anything. Stock on hand is semi-additive. Add it across stores, fine. But a hundred units on Monday and a hundred on Tuesday is not two hundred units. Take the last value or the average. Ratios, like margin percent, never add. Store the parts and divide at the end.
Facts and dimensions · Going deeper
Snowflake schemas, wide tables and where the star came from
A snowflake schema normalises dimensions into more tables; a one big table flattens everything into one.
A snowflake schema normalises dimensions into more tables; a one big table flattens everything into one. Three shapes for the same facts. Star schema facts plus one table per dimension, denormalised (category and brand sit in dim_product). Simple, few joins; the default for BI and for Power BI models. Snowflake schema dimensions normalised further: dim_product points to dim_category, which points to dim_department. Less repetition, more joins. (Not to be confused with the Snowflake product.) One big table facts and all their attributes flattened into one wide table. Column stores make wide tables cheap to scan; it suits a single dashboard but repeats every attribute on every row. Fact table types. Ralph Kimball’s dimensional modelling (build stars per business process, conformed dimensions shared between them) and Bill Inmon’s approach (a normalised enterprise warehouse first, data marts after) are the two classic schools. Most modern lakehouses keep normalised or raw layers and serve stars on top.
Three shapes for the same facts
- Star schema facts plus one table per dimension, denormalised (category and brand sit in dim_product). Simple, few joins; the default for BI and for Power BI models.
- Snowflake schema dimensions normalised further: dim_product points to dim_category, which points to dim_department. Less repetition, more joins. (Not to be confused with the Snowflake product.)
- One big table facts and all their attributes flattened into one wide table. Column stores make wide tables cheap to scan; it suits a single dashboard but repeats every attribute on every row.
Fact table types
Slowly changing dimensions
Type 2 keeps history by adding a row when an attribute changes
A type 1 change overwrites the old value; a type 2 change adds a new row with a new surrogate key and validity dates.
Dimensions change. Ben moves from Leeds to York in June. The simple option, type one, overwrites the city. But now all of Ben's sales, including March, report as York. History has been rewritten. Type two closes the old row, valid until the end of May, and adds a new row with a new key, valid from June, marked current. Each sale keeps the key that was current when it happened, so March stays in Leeds and July is in York. That is why warehouses use their own surrogate keys.
Power BI data models
Relationships carry filters from dimensions to facts; measures sum
A Power BI semantic model is a star: relationships filter from the one side to the many side, and measures calculate.
A Power BI model is a star schema with behaviour. The Sales table holds the facts. Date, Product and Store relate to it one to many, and filters flow along those relationships towards Sales. Pick North on a slicer and every number on the page recalculates for the North. Date carries a hierarchy, so viewers can drill from year to quarter to month. A measure, written in DAX, is calculated at query time for whatever is filtered. A calculated column is computed for every row at refresh and stored. Use measures for anything you add up.
Power BI data models · Going deeper
Power BI: the parts and how data gets in
Power BI Desktop builds models and reports; the Power BI service shares them as reports, dashboards and apps.
Power BI Desktop builds models and reports; the Power BI service shares them as reports, dashboards and apps. The parts. Power BI Desktop the free Windows app where you connect to data, shape it with Power Query, build the model (relationships, measures, hierarchies) and design reports. Power BI service the cloud service (now part of Microsoft Fabric) where you publish, share, schedule refreshes and set row-level security. Mobile apps view the same content. Semantic model the tables, relationships and measures (formerly called a dataset). Many reports can share one. Report one or more pages of interactive visuals built on one semantic model; clicking one visual filters the others. Dashboard a single page of tiles pinned from reports, possibly from several models; a summary to monitor at a glance. App a packaged set of reports and dashboards distributed to an audience. Storage modes.
The parts
- Power BI Desktop the free Windows app where you connect to data, shape it with Power Query, build the model (relationships, measures, hierarchies) and design reports.
- Power BI service the cloud service (now part of Microsoft Fabric) where you publish, share, schedule refreshes and set row-level security. Mobile apps view the same content.
- Semantic model the tables, relationships and measures (formerly called a dataset). Many reports can share one.
- Report one or more pages of interactive visuals built on one semantic model; clicking one visual filters the others.
- Dashboard a single page of tiles pinned from reports, possibly from several models; a summary to monitor at a glance.
- App a packaged set of reports and dashboards distributed to an audience.
Storage modes
Data visualisations
Pick the visual from the question the viewer is asking
Compare with a bar chart, show change with a line chart, relationships with a scatter, one KPI with a card.
The question picks the chart. Comparing categories: a bar or column chart. Change over time: a line. Parts of one whole, and only a few parts: a pie or donut. Do two measures move together: a scatter. One number that matters: a card. A cross-tab with subtotals: a matrix. Values by place: a map. Exact values to look up: a table.
Lesson complete
You finished “Data for analysis”
Nicely done.