The most expensive data architecture mistake I see in mid-size companies is also the most common: reporting straight off the transactional database, and then tuning that database for reporting. Within a year the ERP is slow for the people entering orders and still slow for the people analyzing them. OLTP and OLAP are not two features of one database. They are two workloads with opposite physics, and the model has to respect that.
What each workload is for
Online transaction processing systems record and query the day-to-day business transactions of an organization, and their defining traits are atomic, consistent writes, high concurrency and many small reads and writes touching a few rows each [1]. Online analytical processing organizes large volumes of data for complex, read-heavy analysis without affecting the transactional systems; queries scan millions of rows, aggregate, and return a few [2].
| Dimension | OLTP | OLAP |
|---|---|---|
| Unit of work | One order, one payment, one status change | Revenue by region by month for three years |
| Rows per query | Few | Millions |
| Columns per query | Many (the whole row) | Few (keys and measures) |
| Model shape | Normalized (3NF): no redundancy, fast writes, integrity constraints | Dimensional (star): denormalized dimensions, narrow facts, fast scans |
| Storage | Rowstore, B-tree indexes | Columnstore, compression, partitioning |
| Concurrency | Thousands of short transactions | Tens of long queries |
| Freshness | Now | Minutes to a day, by agreement |
| Failure mode | Lock contention, deadlocks | Full scans, memory spills, stale data |
Why they fight on the same server
A normalized order model stores an order across header, line, customer, address and product tables so that a change is written once. An analytical query over that model joins all of them, every time, for millions of rows. Add covering indexes to make the report fast and every insert now maintains those indexes. Add a long-running aggregate and it holds shared locks (or forces row versioning) that the writers have to wait on. The two workloads degrade each other, and the people who suffer first are usually the ones entering data.
Designing the OLTP side
- Normalize to third normal form; let foreign keys and check constraints enforce integrity rather than application code.
- Keep transactions short; index for the access paths the application actually uses, not for the reports.
- Add change tracking or temporal tables early. Analytics will need to know what changed and when, and retrofitting that is painful.
- Surrogate keys are fine, but keep the natural business key as a unique constraint; the warehouse will need it to conform dimensions.
Designing the OLAP side
- Model dimensionally: facts at a declared grain, conformed dimensions shared across facts, slowly changing dimensions handled explicitly. (See dimensional modeling in 2026.)
- Store facts in columnstore. Columnstore indexes are the standard for storing and querying large data-warehousing fact tables in SQL Server, Azure SQL and SQL database in Fabric, using column-based storage and compression to speed scans [3].
- Partition large facts by date so loads and maintenance touch one slice.
- Put the business definitions in one place (a semantic model or a serving view layer) so every tool reads the same number.
The bridges: how data gets from one to the other
Four patterns cover almost every mid-size case, in rising order of investment.
- Read replica or snapshot. Point reporting at an Always On readable secondary or a nightly restored copy. Cheap, isolates the writers, but the model is still normalized and the queries still slow.
- Real-time operational analytics on the OLTP server. A nonclustered columnstore index on the transactional table lets analytical queries scan compressed column segments while writes continue, which SQL Server documents as the pattern for operational analytics without a separate warehouse [3]. Good for a handful of hot tables; not a substitute for a warehouse.
- Scheduled ETL/ELT into a warehouse. SSIS, Azure Data Factory or Fabric pipelines move changed rows into a star schema on a schedule. The default for most companies; see pipelines that survive change.
- Continuous replication into a lakehouse. Mirroring in Microsoft Fabric continuously replicates data from external databases into OneLake as analytics-ready Delta tables [4], and SQL database in Fabric replicates its own data to OneLake automatically [5]. The transactional system keeps its normalized model; analytics reads the replica in the lake and builds the star on top.
Deciding without a committee
Ask three questions. How fresh must the analytics be? If “end of day” is acceptable, a scheduled warehouse load is the cheapest correct answer. How many sources? One ERP can live with pattern 2 for a while; three systems need a warehouse to conform them. Who owns it? A pattern the team cannot operate is the wrong pattern regardless of its elegance. Write the answers down, pick the lowest pattern that satisfies them, and plan the next one.
The one rule with no exceptions: never let the reporting workload decide the schema of the system of record. The ledger’s model protects the integrity of the business; the warehouse’s model serves the questions. Keep them apart, and build a bridge you can maintain.
References
- Microsoft Learn, Azure Architecture Center, “Online transaction processing (OLTP),” learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing (accessed 9 Oct 2026).
- Microsoft Learn, Azure Architecture Center, “Online analytical processing (OLAP),” learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-analytical-processing (accessed 9 Oct 2026).
- Microsoft Learn, “Columnstore indexes: Overview,” learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview (accessed 9 Oct 2026).
- Microsoft Learn, “What is Mirroring in Fabric?” learn.microsoft.com/en-us/fabric/mirroring/overview (accessed 9 Oct 2026).
- Microsoft Learn, “SQL database in Microsoft Fabric,” learn.microsoft.com/en-us/fabric/database/sql/overview (accessed 9 Oct 2026).
