Physical storage options affect the performance and storage requirements for cubes. You can store a cube in a MOLAP (multidimensional OLAP) structure, a ROLAP (relational OLAP) database, or a HOLAP (hybrid OLAP) combination of multidimensional structure and relational database. There are advantages and disadvantages to each storage strategy.
MOLAP storage uses a multidimensional structure to contain aggregations and a copy of the base data. MOLAP storage provides the potential for the most rapid query response times, depending only on the percentage and design of the cube’s aggregations. In general, MOLAP is more appropriate for cubes with frequent use and the necessity for rapid query response.
ROLAP storage uses tables in the data warehouse relational database to store a cube’s aggregations. In contrast to MOLAP storage, ROLAP does not store a copy of the base data, accessing the original fact table when necessary to answer queries. ROLAP query response is generally slower than that available with the other two storage strategies. A typical use of ROLAP is for large data sets that are infrequently queried, such as historical data from less recent previous years.
HOLAP storage combines attributes of both MOLAP and ROLAP. Aggregation data is stored in MOLAP structures and the base data is left in the data warehouse’s relational database. For queries that access summary data, HOLAP is the equivalent of MOLAP. Queries that access base data, such as a drill-down to a single fact, must retrieve data from the relational database and will not be as fast as if the base data were stored in the MOLAP structure. Cubes stored as HOLAP are smaller than equivalent MOLAP cubes and respond faster than ROLAP cubes for queries involving summary data. HOLAP storage is generally suitable for cubes that require rapid query response for summaries based on a large amount of base data.
Microsoft® SQL Server™ OLAP Services supports all three storage modes. The Storage Design wizard provides options for you to choose the storage mode most appropriate for your cube.
Note Aggregations cannot be created for a cube with ROLAP storage if the data source is OLAP Services (that is, if the provider is “Microsoft OLE DB Provider for OLAP Services”).
Caution Partitioning cubes and merging partitions are advanced techniques - it is possible to create partitioned cubes that contain incorrect data. For more information about specific precautions, see Partitions in Advanced Topics.
Cubes can be divided into partitions, each of which can be stored using a different mode. For example, you can create a cube of several years’ worth of data and partition it at year boundaries. You can store the current year’s data partition in a MOLAP structure with a high percentage of aggregations for quick response to users. You can use HOLAP to store the previous year’s data partition, providing good response to summary queries with reduced storage needs. You can store data for years prior to the previous year in one or more ROLAP partitions with a smaller percentage of aggregations, saving on storage space with a tradeoff in query response.
A cube’s partitions are invisible to the user. In the preceding example, any query valid for the entire cube will execute, but queries that return older data will take more time than those that request current information.
OLAP Services provides a Partition wizard to assist in creating partitions. However, it is important that partitions be defined to contain mutually exclusive data. A cube may return incorrect results for some queries if a portion of the cube's data is included in more than one of its partitions.
A cube’s partitions can be stored on different servers, providing a clustered approach to cube storage.
Two partitions of a cube can be merged into a single partition, which can then be merged into another partition, and so on until only a single partition remains. For example, four partitions, each containing data for a quarter, can be merged into a single partition that contains the data for the entire year. There are precautions that need to be considered when merging partitions to ensure the resulting partition contains correct data.
For precautions and more information about partitions, see Partitions in Advanced Topics.