Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II - Page 5

August 25, 2003

Creating the Dimensions

The next step in the design and construction of our cube will be the addition of the dimensions that we will use for our lesson. We will continue within the Cube Editor to specify the dimension data from the table, to demonstrate the process.

1. Right-click the Date column in the ServerAccessLog table.

2. Click the Insert as a Dimension option on the context menu that appears, as shown in Illustration 18.

Illustration 18: Select Insert as a Dimension ...

3. Right-click the IPAdd column in the ServerAccessLog table.

4. Click the Insert as a Dimension option on the context menu that appears, as before.

5. Click the "+" sign to the left of the Dimensions folder to expand it, if necessary.

The two selected dimensions' key fields appear in the Dimensions folder (sans underscores, and with initial caps, once again) as depicted in Illustration 19.

Illustration 19: The Dimensions Folder with Newly Added Dimensions

The dimensions that we have added are private dimensions, meaning that they belong to this cube only. While we would, in most real-world scenarios, want to leverage the advantages of sharing our date data among cubes, and therefore to use a shared dimension in this instance, we will pursue the private dimension route here, to add a bit of instruction with regard to handling source data that is not ideally formatted. This will also be expedient for another reason: We are using a field within our "fact table," and not a separate time dimension table, to supply the date information for our cube.

One issue within the implementation of OLAP systems that I come across consistently is source data that is less-than-ideally transformed (if you can imagine such a set of circumstances). Despite the wonderful tools that we have available to us - particularly those close at hand in the MSSQL Server environment like Data Transformation Services (to which we gained some exposure in Part I), we often find ourselves acting in the role of OLAP architect, and designing a cube, downstream of data that has been converted by someone else; or in a situation where we otherwise have source data, but little control over its formatting, and so forth. Notwithstanding this less-than-ideal scenario, client needs typically dictate that we formulate a solution, if only to allow us to continue in our design endeavors while the ETL process is "straightened out" by the parties involved.

The Date field found in our data source, as a particularly useful example of this scenario, is a string, consisting of a date in the following format:


Not only is this field potentially problematic with regard to its data type, but it also presents a challenge in the operation of various functions that we might use to carve the date into its constituent parts, so as to allow us to create the various levels of a date hierarchy. There are no doubt numerous approaches to handing this; we will do so in a simple manner that both makes sense within the context of this lesson and illustrates the use of functions within the Cube Editor.

Let's make some adjustments to the dimension properties, to make them more useful to our model; this will also help to provide a review of some of the member properties attributes that we have discussed in earlier lessons, while providing the additional benefit of illustrating the use of functions as a part of dimension level design.

6. Expand the Date dimension.

Both the Date dimension and level are visible.

7. Select the Date dimension in the Dimensions folder.

8. Click Properties beneath the tree pane (if necessary) to display the properties of the Date dimension.

9. Click the Basic tab in the Properties pane, if required.

10.  Rename the dimension to Time.

11.  Click-select the Description property text box.

12.  Type in the following:

     Time Dimension

The Basic tab of the Properties pane for the Time dimension appears as shown in Illustration 20.