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

August 25, 2003


Illustration 20: Time Dimension, Properties Pane, Basic Tab

13.  Click the Advanced tab in the Properties pane.

14.  Click-select the All Caption property text box.

15.  Type in the following:

	All Time

16.  Click-select the Type property.

17.  Using the selector arrow in the property field to the right, select Time.

18.  Press Enter.

The Advanced tab of the Properties pane for the Time dimension appears as shown in Illustration 21.


Illustration 21: Time Dimension, Properties Pane, Advanced Tab

19.  Click the Date level underneath the Time dimension to select it in the cube tree.

20.  Ensure the Basic tab in the Properties pane is selected.

21.  Rename the level to Year.

22.  Type the following into the Description property text box:

	Year Level

23.  Type the following expression into the Member Key Column property:

	Substring("dbo"."ServerAccessLog"."Date",PATINDEX('%200%', Date),4)

24.  Type the following expression into the Member Name Column property (same as the expression for the Member Key Column immediately above):

	Substring("dbo"."ServerAccessLog"."Date",PATINDEX('%200%', Date),4)

Our intent with the above expressions is to extract, as it were, year information from the variable-length string that populates our Date column in the source data. Note that, in many cases, either SQL or VBA can be used in these property fields to grant us a great deal of flexibility in the source data with which we can work.

Cube performance can be handicapped, beyond argument, through this obviously "stringy" approach, so we would need to attempt, as a top priority, to get our data in the optimal format from the ETL process(es) that produce it. Nevertheless, again, my point here is to illustrate approaches (including the use of functions) for handling cases where we need to keep moving with what we have, for whatever reason.

The Basic tab of the Properties pane for the currently single existing Time level, now named Year, appears as shown in Illustration 22.


Illustration 22: Year Level, Properties Pane, Basic Tab

25.  Click the Advanced tab in the Properties pane.

26.  Modify the Level Type property to read Years.

The Advanced tab of the Properties pane for the Year level appears as shown in Illustration 23:


Illustration 23: Year Level, Properties Pane, Advanced Tab

27.  Click the Time dimension again to select it.

28.  Select Insert -> Level from the main menu.

The Insert Level dialog appears, as depicted in Illustration 24.


Illustration 24: The Insert Level Dialog

29.  Click Date to select it in the dialog.

30.  Click OK.

The new Date level appears, subordinate to Year within the Time dimension, as shown in Illustration 25.


Illustration 25: The New Date Level Appears

31.  Click the new Date level to select it in the tree.

32.  Select the Basic tab in the Properties pane, if necessary.

33.  Rename the level to Month via the Name property setting.

34.  Type the following into the Description property text box:

	Month Level

35.  Type the following expression into the Member Key Column property:

	Substring("dbo"."ServerAccessLog"."Date",(PATINDEX('%/%', Date)+1),3)

36.  Type the following expression into the Member Name Column property (same as the expression for the Member Key Column immediately above):

	Substring("dbo"."ServerAccessLog"."Date",(PATINDEX('%/%', Date)+1),3)

37.  Click the Advanced tab in the Properties pane.

38.  Modify the Level Type property to read Months.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers