Modifying the Product Dimension

Article
12/03/2008

You can improve the user-friendliness of a dimension by using a named calculation to generate descriptive dimension member names, by defining additional user hierarchies, and by specifying the name of the member of the (All) level. You specify the name of the All member for attribute hierarchies separately from the All member name for each user hierarchy. In the tasks in this topic, you define a user hierarchy in the Product dimension, use a named calculation to spell out the product line names, and specify the (All) member name for the attribute hierarchies and for the new user hierarchy. You also delete unnecessary dimension attributes, and group attributes into display folders. For more information, see Removing Attributes from a Dimension, Defining Named Calculations in a Data Source View (Analysis Services), Defining and Configuring a User-Defined Hierarchy, Configuring the (All) Level for Attribute Hierarchies

Deleting Unnecessary Attributes

To delete unnecessary attributes

In Solution Explorer, double-click Product in the Dimensions node to open Dimension Designer for the Product dimension.
In the Attributes pane, select and delete the following attributes:

•Arabic Description

•Chinese Description

•English Description

•English Product Name

•Finished Goods Flag

•French Description

•French Product Name

•Hebrew Description

•Product Alternate Key

•Product Subcategory Key

•Size Unit Measure Code

•Spanish Product Name

•Thai Description

•Weight Unit Measure Code

These attributes will not be used in the initial Analysis Services Tutorial cube.

Defining Named Calculations, Modifying Name Property Values, and Defining a User Hierarchy

To define named calculations, modify name property values, and define a user hierarchy

Switch to Data Source View Designer for the Adventure Works DW data source view.
In the Diagram pane, right-click the Product table, and then click New Named Calculation.

The Create Named Calculation dialog box opens. You will use this dialog box to create a named calculation that you will use to display the full product line name instead of the cryptic name.
In the Create Named Calculation dialog box, type ProductLineName in the Column name box.

In the Expression box, type the following SQL script:

CASE ProductLine
   WHEN 'M' THEN 'Mountain'
   WHEN 'R' THEN 'Road'
   WHEN 'S' THEN 'Accessory'
   WHEN 'T' THEN 'Touring'
   ELSE 'Components'
END

This SQL script creates user-friendly names for each product line in the cube.

Click OK.

The ProductLineName named calculation is created.
Switch to Dimension Designer for the Product dimension, select Product Line in the Attributes pane of the Dimension Structure tab, and then change the value of the NameColumn property in the Properties window to DimProduct.ProductLineName (WChar), and then click OK.

The members of the Product Line attribute hierarchy will now display the full name of the product line instead of an abbreviated product line name, after you deploy these changes.
In the Attributes pane, select the Product attribute hierarchy, and then change the value of the NameColumn property in the Properties window to DimProduct.EnglishProductName (WChar).
Change the value of the Name property for the Product attribute to Product Name.
Drag the Product Line attribute from the Attributes pane to the Hierarchies and Levels pane.

A new user hierarchy is created that has a default name of Hierarchy.
In the Hierarchies and Levels pane, select Hierarchy, and then change the Name property in the Properties window to Product Model Lines.
Drag the Model Name attribute from the Attributes pane into the Product Model Lines hierarchy, underneath the Product Line level.

You have defined a second level in the Product Model Lines user hierarchy.
Drag the Product Name attribute from the Attributes pane into the Product Model Lines hierarchy, underneath the Model Name level.

You have defined a third level in the Product Model Lines user hierarchy.

Specifying Folder Names and All Member Names

To specify the folder and member names

In the Attributes pane, select the following attributes:
- Class
- Color
- Days To Manufacture
- Reorder Point
- Safety Stock Level
- Size
- Size Range
- Style
- Weight
In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Stocking.

You have now grouped these attributes into a single display folder.
In the Attributes pane, select the following attributes:
- Dealer Price
- List Price
- Standard Cost
In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Financial.

You have now grouped these attributes into a second display folder.
In the Attributes pane, select the following attributes:
- End Date
- Start Date
- Status
In the AttributeHierarchyDisplayFolder property cell in the Properties window, type History.

You have now grouped these attributes into a third display folder.
Select the Product Model Lines user hierarchy in the Hierarchies and Levels pane, and then change the AllMemberName property in the Properties window to All Products.
Click an open area of the Hierarchies and Levels pane, and then change the AttributeAllMemberName property to All Products.

Clicking an open area lets you modify properties of the Product dimension itself. You could also click the Product dimension icon at the top of the attributes list in the Attributes pane.

Reviewing Product Dimension Changes

To review the Product dimension changes

On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, switch to the Browser tab of Dimension Designer for the Product dimension. Verify that Product Model Lines is selected in the Hierarchy list, and then expand All Products.

Notice that the name of the All member appears as All Products and that the members of the Product Line level now have user-friendly names, instead of single letter abbreviations.
Select Product Line in the Hierarchy list.

Notice that the All member appears as All Products.
On the File menu, click Save All to save the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

In the next task in this lesson, you will review the Analysis Tutorial cube to view all of the changes that you have made in this lesson.

You have successfully improved the user-friendliness of the Analysis Services Tutorial cube by defining a Product Model Lines user hierarchy in the Product dimension, adding the ProductLineName named calculation to the data source view, and then defining the named calculation column as the ColumnID property for the Product Line attribute. You also improved the user-friendliness of the Adventure Works Tutorial cube by deleting dimension attributes that are not required in the cube, by grouping other dimension attributes into display folders, and by defining a name for the (All) level for each attribute hierarchy and for the Product Model Lines user hierarchy.

Next Task in Lesson

Viewing the Changes in Cube Designer