SSAS Cube - Obscure low numbers

Question

I have a business requirement to Obscure low numbers for Confidentiality.

I have been trying to do this using a hidden measure and a calculation. The hidden measure is named 'Encounters_Count' and the MDX I'm using for the calculation is:

CREATE MEMBER CURRENTCUBE.[Measures].[Number of Encounters]
    AS IIF( [Measures].[Encounters_Count] <= 5, "<hidden>", [Measures].[Encounters_Count] ),
FORMAT_STRING = "#,##0", NON_EMPTY_BEHAVIOR = { [Encounters_Count] }, VISIBLE = 1;

This works except that in some cases it's possible to work out the hidden numbers from the group totals.

I've tried to write a recursive Calculation using IsLeaf() to exclude "<hidden>" results from the total by applying different rules depending on if it's the parent or child but I can't work out how to do this correctly.

This is what I've been trying to do:

...
IIF( IsLeaf( [Measures].[Encounters_Count] ),
    IIF( [Measures].[Encounters_Count] <= 5, "<hidden>", [Measures].[Encounters_Count] ),
    SUM( IIF( [Measures].[Number of Encounters] <> "<hidden>", [Measures].[Number of Encounters], NULL ) )
),
...

 

Edit: Here is a screenshot of what I'm trying to avoid, in this case you can work out that the <hidden> number is 4. It would be preferable for the Grand Total to show 31,163 as that is the total of the Visible numbers.

Answer 1

Once you start hiding numbers and fiddling with totals, the report becomes less accurate...so... if your business users are saying they are happy with inaccuracy, why not think of other ways to achieve your goal (before thinking of MDX)?

You could add 5% to all totals / replace the right-most digit with "8" / round up to the nearest 100...

Do this to all totals, and you MDX doesn't have to worry about IsLeaf() or whether some of the totals have hidden values within them and some don't.

WITH MEMBER [Measures].[Obscured Values] AS
IIF([Measures].[Encounters_Count] <= 5, "<hidden>", 
  IIF([Measures].[Encounters_Count] > 100, VBA!round([Measures].[Encounters_Count]*1.1, 0), [Measures].[Encounters_Count])
)

Answer 2

As I understand, what you need is that if either the number is low, or if it is close to the total, you have to hide it.

CREATE MEMBER CURRENTCUBE.[Measures].[Number of Encounters] AS
    IIF( ([Measures].[Encounters_Count] <> 0 AND [Measures].[Encounters_Count] <= 5)
      OR (    ([Measures].[Encounters_Count], [Dim1].[Hier1].[All]) - [Measures].[Encounters_Count] > 0
          AND ([Measures].[Encounters_Count], [Dim1].[Hier1].[All]) - [Measures].[Encounters_Count] <= 5)
      OR (    ([Measures].[Encounters_Count], [Dim1].[Hier2].[All]) - [Measures].[Encounters_Count] > 0
          AND ([Measures].[Encounters_Count], [Dim1].[Hier2].[All]) - [Measures].[Encounters_Count] <= 5)
      OR (    ([Measures].[Encounters_Count], [Dim2].[Hier1].[All]) - [Measures].[Encounters_Count] > 0
          AND ([Measures].[Encounters_Count], [Dim2].[Hier1].[All]) - [Measures].[Encounters_Count] <= 5)
      OR // add all 'All' members of all hierarchies here
       , "<hidden>", [Measures].[Encounters_Count] ),
    FORMAT_STRING = "#,##0", NON_EMPTY_BEHAVIOR = { [Encounters_Count] }, VISIBLE = 1;

would hide these. It is a rather long statement, and needs careful maintenance as soon as you change any hierarchies in the cube.

For a perfect solution, also combinations of more than one All member would have to be treated, but this can lead to a combinatorial explosion, and I am not sure if you can get these combinations with standard client tools that do not allow you to write your own MDX. But - to be honest - I did not think much about that.

Potentially, you would want to use Abs() around the values to avoid issues with negative numbers if they could appear.

Answer 3

I wonder if it acceptable to make the original measure invisible and creating another measure to show the values and secure/suppress it when needed. This way cube can aggregate/calculate things as usual.