OLTP and OLAP

Transactions touch a few whole rows; analysis reads a few columns

OLTP: the applicationUPDATE orders SET status = 'shipped' WHERE id = 101idcustdatestatprodqtypricecitynotetotalone whole row · thousands of requests a secondOLAP: the analystSELECT SUM(total) FROM orders WHERE date >= …idcustdatestatprodqtypricecitynotetotaltwo columns of every row · a few big queriesTransactions read and write a few whole rows; analysis reads a few columns of millions of rows.Online transaction processing (OLTP) runs the business: place an order, update a status; many small, fast, concurrent changes. Onlineanalytical processing (OLAP) asks about it: revenue by month, basket size by city; huge reads, no changes. Same data, opposite shapes.

OLTP reads and writes a few whole rows, fast and often; OLAP reads a few columns across millions of rows.

Columnar storage

A column store keeps each column together, so queries read less

Row store: each page holds whole rows, one after another1014210-01dlv1.201024310-01shp6.501034210-02dlv3.00row 101row 102row 103To sum total, the disk must read every byte of every row: id, cust, status, note and the rest come along.Column store: each column is stored on its own10110210342434210-0110-0110-02dlvshpdlv1.206.503.00idcustdatestatustotalTo sum total, read only the date and total columns, packed together.A column store keeps each column together on disk, so a query reads only the columns it names.Row stores (PostgreSQL, SQL Server, MySQL) suit OLTP: one write puts a whole row in one place, one read fetches it back. Column stores (Synapse,Fabric Warehouse, Redshift, BigQuery, Snowflake, Parquet files) suit OLAP: a query naming 2 of 40 columns reads about 2/40 of the data.The real table has 10 columns; five are drawn.

A row store keeps whole rows together; a column store keeps each column together, so a query reads only the columns it names.

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.

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

CSV, JSONrow-oriented text: easy to read and exchange, large and slow to scan. Here 76.2 MB against Parquet’s 11.8 MB.
Avrorow-oriented binary with an embedded schema: good for streaming and writing records one at a time.
ORCcolumnar like Parquet, from the Hive ecosystem.
Delta, Icebergtables made of Parquet files plus a transaction log (lesson 9).

Bytes read

For one sum over a million orders, the column store read 161× fewer bytes

SELECT SUM(total) FROM orders WHERE order_date >= '2025-10-01' → 252,055 of 1,000,000 rows matchBytes the query has to readRow store, every page108.3 MB: all 13,216 pagesColumn store, 2 of 10 columns2.6 MB: date and total, compressed… skipping row groups by date673 KB: 3 of 9 row groups161× fewer bytes for the same answer. Every row group stores the min and max of each column, so groups that endbefore October are skipped without being opened.For one analytical query on a million orders, the column store read 161× fewer bytes than the row store.Measured: PostgreSQL read all 13,216 pages (108.3 MB) to sum one column. The same rows as Parquet are 11.8 MB in all; the two columns thequery names are 2.6 MB, and the min and max dates in each row group rule out 6 of 9. Less to read is less disk, memory and time.

Measured: a row store read 108.3 MB; the column store read 2.6 MB of two columns, and 673 KB after data skipping.

Column compression

Columns compress well because the values in a column are alike

Dictionary encodingstore each distinct value once; the column keeps small codesdelivered→0delivered→0shipped→1delivered→0cancelled→3delivered→00 = delivered1 = shipped2 = processing3 = cancelledRun-length encodinga sorted column becomes value × count2025-10-01 × 2,740, 2025-10-02 × 2,740, …Measured: text size → stored Parquet sizeorder_datesorted, 365 values4,505× smallerstatus4 values50.1× smallership_city8 values12.8× smallerorder_idunique, increasing5.6× smallertotalnearly unique2.1× smallerValues in one column are alike, so columns compress far better than rows: the fewer distinct values, the smaller.Four statuses need two bits a value, and a sorted date column collapses to a list of runs: 10.0 MB of dates became 2.2 KB. Nearly uniquevalues, like totals, shrink least. The whole table went from 76.2 MB as CSV to 11.8 MB as Parquet, and many engines filter and add up encodedvalues without decoding them. Grey bar: bytes as text; accent bar: bytes stored; both to the same scale.

Dictionary encoding replaces repeated values with small codes; run-length encoding stores a sorted run once with its count.

Star schema

Numbers go in one fact table; descriptions go in dimensions around it

dim_datedate_keydate, monthquarter, yearis_holiday1*dim_storestore_keycityregionopened1*dim_productproduct_keyname, brandcategorypack size1*dim_customercustomer_keyname, citysegmentjoined1*fact_salesdate_keystore_keyproduct_keycustomer_keyquantityamountdiscountgrain: one row per product per receiptA star schema puts the numbers in one central fact table and the descriptions in dimension tables around it.Each fact row records one event at a stated grain (here, one product on one receipt): keys to its dimensions, plus measures to add up. Dimensionsdescribe the who, what, where and when, and give the words to filter and group by. Facts are long and narrow; dimensions are short and wide.

A star schema has a fact table of keys and measures at a stated grain, joined to dimension tables.

Fact and dimension types · Going deeper

Three kinds of fact table, and four dimension words to know

