Column Level Security

[Greg_Deckler]

Introduction

Recently, Chris Webb wrote an excellent blog titled, The Pros and Cons of Modelling Measures as a Dimension in Power BI. It's a great article and well worth the read, I recommend it. In a nutshell, it opines that unpivoting value columns is generally a bad idea. So, in the example Chris uses, there are Sales, Tax and Volumn columns and those columns are unpivoted such that you end up with 3 lines, one for each measure essentially. Chris further explains that the reason you might want to do this is:

1. You can now use a slicer in a report to select the measures that appear in a visual
2. You can now easily add new measures without having to add new columns in your fact table
3. You can use row-level security to control which measures a user has access to

Chris then goes on to explain the disadvantages of doing this and that one should stick to wide, flat fact tables and star schemas. Again, it is a great article. If there was one criticism to be had, the article does not really get into how you address the advantages of implementing a measure dimension within a more traditional data structure. So, I figured I'd give it a go and demonstrate how to get around the 3rd advantage within a more traditional fact table by implementing row level security (RLS) in order to provide column level security (CLS), essentially controlling what columns a user has access to. Here we go.

How to Load the Data

First, let's start with some tips around how to load the data. In our example, we are going to have some incredibly simple data that can be quickly created using an Enter Data query. That data looks like this:

So, think of this as sales transactions with a revenue and a cost. In our scenario we want to have a group of sales people that are allowed to see Revenue but are not allowed to see Cost. In order to accomplish this, we are going to split our data into two tables. We are going to load the columns that everyone can see in one table and load the columns that only certain people should see in another.

To split the data into two tables, we could create two queries off of the same base fact table and just remove the unwanted columns in each.  However, if this is a very large fact table, Power BI would have to enumerate that base fact table twice and we do not want to process all of the data two times, potentially doubling our data load times. To get around that, we can use References by following the steps below:

• Start by creating the data in an Enter Data query
• Right-click the query Table and uncheck Enable Load

• Right-click the query Table again and choose Reference

• Name this query Sales and remove the Cost column

• Right-click the query Table and again choose Reference
• Name this third query Sales_Costs and remove the Item and Amount columns

•  We can now Close & Apply

By using a single, base query that is not loaded into the data model but is used as a reference for our other two queries, we accomplish two important things:

1. We keep unnecessary clutter out of our data model
2. Power BI only processes the data coming from the source a single time and caches it locally for use in the reference queries

How to Fail Miserably

Now that we have our data loaded, we should verify if Power BI automatically created a relationship between our two tables. If not, create the relationship on the Sales_Key columns in both tables. Power BI will correctly detect that this is a One-to-One relationship.

Next, create a simple table visualization that includes Item, Amount and Cost with default aggregations (Sum).

Now, create a role called Sales Person with the following rule by going to Modeling in the ribbon and then Manage roles:

Finally, use the Modeling tab in the ribbon and choose View as and our new Sales Person role and presto...

Failure.

We certainly stopped the sales people from seeing the Cost column but unfortunately this also prevents them from seeing any of the other columns they are supposed to see.

How to Fix Your Problems by Lying

So what is going on? Why did the RLS rule that we put in place to prevent sales people from seeing the Cost column also impact every other column in the report and, more importantly, how do we fix it? The issue lies in the relationship between Sales and Sales_Costs.

Notice that the relationship is One-to-One and that the cross filter direction is Both. Because the cross-filter direction is Both, the RLS rules that filter out the rows in the Sales_Costs table also flow back and filter all of the rows in the Sales table. Bad. So, it stands to reason that we simply need to set the cross-filter direction to be Single flowing from Sales to Sales_Costs and not the other direction and everything should work. Easy. Oh, except you can't set the cross-filter direction to Single on a One-to-One relationship. Hmm, what to do?

Well, I've always found that when one is in a tight spot, it's always advantageous, life affirming and incurs zero bad karma to, well, lie. That's a joke. But, in this case, lying is actually exactly what we want to do. We simply need to tell our Power BI model a little white lie and all is good with our world. That lie is to tell Power BI that the relationship between our two tables is really Many-to-One.

Once we do that, we can set the cross filter direction to Single and we are home free. Sure, it's a lie but I can mostly guarantee that you will suffer no ill effects from karma. Mostly.

In any case, we can now view our report as our Sales Person role and everything works, we can see what we should see and we cannot see Cost.

Conclusion

To fix problems and get ahead in life, lie. Lying is advantageous, life affirming and incurs zero bad karma. And lying most definitely does not have an impact on your outcome in the afterlife. Probably...

I don't know why, but I'm always thinking that I take the wrong lesson away from my blog articles... Oh well.

ColumnSecurity.pbix