KB32819: Nested aggregation with partially-related security filter causes multiple counting and wrong results in MicroStrategy Developer 9.4.x-10.x

SYMPTOM:
Nested aggregation with a filter qualification based on a parent attribute from a different branch is a legitimate reporting requirement and it is supported when the qualification is in the report filter. In this case, the filter is omitted from the final pass and the second aggregation works as expected. But if the semi-related filter is a security filter qualification, nested aggregation is no longer possible. It causes multiple counting and generates wrong results.
 
STEPS TO REPRODUCE:

• Create a metric M1 with the definition Sum (Revenue) {~+, Month+}
   
  

• Create another metric M2 with the definition Sum () {~+}
   
  

• Create a security filter Brand In list (Sony) and assign it to any user e.g. tkelly.
   
  

• Create a report R1 with Subcategory in row and metric M2 in column.
   
  

• Run the report R1 with administrator login. The following results are generated:
   
  

• Login as the user tkelly on which security filter is applied and run the report R1. The following results would be generated:
   
  

Note that the results are inflated. This is because in the final pass of SQL, calculating M2, joins down from the temp table level (Subcategory) to LU_ITEM for the sole purpose of including the security filter in this pass. This causes multiple counting. There is actually no need to apply the security filter at the higher level of aggregation, because it has already restricted the first aggregation. Obviously in this example there is no need for nested aggregation.
  
CAUSE:
This is a known issue in Strategy SQL Generation Engine 9.4.x-10.x and has been logged with the Strategy Technology department.
 
ACTION:
Strategy will continue to investigate and post updates as soon as they are available.
 
WORKAROUND:
Since the attribute-related aggregation is based on the report template, the level to which the security filter should apply in the final pass needs to adapt to the template. So, the level should be the hierarchy containing the attributes related to that particular attribute with absolute filtering and standard grouping. The hierarchy will then resolve to the template attribute from that hierarchy.
In the test case, with Subcategory on the template and a security filter based on Brand, setting absolute filtering on the Products hierarchy gives the right results. 

• Modify metric M2 to include Products hierarchy in the Level (Dimensionality) with absolute filtering and standard grouping: Sum () {~+, *}
  

• Login as the user tkelly on which security filter is applied and run the report R1 again. The correct results would be generated now: 
   
  

Absolute filtering does not remove the security filter from the final pass altogether. But, by converting the filter’s output level, there is no need to join to the Item-level lookup table, eliminating the multiple counting.