OLAP

From Ilianko
Revision as of 15:07, 26 October 2026 by Anko (talk | contribs) (Recreated DP-420 term page: OLAP (Core Concepts))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

← Back to DP-420 Terminology Reference

Category: Core Concepts

Summary: Online Analytical Processing; large aggregations and analytical queries over historical data

Overview

OLAP is a type of workload optimized for analyzing large amounts of historical or operational data to discover trends, generate reports, create dashboards, and support business decisions. 

SELECT Region, SUM(Sales)
FROM Orders
GROUP BY Region

SELECT AVG(temperature)
FROM DeviceTelemetry
WHERE timestamp >= '2026-09-01'

Characteristics:

  • Reads millions of records
  • Aggregations (SUM, AVG, COUNT)
  • Grouping (GROUP BY)
  • Trend analysis
  • Reporting and dashboards
  • Data warehousing
  • Business Intelligence (Power BI)
  • Optimized for complex queries: OLAP systems are optimized for complex analytical queries involving aggregations, joins, and calculations across large datasets
  • Wide tables and denormalized data: OLAP systems use denormalized data models (wide tables with
  • Complex queries: OLAP queries are often complex, involving multiple joins, aggregations, and calculations
  • Historical data: OLAP systems store large amounts of historical data (months, years, or even decades)
  • Batch processing: OLAP queries are often run in batches, with results generated periodically


| Feature                             | OLTP                          | OLAP                         |
| ----------------------------------- | ----------------------------- | ---------------------------- |
| Meaning                             | Online Transaction Processing | Online Analytical Processing |
| Goal                                | Run applications              | Analyze data                 |
| Typical operation                   | Insert, Update, Point Read    | Aggregation, Scans           |
| Latency                             | Milliseconds                  | Seconds/minutes acceptable   |
| Data volume per query               | Few records                   | Millions of records          |
| Example                             | Order placement               | Monthly sales report         |
| Cosmos DB Transactional Store       | ✅                             | ❌                            |
| Analytical Store / Fabric Mirroring | ❌                             | ✅                            |

The analytical store is a columnar representation of the data that is
isolated from the transactional workload, enabling analytics without affecting operational performance
VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
Cosmos DB
│
├── Transactional Store (OLTP)
│
└── Analytical Store (OLAP)
        │
        ├── Synapse Spark
        └── Serverless SQL Pool


VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
Microsoft recommends Azure Cosmos DB Mirroring for Microsoft Fabric for new projects because it provides the same zero-ETL analytics pattern with native Fabric integration
Cosmos DB
     |
     v
Fabric Mirroring
     |
     v
OneLake Delta Tables
     |
+----+-----+------+
|          |      |
Spark    SQL    Power BI

Microsoft now recommends Azure Cosmos DB Mirroring in Microsoft Fabric, which provides the same zero-ETL pattern and deeper Fabric integration 

| Technology                  | Reads From           |
| --------------------------- | -------------------- |
| Cosmos SDK                  | Transactional Store  |
| Change Feed Processor       | Transactional Store  |
| Synapse Spark               | Analytical Store     |
| Synapse Serverless SQL Pool | Analytical Store     |
| Fabric Mirroring            | OneLake Delta Tables |

/-----------------------------------------------------------------------------------\
| Requirement                      | Use Serverless SQL Pool | Use Fabric Mirroring |
| -------------------------------- | ----------------------- | -------------------- |
| Existing Synapse environment     | ✅                     | Possible              |
| Need T-SQL directly on Cosmos DB | ✅                     | ❌                   |
| New greenfield analytics project | ❌                     | ✅                   |
| Power BI in Fabric               | ❌                     | ✅                   |
| OneLake integration              | ❌                     | ✅                   |
| Lakehouse architecture           | ❌                     | ✅                   |
| Legacy DP-420 question           | ✅                     | Sometimes             |
| Future Microsoft direction       | ❌                     | ✅                   |
\===================================================================================/


| Scenario                                          | Recommended Technology                  | Data Source                      | Query Engine                             | Impact on OLTP             |
| ------------------------------------------------- | --------------------------------------- | -------------------------------- | ---------------------------------------- | -------------------------- |
| Transaction processing (application reads/writes) | Azure Cosmos DB for NoSQL               | Transactional store (OLTP)       | Cosmos DB SDK / SQL API                  | Designed for this workload |
| Near real-time analytics (legacy approach)        | Azure Synapse Link + Analytical Store   | Analytical store                 | Synapse Spark or Serverless SQL Pool     | No impact on OLTP          |
| New analytics projects                            | Cosmos DB Mirroring in Microsoft Fabric | Mirrored Delta tables in OneLake | SQL Endpoint, Lakehouse, Spark, Power BI | No impact on OLTP          |
| Reporting & BI in Fabric                          | Cosmos DB Mirroring                     | OneLake                          | Power BI, SQL Analytics Endpoint         | No impact on OLTP          |
| Ad-hoc T-SQL against Cosmos DB without data copy  | Synapse Serverless SQL Pool             | Cosmos DB Analytical Store       | OPENROWSET / T-SQL                       | No impact on OLTP          |

More Info & Links

DP-420 Style Practice Questions

Q1. What best describes an OLTP workload?

  • A. Many small, low-latency transactional operations such as inserts, updates, and point reads
  • B. Large batch aggregations run periodically over historical data
  • C. A single large data warehouse query joining many tables
  • D. A one-time bulk data migration job

Q2. How does Azure Cosmos DB avoid impacting OLTP performance when running analytical queries?

  • A. By maintaining a separate analytical store (via Azure Synapse Link) so analytical queries don't consume transactional RUs
  • B. By throttling all OLTP writes during analytical queries
  • C. By requiring OLTP and analytical workloads to share the same index
  • D. By disabling the change feed during analytics

Answer Key

Answers are listed at the very end so you can attempt the questions first.

  • Q1 (OLTP): A. Many small, low-latency transactional operations such as inserts, updates, and point reads — OLTP workloads consist of frequent, small, fast transactional operations, which is what Azure Cosmos DB's transactional store is optimized for.
  • Q2 (OLTP): A. By maintaining a separate analytical store (via Azure Synapse Link) so analytical queries don't consume transactional RUs — The analytical store is a separate, column-oriented copy of the data, so OLAP/analytical queries don't compete with OLTP workloads for RUs.

DP-420: Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB — term detail page

Retrieved from "https://ilianko.com/index.php?title=OLAP&oldid=6163"