OLTP and OLAP

What are OLTP and OLAP?

OLTP stands for online transaction processing. It describes systems that run the daily operations of a business: orders, invoices, payments, stock changes, bookings, support tickets, and account updates.

OLAP stands for online analytical processing. It describes systems built to analyse data: revenue by month, stock trend by product group, churn by customer segment, or margin over the last three years.

They are workload labels, not products you buy. A transactional database under a webshop is OLTP. A data warehouse, lakehouse, cube, or semantic model used for analysis is OLAP.

The difference matters because the two workloads ask opposite things from a system. A checkout needs fast, safe writes. A sales dashboard needs large scans and aggregations over history.

The main differences

Purpose. OLTP keeps the business running. OLAP helps the business understand what happened and what is changing.

Query pattern. OLTP handles many small reads and writes, often in milliseconds. OLAP handles fewer but much larger reads, often scanning millions or billions of rows.

Data model. OLTP databases are usually normalised, so each fact is stored once and updates are safe. OLAP models are often dimensional, with fact tables and dimensions arranged as a star schema for faster reporting.

History. OLTP focuses on the current operational state. OLAP deliberately keeps history so you can compare months, quarters, regions, products, and customer cohorts.

Users. OLTP is used by applications, employees, customers, and integrations. OLAP is used by analysts, managers, dashboards, notebooks, and AI or machine learning workloads.

Row storage and column storage

OLTP systems often use row-oriented storage. The fields for one record sit together, which makes it efficient to insert, update, or fetch one order or customer at a time.

Analytical systems often use columnar storage. Values from the same column sit together, which is efficient when a query needs the sum of all revenue values but not the customer name, address, or note fields. Columnar storage also compresses well because similar values sit next to each other.

This is a good fit for OLAP, but a weaker fit for constant row-by-row updates. That is why transactional and analytical systems are often separated even when the same vendor supports both patterns.

How data moves from OLTP to OLAP

The source data starts in operational systems: ERP, CRM, ecommerce, accounting, subscriptions, logistics, and support. A pipeline copies that data into a data warehouse or lakehouse where it is cleaned, combined, historised, and shaped for reporting.

The classic pattern is a nightly ETL or ELT batch. That is enough for many management reports. When fresher data is needed, teams use change data capture, streaming, replication, or mirroring so the analytical copy follows the source more closely.

Modern platforms blur the boundary. Some databases support analytical indexes on transactional tables. Microsoft Fabric can mirror operational databases into OneLake for near-real-time analytics. Those features reduce the work needed to copy data, but the analytical workload still runs on a separate copy or analytical structure.

Why direct reporting on OLTP hurts

Connecting Power BI directly to an operational database can be fine for a small report. The problem appears as usage grows.

Analytical queries scan and group large volumes. They can compete with checkout, invoicing, stock updates, or customer-service screens for the same database resources. The normalised model also makes reporting awkward: one simple chart may need joins across many tables, and historical context may be missing because the source stores only the current state.

A read replica, mirror, warehouse, or lakehouse moves the reporting load away from the system that runs the business. It also gives you space to standardise definitions and keep history.

What to watch out for with OLTP and OLAP

Do not send every question to the warehouse. Which orders are still open right now? is an operational question. If the warehouse refreshes nightly, the answer may be stale.

A replica is not a warehouse. A replica removes load from the source, but it may still have the same normalised model and limited history. It is a step, not a full analytical layer.

Freshness costs money and complexity. Real-time analytics needs streaming, CDC, monitoring, and stronger failure handling. Use it where the business actually needs it.

Definitions belong in the analytical layer. Revenue, active customer, churn, and margin should be defined consistently in a warehouse, semantic model, or metrics layer, not rewritten inside every report.