Dimension Storage Modes
A dimension can have one of two storage modes: multidimensional OLAP (MOLAP) or relational OLAP (ROLAP). MOLAP is the default storage mode of a dimension.
The storage mode determines the location and form of a dimension's data. A MOLAP dimension's data is stored in a multidimensional structure on the OLAP server. This structure is created when the dimension is processed. A ROLAP dimension's data is the dimension's table or tables.
MOLAP dimensions provide better query performance than ROLAP dimensions. However, huge dimensions, which are dimensions that have 10 million members or more, cannot support a MOLAP storage mode. If such a dimension's storage mode is MOLAP, processing it produces an error. It is recommended that only huge dimensions have a storage mode of ROLAP.
Note You can create ROLAP dimensions only if you install Analysis Services for Microsoft® SQL Server™ 2000 Enterprise Edition. Very large dimensions, which are generally dimensions that have 5 to 10 million members, can have a MOLAP storage mode.
A dimension's storage mode is set in the Storage Mode property in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private).
Before you set a dimension's Storage Mode property to ROLAP, ensure that it meets these requirements:
- The lowest level's Member Keys Unique property is set to True.
- The dimension does not contain member groups.
- If the dimension is a private dimension, its Aggregation Usage property is set to Standard if currently set to Custom.
- If the dimension is a shared dimension, in all cubes that include the dimension, its Aggregation Usage property is set to one of the following values:
- Standard.
- Top Level Only. This value is valid only if the dimension's All Level property is set to Yes.
- Bottom Level Only.
- Top and Bottom Levels. This value is valid only if the dimension's All Level property is set to Yes.
- Standard.
Important If a dimension's Storage Mode is ROLAP, any changes to its source table must be followed by immediate processing of the dimension. Failure to do so may result in inconsistent results to queries of the cubes that include the dimension. To ensure correct processing, include the update of the source table and the processing of the dimension in the same transaction. If you have installed SQL Server 7.0 or later, you can use Data Transformation Services (DTS) to perform the table update and dimension processing as successive tasks connected by a success precedence constraint. For more information, see Processing Objects Using Data Transformation Services. If the dimension is shared, process it with the Incremental update option. If the dimension is private, process its cube with the Refresh data option.
Virtual and parent-child dimensions always have a MOLAP storage mode.
All regular dimensions that use ROLAP for storage are also changing dimensions. That is, their Changing property is set to True. When you set the Storage Mode property for a regular dimension to ROLAP, its Changing property is automatically set to True. This value cannot be changed if the Storage Mode is ROLAP. For more information, see Changing Dimensions.
ROLAP dimensions do not support slicing in partitions.