Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Greg_Deckler

Column Level Security

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:

0.png

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

1.png

  • Right-click the query Table again and choose Reference

2.png

  • Name this query Sales and remove the Cost column

3.png

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

4.png

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

5.png

 

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

 

6.png

 

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

 

7.png

 

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

 

8.png

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.

5.png

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.

9.png

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.

10.png

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.

 

Comments

Let me engage in a little bit of Power Bi Office Bingo.  "Does it scale?"  How would this look like in a real world data model where you have both Sales and Finance users trying to use the same master dataset and each sensitive column has its own set of rules? (I always get a good chuckle when I hear that other Bingo term "one version of the truth").

 

And it's not lying, just reinterpreting reality.

@lbendlin - Very situational. It's tough to come up with with every possible scenario and it depends on what you mean by scale. It should scale much better than the alternative method, measure dimension, in terms of the amount of data because you are not duplicating, triplicating or quadruplicating everything. It should scale in terms of performance similarly as opposed to the alternative. In terms of rules complexity, I'm not sure it would be that much different between the two methods. Does it clutter up your data model? Most definitely and that would be even worse with different, complex rules over multiple different columns or sets of columns.

 

For the record, I'm not promoting one method or the other. I was just demonstrating how there are complexities and such introduced by not going the measure dimension route, which Chris does not like. Yes, there are downsides to measure dimensions but it's no free lunch going the other route either!

Hello Greg,

 

I tried to recreate the same example mentioned in your blog. Can you help me understand one issue? Column level security does not work, if I do not add Amount in the table. See screenshot below.

saylivaidya_0-1610384260707.png

 

@saylivaidya This is correct. The reason is that when you are viewing as the sales person role and you do not have Amount in your visualization then the only numeric metric (column) is Cost. Since sales people cannot see cost, all of the numeric columns in the visual are blank. Since table and matrix visualizations by default filter out blank rows, you are left with a blank visual. If you wish to get around this, click on the drop down arrow for Item in your Fields tab and select "Show items with no data". Then you should be good.

Greg_Deckler_0-1610404533946.png

 

Hello, 

I tried to follow your example but I obtained a visualization that shows the column I want to hide with no data, but the column header is visible (see attached image).

Can you please help me? 

Thank you very much.

Chiara

 

columnlevelsecurity.JPG

That's how it currently works.  The headers will still be there unless you use Chris' approach.  True column level security will hopefully come later in Power BI (Object Level Security has recetly been announced on the roadmap)

I'm able to get this to work with that same issue of still having the column header. I added some logic in the M query to make the header get renamed dynamically in this case, but the problem remains that I can't rename the column to "" (if so, it is renamed to "Column1") and then hide the column by reducing the width, all programmatically.

Does anyone have a solution for this to achieve the desired result of "removing" the visibility of the column?

 

Check out this tutorial for real solution on CLS in Power BI without tabular editor or unpivoting
https://youtu.be/OhJ9HPoH6VQ

@Vizzy That's a similar technique, just using Parameters to create the additional table. Field parameters are handy but they didn't exist in 2020, they weren't released until May 2022. Also, the technique shown in the video technically does not secure the data the same way that the technique here does. The technique in the video only secures the data at the visual level where the parameter is used. The underlying column is not actually secured and unviewable by end users. End users could access the underlying column's data because it is not actually secured via RLS. That means that if a user can edit a report or use the underlying dataset to create their own report then the data would not be secured. It's a bit of false security really.