Microsoft Developer Network

Creating Calculations

This document details how to create and manage calculations within multidimensional models in SQL Server Analysis Services (SSAS). Calculations are essential for deriving new insights from your data, enabling complex business logic and advanced analytical scenarios.

Understanding Calculations

Calculations in SSAS are typically written using Multidimensional Expressions (MDX). MDX is a query language that allows you to manipulate and aggregate data from your cube. Calculations can be broadly categorized into:

• Measures: Aggregated values that represent business metrics (e.g., Sales Amount, Profit). Calculated measures allow for dynamic calculation based on existing measures and dimensions.
• Calculated Members: Members defined within a dimension that derive their values from other members or measures. This is useful for creating hierarchies of calculated values.
• Named Sets: Predefined sets of tuples that can be reused in MDX queries.

Creating Calculated Measures

Calculated measures are created within the Measures dimension of your cube. They are often used to perform calculations like:

• Percentage of Total: e.g., [Measures].[Sales Amount] / CALCULATE(SUM([Measures].[Sales Amount]))
• Year-over-Year Growth: e.g., ([Measures].[Sales Amount], [Date].[Calendar Year].CurrentMember.PrevMember) - [Measures].[Sales Amount]
• Running Totals: e.g., SUM( [Measures].[Sales Amount].CurrentMember.Hierarchize, Tail(Existing(), 1) )

Steps to Create a Calculated Measure:

1. In SQL Server Data Tools (SSDT), open your Analysis Services project.
2. In Solution Explorer, right-click on the Measures folder within your cube and select New Measure.
3. In the Measure Editor:
   • Provide a descriptive Name for your measure.
   • Select the Source (e.g., a measure or attribute from your data source).
   • Enter the MDX formula in the Expression box.
   • Configure formatting options such as Format String, Visible, and Aggregation Function.
4. Click OK.

Creating Calculated Members

Calculated members are defined within a specific dimension, allowing you to group or derive data in new ways. For example, you might create a calculated member for "North America" by combining "USA" and "Canada" within a Geography dimension.

Steps to Create a Calculated Member:

1. In SSDT, navigate to the dimension where you want to create the calculated member.
2. Right-click on the dimension and select New Calculated Member.
3. In the Calculated Member Editor:
   • Specify the Name.
   • Select the Parent Hierarchy.
   • Write the MDX Expression to define the member's value.
   • Set other properties like Format String and Visible.
4. Click OK.

MDX Basics for Calculations

Familiarity with basic MDX concepts is crucial for effective calculation creation:

• Sets: Collections of tuples.
• Tuples: Ordered lists of members from different hierarchies.
• Functions: Built-in functions like SUM, AVG, COUNT, HEAD, TAIL, FILTER, CALCULATE, etc.
• Scope: Understanding the current context of calculation execution.

Best Practices

• Naming Conventions: Use clear and consistent naming for measures and calculated members.
• Performance: Optimize MDX queries. Avoid overly complex calculations that can degrade query performance. Use subcubes and scopes wisely.
• Testing: Thoroughly test all calculations to ensure accuracy.
• Documentation: Document complex calculations for future reference.

By mastering the creation of calculations, you can unlock the full analytical potential of your multidimensional models.