System OLTP vs Data Warehouse RDBMS vs OLAP Server

> (Data|State) Management and Processing > Data Warehouse

System OLTP Data Warehouse RDBMS OLAP Server
Purpose
System charter Operational Historical and detail data Analytic
Access
Access type Read/write Read-only Read/write
Access mode Atomic, singular, simple update Singular, list-oriented, queries and reports Iterative, comparative analytic investigation
Access process IT-supported queries IT-assisted or preplanned queries and reports IT-independent, ad hoc navigation and investigation drill-down
Response characteristics Fast update, varied query response Varied, potentially very slow query response Fast, consistent query response
Data Storage
Content scope Application-specific
Actual/vertical
Limited historical
Warehouse: cross-subject data
Data mart: single subject area
Historical data
Many cubes. Each cube is a single subject area: historical, calculated, projected, what-if, derived data
Data detail level Transaction detail Cleansed and lightly summarized Summarized, aggregated and calculated using sophisticated analytics
Data structure Normalized Normalized or denormalized Dimensional, hierarchical
Data structure design goal Update List-oriented query Analysis
Data volumes Gigabytes Gigabytes/terabytes Gigabytes

In MOLAP (vs ROLAP):

  • Performance
  • Hierarchy for the measures (formula hierarchy). You can drill down in the calculation.
  • Does not create a new blank category when you need a densification. In ROLAP, you get one:

  • No more “No Results”. The returned result set is always dense along the dimension.

OLTP systems have several limitations for use as a source of data for reporting and analysis:

  • An OLAP query on a OLTP system will cause locking and contention on the database, which slows down its primary role as a transactional system.
  • Large queries can cause performance issues for transactional workloads.
Advertising
data/warehouse/rolap_vs_molap.txt · Last modified: 2017/09/13 21:21 by gerardnico