Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 25, 2003

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

By William Pearson

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:

  • IPAdd

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM