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 9

By William Pearson

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, 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, upon which we have drilled through, as depicted in Illustration 43.

Illustration 43: Details behind the Interaction Summary

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