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

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

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.