Calculations in Multidimensional Models

This topic provides an overview of how to create and manage calculations in SQL Server Analysis Services multidimensional models. Calculations are essential for deriving new data from existing data within your cubes, enabling more complex analysis and reporting.

MDX Calculations

Multidimensional Expressions (MDX) is the query language used in SQL Server Analysis Services. MDX is used extensively for defining calculations, including:

  • Calculated Members: Define new members within existing dimensions or create entirely new measures that are derived from other measures.
  • Calculated Measures: Create measures that perform complex aggregations or calculations based on existing measures.
  • Cell Calculations: Dynamically calculate values for specific cells in a result set, often based on user-defined logic.

Key MDX Functions for Calculations

Several MDX functions are commonly used to build robust calculations:

  • SUM(): Aggregates values.
  • AVG(): Calculates the average.
  • COUNT(): Counts members.
  • IIF(): Conditional logic (If-Then-Else).
  • CASE: More complex conditional logic.
  • HEAD(), TAIL(), MEMBER(): Navigation and member identification.

Example: Calculating Year-over-Year Growth

Here's a simple example of an MDX calculated measure that calculates the year-over-year growth for sales:


CREATE MEMBER CURRENTCUBE.[Measures].[Sales YoY Growth] AS
    ([Measures].[Internet Sales Amount], PREVMEMBER([Date].[Calendar Year].CURRENTMEMBER))
    / [Measures].[Internet Sales Amount],
FORMAT_STRING = '0.00%';
Tip: Understanding the MDX syntax and the structure of your cube is crucial for writing effective calculations. Use the MDX Script Editor in SQL Server Data Tools (SSDT) for a rich development experience.

Scope and Execution Order

Calculations in Analysis Services have a defined scope and execution order. Calculated members are typically evaluated before cell calculations. Understanding this order is vital for avoiding unexpected results.

Perspectives

Perspectives are views of a cube that present a subset of the cube's data to specific users or applications. While not direct calculations, perspectives can simplify the user experience by hiding irrelevant objects and making it easier to focus on specific analytical areas. You can define which measures, dimensions, and attributes are visible within a perspective.

Creating Perspectives

Perspectives are configured within the cube designer in SQL Server Data Tools (SSDT). You select the cube objects that should be included in each perspective.

Translations

Translations allow you to provide localized names for cube objects, such as dimension members, measures, and cube names. This enables users to interact with the analysis services model in their preferred language.

Translating Objects

In the cube designer, you can associate translations with various elements. When a user connects to the cube with a specific locale setting, Analysis Services will attempt to display the translated names.

Example: Translating a measure name like "Internet Sales Amount" to "Ventes en Ligne" for French users.