Fact tableOne row perExampleMeasuresTransactionevent, as it happensa product on a receiptadditive; rows are never updatedPeriodic snapshotthing, per periodstock of each product, each nightsemi-additive: do not sum over timeAccumulating snapshotprocess, start to finishan order: placed, paid, shippedrow updated at each milestone; lagsFactlessevent with nothing to add upa student attended a classnone: you count the rowsDimensionWhat it isExampleConformedone dimension shared by several fact tablesdim_date used by sales and by stock, so the two compareRole-playingone dimension joined several timesdim_date as order date, ship date and delivery dateDegeneratea key kept in the fact, with no tablethe receipt number: nothing more to say about itJunkleftover flags gathered into one tableis_gift, payment type, channel: a row per combinationDeclare the grain first: say exactly what one row of the fact table is, and every other choice follows.Kimball’s four steps: pick the business process, declare the grain, choose the dimensions, choose the facts. Two grains inone table (order lines beside order totals) double count the moment someone sums it. Conformed dimensions are what letseparate stars answer one question together; Kimball calls the plan of shared dimensions the bus matrix. Keep an “unknown”row in each dimension so no fact has a NULL key.

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

SELECT d.month, p.category, SUM(f.amount)FROM fact_sales f JOIN dim_date d ON d.date_key = f.date_key JOIN dim_product p ON p.product_key = f.product_keyGROUP BY d.month, p.categoryNot every measure can be summed every wayKindExampleSum across storesSum across daysAdditiveamount, quantitySemi-additivestock on hand, account balanceNon-additivemargin %, unit price✓yes✓yes✓yes✗no: take the last or average✗no✗no: recompute from sumsEvery analytical question joins the fact table to a few dimensions, groups by their attributes and adds up the measures.Additive measures sum along every dimension. Stock on hand sums across stores but not across days: 100 units on Monday and 100 onTuesday is not 200 units. Ratios never sum: store profit and revenue, and divide after adding. With one join per dimension,stars are what warehouses and BI tools are built around.

Additive measures sum every way; semi-additive ones, like balances, do not sum over time; ratios never sum.

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.

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

Transactionone row per event: a sale line, a click.
Periodic snapshotone row per thing per period: stock at the end of each day. Its measures are usually semi-additive.
Accumulating snapshotone row per process, updated as it moves: an order’s placed, shipped and delivered dates.
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.

Slowly changing dimensions

Type 2 keeps history by adding a row when an attribute changes

Ben moves from Leeds to York on 1 June 2025Type 1: overwritecust_idnamecity42BenYorkLeeds is gone: Ben’s March sales now count as York salesType 2: add a row, keep historykeycust_idnamecityvalid_fromvalid_tois_current104242BenLeeds2023-03-102025-05-31false281742BenYork2025-06-019999-12-31truefacts point to the version true at the timeMarch sale → key 1042July sale → key 2817A slowly changing dimension keeps each old fact with the description that was true when it happened.Type 1 overwrites: simple, and right for corrections such as a misspelt name, but history is rewritten. Type 2 closes the old row and adds a new onewith a new surrogate key and validity dates; each fact keeps the key that was current when it was recorded, so March sales stay in Leeds. This iswhy dimensions use surrogate keys rather than the source system’s id. (Type 3 adds a previous_city column for one step of history.)

A type 1 change overwrites the old value; a type 2 change adds a new row with a new surrogate key and validity dates.

Power BI data models

Relationships carry filters from dimensions to facts; measures sum

DateDateYear › Quarter › Month1*ProductProductKeyCategory1*StoreStoreKeyRegion1*SalesAmountQuantityfilters flow from theone side to the many sideTotal Sales = SUM ( Sales[Amount] )a measure: computed at query time, for whatever is filteredMargin = Sales[Amount] - Sales[Cost]a calculated column: computed per row at refresh, storedA Power BI semantic model is a star schema: relationships carry filters to the facts, and measures do the sums.Click “Region = North” on a chart and the filter flows along the one-to-many relationship to Sales, so every measure recalculates for the North.Hierarchies (Year, Quarter, Month) let viewers drill down. Prefer measures, written in DAX, for anything summed; calculated columns use memory.

A Power BI semantic model is a star: relationships filter from the one side to the many side, and measures calculate.

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.

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

Importdata is copied into the model’s in-memory column store: fastest queries, as fresh as the last refresh.
DirectQueryeach visual sends a query to the source: always current, as fast as the source.
Direct Lakein Fabric, reads Delta tables in OneLake directly into memory: near-Import speed without a copy.

Data visualisations

Pick the visual from the question the viewer is asking

Bar or columncompare categoriesLinea trend over timePie or donutparts of one whole, few partsScatterdo two measures move togetherCardone number that matters12.9 MMatrixa cross-tab with subtotals81012111315141618243155Mapvalues by locationTableexact values to look upPen1,204Ink880Pad642Clip95Choose the visual from the question: compare, trend, share, relationship, a single number, a cross-tab, a place or a lookup.A bar chart compares categories; a line shows change over time; a pie works only for a few parts of one whole; a scatter shows whether twomeasures move together; a card shows one KPI; a matrix cross-tabulates with totals; a map shows geography; a table gives exact values.Shapes and values on this page are illustrative.

Compare with a bar chart, show change with a line chart, relationships with a scatter, one KPI with a card.

Lesson complete

You finished “Data for analysis”

Nicely done.

Open in a new tab