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

Monday Aug 25th 2003 by William Pearson
Share:

Build a cube to analyze web site traffic, with data extracted from the Server Access Log. Author Bill Pearson continues this two-part article with the cube design and construction phases.

About the Series ...

This is the fourteenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Preparation

Prior to beginning the lesson, you will need to download a copy of the sample Server Access Log, ServAccessLog.txt, a zipped text file that we used as a data source in Part I of this lesson. Once the log is downloaded, follow the instructions in Part I to prepare the file, then complete Part I to prepare the data source that we will use to build our cube in this lesson.

Introduction

In our last article, Build a Web Site Traffic Analysis Cube: Part I, we returned to the hands-on design and building of cubes for various business purposes, having focused on some of the reporting options for Analysis Services for the last few lessons. In Part I, after a brief discussion of potential business reasons for collecting web site traffic data, we walked through the design and building of a simple DTS package to extract, transform and load statistical data for ultimate placement into a prospective traffic analysis cube. Next, we set up a simple database to serve as the destination point for the extract process, and as a basis for the design and creation of a web traffic analysis cube.

In this article, we will enter the design and build phases of our lesson, from which we will emerge with a cube. After we have generated the cube, we will perform browses of the data it contains to examine the results of our handiwork.

The topics within Part II of this two-part article will include:

  • Creation of a database in Analysis Services;
  • Connection of the new database to the relational source table;
  • Creation of a measure for our Web Site Traffic Analysis cube;
  • Creation of dimensions and levels for our cube, including the manual creation of a simple Time dimension;
  • Incorporating drillthrough into our cube design.
  • Performance of browse activities against the new Web Site Traffic Analysis cube using the integrated Cube Browser;
  • Performance of drillthrough on a summary value within the Browser to its supporting detail in the relational data source;
  • Verification of the results of our drillthrough exercise, using an independent query against the source data in MSSQL Server.

Web Site Traffic Analysis Cube Design and Construction

As we have learned in earlier articles of this series, our initial action in building any cube is to connect to the source data. We do this via a database in Analysis Services. Keep in mind that this Analysis Services database is distinct from our relational database. Since we do not already have an Analysis Services database established, we will need to create one.

Creating a Database in Analysis Services

As part of the Analysis Services database's creation, we will need to specify the source of the data that will underlie the database. Our source will be the ServerAccessLog table that we created in Part I, located in the WebTrafficAnalysis_DB database that we set up to house it. As we learned in Lesson One, and have practiced in the creation of subsequent cubes in later lessons, we first create an Analysis Services database to organize cubes, roles, data sources, shared dimensions, and other objects. In our simple example, its primary purpose will be to act as a conduit for data from our source relational database to our Web Traffic Analysis cube.

We will call our Analysis Services database WebTrafficSource, setting it up inside Analysis Manager, then connecting the relational data source to our database before we start to construct our cube, as we have done previously.

  1. Start Analysis Manager.
  2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.
  3. Right-click on the Analysis Server name (mine is MOTHER, in this example), to see the context menu shown below:


Illustration 1: Right-Click on the Analysis Server

  1. Click New Database.

The Database dialog box appears.

  1. Fill in the Database Name - WebTrafficSource - along with a description that might be of value to a user or developer down the road.

Let's simply add "Web Stats Reporting Database," here - the description is optional, of course.

The dialog appears as below.


Illustration 2: The Database Dialog Box

  1. Click OK.

The new OLAP database WebTrafficSource appears at a point below the existing FoodMart 2000 database, in the left-side cube tree.

  1. Expand WebTrafficSource's database / cube icon by clicking in the plus (+) sign to the left of the icon.

Predefined, empty folders for object storage appear, as shown in Illustration 3 below.


Illustration 3: The New Database with Directory Structure

We now need to connect to the data source from which we wish to draw values. For purposes of our lesson, our source will be the relational database we created via DTS in Part I of this article.

  1. Right click the folder within WebTrafficSource called Data Sources, and on the context menu, select New Data Source.

The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 4 below. Beginning with the Provider tab (where the dialog opens by default):

  1. Select the Microsoft OLE DB Provider for SQL Server.

Note: For more detailed information on this, review the on-line documentation for OLE DB Providers, and data sources in general.


Illustration 4: The Data Link Properties Dialog - Provider Tab, with our Selection

  1. Click Next.

