Measure Groups

SQL Server > Analysis Services > Multidimensional Models > Measure Groups

Measure groups are fundamental components of a SQL Server Analysis Services (SSAS) multidimensional database. They organize related measures into a single unit, facilitating management, performance optimization, and business logic definition within a cube.

What are Measure Groups?

A measure group is a collection of measures that share the same aggregation source, typically a fact table in a data warehouse. Measures within a measure group are usually related and represent different granularities or calculations of the same underlying business facts. For instance, a measure group for sales might contain measures like 'Sales Amount', 'Sales Quantity', and 'Average Price'.

Key Concepts and Benefits

Organization: Measure groups provide a logical structure for measures, making cubes easier to understand and navigate.
Performance: SSAS can optimize query performance by processing and storing data for measures within the same measure group together.
Aggregation Design: You can define different aggregation strategies for each measure group, allowing for tailored performance tuning.
Data Source Association: Each measure group is linked to a specific data source view and a fact table or view, defining where the measure data originates.
Security: You can apply role-based security at the measure group level to control access to specific sets of measures.

Creating and Managing Measure Groups

Measure groups are typically created and managed using SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS) when designing an Analysis Services project.

Steps involved:

Data Source View: Ensure you have a data source view that includes the fact table(s) containing your measures.
Create Measure Group: In SSDT, right-click on the 'Measure Groups' folder in your project and select 'New Measure Group'.
Select Fact Table: Choose the fact table from your data source view that will serve as the source for the measures.
Define Measures: Associate measures from the fact table (e.g., quantity, amount) with the measure group. You can also define calculated measures here.
Configure Aggregations: Define aggregation designs for the measure group to optimize query performance.
Partitions: Measure groups can be further divided into partitions for granular data management and performance tuning.

Example Scenario

Consider a retail business. You might have a 'Sales' measure group with the following measures:

Sales Amount: The total monetary value of sales.
Sales Quantity: The total number of units sold.
Average Price: Calculated as 'Sales Amount' / 'Sales Quantity'.

These measures would likely be sourced from a 'FactSales' table in your data warehouse.

Code Snippet (Conceptual MDX for a Measure)

While measure groups are defined visually in tools, their members (measures) can be accessed and manipulated using MDX (Multidimensional Expressions). Here's a conceptual example of how a measure might be referenced:

                
SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [YourCubeName]
        WHERE
    ([Date].[Calendar Year].[CY 2023], [Product].[Category].[Electronics])
                
            

Microsoft Docs