Home » BI Tools

SAS OLAP: Using MDX to Dynamically Hide Measures for Compliance

Submitted by on March 21, 2012 – 11:54 am 3 Comments

Hidden Measures in OLAP cube

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.

MDX Logic Helps

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 privilege through SAS OLAP Cube Studio.  This is shown below.

OLAP Cube Permissions

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.

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

The following two tabs change content below.
With about 7 years of experience working with SAS solutions, Steve owns and manages his own SAS-partnered consulting company, Overton Technologies (http://www.overtontechnologies.com), based in Raleigh, North Carolina. He develops SAS Business Intelligence and Data Warehousing solutions specializing in data integration, data architecture design, ETL processing, OLAP technology, systems architecture, dashboards, reporting and analytics. Additional publications and sample code can be found at http://www.stephenoverton.net. Enjoy the knowledge!

Tags: ,

3 Comments »

  • bloger7791 says:

    Great job !! Very usefull !

  • Steve Overton says:

    Great point Angela! I have updated the post accordingly. I definitely prefer incremental updates going forward in PROD environments. This was only made available in SAS 9.2 and above, correct?

  • Angela Hall says:

    Awesome post Steve! One additional note for the readers is that if you refresh rather than completely delete & recreate the cube the metadata permissions do not get wiped out. This is the preferred approach for cube refresh in PROD environments.