Share via

Modifying the Customer Dimension

  • Article

There are many different ways that you can increase the user-friendliness and functionality of the dimensions in a cube. In the tasks in this topic, you modify the Customer dimension by deleting unnecessary attributes changing attribute and user-defined hierarchy names, changing user-defined hierarchy properties, and defining user friendly attribute names based on new named calculations in the data source view. You then deploy these changes, process the modified objects, and browse the dimension to view the changes.

Deleting Unused Attributes

Some attributes in the Customer dimension will not be used in the initial Analysis Services Tutorial cube, and can be deleted. For more information, see Removing Attributes from a Dimension.

To delete unused attributes

  1. Switch to Dimension Designer for the Customer dimension in Business Intelligence Development Studio, and then select the Dimension Structure tab.

  2. In the Attributes pane, select the following attributes and delete them:

    • Address Line1
    • Address Line2
    • Country Region Code
    • Customer Alternate Key
    • First Name
    • French Country Region Name
    • French Education
    • French Occupation
    • Last Name
    • Middle Name
    • Name Style
    • Sales Territory Key
    • Spanish Country Region Name
    • Spanish Education
    • Spanish Occupation
    • State Province Code
    • Suffix
    • Title

Modifying User-Defined Hierarchy and Attribute Properties

In addition to deleting unnecessary attributes in a dimension, you can also change attribute names and add or remove attributes from a user-defined hierarchy. By default, levels in a user-defined hierarchy have the same names as the attributes on which they are based. However, you can change the name of a hierarchy level without changing the underlying attribute name.

To modify user-defined hierarchy and attribute properties

  1. In the Attributes pane, right-click English Country Region Name and select Rename. Change the name of the attribute to Country-Region.

    In Lesson 9 you will learn how to define translation values for cube and dimension metadata. For example, you can change the value that displays for each metadata element so that the value appears in the language that is specified in the client application.

  2. Change the names of the following attributes in the same manner:

    • English Education attribute — change to Education
    • English Occupation attribute — change to Occupation
    • State Province Name attribute — change to State-Province

  3. In the Hierarchies and Levels pane of the Dimension Structure tab, select the State Province Name – Geography hierarchy. In the Properties window, change the Name property for this user hierarchy to Customer Geography.

    The name of the user hierarchy is now Customer Geography.

  4. Drag the Country-Region attribute from the Attributes pane into the Customer Geography user hierarchy above the State Province Name level.

    The Customer Geography hierarchy now has a Country-Region level.

  5. In the Customer Geography user hierarchy, change the name of the State Province Name level to State-Province.

  6. Drag the City attribute from the Attributes pane into the Customer Geography user hierarchy above the Customer level.

    The Customer Geography user hierarchy now has a City level.

  7. Delete Geography in the Customer Geography user-defined hierarchy.

    The following image shows the resulting attributes, hierarchies, and levels after you have made the changes specified in this task.

    Modified Customer dimension in Dimension Designer

Adding a Named Calculation

You can add a named calculation, which is a SQL expression that is represented as a calculated column, to a table in a data source view. The expression appears and behaves as a column in the table. When you create a named calculation, you specify a name and the SQL expression. Named calculations let you extend the relational schema of existing tables in a data source view without modifying the table in the underlying data source. Related topic: Defining Named Calculations in a Data Source View (Analysis Services)

To add a named calculation

  1. In BI Development Studio, switch to Data Source View Designer to view the Adventure Works DW data source view. (If this data source view is not open as one of the tabs in BI Development Studio, open it by double-clicking the data source view in the Data Source Views folder in Solution Explorer.)

  2. In the Tables pane, right-click Customer, and then click New Named Calculation.

  3. In the Create Named Calculation dialog box, type FullName in the Column name box, and then type the following CASE statement in the Expression box:

    CASE
   WHEN MiddleName IS NULL THEN
   FirstName + ' ' + LastName
   ELSE
   FirstName + ' ' + MiddleName + ' ' + LastName
END

    The CASE statement concatenates the FirstName, MiddleName, and LastName columns into a single column that you will use in the Customer dimension as the displayed name for the Customer attribute.

    The following image shows the Create Named Calculation dialog box.

    Create Named Calculation dialog box

  4. Click OK, and then expand Customer in the Tables pane.

    The FullName named calculation appears in the list of columns in the Customer table, with an icon that denotes that it is a named calculation.

  5. In the Tables pane, right-click Customer (dbo.DimCustomer), and select Explore Data.

  6. Review the last column in the Explore DimCustomer Table view.

    Notice that the FullName column appears in the data source view, correctly concatenating data from several columns from the underlying data source and without modifying the original data source.

  7. Close the Explore DimCustomer Table view.

