Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Digging through SQL Server OLAP storage models

Learn how to distinguish among the three SQL Server OLAP storage models and decide which one is best in different use cases.

Basit FarooqBasit Farooq

The physical storage design for your multidimensional applications affects the latency, size and performance of...

your project. Therefore, when designing the physical storage for it, you must determine how to store the dimensions and measures, and how to design an effective physical storage strategy for multidimensional applications. In this article, I will review the standard online analytical processing (OLAP) storage models, and discuss the considerations involved when choosing a storage model for multidimensional applications.

Multidimensional applications store three types of information: metadata, detail data and aggregations. The metadata defines the multidimensional structure, including what facts and dimensions are used. The detail data contains the business metrics for analysis. And the aggregations summarize the detail data. The metadata is always stored in the multidimensional structure, but the storage locations for the detail data and aggregations are defined by the storage model.

Microsoft SQL Server Analysis Services (SSAS) supports three main physical storage models for multidimensional platforms: multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP). In practice, most SQL Server OLAP systems are implemented with the combination of these three storage models. However, considerations for choosing a storage model for SQL Server OLAP depend on various factors, such as data latency, query speed, processing time and the total size. The following are the main features of each OLAP storage model.


The default storage model for multidimensional platforms is MOLAP. In this storage model, a copy of the detail data and any aggregations are stored in a multidimensional structure. Therefore, they do not require a permanent connection to the underlying relational data store.

The MOLAP model also uses compression to store data in the multidimensional structure. It is the fastest model for queries because all the required data is actually stored in the multidimensional structure, and all calculations are pre-generated when the multidimensional structure is processed.

Data in the multidimensional structure is refreshed only when the multidimensional structure is processed, so latency is high. That means any subsequent changes to the relational data store will reflect in the multidimensional structure only once it is reprocessed. It also requires additional storage on the SQL Server OLAP server because the copy of the relational data is stored on OLAP server.

In the ROLAP storage model, the aggregations are stored in indexed views or tables in the source database. When a dimension is stored using ROLAP, the dimensions must be processed immediately if the schema of the source table changes.

Under the ROLAP model, data is accessed directly from the relational source database if it is not in the cache. It is usually the slowest of the three storage options for queries. It also has the slowest processing time, unless there are no aggregations.

In the HOLAP storage model, aggregations are stored in a multidimensional structure, and noncached data is accessed directly from the relational data source. It offers query performance equivalent to that of MOLAP when only summary aggregations are accessed. When detail data is required, its query performance is slower than MOLAP's and ROLAP's  because the data must be retrieved from the relational data source.

Decisions, decisions, decisions

The proactive caching settings in SSAS enable you to balance the issues of higher latency, usually associated with MOLAP solutions, and lower performance, usually associated with ROLAP solutions. This is possible because proactive caching can dramatically and intelligently update dimensions and measures when the underlying data changes. For more information, see Proactive Caching (Partitions).

Dimensions can use either the ROLAP or MOLAP storage model. Measure groups and partitions can use any of the three storage models. As mentioned earlier, MOLAP is the default storage model for both dimensions and measure groups.

The following table compares typical latency, query performance, processing performance and overall database size characteristics for the three storage models.

SQL Server storage models

You should also be aware of the following facts:

  • The proactive caching setting affects the degree of latency for each storage model. Proactive caching is useful in a transaction-oriented system in which changes are unpredictable or when real-time access to the data is required. Furthermore, proactive caching can be configured to access the ROLAP data until the MOLAP cache is rebuilt when maximum latency is reached. However, if ROLAP data is accessed while a MOLAP cache is being rebuilt, this slows the performance on the MOLAP system.
  • The original size of the detail data and the number of aggregations that are created affect the overall size of the storage model solution.
  • The processing performance for ROLAP storage is not slow if the "Do not design aggregations" option is selected.
  • The overall database size when ROLAP is used is small if the "Do not design aggregations" option is selected.
  • For HOLAP, summary queries to the aggregates are fast. Queries from the relational data source are slow.

About the author
Basit Farooq is a lead database administrator, trainer and technical author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms.

This was last published in January 2014

Essential Guide

Guide to SQL business intelligence, analytics and data visualization

Join the conversation


Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What SQL Server OLAP storage model do you usually prefer?
You should add another column, Admin/Dev Overhead, to your comparison table. How many administrators and developers are required to support the end user community? This is important because analysis is an art and one does not know where the data may lead the analyst. If significant down-time is required to reprocess the data into new, previously unplanned multidimensional cubes or drill pathways, that time should be factored into your performance metrics. And if the end user cannot effect that redesign/reprocessing while he is operating "stream of consciousness", they likely will need to be reminded what their original question was by the time the new solution is delivered to them. Anyone with military experience of land navigation knows what it's like to be directed to an area that falls in the white space off the edge of a map. Performing analysis with multidimensional cubes offers an analogous situation -- what if your need falls in the "white space" between cubes? This is where a ROLAP architecture offers significant advantages because of the ready availability of the data (as well as massive scalability), and metadata analysis and intelligent caching can mitigate subsequent query performance issues. This is an approach that Microstrategy, for example, has used for years.