MSBI (SSIS/SSRS/SSAS) Online Training

Tuesday, November 18, 2008

Storage Classes in OLAP

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
DimensionsMOLAP (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)ConclusionSSAS 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).

No comments: