Microsoft Docs

How to Design Measures in Analysis Services Multidimensional Models

Measures are the numerical values in your Analysis Services cube that users will analyze. Designing effective measures is crucial for providing meaningful insights. This guide walks you through the process of designing and implementing measures in a multidimensional model.

Understanding Measure Types

Measures are typically stored in measure groups, which are conceptually similar to tables in a relational database. Within a measure group, you can define different types of measures:

Steps to Design Measures

  1. Identify Key Business Metrics

    Start by identifying the key performance indicators (KPIs) and business metrics that your organization needs to track. Examples include Sales Amount, Profit, Quantity Sold, Average Price, etc.

  2. Determine Aggregation Types

    For each identified metric, decide on the appropriate aggregation type. Common choices include:

    • Sum: For additive values like Sales Amount, Quantity.
    • Count: To count the occurrences of an item.
    • Average: To calculate the average of a value.
    • Min / Max: To find the minimum or maximum value.
    • Count Distinct: To count unique values.

    The default aggregation is usually Sum. You can configure this in your measure properties.

  3. Create Measures in SQL Server Data Tools (SSDT)

    In SSDT, navigate to your cube or measure group. You can add new measures by right-clicking the Measures folder and selecting "New Measure".

    When creating a new measure, you will typically:

    • Select the Source (e.g., a column from a fact table).
    • Choose the Aggregation function.
    • Provide a descriptive Name.
    • Optionally, set a Format string for display.

  4. Implement Calculated Measures

    For more complex calculations, you'll use calculated measures. Click "New Calculated Measure" and write your MDX expression.

    Example: Calculating Profit Margin

    A common calculated measure is Profit Margin. Here's a simplified MDX example:

    
IIF(
    [Measures].[Internet Sales Amount] = 0,
    BLANK(),
    ([Measures].[Internet Sales Amount] - [Measures].[Internet Sales Cost]) / [Measures].[Internet Sales Amount]
)

    Remember to define appropriate format strings for percentages.

  5. Define Measure Formatting

    Use the Format String property to control how measures are displayed. Common format strings include:

    • #,##0.00: Number with two decimal places and thousands separator.
    • $#,##0.00: Currency format.
    • 0.00%: Percentage format.

  6. Organize Measures into Measure Groups

    Measures are logically grouped within Measure Groups. A measure group usually corresponds to a fact table in your data source. This helps in organizing your cube and managing performance.

  7. Review and Refine

    After creating your measures, deploy and process your cube. Connect to it using a client tool (like Excel or Power BI) and verify that the measures are behaving as expected and providing accurate results. Refine your measures based on user feedback and business requirements.

Best Practices for Measure Design

  • Keep Measures Atomic: Measures should represent single, atomic facts. Avoid creating measures that combine different concepts.
  • Use Descriptive Names: Names should clearly indicate what the measure represents (e.g., "Total Sales Amount", "Average Discount Percentage").
  • Standardize Aggregation: Be consistent with aggregation methods for similar types of data.
  • Leverage Calculated Measures Wisely: Use them for derived metrics that cannot be directly aggregated from the source data.
  • Consider Performance: Complex MDX calculations can impact query performance. Optimize your calculations and consider using aggregations for frequently accessed measures.