Home

MSAS: Dimension Storage Modes and Levels

Author : Exforsys Inc. Published on: 3rd Apr 2005

Dimensions are stored in the Multidimensional OLAP or Relational OLAP. The storage mode determines the location and form of the dimensions data. While MOLAP stores data in a multidimensional structure on the Analysis server, ROLAP stores the data in the relational tables. The storage mode can be set using the Dimension or cube editor.

Ads

When a Dimension’s storage mode is set to ROLAP the following settings have to be made to the levels in the dimension.

The lowest level’s Member Keys Unique property is to be set to TRUE.
The dimension must not contain member groups.
If the dimension is a private one, its Aggregation Usage property must be set to Standard.
If the dimension is a shared one, the Aggregation Usage property must be set to Standard, or Top Level Only or Bottom level only or Top and Bottom Levels(if All Level property is YES).
Any changes in the source table requires a reprocessing of the dimension.

Virtual and parent-child dimensions always have a MOLAP storage mode.

Level naming Template and Level Naming Dialog Box

The Level Naming dialog box is displayed only for the Parent child dimensions. This dialog box appears when the user selects a level in a parent child dimension and in the Advanced Tab of the properties box selects Level Naming Template and clicks on the ellipsis.

The Level Naming Template property of the Level Interface defines how levels in a parent child hierarchy are named. A template can be created in two ways.

1. A naming pattern can be designed. A naming pattern contains an asterisk as a placeholder character for a counter that is incremented and inserted into the name of each new and deeper level.

2. A list of names can be specified. A list of level names is created and each is separated from the other using semicolons. Each member of the list then is used to name a level. If the number of levels exceeds the number of names in the list, the last name in the list is used as a template for any additional names.

Allowing duplicate names in Dimensions

In the previous tutorial we saw how to allow duplicate names for members of a level using the Member Keys Unique Property and the Member Names Unique properties of levels. We will briefly recall the facts to refresh the memory. The Member Count returns the total number of unique names in the level. The Member Keys Unique property is TRUE and disabled because the top level of a dimension must always have unique keys. The Member Names Unique property will be FALSE if the Member Key Column is same as the Member Names Column. Duplicate names will be allowed if the Member Keys Unique and the Member Names Unique properties is all levels are set to False.

Ads

Creating Expression for member names in a level to make them unique

As described in the earlier tutorial expressions can be created to distinguish the members of a level when member names are similar or the same. The expression can be a prefix or a suffix that makes the member name unique. This can be done by clicking the basic tab and selecting the Member Name Column and clicking on the ellipsis button and entering the expression in the Select Column dialog box that appears.

Properties of Levels

There are a large number of properties which are supported by the level interface. A few properties that are commonly used are listed below:

AreMemberKeysUnique property indicates whether the members of a level are uniquely identified by their member key column.
AreMemberNamesUnique property indicates whether the members of a level are uniquely identified by their member name column.
ColumnSize defines the size of the members in the level member key column.
ColumnType defines the data type of the level member key column in an aggregation table.
Description is the description of the level.
EnableAggregations determines whether aggregations can be created for a level in a dimension whose AggregationUsage property is set to dimAggUsageColumn.
EstimatedSize details the estimated number of members in a level.
FromClause is the SQL FROM clause for a level.
IsDisabled indicates whether a level is disabled.
IsValid indicates whether a level is valid.
IsVisible indicates whether the level is visible to Client applications.
JoinClause is the SQL JOIN clause for the level.
LevelNamingTemplate defines how levels are named in a Parent Child hierarchy.
LevelType returns the enumeration constant that identifies the specific type of level.
Name is the name of the level.
Ordering specifies how the level should be ordered.
OrdinalPosition returns the ordinal position of the level in the collection of levels.
SkippedLevelColumn identifies the column that holds the number of empty levels between a member and its parent in a parent child hierarchy.
SliceValue is the name of the level member used to define a partition slice.