data-warehouseolaprolap

Difference between a data warehouse and a MOLAP server


What is the difference between a data warehouse and a MOLAP server? Is the data stored at both the data warehouse and on the MOLAP server?

When you pose a query, do you send it to the data warehouse or the MOLAP server?

With ROLAP, it kind of makes sense that the ROLAP server pose SQL queries to the data warehouse (which store fact and dimension tables), and then do the analysis. However, I have read somewhere that ROLAP gathers its data directly from the operational database (OLTP), but then, where/when is the data warehouse used?

Architecture


Solution

  • The 'MOLAP' flavour of OLAP (as distinguished from 'ROLAP') is a data store in its own right, separate from the Data Warehouse.

    Usually, the MOLAP server gets its data from the Data Warehouse on a regular basis. So the data does indeed reside in both.

    The difference is that a MOLAP server is a specific kind of database (cube) that precalculates totals at levels in hierarchies, and furthermore structures the data to be even easier for users to query and navigate than a data warehouse (with the right tools at their disposal). Although a data warehouse may be dimensionally modelled, it is still often stored in a relational data model in an RDBMS. Hence MOLAP cubes (or other modern alternatives) provide both performance gains and a 'semantic layer' that makes it easier to understand data stored in a data warehouse.

    The user can then query the MOLAP server rather than the Data Warehouse. That doesn't stop users querying the Data Warehouse directly, if that's what your solution needs.

    You're right that when the user queries a ROLAP server, it passes on the queries to the underlying database, which may be an OLTP system, but is more often going to be a data warehouse, because those are designed for reporting and query performance and understandability in mind. ROLAP therefore provides the user-friendly 'semantic layer' but relies on the performance of the data warehouse for speed of queries.