Storage Mode in cube SSAS 2008

There are following storage modes in OLAP applications which affect the performance of OLAP queries and cube processing, storage requirements and also determine storage locations.

Ø  MOLAP

Ø  Scheduled MOLAP

Ø  Automatic MOLAP

Ø  Medium-latency MOLAP

Ø  Low-latency MOLAP

Ø  Real time HOLAP

Ø  Real time ROLAP

MOLAP Storage Mode

This is the default and most frequently used storage mode. In this mode when you process the cube, the source data is pulled from the relational store, the required aggregation is then performed and finally the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.

After processing, once the data from the underlying relational database is retrieved there exists no connection to the relational data stores. So if there is any subsequent changes in the relational data after processing that will not reflect in the cube unless the cube is reprocessed and hence it is called offline data-set mode.

Advantage

Ø  Stores the detail and aggregate data in the OLAP server in a compressed multidimensional format; as a result the cube browsing is fastest in this mode.

Ø  Provides maximum query performance, because all the required data (a copy of the detail data and calculated aggregate data) are stored in the OLAP server itself and there is no need to refer to the underlying relational database.

Ø  All the calculations are pre-generated when the cube is processed and stored locally on the OLAP server hence even the complex calculations, as a part the query result, will be performed quickly.

Ø  MOLAP does not need to have a permanent connection to the underlying relational database (only at the time of processing) as it stores the detail and aggregate data in the OLAP server so the data can be viewed even when there is connection to the relational database.

Ø  MOLAP uses compression to store the data on the OLAP server and so has less storage requirements than relational databases for same amount of data. (Note however, that beginning with SQL Server 2008 you can use data compression at relational database level as well).

Disadvantage

Ø  With MOLAP mode, you need frequent processing to pull refreshed data after last processing resulting in drain on system resources.

Ø   Latency just after the processing if there is any changes in the relational database it will not be reflected on the OLAP server unless   re-processing is performed.

Ø  MOLAP stores a copy of the relational data at OLAP server and so requires additional investment for storage.

Ø  If the data volume is high, the cube processing can take longer, though you can use incremental processing to overcome this

Scheduled MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  Notifications are not received when data changes.

Ø  Processing is automatically performed every 24 hours.

Automatic MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with no restriction on latency.

Medium-latency MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with a target latency of four hours.

Low-latency MOLAP

Ø  Measure group data and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  Processing is performed automatically with a target latency of 30 minutes.

Real-time HOLAP

Ø  Measure group data is maintained in a relational format, and aggregations are stored in multidimensional format.

Ø  The server will listen for notifications when data changes.

Ø  All Queries reflect the current state of data.

Real-time ROLAP

In comparison with MOLAP, ROLAP does not pull data from the underlying relational database source to the OLAP server but rather both cube detail data and aggregation stay at relational database source. In order to store the calculated aggregation the database server creates additional database objects (indexed views). In other words, the ROLAP mode does not copy the detail data to the OLAP server, and when a query result cannot be obtained from the query cache the created indexed views are accessed to provide the results.

Advantage

Ø   Ability to view the data in near real-time.

Ø   Since ROLAP does not make another copy of data as in case of MOLAP, it has less storage requirements. This is very advantageous for large datasets which are queried infrequently such as historical data.

Ø   In ROLAP mode, the detail data is stored on the underlying relational database, so there is no limitation on data size that ROLAP can support or limited by the data size of relational database. In nutshell, it can even handle huge volumes of data.

Disadvantage

Ø  Compared to MOLAP or HOLAP the query response is generally slower because everything is stored on relational database and not locally on the OLAP server.

Ø   A permanent connection to the underlying database must be maintained to view the cube data.

Different Parameters and their explanation

Cache Settings:

1. Silence Interval

2. Silence Override Interval

3. Latency

4. Rebuild Interval 

Options:

1. Bring Online Immediately

2. Enable ROLAP Aggregation

3. Apply Settings to Dimension

For example, consider different parameter settings of Low Latency MOLAP option mentioned in below screen shot:

Silence Interval: For "Low Latency MOLAP", Silence Interval has been set 10 seconds. Whenever relational database change happens and notification comes to Analysis Server. Silence Interval start its stopwatch and if no other Database changes comes before 10 seconds then it will start reprocessing cube with new changes. If new database changes comes <10 seconds then Silence interval Stopwatch will be reset to zero so if frequent changes are coming then analysis services will wait till all the changes complete.
Silence Override Interval: As soon as first database change comes after last reprocessing of cube then analysis services start one more stopwatch "Silence Override Interval". It basically overrides "Silence Interval" for reprocessing cube. For the same example, if frequent database changes are happening and silence interval is getting reset each time then SSAS will forcibly process cube after it passes "Silence Override interval" so in this example, cube will be processed after 10 minutes.
Latency: Old MOLAP cache will be dropped after time interval specified in Latency. Latency ensures the time interval after which data will not be old. If new cache is not available after Latency period then queries will be addressed by relational database and user will see significant performance drop in query response time.

Update the cache frequently(Rebuild Interval): MOLAP cache will be rebuild after specified "Rebuild interval" irrespective of database changes. It means if database changes doesn't occur then also MOLAP cache will be rebuild.

Bring Online Immediately: If you select this option then queries will be addressed by relational DB when new cache is not up and old cache has been dropped.

Enable ROLAP Aggregation: If you select this option then Aggregation objects will be created in relational database.
a) Indexed Views in SQL Server 2005 and SQL Server 2008
b) Materialized Views in Oracle

 Apply Settings to Dimension: If you select this option then same cache settings will be applied to all related dimensions from partition. This will not be available for HOLAP standard setting.

Here is the chart which compares different Standard Storage Settings in SSAS. You can customize and create your own settings by clicking on "Custom Settings" and then specifying different parameters.

	Storage Mode	Enable Proactive Caching	Silence Interval	Silence Override Interval	Latency	Rebuild Interval	Bring Online Immediately
MOLAP	MOLAP	No
Scheduled MOLAP	MOLAP	Yes				1 Day
Automatic MOLAP	MOLAP	Yes	10 Sec	10 Min
Medium Latency MOLAP	MOLAP	Yes	10 Sec	10 Min	4 hours		Selected
Low Latency MOLAP	MOLAP	Yes	10 Sec	10 Min	30 Min		Selected
Real Time HOLAP	HOLAP	Yes	0 Sec		0 Sec		Selected
Real Time ROLAP	ROLAP	Yes	0 Sec				Selected