Storage Modes in Analysis Services

Storage mode in SQL Server Analysis Services(SSAS) lets you decide where you want to physically store the data(both aggregations and cube data). There are three types of storage modes:

MOLAP (Multidimensional OLAP) – This mode stores both cube data and aggregations in Analysis Services server. This is the the default storage mode in SSAS and is the fastest of the three modes in terms of query performance. However, this mode requires more disk space(to duplicate the detail-level data) and will obviously increase the cube’s processing time. Also, in this mode, once the cube is processed, the relational data source is not accessed when users query the cube and so, any changes made to the relational data source after the processing of the cube will not be reflected in the cube until the cube in processed again.

ROLAP (Relational OLAP) – Unlike MOLAP, in this mode, both cube data and aggregations are stored in the relational data source. To store aggregations, Analysis Services will create additional tables in the relational data source. When users run MDX queries, Analysis Services will generate SQL statements against the relational data source to retrieve the data requested. Since the data has to be brought over from the relational data source(which in most cases resides on a different server), this mode is the slowest of the three modes in terms of query performance but it supports real-time and is better than MOLAP in terms of storage and processing.

HOLAP (Hybrid OLAP) – This mode is a hybrid between MOLAP and ROLAP and offers the best of the two modes. This mode stores cube data in the relational data source and aggregations & indexes in Analysis Services server. You need to be careful with HOLAP because since the aggregations are on the Analysis Services server, you will get wrong results when the leaf level data(in the relational store) is updated but the cube is not processed. So it is critically important to keep aggregations and the leaf level data in sync.