Using the Dimension Wizard
To invoke the
Dimension Wizard and create a dimension (which we’ll need to do because
we haven’t yet created our time dimension), choose the Cube/Add Cube
Dimension... option, select the Add Cube Dimension... option from the
Dimension pane’s shortcut menus, or click the Add Cube Dimension toolbar
button (sixth from left). Each of these actions brings up the Add Cube
Dimension dialog box, shown in Figure 9.
Click
the New Dimension ... button to bring up the Dimension Wizard. You can
also invoke the Dimension Wizard by right-clicking the Dimensions folder
in the Solution Explorer window and choosing New Dimension. Invoking
the Dimension Wizard using this technique adds the dimension to the
Analysis Services database but not to the cube;
to add the dimension to the cube itself, you must use the Add Cube
Dimension dialog box and select your new dimension from the list of
dimensions in the database.
On the Welcome page of the
Dimension Wizard, click Next. On the Select Data Source View page,
confirm that the Northwind data source view is selected and click Next
again. On the Select The Dimension Type page, you can indicate whether
you are creating a standard dimension (the default), a time dimension,
or a server time dimension. In our case, we’ll select the Time Dimension
option.
For a standard
dimension, you would need merely to accept the default option button
selection and click Next. (Go ahead and try these next steps if you’d
like, but when you’re done, use the Back button to come back to the
Select The Dimension Type page and select Time dimension again.)
On the Select The Main
Dimension Table page, you can select the desired dimension table (from
the top combo box) and its key column(s) (from the checked list box that
takes up most of the page). You can also optionally select a name
column (from the bottom combo box). Click Next to go to the Select
Dimension Attributes page, where you can specify the name, key column,
and name column of each of the dimension’s attributes. Several
attributes are suggested automatically, based on the columns in the
table, and you can edit or deselect any of these.
Click Next to go to the
Specify Dimension Type page. If you’re experimenting with standard
dimensions, you can stick with the Regular dimension type and click Next
to go to the Define Parent-Child Relationship page. You can click
Next to advance to the Detecting Hierarchies page. On this page, the
wizard will attempt to detect, from the structure of your dimension
table, possible hierarchies in your dimension. You should wait for the
processing to complete and then click Next. On the Review New
Hierarchies page, you can review the hierarchies, if any, generated by
the wizard, deselect any of them (or any of their constituent levels) if
you’d like, and then click Next.
If you followed the
previous steps, click the Back button to get back to the Select The
Dimension Type page (not the Specify Dimension Type page), and select
the Time Dimension option button. If you didn’t follow the previous
steps, this should already be done. In the Time Dimension combo box,
select tblTime (it will appear as dbo_tblTime) as the table that will
serve as our time dimension table. Time dimension tables are specially
configured to contain a key (which also appears as a foreign key in the
fact table) and columns with corresponding year, month, or other
time-related data. Figure 10 shows the structure and an abbreviated set of data for tblTime. (The four non-key columns in that table will each support a level in the time dimension.)
Click Next to proceed to
the Define Time Periods page, where you should select the columns in the
Time Table Columns column that correspond to the items in the Time
Property Name column. (See Figure 11
for the proper settings for tblTime.) The Time Intelligence feature in
SQL Server 2005 Analysis Services allows this type of declarative
identification of dimensional levels. Click Next to continue. On the
Review New Hierarchies page, review and confirm the time hierarchy that
the Dimension Wizard constructed for you, and then click Next again.
On the Completing The
Wizard page, you can review the schema for your dimension, accept its
default name or enter a name of your own (for our example, type Time
as the dimension name), and then click Finish. Back in the Add Cube
Dimension dialog box, make sure that the new Time dimension is selected,
and then click OK to add it to the cube and return to the cube
designer. Your new dimension should be displayed on the Dimensions pane
of the Cube Structure tab, and the dimension table should be added to
the Data Source View pane.
For
the new time dimension, or any of the others on the Hierarchies tab or
the Attributes tab, you can drill down and display child nodes. The
first child node in either pane is a clickable hyperlink that opens the
dimension designer and allows you to edit that dimension. (The nodes
that follow enumerate the attributes or hierarchies and levels of the
dimension, if any, depending on which tab you’re on.) In addition to the
hyperlink, you may open the dimension designer by selecting a dimension
in the Dimensions pane and choosing the Edit Dimension option from the
Cube menu, or you may right-click the Dimension and select the Edit
Dimension option from its shortcut menu. Use one of these techniques to
open the Geography dimension in the dimension designer now.
