The Dimension Wizard
provides two general options for Dimension Type, as we see above. A Time
Dimension is a specialized dimension that we use to represent standard time
periods in our cubes. The Time Dimension option does not appear as a
selection when we have chosen (on the earlier dialog of the Dimension Wizard)
to use multiple tables as the source for cube dimension data, because a time
dimension is always based upon a single field, as we have seen. The
single-field criteria obviously precludes the need for multiple tables, so the
wizard factors this concept into the creation process in cases where we choose
multiple tables to source the cube, and does not offer the Time Dimension
option.
33.
Leaving the selector at the_date
(the only selection, as it turns out, in our example), click Next.
We are now prompted to
select the levels of our new time dimension hierarchy. While the month
level is common to a large number of organizations' OLAP reporting structures,
let's go a bit further and select lower levels to further enrich our
comprehension of the capabilities of Analysis Services with regard to
time dimension manipulation and usage.
34.
Select the Year, Quarter,
Month, Day option (the default, as it is the top member of the list) in the
selector dropdown list. Leave the Year Starts On selections at their
defaults, as shown in Illustration 19.
Illustration 19: Selection of the Year, Quarter, Month, Day Option
35.
Click Next.
36.
Click Next at the dialog
that appears for Advanced Options to skip this dialog for now.
The Finish the
Dimension Wizard dialog appears.
37.
Input the word Time
in the Dimension Name box.
38.
Click (to place a check in) the
Create a Hierarchy of a Dimension check box.
39.
Type CalendarTime
into the Hierarchy Name box.
40.
Leave the Share this
Dimension with Other Cubes checkbox at its default (checked).
The completed dialog
should appear as follows:
Illustration 20: Finishing the Dimension Wizard... Option
41.
Click the "+" sign to
the left of the All New Dimension icon in the Preview tree.
We see the various
hierarchical levels in the new dimension appear as a preview. Note that the
levels go all the way down to the Day level, and that the number
of days in each month reconcile with our expectations for those appearing
in the standard calendar. Notice, too, that, although our choice of dimension
tables was sales_fact_1997, the year 1998 appears in the hierarchy
preview.
This is because our
choice for the date dimension table, time_by_day, includes both years,
demonstrating one of the strengths that we discussed for using a separate date
dimension table earlier: we can share the dimension table among multiple fact
tables, and thus decrease the size of the overall database (above and beyond
our already saving space by planting a single integer key on each row of
the fact table (approximately 4 bytes in size) instead of placing a date/time
field on every row of the fact table (approximately 8 bytes in size, with
much potential redundancy, over potentially millions of lines).
42.
Click Finish.
The Dimension Wizard
completes the creation of our new Time dimension and its CalendarTime
hierarchy. We see, at this point, that the Dimension Wizard has created
a dimension called Time.CalendarTime, as shown in the illustration
below:
Illustration 21: The New Dimension Appears Option
The name Time.CalendarTime,
appearing in the wizard and used as the object identifier throughout
Analysis Services, represents the dimension name as Time, with a
hierarchy name of CalendarTime. We will complete the cube creation
process at this stage, as we have the new Time dimension in place, by
taking the following steps.
43.
Click Next.
A message box appears,
indicating that the Fact Table Row Count is about to begin, and warning
us that the process may take some time, as shown below.
We will proceed, as we
know that our tables are relatively small.
44.
Click Yes to begin the
count process.
45.
Next, name the cube by typing MyCube2
into the Cube Name box of the Finish the Cube Wizard dialog, as
illustrated below:
Illustration 22: Name the Cube and Finish the Cube Wizard
46.
Click Finish.
The wizard disappears,
and Cube Editor (with the Schema pane showing) appears, as
follows:
Illustration 23: The Cube Editor, with Schema Pane as Shown, Appears
47.
Select File
> Save from the top menu to save the
work so far.
48.
Select File
> Exit to close the Cube Wizard
at this stage.
The Design Storage
dialog appears, as shown below.
Illustration 24: Design Storage Dialog
We will skip this
operation for the present.
49.
Click No to dismiss the Design
Storage dialog.
We
return to the Analysis Manager console.
Page 8: An Examination of Time-related Properties within a Time Dimension
» 