Remove Parent Aggregation
By Kepion Solution - Updated March 9, 2016
SSAS cubes are great at aggregating values. However, there are many times when we really wouldn’t want an aggregation to occur, such as with pricing data, or when dealing with percentage numbers. In order to augment the behavior of SSAS, one will need to write MDX rules to NULL out these aggregations.
Here is an example MDX script that shows how to NULL out unwanted aggregations in a cube. Notice that the dimensions that we do not want to aggregate by is tested with an ISLEAF function.
-- Remove Aggregation
[Account].[Account].[No Aggregate] =
-- Check if at Lowest Level
AND ISLEAF([Time].[Fiscal Calendar].CurrentMember)
Applying these scripts can lead to 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) will cause negative performance impact on queries.
Build Enterprise BI & CPM solutions within minutes with Kepion Modeler.