ROLAP Dimension?

  • I was reading through some material and the words 'ROLAP Dimension' were pronounced but not explained.  How do they differ from dimensions used for MOLAP and HOLAP?

     

    Thanks : )

  • ROLAP:  refers to a relational database design.  Typical design methodology follows a star schemas for data storage and relation.  Open relational designs allow unlimited scalability by raw db size and the ability to drill or query anywhere throughout your logical model specifically detail level transactions.  Relational OLAP (ROLAP) stores aggregations in a relational structure and leaves the partition's source data in its existing relational structure.

    MOLAP:  refers to a multi-dimensional design.  Often built from a relational db strucutre (but don't have to be) data is pre-aggregated and stored in cubes that must be supported by the RDBMS.  Performance is its best attribute with data pre-aggregated at a summary level.  Multidimensional OLAP (MOLAP) stores aggregations and a copy of the partition's source data in a multidimensional structure on an Analysis server computer.

    HOLAP:  refers to a hybrid of the two taking most of its storage desing from MOLAP.  Hybrid OLAP (HOLAP) stores aggregations in a multidimensional structure on an Analysis server computer and leaves the partition's source data in its existing relational structure.

     

    BOL has more on the subject.

  • Thank you for taking the time to provide the information.  Unfortunately the power in the 'ROLAP Dimension' escapes me. 

     

    I want Real-Time Cubes where I don't have to move data into the cube or wait for it. So I thought I would pick ROLAP and check the Enable real-time updates checkbox … I think I can't have aggregations.

     

    But ROLAP Dimensions was specified but not extended upon for better performance.  Do you kow how a ROLAP dimension differs when compared to a normal cube dimension?

     

    Thanks Again

     

  • The key is how each are stored.  ROLAP doesn't change the storage source - as it exists in your relational table it is used for queries.  MOLAP makes a complete copy of the table and uses it for the cube structure.  In a ROLAP model, update your dimension tables first followed by fact table additions. 

    source:  Books on-line.

    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.

    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.

  • Great!  Thanks for the info!

  • you bet, if you need more info or a better example ( I know that's a lot of info thrown at you) let me know.  I have run full relational models with no user outage with great performance as well as Analysis Services with cube architecture.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply