MSAS : Working with Standard Dimensions Page - 2

Creating Expressions for Member Names

Analysis server can be prevented from grouping members together by setting the Member Key Column property to the store_id column which is unique for each store. However, first the user must set a dimension property that allows the dimension to have duplicate names. Select the store dimension and set the Allow Duplicate Names Property to True. In the dimension tree select the Store name level.

On the basic tab of the properties pane select the Member Key Column property and click the ellipsis button and select the Store_id column in the Store table and click Ok.

The dimension preview changes to show each of the individual items as separate, identically named members.

Now select the Advanced tab in the properties pane. The Member Count property value has changed to 25, reflecting the total number of stores in the dimension table. The individual stores are shown as separate identically named members. This is technically correct but can be confusing. This is why the Dimensions “Allow Duplicate Names property” has False as the default value.

The values for Member Key Column and Member name column properties can be made unique by changing the values using SQL expressions. For instance the Store name can be made unique by changing the Member Key Column value to “Store”.“Store_number” + ‘ ‘+ “Store”.” “Store_Name”. The Data tab now shows the unique store names in the data tab.

It must be noted that each level of a dimension has a member key and a member name. The member key internally distinguishes one member from another. The member name appears on report captions and the default member key is the same as the member name. If the member name is to be distinguished from the member key then, the user needs to make sure that the member names appear as distinct on reports.

Every Dimension has a default member which is used when the dimension is not included in a query (especially while using Office 2000 Analysis components). The standard default member is the All level member. However, a constant default member can be specified. The Member Keys Unique and Member Names Unique property settings impact on the way the default member is specified.

In the Dimensions editor select the dimension for which you want to set the default property and switch to the Advanced tab of the Properties pane. Select Default Member Property and click the ellipsis button. Expand the members All Product and click OK. The description appears as the value of the Default Member Property and is an elaborate way of specifying a Unique name for the member. If the Member Keys Unique is set to True, Analysis manager can create a simpler default name.