SQL Server Analysis Services Documentation

Creating Measures

Measures are the quantitative values that users analyze in a cube. They represent business metrics such as sales amount, quantity, profit, or employee count. Measures are typically stored in the fact tables of your data warehouse.

Understanding Measure Types

Measures can be classified into two primary types:

• Simple Measures: These directly correspond to a column in a fact table. For example, a SalesAmount column in a fact table can be directly represented as a simple measure.
• Calculated Measures: These are measures computed based on existing measures or other calculations using MDX (Multidimensional Expressions). They allow for more complex analysis, such as year-over-year growth, percentages, or ratios.

Creating Simple Measures

In SQL Server Data Tools (SSDT) for Analysis Services, you can create simple measures through the Cube Designer.

1. Open your Analysis Services project in SSDT.
2. Navigate to the Cube Designer for the desired cube.
3. In the Measures pane, click New Measure.
4. Source Table: Select the table containing the measure data (usually a fact table).
5. Source Column: Select the column from the fact table that holds the numeric data for your measure.
6. Name: Provide a descriptive name for the measure (e.g., "Sales Amount").
7. Aggregation Function: Choose an appropriate aggregation function (e.g., Sum, Count, Average, Min, Max). For most transactional data, Sum is commonly used for measures like sales or quantity.
8. Click OK to create the measure.

Creating Calculated Measures

Calculated measures are defined using MDX expressions.

1. In the Cube Designer, click New Calculated Measure in the Measures pane.
2. Name: Provide a descriptive name for the calculated measure (e.g., "Year-over-Year Sales Growth").
3. Expression: Enter the MDX formula. For example, to calculate the percentage of sales for a specific product relative to total sales:

   
   -- Assuming [Measures].[Sales Amount] is your base measure
   -- and [Product].[Product Category].[Category].CurrentMember is the current member in the Product dimension
   DIVIDE([Measures].[Sales Amount], SUM([Measures].[Sales Amount] * [Product].[Product Category].[Category].CHILDREN))
                       

   For a year-over-year growth calculation (assuming you have a Time dimension with Year level):

   
   DIVIDE(
       ([Measures].[Sales Amount] - ([Measures].[Sales Amount], [Date].[Calendar].[Calendar Year].PrevMember)),
       ([Measures].[Sales Amount], [Date].[Calendar].[Calendar Year].PrevMember)
   )
                       

4. Format String: Optionally, specify a format string to control how the measure is displayed (e.g., "Percent", "$#,##0.00").
5. Click OK.

Measure Properties

Measures have several properties that affect their behavior and presentation:

• Name: The display name of the measure.
• Source Column: For simple measures, the source column in the fact table.
• Aggregation Function: How the measure values are aggregated (e.g., Sum, Average).
• Format String: Controls the number formatting.
• Description: A textual description of the measure.
• Kpi: Link to a Key Performance Indicator if applicable.

Best Practices

• Granularity: Measures should be at the lowest level of granularity defined by the associated fact table.
• Naming Conventions: Use clear and consistent naming conventions for measures.
• Calculated Measures: Use them sparingly and ensure their logic is well-documented and tested. Complex calculations can impact performance.
• Aggregation Designs: Properly configure aggregation designs to improve query performance for frequently accessed measures.