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.