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!
