Masking Measure Values in Analysis Services and Power BI

Object-level security is one of the significant new features introduced for SQL Server Analysis Services 2017 and Azure Analysis Services. With object-level security, you have row-level security and add the ability to lock down tables and columns so that users are unaware of their existence. Table-level security, column-level security, and row-level security can be used independently or in combination with each other.

I had a discussion this week about object-level security and how it could be useful when an organization eventually upgrades to SSAS 2017. In the meantime, what can developers do if they are using Power BI or earlier versions of SSAS? In short, there is no workaround that will get full object-level security. You can hide tables and columns from all users, but you cannot selectively hide objects from a group of users. Users can also still access data from hidden objects if they know what to do.

The approach outlined here is not truly security — much like we say Perspectives are not security. Instead, it masks values from a group of users who hopefully stay ignorant of what they otherwise could see. It is a worse situation than using Perspectives, but in the case of Power BI Desktop, it’s better than nothing. As long as someone does not have access to a PBIX file, it might be a viable solution (again not truly secure though) once a report is deployed to Service. Let’s hope object-level security comes to Power BI soon…

Please take the time to vote for this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15578172-column-level-security-for-reports

For now, imagine a scenario where an organization wants to deploy a single Power BI report that contains volume, net sales, and margin. It is very adamant about not having multiple copies of the same report. The organization wants to restrict certain users from seeing margin, but everyone can see sales and volume. Without OLS, you have to either create separate reports for the different audiences or fudge it using DAX to mask the margin values from users.

Get ready for fudge.

The end result will show BLANK() for restricted values. In the case of my sample user, they cannot see the restricted margin, but they would have access to see a restricted net sales and restricted volume measure. What each individual user could or could not see would depend on how you set them up in a security table. Original margin, net sales, and volume appear next to their DAX-enhanced cousins for comparison.

Create a restriction table that contains a user name or user principal name as well as the name of the object to restrict. In the case of row-level security tables, you would likely include rows a user *can* see. In the case of my table, I chose to do the opposite and only list out items a user *cannot* see. It lists restrictions only. This assumes that most people would be able to see all measures unless they have an explicit restriction in the restrictions table. In the screenshot below, I would not be able to see the Margin but could see Net Sales and Volume because my username only has a record for Margin. “Someone else” is setup so that they would not be able to see any of the three since they have a record for each.

Next, create an explicit measure following the pattern in the snippet below. The key is that the variable ColName match the Restrict value in the restrictions table. The ColAggregation variable defines the primary measure. The main CheckedMeasure variable uses an IF to check whether the ColName is listed in the restriction table for the logged in user. If true, there is a restriction and the measure is blank. If false, it provides the ColAggregation value. To reuse this for different measures, you only need to change the top two lines that define the ColName and ColAggregation variables.

RestrictedMargin = VAR ColName = "'Financials'[Margin]"
VAR ColAggregation = SUM('Financials'[Margin])
VAR CheckedMeasure = IF(ColName IN CALCULATETABLE(
 VALUES('SecurityRestrictedObjects'[Restrict]),
 SecurityRestrictedObjects[UserId] = USERPRINCIPALNAME()
 ),
 BLANK(),
 ColAggregation
)
RETURN CheckedMeasure

In the case of this example, I would repeat the process and add new measures for Net Sales and Volume.

Finally, hide the original Margin, NetSales, and Volume columns so that they are not available in the report view (Power BI). Again, this is not ideal and not secure, but it’s an approach that may work in a pinch as long as users are only accessing the deployed report and do not have access to the original PBIX file.

In the next post, explore how you might be able to leverage Power BI’s “Unpivot” functionality to approach this problem as well: Unpivoting Sensitive Columns in Power BI to Apply Row-Level Security