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

Let's take a look at some of the time-specific properties that have been established by the wizard for our new dimension/hierarchy set.

50.  Right-click the Time.CalendarTime dimension under the Shared Dimensions folder for MyCube2.

51.  Select Edit from the shortcut menu, as seen in Illustration 25 below.

Illustration 25: Select Edit to Resurrect the Dimension Editor

52.  Select the new Time.CalendarTime dimension in the Dimension tree.

53.  Expand the Properties pane beneath the dimension tree.

54.  Select the Advanced tab.

We view the properties as partially shown below:

Illustration 26: The Advanced Properties Tab within the Dimension Editor

Notice that the Type property exhibits the expected Time setpoint. The Type property, for both a dimension and a dimension level, is, in general, not critical in most respects. It exists primarily for the benefit of some client applications, and specifically from the perspective of certain MDX functions that use the setpoint to ascertain the default date in specific situations; it has no direct impact from the perspective of the Analysis Server. (For more information on this topic, do a topic search in the Books Online that are installed with the Typical MS SQL 2000 Analysis Services installation and also available on the installation CD or from the Microsoft Website.)

55.  Click on the All Caption property.

56.  Type Calendar Time, as shown in Illustration 27, into the associated value field.

Illustration 27: Changing the All Caption Value in the Advanced Properties Tab

57.  Press Enter.

58.  Click on the Basic Tab.

59.  Click on the Year level within the Dimension tree, to display the Year member's Basic properties tab.

The Basic Properties Tab of the Year level member appear as follows:

Illustration 28: The Basic Properties Tab -- Year Level Member

Note the position and setting of the level Name property. While the Dimension Wizard automatically creates member names, these names may not include a description or other identification that is optimal within the reporting conventions of the organization for which we are creating the cube and its component structures. The Name that we see displayed in the Basic Properties tab typically appears in conjunction with headings in the browser, and we may want to display them elsewhere in a reporting scenario. We can customize Names easily enough, so as to make reporting self-evident; this is particularly useful, as we will see, if we create multiple hierarchies within the dimension and want Information Consumers to be able to identify precisely the meanings of the fields they see in their reports.

We could easily change the physical Names of the levels, but this would only affect the level Name itself, and not those of its members. Let's go a step further to cause the automatic creation of custom member names that will more closely meet the example reporting environment of a group of Information Consumers. (Remember that the default value for a given Member Name Column is the same as the value assigned to the Member Key Column.)

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

Note: The expressions I present in the Custom Name Column Property column can be cut and pasted to the Member Name Column property value field, if typing is cumbersome. The objective of this exercise is to gain a conceptual understanding, not to attempt to learn MDX expressions (although a study of MDX is certainly a wonderful time investment).

A sample view of the resulting dimension hierarchy Preview is as follows:

Illustration 29: Sample View of the Effects of the Modifications to the Name Column Property

(Note, too, the change in the top dimension Name, which now appears as Calendar Time.)

Customizations similar to those we have implemented in the Name Column Properties above provide numerous key benefits, among which are included the ability to use the headings on reports and in browser activities, and so forth, in such a way that we do not have to refer to higher levels in the hierarchical tree to specify the fields in a way that is not ambiguous. If we create another hierarchy for the Time dimension, as we show later, an Information Consumer can still readily discern exactly which member is being retrieved at the member level, removing the possibility of confusion between similar, but possibly very different, members and their corresponding measures.

We can also make simple format changes for ease of use of OLAP data by the targeted audience, as we did at the Day level, where we simply changed the existing digits to the actual date format (we could have added full years vs. 2-digit years, of course, for those concerned with the 2-digit presentation; this is simply an exercise to demonstrate examples from a very large population of possibilities. To make reporting easier for special situations, for instance, we might even add such properties as Day in Week, whereby we could analyze data by day of the week and so forth, by adding a leaf level to the Time dimension. The range of possibilities is, indeed, substantial.

61.  Save the work up to this point by selecting File > Save from the top menu. (We will be prompted to save changes upon attempting to leave any given dimension for which we have enacted changes in the Editor.)

Page 9: Building and Managing a Fiscal Date Hierarchy