9.09.2012

MOLAP vs ROLAP vs HOLAP

Difference between MOLAP, ROLAP and HOLAP in SSAS


MOLAP
ROLAP
HOLAP
MOLAP stands for Multidimensional Online Analytical Processing
ROLAP stands for Relational Online Analytical Processing
HOLAP stands for Hybrid Online Analytical Processing
The MOLAP storage mode causes the aggregations of the partition and a copy of its source data to be stored in a multidimensional structure in Analysis Services when the partition is processed.
The ROLAP storage mode causes the aggregations of the partition to be stored in indexed views in the relational database that was specified in the partition’s data source.
The HOLAP storage mode combines attributes of both MOLAP and ROLAP. Like MOLAP, HOLAP causes the aggregations of the partition to be stored in a multidimensional structure in an SQL Server Analysis Services instance.
This MOLAP structure is highly optimized to maximize query performance. The storage location can be on the computer where the partition is defined or on another computer running Analysis Services. Because a copy of the source data resides in the multidimensional structure, queries can be resolved without accessing the partition’s source data.
Unlike the MOLAP storage mode, ROLAP does not cause a copy of the source data to be stored in the Analysis Services data folders. Instead, when results cannot be derived from the query cache, the indexed views in the data source are accessed to answer queries.
HOLAP does not cause a copy of the source data to be stored. For queries that access only summary data in the aggregations of a partition, HOLAP is the equivalent of MOLAP.
Query response times can be decreased substantially by using aggregations. The data in the partition’s MOLAP structure is only as current as the most recent processing of the partition.
Query response is generally slower with ROLAP storage than with the MOLAP or HOLAP storage modes. Processing time is also typically slower with ROLAP. However, ROLAP enables users to view data in real time and can save storage space when you are working with large datasets that are infrequently queried, such as purely historical data.
Queries that access source data—for example, if you want to drill down to an atomic cube cell for which there is no aggregation data—must retrieve data from the relational database and will not be as fast as they would be if the source data were stored in the MOLAP structure. With HOLAP storage mode, users will typically experience substantial differences in query times depending upon whether the query can be resolved from cache or aggregations versus from the source data itself.
Pros
  • Provides maximum query performance, because all the required data (a copy of the detail data and calculated aggregate data) are stored in the OLAP server itself and there is no need to refer to the underlying relational database.
  • All the calculations are pre-generated when the cube is processed and stored locally on the OLAP server hence even the complex calculations, as a part the query result, will be performed quickly.
  • MOLAP uses compression to store the data on the OLAP server and so has less storage requirements than relational databases for same amount of data.
  • MOLAP does not need to have a permanent connection to the underlying relational database (only at the time of processing) as it stores the detail and aggregate data in the OLAP server so the data can be viewed even when there is connection to the relational database.
Pros
  • Ability to view the data in near real-time.
  • Since ROLAP does not make another copy of data as in case of MOLAP, it has less storage requirements. This is very advantageous for large datasets which are queried infrequently such as historical data.
  • In ROLAP mode, the detail data is stored on the underlying relational database, so there is no limitation on data size that ROLAP can support or limited by the data size of relational database. In nutshell, it can even handle huge volumes of data.

Pros
  • HOLAP balances the disk space requirement, as it only stores the aggregate data on the OLAP server and the detail data remains in the relational database. So no duplicate copy of the detail data is maintained.
  • Since HOLAP does not store detail data on the OLAP server, the cube and partitions would be smaller in size than MOLAP cubes and partitions.
  • Performance is better than ROLAP as in HOLAP the summary data are stored on the OLAP server and queries can be satisfied from this summary data.
  • HOLAP would be optimal in the scenario where query response is required and query results are based on aggregations on large volumes of data.

Cons
  • With MOLAP mode, you need frequent processing to pull refreshed data after last processing resulting in drain on system resources.
  • Latency; just after the processing if there is any changes in the relational database it will not be reflected on the OLAP server unless re-processing is performed.
  • MOLAP stores a copy of the relational data at OLAP server and so requires additional investment for storage.
  • If the data volume is high, the cube processing can take longer, though you can use incremental processing to overcome this.
Cons
  • Compared to MOLAP or HOLAP the query response is generally slower because everything is stored on relational database and not locally on the OLAP server.
  • A permanent connection to the underlying database must be maintained to view the cube data.

Cons
  • Query performance (response time) degrades if it has to drill through the detail data from relational data store, in this case HOLAP performs very much like ROLAP.


2 comments: