SSAS 2008 Storage Modes

In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). There are advantages and disadvantages to each, so I figured I'd take a few minutes to give a quick overview describing the storage modes and laying out some of the pros and cons of each.

Relational Online Analytical Processing (ROLAP)

The ROLAP storage mode allows the detail data and aggregations to be stored in the relational database. If you plan on using ROLAP, you need to make sure that your database is carefully designed or you'll run into some bad performance issues.

Pros:

• Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
• Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
• Low latency.

Cons:

• With all the data being stored in the relational database, query performance is going to be much slower than MOLAP.
• You must maintain a permanent connection with the relational database to use ROLAP.

Multidimensional Online Analytical Processing (MOLAP)

MOLAP is the default and thus most frequently used storage mode. With MOLAP storage, the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database.

Pros:

• Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
• The data is compressed so it takes up less space.
• And because the data is stored on the OLAP server, you don't need to keep the connection to the relational database.
• Cube browsing is fastest using MOLAP.

Cons:

• Because you don't have a real time connection to the relational database, you need to frequently process the cube to update your data.
• If there's more data, processing is going to take longer.
• There's also an additional amount of storage since a copy of the relational database is kept on the OLAP server.
• High latency.

Hybrid Online Analytical Processing (HOLAP)

HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Pros:

• HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
• Cubes are smaller than MOLAP since the detail data is kept in the relational database.
• Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
• Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Cons:

• Query performance can head downhill fast when more detail data is queried from the relational database.

This is by no means an exhaustive list of the details in regards to each storage mode, but I hope this has given you a good general understanding of the differences between ROLAP, MOLAP, and HOLAP. Feel free to post any comments or questions. Thanks, guys!