KB484318: Get the correct value for metrics defined with scope scripts using a new MDX pattern in MicroStrategy 2020 Update 2

Use the following table of contents to navigate this article: 
Background
In Scope
Prerequisites 
Limitation-Functional 
     Without Parallel Hierarchy
     With Parallel Hierarchy
Limitation-Performance 
    Without Parallel Hierarchy
    With Parallel Hierarchy
 

Background

Strategy reported this problem about the incorrect metric data with MDX Scope functions returned with MSTR Security Filter applied. In the cube modeling, a SCOPE was implemented in the cube on the area dimension at the federal state level, which converts some implausible values to newly calculated values. In addition, a security filter is also set for this user, which selects a subtree for the area dimension. The security filter and the SCOPE overlap in the DIM. The current where clause MDX statement couldn't get the data as expected. Generally speaking, with security filter applied at RA not on the Template, we want to show the metric value as the aggregation of elements.
 

In Scope

• We would use a new MDX pattern to calculate the metric value as the aggregation of elements at highest level.
  
• Aggregates the top-level elements in where clause without parallel hierarchy to the hierarchy used in Security Filter.
• Sub Query to use a standalone complete MDX statement to replace where clause for parallel hierarchy in Security Filter.
• This new pattern would be only applied for Recursive Attribute against SSAS data source.
• The new pattern applies to where clause when the Recursive Attribute in Security Filter is not on the Visualization Template.
• The filter must include Security Filter.
• If the Security Filter also meets the conditions of, Security Filter optimization in Hierarchical Reporting, the Security Filter would be ignored.
• Below type of case: the expected value is that the measure won’t have scope.
  
• Source Side: In attribute relationships, "Product" is the child of "Style"
• MDX Script: Define a scope which includes the parent like below: 
  SCOPE ([Product].[Style].members); [Measures].[M1] = [Measures].[M2 – M3]; END SCOPE;
  
• MSTR Case: Put "Product" and "M1". "M1" doesn't equal to [M2 – M3] which means this case is not in the scope

Prerequisites

• Set Registry key: “F22773_Customer_Specific” =dword:00000001 at HKEY_LOCAL_MACHINE\SOFTWARE\MicroStrategy\Financial Reporting\
• Set a new Registry key for this new MDX pattern: AggregateTop of string value type under the same path. User needs to enter the Project ID. If user wants to add multiple projects, comma is used to separate the different project IDs.
  

Limitation-Functional

Without Parallel Hierarchy

Case 1: Filtering on elements with text measure values won’t get the expected value. The Amount of Statistical Account is text value in the source side.

Case 2: use security filters to filtering on branches or elements from different levels. If we put the attribute on the grid, we’ll see collapsed elements comes from different levels. Example: Security filter on the branches of ‘CY 2001’, branches of ‘H1 CY 2002’, and branches of ‘H2 CY 2002’.

Case 3: The measure data is incorrect when aggregate top on two elements which has separate scope calculations or one element in scope and another element is out of scope. France is in Scope 1 while USA is in Scope 2.

With Parallel Hierarchy

Case 1: Subquery didn’t return expected result for cases where the parallel hierarchy is used in SCOPE. Security filter on the Product Categories hierarchy. Put one parallel hierarchy Size on the grid. The metric value is not expected. Excel has a similar result.
Case 2: Customer selects multiple branches/elements that falls into the SCOPE definition in the security filter and then put parallel hierarchies on the grid? Example: Security filter on the elements ‘Fenders’ and ‘Mountain Bikes’. Put the parallel hierarchy ‘Size’ on the grid.
Case 3: Filter on different levels with different kinds of SCOPE in source scripts Security filter on the Customer Hierarchy hierarchy. Put one parallel hierarchy CUST STATE NAME on the grid. The metric value is not expected. Excel has a similar result.

Case 4: Measures defined in cube scripts with solve_order=65535 couldn’t get the expected result Excel has a similar result. There’s one workaround to bypass this limitation, which is to modify the Solve_Order to any other smaller value.
Case 5: Filter on single or multiple elements in Hierarchy used in Security Filter with other parallel hierarchy put on Grid Example: User selects Taxes and Unit in Accounts and Accounts has been used in SF. The metric value of Amount is not expected when Accounts is applied in Where clause.

Case 6: element order is incorrect for some case when parallel hierarchy on security filter With Product defined in Security Filter, its parallel hierarchy ProductSub is put on Grid with other hierarchies and metric. No filter is applied on the hierarchy and metric. The element order is TimeQrt is incorrect. There’s one workaround to bypass this issue, which is to add a filter on any Hierarchy on Grid such as select all the branches. The element order would be correct again.

Case 7: Error "Expression cannot be resolved in the context of an arbitrary shape." happens to specific hierarchy. The hierarchies used on grid and where are parallel hierarchies with following type of shared levels:

The measure 'Revenue_HY' is a calculation created in the cube script. This issue is caused by SSAS since even the same set with different kinds of expression will lead to different result. In addition, this error couldn’t be reproduced with other Cubes.

Limitation-Performance

There would be performance overhead for some filtering scenarios in I-server Processing Time (s). (This is just internal test results, which different from customer's actual cube.)

Without Parallel Hierarchy

1. Performance of pure branch selection, level selection, level in branch is on par or even better than baseline.
2. Performance with mixed selections may vary significantly due to the selections of different elements in Warehouse, such as the performance difference between case 5 and case 6, case 7 and case 8.

Example Performance Data

With Parallel Hierarchy

1. There is general performance degradation introduced by Sub-query.
2. Performance with mixed selections may vary significantly due to the selections of different elements in Warehouse, such as the performance difference between case 10 and case 11, case 12 and case 13.

Example Performance Data