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