Storage Modes in SSAS (MOLAP, ROLAP and HOLAP)

HOLAP (Hybrid OLAP)

This mode is a hybrid of MOLAP and ROLAP and attempts to provide the greater data capacity of  ROLAP and the fast processing and high query performance of MOLAP.

In HOLAP storage mode, the cube detail data remains in the underlying relational data store and the aggregations are stored on the OLAP server. If you query only summary data in aggregation, the HOLAP storage mode will work similar to MOLAP. For the detail data queries, HOLAP will drill through the detail data in underlying relational data store and hence performance would not be as good as MOLAP. Therefore, your query would be as fast as MOLAP in if your query result can be provided from query cache or aggregation but performance would degrade if it needs the detail data from relational data store.

·         Pros

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

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

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

o    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

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

Summary and comparison

Basic Storage Mode

Storage Location for Detail Data

Storage Location for Summary/ Aggregations

Storage space requirement

Query Response Time

Processing Time

Latency

MOLAP

Multidimensional Format

Multidimensional Format

Medium

Because detail data is stored in compressed format.

Fast

Fast

High

HOLAP

Relational Database

Multidimensional Format

Small

Medium

Fast

Medium

ROLAP

Relational Database

Relational Database

Large

Slow

Slow

Low

Configuring the storage mode

Setting the storage mode is a relatively straightforward process – select a particular OLAP object in BIDS (Business Intelligence Development Studio), right click on it and then select properties. The property called StorageMode will allow you to set the storage mode to MOLAP, ROLAP or HOLAP.

Conclusion

In the part 1 of this article, I discussed the basic OLAP storage modes as well as their pros and cons and then finally I showed how you can configure the storage mode of OLAP objects in SSAS.

In part 2 of this article I will cover proactive-caching feature of SSAS, which allows the administrator to better control the frequency of cube data refresh, so that cube can refer to near real time data and at same time it also provides the query performance of MOLAP storage mode.  


References

http://msdn.microsoft.com/en-us/library/ms174915.aspx

SQL Server Books Online (BOL) / MSDN

Pages: 1 2




Related Articles :

  • No Related Articles Found

2 Responses to “Storage Modes in SSAS (MOLAP, ROLAP and HOLAP)”

  1. Very good explanation of MOLAP and ROLAP differences.

  2. Very well explained difference between each storage mode.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |