Build a Web Site Traffic Analysis Cube: Part I - Page 5

July 21, 2003

Because no tables exist in the new WebTrafficAnalysisDB database, we will take advantage of the opportunity provided by DTS to create our destination table. As a part of the process, we will use the transformation features of DTS to filter out any part of the log that we do not require, as well as to facilitate other finishing touches.

33.         Select Task -> 3 Transform Data Task from the main menu.

A small icon, labeled Select Source Connection, appears, attached to the cursor.

34.         Position the cursor / icon combination over the Server Access Log connection icon.

35.         Click the ServerAccessLog connection, once the cursor is over it.

The ServerAccessLog connection is now designated the source connection. The cursor label immediately becomes Select Destination Connection.

36.         Position the cursor / icon combination over the WebTrafficAnalysisDB connection icon.

37.         Click the WebTrafficAnalysisDB connection, once the cursor is over it.

The icon label disappears, and a directional line, representing the Transform Data Task, is drawn between the two data connections, as shown in Illustration 13.

Illustration 13: Directional Line Connects the New Data Connections

38.         Double-click the Transform Data Task line.

The Transform Data Task Properties dialog appears.

39.         Click the Source tab, as necessary.

40.         Type ETL_ServerAccessLog into the Description box.

The Source tab appears as shown in Illustration 14.

Illustration 14: Transform Data Task Dialog Source Tab

41.         Click the Destination tab.

The Create Table dialog appears. Here we can modify the existing SQL to create the new destination table.

42.         Type the following into the SQL Statement box, replacing the SQL that is in place initially.

		CREATE TABLE [ServerAccessLog] (
		[Date] varchar (11) NULL,
		[IPAdd] varchar (15) NULL

43.         Click OK.

The Destination tab appears as shown in Illustration 15 after our changes.

Illustration 15: Transform Data Task Dialog Destination Tab

44.         Click the Transformations tab.

45.         Click Delete All to remove any pre-existing mapping lines.

Illustration 16 represents our starting point in the Transformations tab.

Illustration 16: Transform Data Task Dialog Transformations Tab

46.         Click New.

The Create New Transformation dialog appears.

47.         Select the Middle of String transformation, as shown in Illustration 17.

Illustration 17: Create New Transformation Dialog

48.         Click OK.

The Transformation Options dialog appears, defaulted to the General tab.