Introduction to Measures

Measures are fundamental building blocks in Analysis Services data models. They represent numerical values that can be aggregated and analyzed, such as sales revenue, unit count, or profit margin. Measures are typically derived from fact tables in your data warehouse and are defined using DAX (Data Analysis Expressions).

Types of Measures

• Implicit Measures: These are automatically created by Analysis Services when you drag numerical columns from fact tables into a model. They are convenient but offer less control.
• Explicit Measures: These are defined manually using DAX formulas. They provide greater flexibility, control over aggregation logic, and the ability to implement complex business calculations. It is generally recommended to use explicit measures for better maintainability and performance.

Creating Explicit Measures

You can create explicit measures using tools like SQL Server Data Tools (SSDT) or by directly editing the model metadata. A common DAX function for creating simple aggregation measures is SUM, AVERAGE, COUNT, or DISTINCTCOUNT.

-- Example: Total Sales Amount Measure
Total Sales = SUM(Sales[SalesAmount])

Measure Groups

In tabular models, measures can be organized into measure groups. This helps in logical grouping and presentation of related measures, especially in large models.

Understanding KPIs

Key Performance Indicators (KPIs) are business metrics used to evaluate the success of an organization or a specific activity. In Analysis Services, KPIs are built upon measures and provide a standardized way to track performance against defined goals.

Components of a KPI

• Base Measure: The core measure that the KPI is based on (e.g., Total Sales).
• Goal: A target value or expression that defines the desired performance level. This can be a static value, another measure, or a calculation.
• Status: An expression that evaluates the current performance of the base measure against the goal. This typically returns a numerical value representing the status (e.g., 1 for good, 0 for neutral, -1 for bad).
• Target (Optional): An expression that defines the goal itself.
• Graphic Representation (Optional): Icons or colors to visually represent the status.

Defining a KPI

KPIs are defined within the Analysis Services model. For example, you might define a "Sales Target Achieved" KPI where:

• Base Measure: `Total Sales`
• Goal: `[Sales Target]` (another measure representing the sales target)
• Status: `IF( [Total Sales] >= [Sales Target], 1, IF( [Total Sales] >= [Sales Target] * 0.9, 0, -1) )`

This KPI would indicate success if total sales meet or exceed the target, neutral if they reach 90% of the target, and poor otherwise.

Best Practices

• Use Explicit Measures: Always prefer explicit measures over implicit ones for clarity, consistency, and advanced logic.
• Meaningful Naming: Use clear and descriptive names for your measures and KPIs.
• Organize Measures: Group related measures logically using measure groups.
• Document Calculations: Add descriptions to complex measures to explain their logic.
• Performance Tuning: Regularly review and optimize your measure calculations for efficient query performance.