THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands

SSAS – MOLAP, ROLAP and HOLAP storage types

A big advantage of a BI solution is the existence of a cube. Data and aggregations are stored in a optimized format to offer very fast query performance.
Sometimes, a big disadvantage of storing data and aggregations in a cube is the latency that it implies. SSAS processes data from the underlying relational database into the cube. After this is done the cube is no longer connected to the relational database so changes to this database will not be reflected in the cube. Only when the cube is processed again, the data in the cube will be refreshed.

SSAS 2005 gives you the possibility to choose different storage types for the following objects:

  • Cubes
  • Partitions
  • Dimensions

MOLAP (Multi dimensional Online Analytical Processing)
MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

ROLAP (Relational Online Analytical Processing)
ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.
Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.
 

HOLAP (Hybrid Online Analytical Processing)
HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type.
Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification and will process the aggregations again.
With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.


The different storage types of SSAS:

 


 

Data storage

 

Aggregations storage

 

Query performance

 

Latency

 

MOLAP

 

Cube

Cube

 

High

 

High

HOLAP

 

Relational database

Cube

Medium

Low (none)

ROLAP

 

Relational database

Relational database

Low

Low (none)



Conclusion

 

SSAS offers three storage types that give you all the flexibility you need. You can choose between high performance and high latency on one side(MOLAP) and lower performance but low latency(ROLAP) on the other side. There is also a possibility to choose a way in between(HOLAP).

 

Published Thursday, March 27, 2008 11:39 PM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Palik said:

it's Perfect!

Thank's alot

November 6, 2012 4:43 AM
 

Greg said:

This is a very helpful description of the various types of cubes in SSAS. Great job!

December 9, 2012 10:49 PM
 

Ram said:

Its description ,Can u pls post how to schdule patition if new record add to  DWH.

September 12, 2013 4:58 AM
 

Ram said:

Its description ,Can u pls post how to schdule patition if new record add to  DWH.

September 12, 2013 4:58 AM
 

Haja said:

Really it is wonderful information Jorg. Thank you so much.

December 29, 2013 6:59 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement