Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions - Page 9

As most of us have encountered at some point in our business careers, many organizations use "alternative" calendars, either solely, or, more likely, in some way in conjunction with calendar date systems. The Dimension Wizard can often create these fiscal (and other) date hierarchies, for the most part, from a Date/Time field occurring in the database, although we may have to make manual adjustments to select properties to achieve the full benefit of our intended designs. We have seen that modification of the expressions that support the hierarchical levels is straightforward enough; we have also mentioned that custom levels can be added with minimal effort to support even the most demanding of Information Consumer needs.

Creating the Fiscal Time Hierarchy with the Dimension Editor

Let's explore the creation of a fiscal time hierarchy that will share the Time dimension. This will provide many benefits, such as elimination of redundancy, the ability to perform "alternate" drilldowns and zooms, and other efficiencies from a consumption perspective. We will take the following steps to achieve our ends:

62.  Select File from the top menu within the Dimension Editor.

63.  Select New Dimension.

64.  Click Wizard from the shortcut menu, as shown in the illustration below:

Illustration 30: Initialize the Dimension Wizard from the Dimension Editor

65.  Click Next at the Dimension Wizard Welcome dialog.

66.  Select Star Schema: A Single Dimension Table on the next wizard dialog.

67.  Click Next.

68.  At the Select Dimension Table dialog, select time_by_day as the dimension table once again.

69.  Click Next.

70.  Select Time Dimension on the Select Dimension Type dialog, once again.

71.  Leave the_date as the Date Column in the selector box.

72.  Click Next again.

73.  At the Create the Time Dimension Levels dialog, leave the Select Time Levels option at the default of Year, Quarter, Month, Day again.

74.  Select 1 and October in the respective Year Starts On selector boxes.

75.  Click Next.

76.  Click Next again, to skip the Select Advanced Options dialog.

77.  At the Finish the Dimension Wizard dialog, type (or select via the dropdown list) Time in the Dimension Name box, once again.

78.  Place a check in the Create a Hierarchy of a Dimension checkbox by clicking the checkbox.

79.  Type FiscalTime into the Hierarchy Name box.

The completed dialog should appear as follows:

Illustration 31: Finishing the Dimension Wizard...

80.  Click the "+" sign to the left of the All New Dimension icon in the Preview tree.

We notice that, instead of two years in the Preview, we now see three years (1997, 1998 and 1999). This is because, even though the dates we have entrained from the time_by_day dimension table fall (as we have learned) in the 1997 and 1998 calendar date range, we now have defined years as crossing the boundaries of standard calendar time, and have thus defined dates into a 1999 year group, for example, by stating in our Year Starts On setpoints earlier, that a new year begins with October 1, 1998 -- a year that is defined as 1999, in our new fiscal grouping scenario.

Expanding the hierarchy in the Preview pane down to 1999's Days level will bear this out. We see that Quarter 1 of the 1999 Year level consists of dates within the months of October, November and December, confirming the creation of the levels within the parameters we have specified. We have created a fiscal time hierarchy, just as we intended.

81.  Click Finish.

We arrive again at the Dimension Editor, having created our second Time dimension hierarchy.

82.  With the Time.FiscalTime dimension selected, click the Advanced tab in the Properties section at the bottom of the Dimension tree.

83.  Change the All Caption property value to read Fiscal Time.

84.  Modify the Type property value to Standard (the top item in the selection list, accessed via the dropdown arrow).

The affected values of the Time.FiscalTime dimension should appear as shown in Illustration 32:

Illustration 32: Property Values for the New Time.FiscalTime Dimension

Modifying the Type property value allows us to distinguish between the Time.CalendarTime and Time.FiscalTime hierarchies at the property level, should the need arise to ascertain which is the default time hierarchy. While order of addition (that is, the status of "first added") would accomplish the same thing; An MDX function for which the default property was relevant would see the Time.CalendarTime hierarchy as the default, since it was added first -- of the Time-type dimensions present in the cube we have constructed.

Let's finish the tutorial by making modifications to the Member Name Columns for the levels of the Time.FiscalTime hierarchy to make them consistent with the conventions we adapted for their Time.CalendarTime hierarchy counterparts. The purpose was, after all, to illustrate this as a means of uniquely identifying similar, yet different, members of corresponding levels in the two hierarchies -- without having to refer to other levels that might not be visible or conveniently accessed.

85.  Change the Member Name Column property for each of the levels within the Time.FiscalTime hierarchy, clicking on each in turn, from the Wizard-created Name Column Property to the less ambiguous Custom Name Column Property, as follows:

A Preview of the resulting dimension hierarchy should bring back results similar to those shown in Illustration 29 above, with the classifications of the members into Fiscal groups, and the presence of 1999, etc., being the only significant differences.

One point, however, that bears special consideration at this juncture, is the fact that the Day members of the Time.FiscalTime hierarchy are treated identically to the Day members of the Time.CalendarTime hierarchy. This can be noted most strongly in the results shown in the partial depiction of the Preview pane for the Day members under the new Fisc 1999, Fisc Q1 - 1999 and Fisc October-1999 groups, as shown in Illustration 33 below.

Illustration 33: Day Members Retain Actual Dates in Calendar and Fiscal Hierarchies

We see clearly that, even though the first quarter of 1999 is a Fiscal quarter, its members consist of 1998 dates -- indeed, the Day level is the common level from which both hierarchies spring.

86.  Select File > Save from the top menu to save the work we have completed in the tutorial.

87.  Select File > Exit to return to the Analysis Manager console.

88.  Select Console > Exit from the Analysis Manager Console top menu to close Analysis Services.

Page 10: Next in Our Series...