Design and Create cube by identifying measures and dimensions for Design storage using storage mode MOLAP, ROLAP and HOLAP.

Practical - 3
Design and Create cube by identifying measures and dimensions for Design storage using storage mode MOLAP, ROLAP and HOLAP.

Download Practical

Microsoft SQL Server Analysis Services provides several standard storage configurations for storage modes and caching options. These provide commonly used configurations for update notification, latency, and rebuilding data.Storage modes are ways to physically store the data in cubes and dimensions.

SQL Server offers three cube types-MOLAP, HOLAP and ROLAP.

User can select the storage mode from the settings.

Fig.  1 Storage Mode

All the settings are discussed below:

1.        Multidimensional OLAP (MOLAP):

MOLAP storage mode uses array-based multidimensional storage engine for multidimensional views of data. With Multidimensional data stores, the storage utilization is low if the data set is sparse. Many MOLAP servers use two levels of data storage representation to handle dense and sparse data sets.

2.        Scheduled MOLAP:

Scheduled MOLAP used for a data source when only daily updates are required. Queries are always against data in the MOLAP cache, which is not discarded until a new cache is built and its objects are processed.

3.        Automatic MOLAP:

Automatic MOLAP used for a data source when query performance is of key importance. It is   automatically processes MOLAP objects whenever required after the latency interval. Queries do not return the most recent data while the new cache is being built and processed.

4.        Medium- Latency MOLAP:

Medium-Latency MOLAP used for a data source with frequent (or less frequent) updates when query performance is more important than always providing the most current data. It is   automatically processes MOLAP objects whenever required after the latency interval. Performance is slower while the MOLAP objects are being reprocessed.

5.        Low-Latency MOLAP:

Low-Latency MOLAP used for a data source with frequent updates when query performance is somewhat more important than always providing the most current data. It is automatically processes MOLAP objects whenever required after the latency interval. Performance is slower while the MOLAP objects are being reprocessed.

6.        Hybrid Online Analytical Processing (HOLAP):

Hybrid Online Analytical Processing used for a data source with frequent and continuous updates (but not so frequent as to require real-time ROLAP) and users always require the latest data. This method normally provides better overall performance than ROLAP storage. Users can get MOLAP performance from this setting if the data source stays silent long enough.

7.        Real Time ROLAP (ROLAP):

Real time ROLAP used for a data source with very frequent and continuous updates when the very latest data is always required by users. Depending on the types of queries generated by client applications, this method is liable to give the slowest response times.

Fig.  2Cube Data Source View

There are two types of storage settings available-

·         Standard setting

Fig.  3 Standard Storage

·         Custom setting

Fig.  4Custom Storage

Now we will see the implementation of different Storage Modes on the above experimental Cube Data Source.

1.      MOLAP (Multidimensional Online Analytical Processing)

Fig.  5 Storage mode MOLAP selected

Fig.  6 Processing Time of MOLAP

2.      ROLAP (Relational Online Analytical Processing)

Fig.  7 Storage mode ROLAP selected

Fig.  8 Processing Time of ROLAP

3.      HOLAP (Hybrid Online Analytical Processing)

Fig.  9 Storage Mode HOLAP selected

Fig.  10 Processing Time of HOLAP

Comparison:

Basic Storage Mode	Storage Location for Detail Data	Storage Location for Summary/ Aggregations	Storage space requirement	Query Response Time	Processing Time	Latency
MOLAP	Multidimensional Format	Multidimensional Format	Medium Because detail data is stored in compressed format.	Fast	Fast	High
HOLAP	Relational Database	Multidimensional Format	Small	Medium	Fast	Medium
ROLAP	Relational Database	Relational Database	Large	Slow	Slow	Low