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).
it’s Perfect!
Thank’s alot
LikeLike
This is a very helpful description of the various types of cubes in SSAS. Great job!
LikeLike
Its description ,Can u pls post how to schdule patition if new record add to DWH.
LikeLike
Really it is wonderful information Jorg. Thank you so much.
LikeLike
Its Simply Superb !!
LikeLike
Simple and understandable very nice.. thank u
LikeLike
Can u please post detailed explanation partitions and aggregations
..
Thank u.
LikeLike
Perfect Explanation!
LikeLike