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




Array

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 |