Follow

Measures

Measures

Measures determine the numerical values of a cube and a cube must contain at least one measure. You can define the following three types of measures:

  • Standard measures obtain their values directly from a column in a source fact table.
  • Derived measures are derived before aggregation or summing of columns. This means that they are calculated when the cube is processed and are stored in the fact table. You can use standard arithmetic operators and MDX statements to create derived measures.
  • Calculated measures are calculated after aggregation and summing. They are calculated at query time and are never stored. You can create calculated measures using standard arithmetic operators and MDX statements and can also combine them with other measures.

Adding Standard Measures

  1. Expand the cube to which you want to add a measure, right-click Measures and click Add Standard Measure. The Add Measure window appears.
  2. In the Name box, type a name for the measure.
  3. In the Fact Tablelist, select the fact table that you want to use for the measure.
  4. In the Field list, select the field that you want as the measure. Disable this field by clicking the box next to it.
  5. In the Type list, select the preferred aggregation method. You have the following options:
    Aggregation MethodDescription
    SUMReturns the sum of all values
    COUNTCounts all rows and returns the total number of rows
    MIN Returns the lowest value
    MAXReturns the highest value
    DistinctCountReturns the number of unique values
  6. Select Visible if you want the measure to be displayed in the front-end application.
  7. In the Format string field, specify how you want the numeric results displayed. You have the following options:
    Format StringDescription
    NoneApplies no formatting
    0Displays a digit if the value has a digit where the zero (0) appears in the string, otherwise a zero is displayed
    #Displays a digit if the value has a digit where the number sign (#) appears in the string, otherwise nothing is displayed
    .Determines the number of digits displayed to the left and right of the decimal separator.
    % Is a percentage placeholder
    ,Separates thousands from hundreds
    PercentTyping Percent will default the measure to showing as a percentage with two decimal places

    Below are examples of what the output will look like for various combinations of the format strings:
    Format StringOutput
    None1234567.89
    #,#1,234,567
    #,#.001,234,567.89
    #,#%1%
    #,#.0%1.2%
    Percent1.23%

Adding Derived Measures

  1. Expand the cube to which you want to add a measure, right-click Measures and click Add Derived Measure. The Add Derived Measure window appears.
  2. In the Fact Table list, select the fact table that you want to use for the measure.
  3. In the Name box, type a name for the derived measure.
  4. Select Visible if you want the measure to be displayed in the front-end application.
  5. In the Format String box, type how you want the numeric results displayed. The format is the same as for standard measures. See Adding Standard Measures.
  6. 7. In the Expression box, enter an MDX statement
    or
    In the Measures list, select the measures from the fact table that you want to use for the derived measure and clickAdd. Your selections will be added to the expression, where they can be combined with mathematical operators to achieve the outcome you desire.
  7. Click OK to add the derived measure.

Adding Calculated Measures

  1. Expand the cube to which you want to add a measure, right-click Measures and click Add Calculated Measure. The Add Calculated Measure window appears.
  2. In the Name box, type a name for the calculated measure.
  3. Select Visible if you want the value to be displayed in the front-end application.
  4. In the Format string field, specify how you want the numeric results displayed. The format is the same as for standard measures. See Adding Standard Measures.
  5. (Optional) In the Non-empty Behavior list, select the measure or measures used to resolve NON EMPTY queries in MDX.
  6. In the Expression field, write an MDX statement or, in the Measures list, drag the measures to be used for the calculated measure into the workspace in the middle.
Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.