## 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

- Expand the cube to which you want to add a measure, right-click Measures and click Add Standard Measure. The
**Add Measure**window appears. - In the Name box, type a name for the measure.
- In the Fact Tablelist, select the fact table that you want to use for the measure.
- In the Field list, select the field that you want as the measure. Disable this field by clicking the box next to it.
- In the Type list, select the preferred aggregation method. You have the following options:
Aggregation Method Description SUM Returns the sum of all values COUNT Counts all rows and returns the total number of rows MIN Returns the lowest value MAX Returns the highest value DistinctCount Returns the number of unique values - Select Visible if you want the measure to be displayed in the front-end application.
- In the Format string field, specify how you want the numeric results displayed. You have the following options:
Format String Description None Applies no formatting 0 Displays 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 Percent Typing 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 String Output None 1234567.89 #,# 1,234,567 #,#.00 1,234,567.89 #,#% 1% #,#.0% 1.2% Percent 1.23%

### Adding Derived Measures

- 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. - In the
**Fact Table**list, select the fact table that you want to use for the measure. - In the
**Name**box, type a name for the derived measure. - Select
**Visible**if you want the measure to be displayed in the front-end application. - 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. - 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 click**Add**. Your selections will be added to the expression, where they can be combined with mathematical operators to achieve the outcome you desire. - Click OK to add the derived measure.

### Adding Calculated Measures

- 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. - In the Name box, type a name for the calculated measure.
- Select Visible if you want the value to be displayed in the front-end application.
- 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.
- (Optional) In the Non-empty Behavior list, select the measure or measures used to resolve NON EMPTY queries in MDX.
- 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.

## 0 Comments