The focus moves to the Connection tab.

  1. Select / type in the appropriate Server name in Box 1.
  2. Make the appropriate selection, and enter any authentication information required by your local environment, to log on to the server in Box 2.
  3. Click the Allow Saving Password checkbox, to check it for this exercise.
  4. Select the WebTrafficAnalysis_DB database in Box 3.

The Data Link Properties Dialog - Connection Tab appears similar to that shown in Illustration 5, except for the environmental differences we have noted.


Illustration 5: The Data Link Properties Dialog - Connection Tab

  1. Click the Test Connection button in the bottom right hand corner of the Connection tab.

We should get a verification dialog confirming that the source has been established in our definition (as depicted in Illustration 6).


Illustration 6: Verification of the Connection to the FoodMart 2000 Database

  1. Click OK, and the Microsoft Data Link verification dialog box closes.

We will leave all the other Data Link Properties at their default setpoints for now.

  1. Click the OK button on the Data Link Properties dialog.

The Data Link Properties dialog closes, and a Security Warning dialog appears, as shown in Illustration 7.


Illustration 7: Security Warning Dialog

For purposes of this lesson, we will overlook the warning and proceed. Keep in mind that most real-world environments would likely not allow for the luxury of this flexibility, and to take steps to secure any embedded passwords, etc., as appropriate.

  1. Click the OK button on the Security Warning dialog.

The Security Warning dialog closes.

  1. Expand the Data Sources folder, within which we have created the source, if necessary.

We can see that the new source appears under the Data Sources folder in the tree area, again on the left side of the Analysis Manager Console. The data source displays the actual file name, a composite of the server and database names, and as shown in Illustration 8.


Illustration 8: Initial Tree View of Our Newly Created Data Source

Let's rename the file to be a bit more concise.

  1. Right-click the new data source.
  2. Click Copy.
  3. Right-click the Data Sources folder.
  4. Select Paste from the context menu that appears.

This causes Analysis Services to indicate that a duplicate has been detected, and to prompt us for a unique name. We will respond to the new name request with WebTrafficSource, using the dialog box that appears (as shown in Illustration 9).


Illustration 9: Changing the Name of the Duplicated Data Source as a Means of Renaming

  1. Type WebTrafficSource into the Name box of the Duplicate Name dialog.
  2. Click OK to close the Duplicate Name dialog.

Once we click OK, the Duplicate Name dialog disappears, and the newly named data source appears under the Data Sources folder. All that remains is to delete the original data source, from which we cloned WebTrafficSource.

  1. Right-click the original data source.
  2. Select Delete on the context menu that appears.
  3. Click the Yes button, to confirm the deletion.

Our tree should now resemble that shown in Illustration 10.


Illustration 10: WebTrafficSource Appears in the Tree

In Lesson One, as well as in other lessons, we used the Cube Wizard, together with the specialized "sub-wizards" (including the Dimension Wizard), as called by the Cube Wizard, to rapidly create a simple cube to explore the various aspects and steps of the process from a relatively high level. In this lesson, we will create a simple cube from the table we created in our last lesson, using the DTS package we created to perform extraction, transformation and load of the original data source, the sample Server Access Log.

Our preparation for the lesson is complete. We now have an Analysis Services database in place, linked to a valid data source (the WebTrafficAnalysis_DB database, containing the ServerAccessLog table, created via the DTS package in our last lesson). Next, we will initialize the Cube Editor, and begin creating our cube.

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.

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:

1/Apr/2002

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.


Illustration 20: Time Dimension, Properties Pane, Basic Tab

13.  Click the Advanced tab in the Properties pane.

14.  Click-select the All Caption property text box.

15.  Type in the following:

	All Time

16.  Click-select the Type property.

17.  Using the selector arrow in the property field to the right, select Time.

18.  Press Enter.

The Advanced tab of the Properties pane for the Time dimension appears as shown in Illustration 21.


Illustration 21: Time Dimension, Properties Pane, Advanced Tab

19.  Click the Date level underneath the Time dimension to select it in the cube tree.

20.  Ensure the Basic tab in the Properties pane is selected.

21.  Rename the level to Year.

22.  Type the following into the Description property text box:

	Year Level

23.  Type the following expression into the Member Key Column property:

	Substring("dbo"."ServerAccessLog"."Date",PATINDEX('%200%', Date),4)

