• Article

Modifying the Product Dimension

In the tasks in this topic, you use a named calculation to provide more descriptive names for the product lines, define a hierarchy in the Product dimension, and specify the (All) member name for the hierarchy. You also group attributes into display folders.

Adding a Named Calculation

You can add a named calculation to a table in a data source view. In the following task, you create a named calculation that will display the full product line name.

To add a named calculation

  1. Open the Adventure Works DW data source view by double-clicking it in the Data Source Views folder in Solution Explorer.

  2. In the diagram pane, right-click the Product table, and then click New Named Calculation.

  3. In the Create Named Calculation dialog box, type ProductLineName in the Column name box.

  4. In the Expression box, type the following CASE statement:

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

    This CASE statement creates user-friendly names for each product line in the cube.

  5. Click OK to create the ProductLineName named calculation.

  6. On the File menu, click Save All.

Modifying the NameColumn Property of an Attribute

To modify the NameColumn property value of an attribute

  1. Switch to Dimension Designer for the Product dimension. To do this, double-click the Product dimension in the Dimensions node of Solution Explorer.

  2. In the Attributes pane of the Dimension Structure tab, select Product Line.

  3. In the Properties window, click in the NameColumn property field and then click the browse (…) button to open the Name Column dialog box.

  4. Select ProductLineName in the Source column list and then click OK.

    The NameColumn field now contains the text Product.ProductLineName (WChar). 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.

  5. In the Attributes pane of the Dimension Structure tab, select Product Key.

  6. In the Properties window, click in the NameColumn property field and then click the ellipsis (…) button to open the Name Column dialog box.

  7. Select EnglishProductName in the Source column list and then click OK.

    The NameColumn field now contains the text Product.EnglishProductName (WChar).

  8. In the Properties window, change the value of the Name property for the Product Key attribute to Product Name.

Creating a Hierarchy

To create a hierarchy

  1. Drag the Product Line attribute from the Attributes pane into the Hierarchies pane.

  2. Drag the Model Name attribute from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Product Line level.

  3. Drag the Product Name attribute from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Model Name level.

  4. In the Hierarchies pane of the Dimension Structure tab, right-click the title bar of the Hierarchy hierarchy, select Rename and type Product Model Lines.

    The name of the hierarchy is now Product Model Lines.

  5. On the File menu, click Save All.

Specifying Folder Names and All Member Names

To specify the folder and member names

  1. In the Attributes pane, select the following attributes by holding down the CTRL key while clicking each of them:

    • Class

    • Color

    • Days To Manufacture

    • Reorder Point

    • Safety Stock Level

    • Size

    • Size Range

    • Style

    • Weight

  2. In the AttributeHierarchyDisplayFolder property field in the Properties window, type Stocking.

    You have now grouped these attributes into a single display folder.

  3. In the Attributes pane, select the following attributes:

    • Dealer Price

    • List Price

    • Standard Cost

  4. In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Financial.

    You have now grouped these attributes into a second display folder.

  5. In the Attributes pane, select the following attributes:

    • End Date

    • Start Date

    • Status

  6. In the AttributeHierarchyDisplayFolder property cell in the Properties window, type History.

    You have now grouped these attributes into a third display folder.

  7. Select the Product Model Lines hierarchy in the Hierarchies pane, and then change the AllMemberName property in the Properties window to All Products.

  8. Click an open area of the Hierarchies 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.

  9. On the File menu, click Save All.

Defining Attribute Relationships

If the underlying data supports it, you should define attribute relationships between attributes. Defining attribute relationships speeds up dimension, partition, and query processing. For more information, see Defining Attribute Relationships and Attribute Relationships.

To define attribute relationships

  1. In the Dimension Designer for the Product dimension, click the Attribute Relationships tab.

  2. In the diagram, right-click the Model Name attribute and then select New Attribute Relationship.

  3. In the Create Attribute Relationship dialog box, the Source Attribute is Model Name. Set the Related Attribute to Product Line.

    In the Relationship type list, leave the relationship type set to Flexible because relationships between the members might change over time. For example, a product model might eventually be moved to a different product line.

  4. Click OK.

  5. On the File menu, click Save All.

Reviewing Product Dimension Changes

To review the Product dimension changes

  1. On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.

  2. After you have received the Deployment Completed Successfully message, click the Browser tab of Dimension Designer for the Product dimension and then click the reconnect icon on the toolbar of the designer.

  3. 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. This is because you changed the AllMemberName property for the hierarchy to All Products earlier in the lesson. Also, the members of the Product Line level now have user-friendly names, instead of single letter abbreviations.

Next Task in Lesson

Modifying the Date Dimension