Using the Cube Editor to Work with Measures
Having set up our
dimensions, we will now conclude our cube design by focusing on the measures we
have designated. While measures can come directly from the fact table (a
minimum of one measure is required in an Analysis Services cube), as have the
measures we have defined up to this point in our design exercises, we have
additional options for measure addition, including the use of expressions.
Let's examine several characteristics of measures as we complete our cube
development in the following steps.
Let's say, as an
illustrative requirement, that information consumers have asked us to make
available a measure for the gross profit generated by the various
products of the organization. We ascertain that what they want is the difference
in Store Cost and Store Sales, two measures that we already
include in our model. Further, the consumers want to generate a measure that
presents gross profit as a percentage of Store Sales.
We will create a derived
measure (a measure that does not occur naturally in the database) and a calculated
measure (a calculated member in the Measures dimension) to
add the requested information to our cube. To do so, we will take the
following steps within the Cube Editor.
1.
Right-click
the Measures folder for the ANSYS05 cube.
2.
Click New
Measure from the context menu, as shown below.
Illustration 53: Click New Measure to Begin Creation of a New Measure
The Insert Measure dialog appears.
3.
Select Store
Cost, as shown in Illustration 54.
Illustration 54: Select the Fact Table Column upon which to Base the New Measure
4.
Click OK.
We see a new measure, Store Cost 1, appear in the cube
tree.
5.
With Store
Cost 1 selected, click the Basic tab in the Properties pane
beneath.
6.
Rename Store
Cost 1 by modifying the Name field to read Gross Profit.
7.
Type the
following into the Source Column field.
"sales_fact_1997"."store_sales" -
"sales_fact_1997"."store_cost"
8.
Click the Advanced
tab in the Properties pane for the Gross Profit measure.
9.
Click the Display
Format property.
10.
Click the
dropdown selector in the Display Format property field and select Currency (
if not already selected), as shown below.
Illustration 55: Display Format Property Setpoint - Currency
11.
Right-click
the Calculated Members folder for the ANSYS05 cube.
12.
Click New
Calculated Member from the context menu, as shown below.
Illustration 56: Click New Calculated Member to Begin Creation of a Calculated Measure
The Calculated Member Builder appears.
13.
Type the following
expression into the Value Expression box:
[Measures].[Gross Profit]/[Measures].[Store Sales]
The components of the expression can also be
selected by double clicking the objects in the Data tree below the Value
Expression box.
14.
Type GP
Percent Sales into the Member Name box.
15.
Ensure that
the Parent Dimension is indicated as Measures.
The Calculated Member Builder appears as shown in Illustration
57.
Illustration 57: The Completed Calculated Member Builder
16.
Click OK.
We see a new calculated member, GP Percent Sales,
appear in the Calculated Members folder in the cube tree, as shown
below:
Illustration 58: The New Calculated Member Appears
17.
With GP
Percent Sales selected, click the Advanced tab in the Properties
pane beneath.
18.
Click the Format
String property.
19.
Click the
dropdown selector in the Format String property field and select Percent,
as shown below.
Illustration 59: Format String Property Setpoint - Percent
We now
have two derived measures in addition to the original measures based directly
on fact table fields. At any time we can click the Data tab to see the
layout of our work via the Preview pane, but we will need to keep in
mind that, until we process the cube, only sample values appear (again, note the
warning at the bottom of the Preview pane). This provides adequate
testing for formats but obviously not for the results of most expressions,
etc.
Another
consideration before processing the cube might be the order in which the
measures appear in the cube tree: the first measure listed in the tree is the
default measure for the cube.
Page 14: Process the Cube from the Cube Editor
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
