Remove Parent Aggregation with MDX

Author: Kepion Product Team

Introduction

SSAS cubes are great at aggregating values. However, there are times when we don't want an aggregation to occur, such as with pricing data or dealing with percentage numbers. In order to augment the behavior of SSAS, one will need to write MDX rules to NULL out these aggregations.

Example

Here is an example MDX script that shows how to NULL out unwanted aggregations in a cube. Notice that the Dimensions we don't want to aggregate by are tested with an ISLEAF function.

--
-- Remove Aggregation
--
SCOPE([Measures].[Value]);
   [Account].[Account].[No Aggregate] =
      IIF
      (
         -- Check if at Lowest Level
         ISLEAF([Entity].[Entity].CurrentMember)
         AND ISLEAF([Time].[Fiscal Calendar].CurrentMember)        
         -- true
         ,[Entity].[Entity].CurrentMember
         -- false
         ,NULL
      );
END SCOPE;

Applying these scripts can create more understandable reports. However, please be cautious when using this type of calculation as fighting against the nature of SSAS cube behavior (i.e., causing the SSAS engine to not aggregate) can have a negative performance impact on queries.