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.
» 
Comment and Contribute
