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.
the Measures folder for the ANSYS05 cube.
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.
Cost, as shown in Illustration 54.
Illustration 54: Select the Fact Table Column upon which to Base the New Measure
We see a new measure, Store Cost 1, appear in the cube
Cost 1 selected, click the Basic tab in the Properties pane
Cost 1 by modifying the Name field to read Gross Profit.
following into the Source Column field.
Click the Advanced
tab in the Properties pane for the Gross Profit measure.
Click the Display
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
the Calculated Members folder for the ANSYS05 cube.
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.
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
Percent Sales into the Member Name box.
the Parent Dimension is indicated as Measures.
The Calculated Member Builder appears as shown in Illustration
Illustration 57: The Completed Calculated Member Builder
We see a new calculated member, GP Percent Sales,
appear in the Calculated Members folder in the cube tree, as shown
Illustration 58: The New Calculated Member Appears
Percent Sales selected, click the Advanced tab in the Properties
Click the Format
dropdown selector in the Format String property field and select Percent,
as shown below.
Illustration 59: Format String Property Setpoint - Percent
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,
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