Modifying Measures, Attributes and Hierarchies (SQL Server Video)

Video Summary

This video demonstrates how to modify the measures, attributes and hierarchies in a cube.

Video Transcript

Hi, my name is Mary Brennan. I’m a technical writer for Microsoft SQL Server.

In this video, I’ll show you how to modify the measures, attributes and hierarchies in a cube.

You can control how measures are displayed to users by setting formatting properties:

• In the Internet Sales measure group, click Order Quantity, and click Properties.

• In the FormatString list, type #,#.

  The grid view lets me select multiple measures at the same Date by holding down the CTRL key.

• In the FormatString list, select Currency. Select Unit Price Discount Pct, and then select Percent. Change the Name property to Unit Price Discount Percentage. Change the name to Tax Amount.

Next, I’ll modify the attributes in the Date dimension. I’ll add a named calculation to a table in the data source view. A named calculation is an SQL expression that appears as a column in the table. Named calculations let you to extend tables in the data source view without modifying the underlying data source.

• In the data source view, right-click Date, and select New Named Calculation.

  I’ll call it SimpleDate. The statement combines the year, month, and day values from the FullDateAlternateKey column. The SimpleDate named calculation appears in the list of columns in the Customer table, with an icon that indicates that it is a named calculation. Later, we’ll use this new column as the displayed name for the FullDateAlternateKey attribute.

• Select Explore Data to view the SimpleDate column.

  After you have created a named calculation, you can use it as the property of an attribute. I’ll change the namecolumn of Date Key to use the dimple date named calculation.

• Click the Date Key attribute. Click in the NameColumn property field, and then click the ellipsis button. Select SimpleDate.

You create a new hierarchy by dragging attributes from the Attributes pane to the Hierarchies pane. I’ll create a hierarchy using the Calendar Year, Calendar Semester, Calendar Quarter, English MonthName and Date Key attributes.

• Drag the Calendar Year attribute from the Attributes pane into the Hierarchies pane.

• Drag the Calendar Semester attribute into the <new level> cell in the Hierarchies pane, underneath the Calendar Year level.

• Drag the Calendar Quarter attribute under the Calendar Semester level.

• Drag the English Month Name attribute from under the Calendar Quarter level.

• Drag the Date Key attribute under the English Month Name level.

• Rename the hierarchy to Calendar Date.

• Rename the English Month Name level to Calendar Month and rename the Date Key level to Date.

  We won’t be using the FullDateAlternateKey attribute so I’ll delete it.

You should define attribute relationships between attributes if the underlying data supports it. Defining attribute relationships speeds up dimension, partition, and query processing.

• Click the Attribute Relationships tab.

  I’ll create a New Attribute Relationship between English Month Name and Calendar Quarter because there’s a relationship because a quarter contains months.

• Set the relationship type to Rigid because relationships between the members will not change over time.

  In other words, months will always stay in the same quarter.

We’ll create an Attribute Relationship between Calendar Quarter and Calendar Semester because a semester contains quarters. We do the same with Calendar Semester and Calendar Year. Next we’ll create user-friendly name columns that will be used by the EnglishMonthName, CalendarQuarter, and CalendarSemester attributes.

Switch to the data source view.

I’ll create a Named Calculation called MonthName that concatenates the month and year into a new column in the Date table. Next, I’ll create a Named Calculation called CalendarQuarterDesc that concatenates the calendar quarter and year for each quarter. And finally, I’ll create a Named Calculation called CalendarSemesterDesc that concatenates the calendar semester and year for each semester.

The KeyColumns property specifies the columns that represent the key for the attribute. The NameColumns property specifies the columns that represent the name. I’ll show you how to define KeyColumns and NameColumns:

• Open the Dimension Structure tab for the Date dimension.

• For the English Month Name attribute, click in the KeyColumns field and then click the browse button. I make it so both the EnglishMonthName and CalendarYear columns will now be used as Key Columns.

• Next, I’ll set the NameColumn property of the EnglishMonthName attribute, to MonthName which is the Named Calculation that we created earlier.

• I’ll set the KeyColumns for the Calendar Quarter attribute to CalendarQuarter and CalendarYear and I’ll set the NameColumn property of the Calendar Quarter attribute to the CalendarQuarterDescNamed Calculation.

• Now, I’ll set the KeyColumns for the Calendar Semester attribute to CalendarSemester and CalendarYear and I’ll set its NameColumn property to CalendarSemesterDesc.

You have to deploy the changes and reprocess the related objects before you can view the changes:

• Click Deploy Analysis Services Tutorial.

• Wait for the Deployment Completed Successfully message, and then click the Browser tab of Dimension Designer.

• You must click the reconnect icon.

• Review the members in the attribute hierarchy.

Notice that the names are more user-friendly because we created a named calculation to use as the name. The members are not sorted in chronological order. They are sorted by quarter and then by year. Next we’ll sort the members chronologically. We’ll change the sort order by changing the order of the columns that make up the composite key.

• Select the Dimension Structure tab for the Date dimension, and then select the Calendar Semester attribute.

  The value for the OrderBy property is set to Key. With a composite key, the ordering of the member keys is based on the value of the first member key, and then on the value of the second member key. In order words, the members of the Calendar Semester attribute hierarchy are sorted first by semester and then by year.

• We need to change the order of the keys specified in the KeyColumns property so that the members of the attribute hierarchy are sorted first by year and then by semester.

• Do the same thing for the Calendar Quarter attribute so that the members of the attribute hierarchy are sorted first by year and then by quarter.

• And finally, do the same for the English Month Name attribute so the members are sorted first by year and then by month.

• Deploy the project.

• Click the Browser tab in Dimension Designer.

• Click the reconnect icon.

  Notice that the members of these hierarchies are now sorted in chronological order.

Now, let’s browse the Analysis Services Tutorial cube:

• Switch to Cube Designer.

• Click the Browser tab, and click Reconnect.

  The left pane of the designer shows the metadata for the cube. The two panes to the right of the metadata pane are the filter pane, and the data pane.

• Drag the Sales Amount measure to the Data pane.

• Drag the Product Model Lines user hierarchy to the Drop Column Fields Here area, and then expand the Road member of the Product Line level of this user hierarchy.

  The user hierarchy provides a path to the product name level.

• Drag the Customer Geography hierarchy to the Drop Row Fields Here area.

• Expand United States to view the sales details by region.

• Expand Oregon to view the sales details by city within the state of Oregon.

• Drag the Order Date.Calendar Date hierarchy to the Drop Filter Fields Here area.

• Select the check box for February 2002.

  Internet sales by region and product line for the month of February, 2002 are displayed.

• Right-click the 10+ Miles member, and then click Add to Subcube Area to only show data for customers who commute more than 10 miles.

Thanks for watching. I hope you found it helpful. For more information, you might want to take a look at the Analysis Services Tutorial on the MSDN web site.