OLAP cubes are great for summarizing data very fast – I love them .   In certain environments, HIPAA compliance for personally identifiable data might be a concern.  Even at the lowest grain of a cube it might be possible to identify the specific detail data beneath the surface if the summarized number is small enough.  For example, if a report shows the number of people that work for a company in a county of a state that are over the age of 50 – it may be beneficial to hide the statistic to protect the identity of that sample size.

Using MDX logic, we can dynamically hide measures that meet a certain criteria.  In the MDX code below, I have added an additional measure to the OLAP cube I used for SAS Global forum in 2011.   It will aggregate the Total Sales measure like normal but if the Total Sales measure is less than 50,000 it will show nothing.

DEFINE Member “[SGF2011].[Measures].[Restricted Total Sales]” AS
‘iif([Measures].[Total Sales]<50000,NULL,[Measures].[Total Sales]),FORMAT_STRING = “DOLLAR20.0″‘;

The Enterprise Guide screenshot to the right shows the output!

Secure the Original Measure with Metadata Permissions

One concept I always promote with writing MDX code is that you can build one measure on another and another to accomplish the most complex tasks.  In this example, the original Total Sales measure will still be available to users accessing the OLAP Cube directly in a client tool such as Enterprise Guide.  You can easily prevent users from accessing this data by restricting the ReadMetadata privelage through SAS OLAP Cube Studio.  This is shown below.

If the cube gets rebuilt over time (deleted and recreated), you will want to store the security permissions for the OLAP cube in a permission table and apply after each rebuild.  I will discuss this in future blog posts but you can also check out documentation at SAS Support.  The preferred method for production environments is to update the OLAP cube in-place.  This is much better because it maintains the security permissions for the cube and allows access to the cube while the update is taking place.

Tags: MDX Code