Using the Named Calculation for Member Names

After you have created a named calculation in the data source view, you can use the named calculation as a property of an attribute, such as the NameColumn property to increase the usability of the attribute by making its name more user-friendly.

To use the named calculation for member names

  1. Switch to Dimension Designer for the Customer dimension, and then click the Customer attribute hierarchy in the Attributes pane of the Dimension Structure tab.

    Make sure you click the Customer attribute hierarchy and not the Customer dimension object in the Attributes pane.

  2. In the Properties window, change the Name property to Full Name.

  3. Expand the NameColumn property collection, expand the Source property collection, and then change the ColumnID property from EmailAddress to FullName.

    The name of each member of the Customer attribute hierarchy and the name of each member of the Customer level in the Customer Geography user hierarchy will be the customer's full name instead of the customer's e-mail address. This change will be visible after you deploy these changes and process the dimension and the cube.

    The following image shows the Customer attribute hierarchy and the ColumnID changed to FullName.

    Customer attribute hierarchy and ColumnID change

  4. In the Customer Geography user hierarchy, change the name of the lowest level from Customer to Full Name.

    Changing the name of an attribute does not change the name of a level in the hierarchy that is based on the attribute, nor does changing the name of a level in the Hierarchies and Levels pane change the name of the underlying attribute.

Adding an Attribute and Defining Display Folders

You can use display folders to group user and attribute hierarchies into folder structures to increase the user-friendliness of user and attribute hierarchies when users browse the dimension and the cube. Display folders can contain both user and attribute hierarchies.

To add an attribute and define display folders

  1. On the Dimension Structure tab for the Customer dimension, drag the EmailAddress column from the Customer table of the Data Source View pane into the Attributes pane.

    The Email Address attribute hierarchy is created.

  2. In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then in the Properties window, set the AttributeHierarchyDisplayFolder property for the selected attributes to Location:

    • City
    • Country-Region
    • Postal Code
    • State-Province

  3. In the Hierarchies and Levels pane, click Customer Geography, and then select Location as the value of the DisplayFolder property in the Properties window.

  4. In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then set the AttributeHierarchyDisplayFolder for the selected attributes to Demographic:

    • Commute Distance
    • Education
    • Gender
    • House Owner Flag
    • Marital Status
    • Number Cars Owned
    • Number Children At Home
    • Occupation
    • Total Children
    • Yearly Income

    The following image shows these attributes selected in the Attributes pane.

    Attributes selected on Dimension Structure tab

  5. In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then set the AttributeHierarchyDisplayFolder for the selected attributes to Contacts:

    • Email Address
    • Phone

Deploying Changes, Processing the Objects, and Viewing the Changes

After you have changed attributes and hierarchies, you must deploy the changes and reprocess the related objects before you can view the changes.

To deploy the changes, process the objects, and view the changes

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

  2. When deployment has successfully completed, click the Browser tab of Dimension Designer for the Customer dimension and then click Reconnect on the toolbar.

  3. Verify that Customer Geography is selected in the Hierarchy list, and then in the browser pane expand All, expand Australia, expand New South Wales, and then expand Coffs Harbour.

    As shown in the following image, the hierarchy now has a Country-Region level and a City level, and displays the full name of each customer at the Customer level, instead of displaying the e-mail address for each customer.

    Modified Customer Geography hierarchy

  4. Switch to Cube Designer for the Analysis Services Tutorial cube, click the Browser tab and then click Reconnect on the toolbar.

  5. In the Metadata pane, expand Customer.

    Notice that instead of a long list of attribute and user hierarchies, only the display folders and the hierarchies that do not have display folder values appear beneath Customer.

    Notice that four attribute hierarchies and one user hierarchy appear in this display folder, as shown in the following image.

    Location display folder

  6. Expand the Location display folder.

  7. On the File menu, or on the toolbar of BI Development Studio, click Save All so that you can stop the tutorial here if you want and resume it later.

You have successfully increased the user-friendliness of the Customer dimension in the Analysis Services Tutorial cube, by changing the hierarchy name and by changing hierarchy levels. You have also improved user-friendliness by deleting unused attributes; by creating a named calculation in the data source view that concatenates the first, middle, and last name columns into a single column and by then defining an attribute that is based on this named calculation; and by using this new attribute in the user hierarchy. Finally, you have increased user-friendliness by grouping user and attribute hierarchies into display folders.

Next Task in Lesson

Modifying the Time Dimension

See Also

Other Resources

Defining and Configuring Dimension Attributes
Removing Attributes from a Dimension
Renaming an Attribute
Defining Named Calculations in a Data Source View (Analysis Services)

Help and Information

Getting SQL Server 2005 Assistance