Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube

In our example, the FoodMart 2000 database, we have a time dimension table called time_by_day. While in the real world, it is sometimes the case that the time dimension is derived from a column in the fact table, more often than not, in a Data warehouse of any sophistication, a time dimension table, such as the one we see here, is in place, and is joined to the fact table by a time_id (or similar) key. Having selected the time_by_day table as shown, we click Next, and are prompted to select a dimension type. The Select the dimension type dialog is presented any time the Dimension Wizard senses a date/time column within the table selection we provide. If there are multiple date columns available, we're given the opportunity to select which column we wish to use to drive our date dimension; if not, the drop down box defaults to the single available date column. In our present exercise, we will select the Time dimension radio button and leave the date column drop down box at the_date, as shown below (Illustration 29).

Illustration 29: The Select the Dimension Type Dialog for a Time Dimension

Clicking Next, we arrive at the Create the time dimension levels dialog box. The Dimension Wizard offers numerous common date hierarchy options from which we can choose to save time in constructing our cube. In addition, we are provided an opportunity to select the beginning day and month of the year; this is particularly valuable in building cubes for organizations with fiscal years. We will leave the selections in this dialog box at default, for the sake of simplicity, and assume a fiscal year that coincides with the calendar year (with January the first as its start date). This is illustrated in Illustration 30, shown below.

Illustration 30: The Create the Time Dimension Levels Dialog for a Time Dimension

Because the wizard does not provide for user-selected date dimension levels, per se, when we click Next we skip to the Advanced Options dialog -- a step we will also, again, simply bypass by clicking Next. We then arrive at the Finish the Dimension Wizard dialog, where we can type the word Time, to rename the dimension from default of All New Dimension. Note that the Wizard has proactively created typical time dimension hierarchies for us (year, quarter, month and day) by expanding the hierarchy in the Preview list box. You should see something similar to the illustration excerpted in Illustration 31, below.

Illustration 31: The Create the Time Dimension Levels Dialog for a Time Dimension

Clicking Finish returns us to the Cube Wizard, where we see the addition of our newly crafted dimension appearing alongside the others in the Cube Dimensions list, as pictured below (Illustration 32). We have now reached a stage of completeness in the simple model we are using in our examination of the Cube Wizard. We have defined a sample set of dimensions, and we've directed the Wizard where to find the values, or measures, that we want to incorporate into the cube (after specifying the fact table in which those measures reside).

Illustration 32: The Create the Time Dimension Levels Dialog for a Time Dimension

Saving the Cube Model as Designed

Clicking Next in the Cube Wizard at this point results in the appearance of the following message box (Illustration 33):

Illustration 33: Message: Time May Be Involved ...

As we will decide to count the rows in our simple model, we'll click Yes to proceed. The next message we encounter is the following error (Illustration 34) -- that is, if we've worked completely in parallel through all the steps I have outlined in this session. My intention is to illustrate a point in a meaningful way -- as forgetting to ensure the presence of joins is a very common mistake for early cube architects to make.

Illustration 34: An Error Message to Understand ...

Page 7: Saving the Cube Model (Continued)