MSAS : Working with Standard Dimensions

Dimensions are created, based on dimension table columns, member properties, or from the structure of OLAP data mining models. When a dimension is defined, there are a number of possible approaches. Each approach produces a different dimension variety. Standard Dimensions are regular dimensions. They can be of two types. The standard star schema dimension and the standard snowflake dimension.

Within the Dimension editor a new standard dimension can be created in two ways:-Using the Dimension Wizard or multiple dimension tables

Open the Dimension editor and select <new> from the dimension drop down list box on the tool bar. The Editor displays the ‘Choose a Dimension Table’ which allows the user to select only one user.

Select the Store table and click OK. When selecting a table for a snowflake dimension, it is better to select either the lowest level or the highest level. This kind of selection, enables the Dimension Editor create the joins correctly.

To add additional tables the user needs to click “Tables” on the Insert menu. Double click the Employee and salary tables and click Close. The Dimension Editor automatically joins the tables if a column with the same name is available in both the tables.

Click on the Name option in the basic tab of the Properties dialog box on the left pane of the editor and type in “Store” as the name of the Dimension.

To browse the contents of the tables, right-click on the caption bar of the dimension table and click browse data to see the contents of the table.

Member Count, Member Names Unique Property and Member Keys Unique Property

Double clicking a column name is equivalent to dragging the column name onto the dimension tree. It adds the column as a new bottom level.

Drag the column to the dimension tree and select Advanced tab of the properties pane. The member count is 13 because there are 13 unique members in the Store table. The Member Keys Unique property is set to TRUE and is also disabled because it is the top level of a dimension and must have unique key values.

The member names property is FALSE because the member names unique property is the same as the Member Key Column and hence does not have unique names. This can be changed in Specify a default Member which we will take up a little later in this tutorial.

Select the Employee dimension and look at the member count in the advanced tab. It shows 1155 member count and the Member Keys unique and the Member Names unique properties should be false. If either is set to true, it should be set to false.

Do a similar exercise for the Salary table and set the Member Keys Unique and Member Names Unique properties to false. Now click the Data tab to preview the dimension.