MSAS: Working with Levels and Hierarchies Page - 2

The levels in the dimension can be defined using the Dimension Wizard or the Dimension editor. When the levels are created using the Dimension wizard, the columns appear under Available columns if the schema selected is the Star schema: Single dimension table; Snowflake schema: Multiple, related dimension table, or Mining Model: A predictable Column of an OLAP mining model. The level must begin with the most summarized and drilldown to the most detailed.

Available columns

Click the columns you want to use in your dimension. Use the arrow keys to move selected columns to the dimension levels pane.

Dimension levels

The levels that will be generated can be viewed based on the columns selected in the Available Columns. The arrow keys can then be used to remove the unwanted levels or to change their order.

Count level members automatically

The members of the selected level can be counted in Available dimensions. To speed up the process the Count level members automatically can be checked.

Uniquely identifying level members

The member key columns can then be changed for the level members using the next screen of the Wizard. This step is used when the default member key columns do not identify the level members. This wizard step is only visible if you selected Star Schema: A single dimension table or Snowflake Schema: Multiple, related dimension tables in the second step of the wizard.

Selecting Level using Dimension Editor

Levels in the Dimension Editor can be selected by double clicking on the level or by dragging the level and dropping it on to the dimension tree pane. We learnt how to do this while creating Standard dimensions and parent child dimensions in the previous tutorial. Once the levels of a dimension have been selected with the Dimension wizard, the same can be edited using the Dimension editor. Levels can be dragged and dropped into new positions in the Dimension Editor.

Working with All level and All Member

We have stated that Levels are created when the Dimension is created using the tools in Analysis Services. The All level is optional for all dimensions except the virtual dimension. However, the All level is created by default in all dimensions and it is named All followed by the name of the dimension. This level can be removed if not required.

The All level is a special Level and the topmost level in the hierarchy. It contains only one member whose value is an aggregation of the values of all members in the immediately subordinate levels in the hierarchy. The single member of the All Level is called the All Member. The Dimension Editor or the cube Editor properties pane is used to add or remove or change the name of the (All) member. The All Level property controls the inclusion of the ( All )Level. The name of the All Member is set in the Dimension’s All Caption property.

Queries on cubes are impacted by the existence or non existence of the All Level. If the query does not slice explicitly on a particular dimension or projects on the axis of a dimension, the default All member is sliced. The data set that is retrieved is complete data set of that dimension. If the All member does not exist in the dimension then, or a different default member has been specified for the dimension, an incomplete data set of the dimension is retrieved. The incompleteness of this data set may also be hidden from the user. Hence it is recommended that an All level and member is set for every dimension where no other default member has been set.

The default member of a dimension is specified in the Default member property of the properties pane of the Dimension or cube editor. Within a dimension separate default members can be selected for each role. The procedure for setting the default member has already been explained in the previous tutorial.(Building dimensions using Dimension Editor)