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 2

By William Pearson

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.

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