24.  Type the following expression into the Member Name Column property (same as the expression for the Member Key Column immediately above):

	Substring("dbo"."ServerAccessLog"."Date",PATINDEX('%200%', Date),4)

Our intent with the above expressions is to extract, as it were, year information from the variable-length string that populates our Date column in the source data. Note that, in many cases, either SQL or VBA can be used in these property fields to grant us a great deal of flexibility in the source data with which we can work.

Cube performance can be handicapped, beyond argument, through this obviously "stringy" approach, so we would need to attempt, as a top priority, to get our data in the optimal format from the ETL process(es) that produce it. Nevertheless, again, my point here is to illustrate approaches (including the use of functions) for handling cases where we need to keep moving with what we have, for whatever reason.

The Basic tab of the Properties pane for the currently single existing Time level, now named Year, appears as shown in Illustration 22.


Illustration 22: Year Level, Properties Pane, Basic Tab

25.  Click the Advanced tab in the Properties pane.

26.  Modify the Level Type property to read Years.

The Advanced tab of the Properties pane for the Year level appears as shown in Illustration 23:


Illustration 23: Year Level, Properties Pane, Advanced Tab

27.  Click the Time dimension again to select it.

28.  Select Insert -> Level from the main menu.

The Insert Level dialog appears, as depicted in Illustration 24.


Illustration 24: The Insert Level Dialog

29.  Click Date to select it in the dialog.

30.  Click OK.

The new Date level appears, subordinate to Year within the Time dimension, as shown in Illustration 25.


Illustration 25: The New Date Level Appears

31.  Click the new Date level to select it in the tree.

32.  Select the Basic tab in the Properties pane, if necessary.

33.  Rename the level to Month via the Name property setting.

34.  Type the following into the Description property text box:

	Month Level

35.  Type the following expression into the Member Key Column property:

	Substring("dbo"."ServerAccessLog"."Date",(PATINDEX('%/%', Date)+1),3)

36.  Type the following expression into the Member Name Column property (same as the expression for the Member Key Column immediately above):

	Substring("dbo"."ServerAccessLog"."Date",(PATINDEX('%/%', Date)+1),3)

37.  Click the Advanced tab in the Properties pane.

38.  Modify the Level Type property to read Months.

Because we are focusing on concepts and general procedures in this lesson, we will stop at the Month level, even though, had our source date fields consisted of data that was truly of a datetime type, we might have proceeded to build our model to support analysis at the hour, or even lower, levels. This is not an uncommon requirement with web statistics analysis, in my experience, and Analysis Services is certainly up to the task, assuming that the appropriate level of granularity exists in the source data.

At this juncture, the cube tree should appear as shown in Illustration 26.


Illustration 26: Cube Tree, Reflecting Our Modifications, and Additions of Levels, Thus Far

Now let's focus on the IPAdd dimension, which, in this simple model, will store IP Address information for the visitors to our web site.

39.  Select the IPAdd dimension in the Dimensions folder.

40.  Click the Basic tab in the Properties pane, as required.

41.  Rename the dimension to Source.

42.  Type the following into the Description property:

     Visitor IP Address

The Basic tab of the Properties pane for the Source dimension appears as shown in Illustration 27.


Illustration 27: Source Dimension, Properties Pane, Basic Tab

43.  Click the Advanced tab in the Properties pane.

44.  Type the following into the All Caption property:

	All Source

The Advanced tab of the Properties pane for the Source dimension appears as depicted in Illustration 28.


Illustration 28: Source Dimension, Properties Pane, Advanced Tab

45.  Expand the Source dimension to expose the IPAdd level underneath.

46.  Click the IPAdd level to select it in the cube tree.

47.  Ensure the Basic tab in the Properties pane is selected.

48.  Rename the level to IP Address.

49.  Press Enter to apply the changes.

The cube tree should now appear as depicted in Illustration 29.


Illustration 29: Cube Tree, Reflecting All Modifications and Additions

Let's make one more enhancement to our cube before saving and processing it. We will enable drillthrough to allow us to "explode" on a specific value within our browses to see the underlying detail at the source table level.

1. Select Tools -> Drillthrough Options ... from the main menu.

The Cube Drillthrough Options dialog appears.

2. Click the Enable Drillthrough check box (in the upper left corner of the dialog) to check it.

