SAS OLAP: Using MDX to Dynamically Hide Measures for Compliance

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.

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.

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.

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.

Need SAS Admin Support – Hire Zencos

Join me in Dallas, Texas!

SAS Global Forum – I’m a Speaker!

3581 - Visualizing Your Big Data Whether you have a few variables to compare or billions of rows of data to explore, seeing the data in visual format can make all the difference in the insights you glean. More

3352 - Tactical Marketing with SAS® Visual Analytics Presented with Johann Pasion (89 Degrees) Marketers often face a cross-channel challenge in making sense of the behavior of web visitors who spend considerable time researching an item online, even putting the item in a wish list or checkout basket, but failing to follow up with an actual purchase online, instead opting to purchase the item in the store.
More

Ever had the need to update as table metadata by submitting SAS code? If so, you can use the SAS BI METALIB procedure to do so. This may be useful if you want to automate an ETL process, which loads tables and registers in metadata at the same time.
Update Existing Table in SAS BI Metadata
The following code connects to a SAS library already registered in metadata and …

When I think of corruption, it usually is more about government. Certainly, I don’t picture myself slipping a C-Note to the metadata server for favors, such as giving me special access or maybe ensuing my jobs have priority (wink). Stig Eide, a SAS BI administrator, reminds me that corruption does occur in the metadata – but not exactly what I was picturing. When metadata corruption occurs, …

I created a LogCheck macro [code provided] to review the log during a batch process. The SAS log files can be rather large and it is so much easier if you can just extract the issues in an easy to understand format. This macro reads in each line of the log file and keeps the ones with Error, Warning, Note, or specific text you want …

Hey brilliant SAS Institute developers – I love your new SAS Enterprise Guide 6.1 Log Summary feature! When I was reading through the What’s New in SAS 9.4 for SAS EG 6.1 there were only a few entries. But BAM! the second bullet made me run over to the demo machine to see what I was missing:

improved programmer productivity with the new Log Summary window, which lists all …