Microsoft Docs > SQL Server > Analysis Services > Multidimensional Models > Creating Measures

Creating Measures in Multidimensional Models

Measures are numerical values that represent business metrics and are the core of any cube. They enable users to perform calculations and analyze data. This document guides you through the process of creating measures within SQL Server Analysis Services (SSAS) multidimensional models.

Understanding Measures

Measures are typically stored in a fact table and are aggregated using standard aggregation functions like Sum, Count, Average, Min, and Max. You can also create calculated measures using MDX (Multidimensional Expressions) to perform more complex calculations.

Types of Measures

  • Regular Measures: Directly derived from columns in the fact table.
  • Calculated Measures: Defined using MDX expressions, allowing for dynamic calculations based on other measures or attributes.

Steps to Create a Measure

You can create measures using SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS).

Using SQL Server Data Tools (SSDT)

  1. Open your Analysis Services project in SSDT.
  2. Navigate to the Cube Designer for the cube where you want to create the measure.
  3. In the Data Source View pane, locate the fact table that contains the data for your measure.
  4. Right-click on the column you want to convert into a measure and select New Measure.
  5. Alternatively, navigate to the Measures folder in the Cube Browser pane, right-click, and select New Measure.
  6. In the Create Measure dialog box:
    • Name: Provide a descriptive name for your measure (e.g., 'Sales Amount', 'Quantity Sold').
    • Source: Select the table and column from which the measure will be derived.
    • Aggregation: Choose the aggregation function (e.g., Sum, Count, Average).
    • Format String: Define how the measure will be displayed (e.g., currency, percentage).
    • Description: Add a brief description for clarity.
  7. Click OK.

Creating Calculated Measures

Calculated measures are powerful for deriving new insights from your data.

  1. In the Cube Designer, navigate to the Measures folder.
  2. Right-click and select New Calculated Measure.
  3. In the Measure Editor:
    • Name: Provide a name for your calculated measure.
    • Expression: Write your MDX expression. You can use the Expression Builder to assist you.
    • Format String: Define the display format.
    • Description: Add a description.

Example MDX Expression for a Calculated Measure (Profit Margin):

CREATE MEMBER CURRENTCUBE.[Measures].[Profit Margin] AS
    ( [Measures].[Sales Amount] - [Measures].[Cost Amount] ) / [Measures].[Sales Amount]
,FORMAT_STRING = 'Percent'
,DESCRIPTION = 'Calculates the profit margin percentage.'

This MDX expression calculates the profit margin by subtracting the 'Cost Amount' from the 'Sales Amount' and dividing the result by the 'Sales Amount'. The result is formatted as a percentage.

Measure Groups

Measures are organized into measure groups within a cube. A measure group typically corresponds to a fact table in your data source. Each measure group can have its own aggregation settings and storage mode.

You can manage measure groups in the Measure Groups pane of the Cube Designer.

Best Practices

  • Use clear and consistent naming conventions for your measures.
  • Define appropriate aggregation functions based on the data.
  • Utilize format strings for better data presentation.
  • Leverage calculated measures for complex business logic.
  • Organize measures logically into measure groups.
  • Document your measures and calculated measures thoroughly.
Note: Ensure your data source view is properly configured and that the relationships between dimension and fact tables are correctly defined before creating measures.
Tip: Experiment with different aggregation functions and MDX expressions to uncover deeper insights from your data.