Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II - Page 4
August 25, 2003
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.
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.
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.
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.
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.
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:
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:
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.