BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools

SAS OLAP: Using MDX to Dynamically Hide Measures for Compliance

Submitted by on 2012-03-21 – 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.
Spread the love


  • 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.