Creating the Web Site Traffic Analysis Cube
Although Analysis Services provides us with a Cube Wizard,
which guides us through cube creation with a series of step-by-step dialogs, an
alternative approach, the Cube Editor, allows us to build cubes more
directly (we may have to use it for more complex cube / calculated member
designs, etc.), or to modify cubes that we have already created. We will use
the Cube Editor at this point to create measures, then dimensions and
levels, for our Web Site Traffic Analysis Cube.
Creating the Interactions Measure
As an initial step in
designing and building our cube, we will create a measure that will reflect the
number of interactions by a visitor to our site. For purposes of our
lesson, this will be a simple count of "hits," although, in reality,
many considerations enter the picture in getting a true picture of the activity
of a visitor to a site, as we mentioned in Part I of this article.
Creating a measure largely
consists of specifying a "fact table" to Analysis Services, and
further specifying the column(s) within that table that will define the
measure. Let's begin by launching the Cube Editor and taking the
following steps:
1.
Right-click the new Cubes
folder under the WebTrafficSource database we created above.
2.
Select New Cube from the
initial shortcut menu.
3.
Click Editor, as shown
in Illustration 11.
Illustration
11: Initializing the Cube Editor
The Cube Editor
initializes. Beginning with the Choose a Fact Table dialog, it provides
us an opportunity to select a "fact table" for our cube, as depicted
in Illustration 12. As our example database contains only one table
(see Part I of this lesson for details), our choice is
relatively obvious.
Illustration 12:
The Choose a Fact Table Dialog
4.
Click the ServerAccessLog
table to select it.
The
list of columns in the ServerAccessLog table appears in the Details
pane on the right half of the dialog, as shown in Illustration 13. We
see that the columns in this simple table contain Date and IP Address
information (Date and IPAdd, respectively).
As
we can readily see, neither of the two columns in our table contains data that
can be directly used to measure site activity. This is a common enough
scenario in a "hit reporting" environment, because the transactional detail
captured in the typical Server Access Log is intended to present information
surrounding a given access event for the visitor.
We
will need to derive a quantitative statistic for site activity, because we have
no such "measure" conveniently stored in our fact table. We will
handle this requirement using a simple count function, as we shall see
in a few steps.
Illustration
13: Choose a Fact Table Dialog -
Details Pane
5.
Click OK.
The
Fact Table Row Count message box appears, asking if we want to count
fact table rows, as shown in Illustration 14.
Illustration
14: The Fact Table Row Count Message
Box
6.
Click Yes. (This should
take only a second or two).
The
Cube Editor window appears, showing the cube tree and properties pane
(top and bottom, respectively, on the left side of the window), and the fact
table schema (the Schema tab view) on the right, by default. The
window should appear as depicted in Illustration 15.
Illustration
15: The Cube Editor, with the ServerAccessLog Table Schema View (Compressed)
7.
Add the following column to the
Measures folder, by dragging it from the ServerAccessLog table, and dropping it onto the folder:
The selected field
appears in the Measures folder ("initial caps" formatting is
an automatic conversion feature, as we have noted in earlier lessons), as
depicted in Illustration 16:
Illustration
16: The Measures Folder with Newly
Added Measure (Unadjusted)
Now we
need to make our IPAdd field a real measure. In addition, we will do a
couple of additional modifications to enhance the overall appearance of the
cube.
8.
In the cube tree, expand the Measures
folder (as necessary), by clicking the "+" sign to its left.
9.
Click IPAdd in the Measures
folder to select it in the cube tree.
10.
Click Properties beneath
the tree pane (if necessary) to display the properties of IPAdd.
11.
Click the Basic tab in
the Properties pane, as required.
12.
Rename the dimension to Interactions
in the Name property box.
13.
Type the following into the Description
property box:
IP Address Activity Count
14.
In the Aggregate Function
property, click the downward selector arrow.
15.
Select Count as the
function, if not already defaulted.
The Basic tab of
the Properties pane for the Interactions measure appears as shown
below:
Illustration 17: Interactions
Measure, Properties Pane, Basic Tab
16.
Click the Advanced tab
in the Properties pane.
17.
Using the selector arrow
provided, modify the Display Format property to the "#,#"
option.
18.
Press Enter to apply
changes.
We mentioned in earlier lessons that a cube must contain a minimum
of one measure from the designated fact table; in addition, it must also
contain at least one dimension that is associated with a key column in
the table. We have constructed a measure that will allow us to accumulate
simple counts of visitor interactions with our site.
We will add our cube's dimensions next.
» 
Comment and Contribute
