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
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.
- Start Analysis Manager.
- Expand the Analysis Servers folder by clicking
the "+" sign to its immediate left.
- Right-click on the Analysis Server name (mine
is MOTHER, in this example), to see the context menu shown below:
1: Right-Click on the Analysis Server
- Click New Database.
The Database dialog box appears.
- 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.
2: The Database Dialog Box
- Click OK.
The new OLAP database WebTrafficSource appears at a point
below the existing FoodMart 2000 database, in the left-side cube tree.
- 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: 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.
- 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):
- 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.
4: The Data Link Properties Dialog - Provider Tab, with our Selection
- Click Next.
The focus moves to the Connection tab.