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.
» 
Comment and Contribute
