MOLAP vs ROLAP

OLTP vs OLAP, cubes, MDX, data maturity and salaries.

OLTP vs OLAP

Before comparing cube types, separate the two kinds of database workload.

Characteristic	OLTP (transactional system)	OLAP (decision support system)
Application	Day-to-day operations, production	Analysis, decision support
Use cases	Mobile apps and ERP systems such as SAP, Oracle, and Microsoft Dynamics 365 F&O	BI tools such as Power BI, Tableau, and others
Users	Information system experts	Decision-makers
Data schema	Entity / Relationship	Star / Snowflake / Constellation
Normalization	Frequent	Rare
Data	Up to date, raw	Archived, aggregated
Updating	Immediate, real time	Delayed or postponed
Queries	Simple, regular, predefined, predictable	Complex, irregular, unpredictable, ad hoc
Query language	SQL, QBE, QUEL	MDX, XQuery, XMLA
Analysis axis	One- or two-directional	Multidimensional
Operations	Insert, update, cancel	Read, cross-analysis, refresh
Data size	Megabytes to gigabytes	Terabytes to zettabytes

OLTP systems run the business: many small, fast writes against a normalized schema. OLAP systems analyze the business: few, heavy, unpredictable reads over large volumes of historical, aggregated data modeled as a star or snowflake.

Online Analytical Processing (OLAP)

An OLAP cube is data pre-aggregated and cached to a file or to memory. Picture a cube with three axes: Products (laptop, mobile, TV, tablet), Geography (USA, Asia, Australia, Europe) and Time (Q1–Q4). Each cell holds a measure, such as sales for TVs in Asia in Q2. Slicing along one axis gives you a whole row or column of cells at once.

The best-known cube engine is Microsoft SQL Server Analysis Services (SSAS). You query it with MDX (Multidimensional Expressions) instead of SQL:

SELECT
    NON EMPTY TOPCOUNT({[ASIN].[ASIN].Members}, 5,
        ([Measures].[Gross.Profit], [Category].[Category].[LCD])) ON ROWS,
    NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS
FROM [Amazon]
WHERE ([Measures].[Gross.Profit], [Time].[ALL].[AllMember].[2011].[5], [Brand].[Brand].[LG])

This query returns the top 5 products (ASINs) by gross profit in the LCD category, for the LG brand, in May 2011. Rows and columns are axes of the cube, not tables, and WHERE picks a slice of the cube rather than filtering rows.

Cube types: MOLAP vs ROLAP

	MOLAP (Multidimensional OLAP)	ROLAP (Relational OLAP)
Where the data lives	In a dedicated multidimensional cube (Time × Product × Location)	In relational tables: a Sales fact table joined to Product, Location and Time dimensions
Query language	MDX	SQL
Caching	The cube itself is the cache, pre-aggregated in advance	Queries run on the database, optionally cached by the BI tool
Typical tools	MS SSAS (modern alternative: Tabular Model)	SAP BusinessObjects, Tableau, Power BI

MOLAP precomputes aggregates into a cube, so reads are very fast, but the cube has to be rebuilt when the data changes. ROLAP keeps the data in a star schema in the warehouse and generates SQL on the fly. This is how most modern BI tools work, and it scales with the power of the database underneath.

Do you need to learn OLAP and MDX?

Only if you want to become an expert in the Microsoft BI stack:

• Analysis Services (MOLAP and Tabular model)
• Integration Services
• Power BI
• SQL Server
• T-SQL
• and later, the Azure cloud

That path makes sense if you want to become a certified Microsoft specialist and set other vendors aside. Even there, modern solutions use the Tabular model far more often than classic MOLAP cubes, so MDX is a niche skill today. For most analysts, SQL on a star schema (ROLAP) is what matters.