3. Click the "Date" and "IPAdd" check boxes to check them.

The Cube Drillthrough Options dialog appears as shown in Illustration 30.


Illustration 30: The Save the Cube Dialog

4. Click OK.

The Drillthrough Settings warning message box appears, as seen in Illustration 31.


Illustration 31: The Save the Cube Dialog

5. Click OK.

We will process the cube at this stage.

Processing the Cube

Let's process our new cube, and then take a look at the results via the Cube Browser.

1. Select Tools -> Process Cube from the main menu.

The Save the Cube dialog appears as shown in Illustration 32.

Click for larger image

Illustration 32: The Save the Cube Dialog

2. Click Yes.

The Cube: New Name dialog appears.

3. Name the cube Web Site Traffic, as shown in Illustration 33.


Illustration 33: The Cube: New Name Dialog

4. Click OK.

A message box (shown in Illustration 34) appears warning that the cube has no aggregations designed, and asking if we want to design aggregations prior to processing the cube.


Illustration 34: Warning Message Box: No Aggregates Designed

5. Click No.

The Process a Cube dialog appears, as depicted in Illustration 35. The Full Process option is selected by default, as this is the first time the cube has been proposed for processing.


Illustration 35: The Process a Cube Dialog

6. Click OK.

Cube processing begins, as evidenced in the status display of the Process dialog that appears. Processing rapidly completes, at which point the Process dialog appears similar to that partially shown in Illustration 36, displaying a "Processing Completed Successfully" statement in green at the bottom of the dialog.


Illustration 36: The Process Dialog, Displaying Status (Partial View)

7. Click Close.

8. Click File --> Exit from the top menu to close the Cube Editor.

The Cube Editor closes, and we are returned to the Analysis Manager console.

9. Expand the Cubes folder within which we have been working.

We see the Web Site Traffic Cube in the tree pane, as depicted in Illustration 37.


Illustration 37: The Analysis Manager Console - Tree Pane (Partial View)

Keep in mind that we must process the cube before browsing actual data, anytime we build a new cube and design its storage options and aggregations, or anytime we change a cube's structure (measures, dimensions, and so on), where we intend to save the changes to the cube. We must also process a cube anytime we change the structure of a shared dimension (via the Dimension Wizard) used in the cube. If data in the data source (i.e. data warehouse) supporting the cube has been added or changed, processing is appropriate to furnish updated, accurate results when browsing the cube.

Let's take a look at the data in our cube at this point, to get a feel for the effectiveness of our design.

Viewing Data in the Web Site Traffic Analysis Cube

As most of us know, we can easily view data in our cube from our present position in the Analysis Manager. The Cube Browser that is supplied with Analysis Manager is an excellent way to do rapid reviews of both cube structure and data during and after the design process. The Cube Browser is displayed anytime we right-click a cube that has been processed, and then select Browse Data from the context menu that appears. (If the cube is not yet processed, we can always view sample data, while designing, in the Data Tab of the Cube Editor, as most of us are aware.)

Let's take the following steps to see the results of our work within the Web Site Traffic Analysis Cube:

1. Right-click the Web Site Traffic Cube.

2. Select Browse Data from the context menu that appears.

After a "Retrieving the Cube Data" message briefly appears, we see the actual data presented in the Browser, as partially shown in Illustration 38.


Illustration 38: Actual Data in the Cube Browser (Compressed View)

3. Drag the Time dimension from the data slicing pane (the button atop, and to the upper left, in the Cube Browser) to drop over the IP Address row dimension in the Data Viewing pane.

The Time dimension, Year summary level, now appears in the rows, as shown in Illustration 39.


Illustration 39: Time Dimension - Year Level in the Rows (Compressed View)

Let's delve a bit deeper into the data through the Time dimension, by drilling down on the Years we see summarized.

4. Double-click the Year column label.

Years 2002 and 2003 are expanded, displaying monthly summaries of site interactions as depicted in Illustration 40.


Illustration 40: Drill Down to the Month Level in the Rows (Compressed View)

Let's see what we can find out about the visitors themselves using the Cube Browser. We will nest the Source dimension within the Time dimension, meaning that it will share the row axis and allow further subanalysis of Time. The end result will be to give us a picture of interaction counts, by Month, by Visitor IP Address, at the lowest level of the display.

