| 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.