System OLTP vs Data Warehouse RDBMS vs OLAP Server
|System||OLTP||Data Warehouse RDBMS||OLAP Server|
|System charter||Operational||Historical and detail data||Analytic|
|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|
|Content scope|| Application-specific |
| Warehouse: cross-subject data |
Data mart: single subject area
|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|
In MOLAP (vs ROLAP):
- 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.