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.
The Database dialog box appears.
Let's simply add "Web Stats Reporting Database," here - the description is optional, of course.
The dialog appears as below.
The new OLAP database WebTrafficSource appears at a point below the existing FoodMart 2000 database, in the left-side cube tree.
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.
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):
Note: For more detailed information on this, review the on-line documentation for OLE DB Providers, and data sources in general.
The focus moves to the Connection tab.