Remove Parent Aggregation with MDX

Author: Kepion Product Team

Introduction

SSAS cubes are great for aggregating values. However, sometimes we need to use SSAS cubes but don't want to aggregate values (e.g., pricing data or percentages). In order to NULL out aggregations, we need to write MDX rules. 

Example

Here is an example MDX script to NULL out unwanted aggregations in a cube. Notice that the Dimensions we don't want to aggregate 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;

Attention: Please be cautious when using this type of calculation as fighting against the nature of SSAS cube behavior (i.e., causing the SSAS engine not to aggregate) can negatively impact query performance.