5. Drag the Source dimension button from the data slicing pane, to the right of the Month column, placing the cursor over the "white space" of the Measures column.

The cursor becomes a ghost-like icon, similar to that shown in Illustration 41, at the "drop point."


Illustration 41: The Cursor "Drop Point" Icon to Nest within the Row Axis

6. Release the mouse to drop the Source dimension within the rows axis.

The Source dimension appears nested within the Month levels of the Time dimension, all in the rows axis of the display, as partially shown (the representative month of August 2002) in Illustration 42.


Illustration 42: The Source Dimension is Nested within the Time Dimension (Partial View)

Now we can use drillthrough to illustrate a means of investigating the values we see. Say we wish to see the detailed dates of interaction from a given IP Address (we'll use 137.144.139.74, which, as circled in Illustration 42 above, indicates a total of six interactions with the site during the month of August 2002).

7. Double-click on the value indicated above (the measure "6") to drill through to the source table details.

We are greeted with the details of the visits, directly from the source database table, that make up the total number of interactions (six) for IP Address 137.144.139.74, upon which we have drilled through, as depicted in Illustration 43.


Illustration 43: Details behind the Interaction Summary

It is easy to see how valuable the drillthrough capability will be for other general analysis of our business results, forecasts and so forth, for finance and accounting, human resources, inventory, and a host of other perspectives within the enterprise. Enabling drillthrough with Analysis Services gives us the best of both worlds - summary level analysis, with the ad hoc capability to see the details that underlie a value that warrants further scrutiny. A simple double click of a value in the Cube Browser delivers the filtered list of transactions for examination.

To gain a sense of comfort for the accuracy and completeness of our results, we have only to examine the data within the independent source database. We will do so with the following procedure. Leaving Analysis Manager "parked" where we are:

8. Start the MSSQL Server SQL Query Analyzer.

The Connect to SQL Server dialog appears, as shown in Illustration 44.


Illustration 44: The Connect to SQL Server Dialog

9. Complete the dialog as appropriate to your local environment.

10. Click OK.

A blank, untitled query window appears, pointed to the default database. As most of us are aware, the Query Editor therein provides a convenient point for creating queries (and other SQL scripts), and executing them against SQL Server databases.

11. Using the Database Selector atop the query window, select the WebTrafficAnalysis_DB as shown in Illustration 45.


Illustration 45: Select the WebTrafficAnalysis_DB

12. Type the following simple query into the Editor Pane:

 
 SELECT *
 FROM dbo.ServerAccessLog
 WHERE IPAdd = '137.144.139.74' and Date = '27/Aug/2002'

Here we are asking for the details for the IP address and date upon which we drilled earlier, but this time from the source database, as a means of verifying the results we obtained in the Analysis Manager / drillthrough scenarios above.

13.  Select Query -> Execute to run the query.

The results appear (grid tab), as shown in Illustration 46.


Illustration 46: Query Results

We see that, indeed, there were six interactions from the selected IP Address on the date we specified. We can verify virtually any drillthrough details we obtained in this manner to ascertain that we have designed our cube properly from the associated perspectives.

14.  Close the Query Analyzer when desired.

15.  Close the Drillthrough Data viewer in Analysis Manager.

16.  Click Close on the Cube Browser to close it.

17.  Exit the Analysis Manager console as desired.

Our lesson concludes with the above actions, leaving us with an idea of an approach for creating a Web Site Traffic Analysis Cube. While a far more elaborate cube can be developed, this will perhaps serve as a general overview of some of the basic considerations that we encounter in this, and similar, real-world scenarios.

Summary ...

In this two-part lesson, Build a Web Site Traffic Analysis Cube, we focused on the design and construction of a basic Web Site Traffic Analysis Cube. In Part I, we briefly discussed potential business reasons for collecting web site traffic data. We then discussed the use of the Server Access Log as a source of data surrounding web site activity, and designed and built an extract package using Data Transformation Services, to illustrate a straightforward approach for extracting and transforming simple data, then loading it into an MSSQL Server data source.

In this part of the lesson, we entered the design and build phases for our Web Site Traffic Analysis Cube. We created an Analysis Services database, and then we connected the database to the relational source table we created in Part I. After we generated the cube, we performed browses of the data it contained, to examine the results of our handiwork. Finally, we verified drillthrough results we obtained through an independent query against the source relational data in MSSQL Server.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved