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

August 25, 2003

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:

4. Click New Database.

The Database dialog box appears.

5. 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.

6. Click OK.

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

7. 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.

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.

8. 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):

9. 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.

10. Click Next.

The focus moves to the Connection tab.