I am currently working on project building information system for one of the provincial health registries. Patient data protection and privacy is one of the main requirements that touched all parts of the information system including standard reports and SSAS cubes.
One of the requirements I’ve encountered while working with SSAS cubes was that measure values that contain values less than 6 must be suppressed and displayed to the user as “<6” in order to minimize potential patient re-identification.
My first intuition was to implement this using a calculated measure that would inspect the value of the cube measure and overwrite it with “<6”using the following statement:
CREATE MEMBER CURRENTCUBE.[Measures].[# of Something – All Supressed]
AS
AS
iif([Measures].[# of Something – All] > 0 AND [Measures].[# of Something – All] < 6, “<6”, [Measures].[# of Something – All]),
FORMAT_STRING = “0”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Something’;
FORMAT_STRING = “0”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Something’;
The following picture shows the result using the actual measure from the cube and calculated measure described above side by side. You can see that this approach works very well and values in small cells do get supressed and displayed to the end user as “<6”.
Now, one can argue that this approach is too simple and it doesn’t protect suppressed values from possible re-identification if result contains only one suppressed value. Here is the scenario that describes it:
Column “Actual value” contains the original non-modified values.
Column “Suppressed value” contains suppressed values using calculated member method described in MDX above.
Column “Suppressed value with modified total” shows desired behaviour of total value when result contains only one suppressed value.
|
Dimension Members
|
Actual Value
|
Suppressed value
|
Suppressed value with modified total
|
|
Member 1
|
10
|
10
|
10
|
|
Member 2
|
5
|
<6
|
<6
|
|
Member 3
|
10
|
10
|
10
|
|
Grand Total*
|
25
|
25
|
20
|
At this point I was unable to find the proper solution that can be implemented within SSAS as calculated measure or perhaps SCOPE statement that would effectively overwrite the value of totals.
Solutions for a limited number of scenarios can be found in the blog maintained by Vinuthan (http://vnu10.blogspot.ca/2011/01/mdx-grand-total-sub-total.html), however he doesn’t provide a generic solution that would work for potential ways of querying the data (filters, intersection of any attributes, etc).
Please let me know if you can help in resolving this puzzle. Your help will be greatly appreciated!