Modifying the Time Dimension

Article
12/03/2008

You can define composite keys for attributes to control the members that are returned by the SELECT DISTINCT processing query. When you use composite keys, the sort order of the attribute members is determined by the order of the members of the composite key. In the tasks in this topic, you change the user-defined hierarchy name, change the member names that are displayed for the Date, Month, Calendar Quarter, and Calendar Semester attributes, define composite keys for the EnglishMonthName, CalendarQuarter, and CalendarSemester attributes, and then modify the order of the composite key members to control the sort order of dimension members.

Modifying the User-Defined Hierarchy

To modify the user-defined hierarchy

Switch to Dimension Designer for the Time dimension in Business Intelligence Development Studio, and then click the Dimension Structure tab.
In the Hierarchies and Levels pane, click the CalendarYear - CalendarSemester - CalendarQuarter - EnglishMonthName - FullDateAlternateKey hierarchy, and then change the Name property to Calendar Time in the Properties window.

The name of this user-defined hierarchy is now Calendar Time.
In the Calendar Time user-defined hierarchy, change the English Month Name level to Calendar Month and the Full Date Alternate Key level to Date.
Switch to Data Source View Designer for the Adventure Works DW data source view, right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.
In the Create Named Calculation dialog box, type SimpleDate in the Column name box, and then type the following SQL script in the Expression box:
```
DATENAME(mm, FullDateAlternateKey) + ' ' +
DATENAME(dd, FullDateAlternateKey) + ', ' +
DATENAME(yy, FullDateAlternateKey)
```
This SQL script extracts the year, month, and day values from the FullDateAlternateKey column. You will modify the Date level in the Calendar Time hierarchy in the Time dimension to display the value of this new column instead of the original value that is stored in the FullDateAlternateKey column of the DimTime table.
Click OK, and then switch to Dimension Designer for the Time dimension.
Click Date in the Calendar Time hierarchy, and then review the properties of the Date level in the Properties window.

Notice that the Date level in the hierarchy is based on the FullDateAlternateKey attribute. In the next steps, you will change the attribute on which this level is based to the TimeKey attribute, and then will modify the properties of the TimeKey attribute so that the names of its members are based on the values in the SimpleDate column that you just defined. You could also leave FullDateAlternateKey as the attribute on which the Date level is based and change the attribute so that its member names are based on the SimpleDate column. However, the first method is more efficient; the TimeKey attribute and the FullDateAlternateKey attribute are not both needed, so you will therefore delete the FullDateAlternateKey attribute.
In the Calendar Time user-defined hierarchy, change the value for the SourceAttribute property of the Date level to TimeKey, and then delete the FullDateAlternateKey attribute from the Attributes pane.
Select TimeKey in the Attributes pane, expand the NameColumn property collection, and then expand the Source property collection in the Properties window. Change the value for the ColumnID property to SimpleDate.

Notice that the OrderBy property for the TimeKey attribute specifies that the members of the attribute hierarchy are ordered by their key values.
On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension and then click Reconnect on the toolbar.
Expand the levels in the Calendar Time hierarchy to review the members of the Date level.

Notice that the members of the Date level are more user-friendly than they were before. However, notice also that the semester, quarter, and month members do not indicate their parent year.

The following image shows the members of the Date level.
In the Hierarchy list, select EnglishMonthName, and then expand the All member.

Notice that each month appears only once, instead of once for each year in the Time dimension. In the next task in this topic you will generate unique names for each month in each year.
Select CalendarQuarter in the Hierarchy list, and then expand the All member.

Notice that each quarter appears only once, instead of once for each quarter in the Time dimension. Notice also that the months are sorted alphabetically. In the next task in this topic you will generate unique names for each calendar quarter in each year. In Lesson 4, you will configure the months to sort chronologically.

Providing Unique Dimension Member Names

To provide unique dimension member names

Switch to Data Source View Designer for the Adventure Works DW data source view, right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.
In the Create Named Calculation dialog box, type MonthName in the Column name box, and then type the following SQL script in the Expression box:
```
EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)
```
This SQL script concatenates the month and year for each month in the DimTime table into a new column.
Click OK.
Right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.
In the Create Named Calculation dialog box, type CalendarQuarterDesc in the Column name box, and then type the following SQL script in the Expression box:
```
'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY ' +
CONVERT(CHAR (4), CalendarYear)
```
This SQL script concatenates the calendar quarter and year for each quarter in the DimTime table into a new column.
Click OK.
Right-click Time in the Tables pane, and then click New Named Calculation.
In the Create Named Calculation dialog box, type CalendarSemesterDesc in the Column name box, and then type the following SQL script in the Expression box:
```
CASE
WHEN CalendarSemester = 1 THEN 'H1' + ' ' + 'CY' + ' ' 
       + CONVERT(CHAR(4), CalendarYear)
ELSE
'H2' + ' ' + 'CY' + ' ' + CONVERT(CHAR(4), CalendarYear)
END
```
This SQL script concatenates the calendar semester and year for each semester in the DimTime table into a new column.
Click OK, and then switch to Dimension Designer for the Time dimension and click the Dimension Structure tab.

You will modify the EnglishMonthName, CalendarQuarter, and CalendarSemester attributes in the Time dimension to use the values in the new columns as member values.
In the Attributes pane, select EnglishMonthName. In the Properties window, expand the NameColumn property and then the Source property, and then change the value of the ColumnID property to MonthName.
In the same way, change the ColumnID property for the Calendar Quarter attribute and the Calendar Semester attribute to the following:
- Calendar Quarter — change the ColumnID property to CalendarQuarterDesc
- Calendar Semester — change the ColumnID property to CalendarSemesterDesc
On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.
On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter attribute hierarchy in the Calendar Quarter attribute hierarchy.

Notice that while the names of the members of the CalendarQuarter attribute hierarchy are more user-friendly, there are still only four members in the attribute hierarchy, instead of a member for each year and quarter combination. If you review the EnglishMonthName or CalendarSemester attribute hierarchies, you will notice similar behavior. In the next task in this topic you will modify this behavior by specifying a composite key for these attributes.

Specifying Composite Key Values

To specify composite key values

Select the Dimension Structure tab of Dimension Designer for the Time dimension, select the EnglishMonthName attribute, and then click the ellipsis button (…) in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor opens to display the member key column for this attribute. When Analysis Services processes a dimension, it issues a SELECT DISTINCT query to determine the members of the dimension. In the DataItem Collection Editor, you can specify a composite key instead of a single column key for the attribute, to return additional dimension members.
Click Add to define a second member key for the dimension attribute.

A new member key appears in the members list. You must define the properties of this new key member.
Under New Binding (WChar) properties, click the ellipsis button (...) in the Source property cell to change its value.

The Object Binding dialog box appears. In this dialog box, you either specify an existing column in a specified table or specify the type of column you want to create if you are generating the relational schema by using the top-down design method.
In the Binding type list, select Column binding.
Verify that Time is selected in the Source table list.
In the Source column list, select CalendarYear.

The following image shows the Object Binding dialog box.
Click OK to close the Object Binding dialog box, and then click OK again to close the DataItem Collection Editor.

Notice that the value for the KeyColumns property is now set to (Collection).
Select the CalendarQuarter attribute, and then click the ellipsis button (...) in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor appears.
Click Add to define a second member for the KeyColumns property.
Under New Binding (WChar) properties, click the ellipsis button (...) in the Source property cell to change its value.

The Object Binding dialog box appears.
In the Binding type list, select Column binding.
In the Source table list, verify that Time is selected.
In the Source column list, select CalendarYear.
Click OK, and then click OK again.
Select the CalendarSemester attribute, and then click the ellipsis button (...) in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor dialog box appears.
Click Add to define a second member for the KeyColumns property.
Under New Binding (WChar) properties, click the ellipsis button (...) in the Source property cell to change its value.

The Object Binding dialog box appears.
In the Binding type list, select Column binding.
In the Source table list, verify that Time is selected.
In the Source column list, select CalendarYear.
Click OK, and then click OK again.
On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.
On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter attribute hierarchy in the Calendar Quarter attribute hierarchy.

Notice that while members now exist in the CalendarQuarter attribute hierarchy for each quarter in each year, the members are not sorted in chronological order. Instead they are sorted by quarter and then by year. In the next task in this topic, you will modify this behavior to sort the members of this attribute hierarchy by year and then by quarter.

The following image shows the current structure of the CalendarQuarter attribute hierarchy.
Review the members of the EnglishMonthName and CalendarSemester attribute hierarchies.

Notice that the members of these hierarchies are also not sorted in chronological order. Instead, they are sorted by month or semester, respectively, and then by year. In the next task in this topic, you will modify this behavior to change this sort order.

Changing the Sort Order by Modifying Composite Key Member Order

To modify the composite key member order

Select the Dimension Structure tab of Dimension Designer for the Time dimension, and then select CalendarSemester in the Attributes pane.
In the Properties window, review the value for the OrderBy property.

The members of the CalendarSemester attribute hierarchy are sorted by their key value. With a composite key, the ordering of the member keys is based first 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 CalendarSemester attribute hierarchy are sorted first by semester and then by year.
In the Properties window, click the ellipsis button (...) to change the KeyColumns property value.

The DataItem Collection Editor opens.
In the Members table of the DataItem Collection Editor, verify that DimTime.CalendarSemester (UnsignedTinyInt) is selected, and then click the down arrow to reverse the order of the members of this composite key. Click OK.

The members of the attribute hierarchy are now sorted first by year and then by semester.
Select CalendarQuarter in the Attributes pane, and then click the ellipsis button (...) for the KeyColumns property in the Properties window.
In the Members table of DataItem Collection Editor, verify that DimTime.CalendarQuarter (UnsignedTinyInt) is selected, and then click the down arrow to reverse the order of the members of this composite key. Click OK.

The members of the attribute hierarchy are now sorted first by year and then by quarter.
Select EnglishMonthName in the Attributes pane, and then click the ellipsis button (...) for the KeyColumns property in the Properties window.
In the Members table of DataItem Collection Editor, verify that DimTime.EnglishMonthName (WChar) is selected, and then click the down arrow to reverse the order of the members of this composite key. Click OK.

The members of the attribute hierarchy are now sorted first by year and then by month.
On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.
On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter and CalendarSemester attribute hierarchies.

Notice that the members of these hierarchies are now sorted in chronological order, by year and then by quarter or semester, respectively.
Review the members of the EnglishMonthName attribute hierarchy.

Notice that the members of the hierarchy are now sorted first by year and then alphabetically by month (this is because the data type for the EnglishCalendarMonth column in the data source view is a string column - based on the nvarchar data type in the underlying relational database). However, it would be more useful for the months to be sorted chronologically within each year. You will make this change in Lesson 4.

The following image shows the EnglishMonthName attribute hierarchy.
On the File menu of BI Development Studio, 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.

You have successfully increased the user-friendliness of the members of the time dimension attribute hierarchies, by creating named calculations in the data source view and then using these named calculations as the attribute member names. You have also increased user-friendliness by defining composite keys and specifying the appropriate order of the composite key members to control dimension member sort order.

Next Task in Lesson

Modifying the Product Dimension