Wouldn’t it be nice if you could filter the rows a user sees in Power BI? What’s that? You’ve already implemented Row-Level Security at the database level? Well, that actually won’t help you in Power BI. Yikes. In this episode we discuss ways to filter by records in Power BI and why it might be so confusing to implement what at first glance seems like a straightforward request.
“Power BI Row-Level Security is completely different [than row-level security in SQL Server], uses the same exact name and is a source of confusion and frustration with customers.”
“When it comes to Power BI, you’ve got two types of row-level security that work and one that doesn’t. The one in the relational engine is useless, and then you’ve got one that’s resident in SSAS and you’ve got one that’s resident and managed in the Power BI service itself.”
“You have a good chance that you’ll go right up to the edge, and then you’ll add row-level security and go over that threshold of usability, so you have to be careful.”
“It’s a narrow but useful tool, ideal for large organizations.”
Listen to Learn
00:38 Intro to the team & topic
01:36 Episode 200 is approaching – would you like some podcast SWAG?
02:21 Compañero Shout-Outs
03:30 Row-level Security in Power BI is different from row-level security in SQL Server
06:47 The benefits of row-level security in Power BI
07:41 How you want to be implementing this for users
09:17 It might slow things down, so be careful
13:31 When do you just want to create two different reports?
15:42 You can’t limit the columns that are displayed
16:27 Will the separate row-level security pieces ever be integrated together?
17:36 This is more useful for large organizations
19:09 Closing Thoughts
[/et_pb_text][et_pb_text admin_label=”Podcast OptIn” _builder_version=”3.29.3″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” use_border_color=”off” border_color=”#ffffff” border_style=”solid” global_module=”2113″ saved_tabs=”all”]
Carlos: Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon with the sneezy and hopefully not too coughy, Eugene Meidinger.
Eugene: Yeah, hopefully it’s the common cold that I’ve got, here.
Carlos: Yeah, so whatever it was, maybe Kevin gave it to you.
Eugene: Oh yeah, Kevin showed me a dashboard of a recent epidemic, and hopefully Pittsburgh doesn’t pop up there.
Carlos: Yes. And Kevin Feasel’s with us today, as well.
Kevin: Hello, and technically, I did not have a cold. I had a 12-pack-a-day smoking habit. I’ve dropped the habit. I’m now back to normal.
Carlos: Now back to normal. That and a lung transfer will do wonders. Okay, so this is Episode 191 and compañeros, thanks for joining in today. We always enjoy when you’re here. And today, our topic Row-Level Security, so we’re going to talk about that, but as always, compañeros, we do have a few shout-outs. And I should say, even before we get to the shout-outs, so Episode 191. We are very, very quickly approaching Episode 200 and we have a couple of things planned. So, one of the interesting things that’s happened, which I haven’t been good about, lately, and I apologize, but at one point, we were offering t-shirts and several of you have approached, in the past as well, like, how can you support the podcast, or like, “hey, I’d love to help offset the cost of the t-shirt,” things like that. And so, for Episode 200, we are going to be putting together some things about how you can connect with some of that SWAG. So, if there are things beyond t-shirts that you’d be interested in, let us know as we’re working on putting some of that together as we approach Episode 200. Okay, and now for our shout-outs, compañeros, and we have a long list, today. So, thanks, always, for giving us a little love in social media. We’ve got Priscilla Camp, Gabriel Bonotti, Alex Kinney, Jorge Sanchez, Seba Ruido Fredes, Aislan Honorato, Ankit Raikar, David Stokes, who we know and love, hails from our own Chesapeake, Virginia. And Lynn Swayze, who actually used to help put the podcast together. So special thanks to you all. We’ve got shout-outs to Nickolas Jones and James Dandridge. James has a question that we’re going to make into an episode here, about, “hey, I am a DBA, but there’s all these other technologies.” He’s a little nervous, I think, about his future and he wants to get some of our thoughts.
Kevin: If only the combination of Eugene and I had ever talked about the subject in the past.
Kevin: We will have good answers for you, I promise.
Carlos: There you go. So, James from the UK, will be watching out for that in a future episode. Okay, so today, compañeros, the show notes for today’s episode will be at sqldatapartners.com/powerbi or at sqldatapartners.com/191.
Kevin: So, Eugene?
Kevin: This row-level security thing?
Kevin: It’s a thing in SQL Server called Row-level Security. And it kind of lets you do, like, security at the row-level.
Eugene: Makes sense.
Carlos: Interestingly enough, one of those few features that actually tells you what it does.
Eugene: Yeah, PolitiFact rates your statements true so far. Continue.
Kevin: I’m going to try harder now, then. So, alright, I’ve got opinions about row-level security in SQL Server. Those opinions are not fantastic, not great, Bob. But we’re not talking about row-level security in SQL Server, are we, Eugene?
Eugene: No, no. Power BI Row-Level Security is completely different, uses the same exact name and is a source of confusion and frustration with customers. And actually, we’ve run into that issue before. We had one customer where they’re like, “oh, well, we already have this implemented,” and we’re like, “no, you don’t.”
Kevin: Alright, so what is the difference, then? I mean, you said, “hey, if I have row-level security in SQL Server, it doesn’t mean squat in Power BI.” Why not?
Eugene: Well, so generally speaking, when you’re accessing data from SQL Server on a scheduled, recurring basis, it’s going through the on-premises Data Gateway. Which means it’s using whatever credentials you gave it and hopefully not SA. And so, that doesn’t change, that doesn’t past through. So, I could view a report and you could view a report and it’s still going to use whatever credentials we gave it for the Gateway. And so, technically speaking, on a very technical, pedantic basis, which I’m sure you appreciate, row-level security on SQL Server does apply, it’ll just apply to that same set of credentials we gave it. So, we wouldn’t get any of the benefits.
Carlos: Sure, so you kind of have like a double hop issue?
Eugene: Yeah, pretty much. Sort of like what you might be used to with Kerberos and SSRS and SharePoint or that kind of thing. So here’s the interesting thing: with SQL Server Analysis Services on-prem, we don’t have the double hop issue. I don’t know why they have it implemented and working for SSAS, but not the relational engine, but that’s where we’re at. So, if you do consume a report with live connection modes, so it’s sending the queries live, and you’re using SSAS, then you can use the row-level security built into SSAS and it’ll pass through your username. Technically speaking, it’ll pass through– I think it’ll pass through your user principle name and then try to map that to your local AD account, and you can make some search and replace kind of rules to change that if, say your Office 365 domain doesn’t match your on-prem domain. If you’re one of the poor souls that followed the best practice that the time of using .local, for example. So, when it comes to Power BI, technically speaking then, you’ve got two types of row-level security that work and one that doesn’t. So, the one in the relational engine is useless, and then you’ve got one that’s resident in SSAS and you’ve got one that’s resident and managed in the Power BI service itself. So that’s a quick summary.
Kevin: Okay, so what benefits am I going to get out of using row-level security for Power BI?
Eugene: So, generally, I see this as a data governance kind of issue. It’s really the only way that you have available on a report level to segment data or control what people see. Cause with the same customer, we would get these questions, sometimes, of like, “well, is there any way to hide this visual based on who the user is?” And right now, the answer is no, there’s no object-level control on the report. The only really two security mechanisms you have is you can limit who can access an entire workspace or you can use row-level security to control what people see, so it’s the same benefit you would generally get from implementing row-level security on SQL Server. And so, again, it’s just a security and data governance kind of thing.
Kevin: Okay, so when you’re implementing this kind of thing, your recommendation, would it be to try to use active directory groups or individual accounts? Or can you use active directory groups, as well?
Eugene: You can. It gets a little bit clunky, because there’s two levels of management when it comes to implementing it in the service, in powerbi.com, because you’re able to create roles, specifically, but then you’re creating these DAX rules underneath that. So, the ideal scenario is that your data is just segemented by say, geography or department or something like that and you can just make a handful of roles. So, for example, maybe you say that we want a role for Latin America, we want a role for AMEA, for APAC, for all that kind of stuff, and so you only need about four or five different roles. In that case, you would be adding an Office 365 group to those specific roles and you manage it that way. And then the DAX part is really simple, because you just filter in a specific region. So that’s the ideal and the simplest way to do it. You definitely don’t want to have to be adding people one by one. You can, but that gets to be tedious. The area where it gets to be more of a challenge is whenever you have to do dynamic row-level security, because you don’t really have your Office 365 administration under control. So, you don’t have a group just for sales, and so you want to add everyone, and then you want this table that tells you who’s actually in sales, and you want to have really, really complex logic for the DAX piece.
Kevin: So, you can do that?
Eugene: You can.
Kevin: That’s probably my best question, ever.
Eugene: Well, there was a rising intonation, so I could tell it was a question. Yeah, no, we’ve done that, before, and so at that point, what you’re doing is, you’re doing basically a lookup to see, “okay, what group, or what permissions does the user have?” I think with the one customer, what we did was we had a many-to-many relationship. And so, we basically just said, “okay, here’s a table, and it’s kind of a cross-product of all the different geographies or all the different verticals and all the users, but specifically which ones they’re allowed to see.” So, let’s say that you had access to all of these verticals, so they have maybe 10 marketing verticals. What would happen is that you would have 10 rows. And so, you would have one with your username for each, whereas maybe I only have permission to one vertical, so there would be one row. So, we would do this many-to-many join and the security filter would just kind of flow its way down to the fact tables. Now that worked, but we still ran into issues, just because of the complexity of the way they wanted to do it. Because, you’ve got this rule in there that says, okay, based on this name ‘if this is equal to this, then true and then filter’. Well, the problem is, with row-level security, if you’re doing it that way, you can put it in a predicate that just says ‘true’. We had a predicate where if there was a row that had a vertical ID of -1, that meant they had full access. Well, the problem is that the row-level security, even if the predicate always returns true, it’s still being applied on a row-by-row level basis and so DAX is kind of goofy, because the DAX table, it creates this hidden row that has just blanks everywhere for missing values. And so, we had the interesting situation that, let’s say you had full access and so instead of putting a row for each physical vertical, we just put in a row that had you and then -1. And then we had just one row for me that had a vertical of 6 and we’ll say that was, like, oil and gas or something like that. Well, when you would go log in, you would only be able to see that vertical, because the join would apply just on the valid rows. So, it’s kind of hard to explain over a podcast, but the key part is, even if you have a predicate that always returns true on the table, it’s still going to do an implicit filter on a row-by-row basis for everything.
Kevin: Which sounds like it’ll be slow.
Carlos: I was going to say, translation: right yeah–
Eugene: Yeah, I mean, it’s definitely slowing things down. I don’t think it’s terrible, per se, especially if you’re sticking to these dimension tables, and so there’s only a few to go through. But one of the things that you kind of run into, potentially, is that the way that the DAX engine is designed is, it’s good for basic, simple filtering, it’s good for basic math and that kind of stuff. And then when you get anything more complicated than that, it goes real slow. So, it switches from using something called the storage engine, which is fast but simplistic, to the formula engine, and so you definitely have a risk of slowing things down. In fact, we ran into a really weird bug that we resolved through just luck and happenstance, because, we added in row-level security and we were getting like a 10x slow-down that we shouldn’t have been getting. And our ultimate solution was reverting it back a week’s worth of work, redoing it, and everything seemed fine. So yeah, you have to engage in some caution, and if you’re planning on row-level security, you need to assume there’s going to be a slow-down, because what’s going to happen is, you’ll build the report based on certain constraints. And with this customer, we kept pushing those constraints. It’s like, “okay, this report’s super fast.” “Oh, well, can we increase from 3 million rows to 10 million rows?” And it’s like, “well, it’s going to go slower, but yeah.” And then it’s like, “Oh, well, we actually need it so that we can switch between local currency and USD with a single parameter.” And it’s like, “well, it’s going to go sl–” you know, and we kept pushing those edges. And so, you have a good chance that you’ll go right up to the edge, and then you’ll add row-level security and go over that threshold of usability, so you have to be careful.
Kevin: That makes sense. Carlos, I’ve cut you off a couple of times. I know you’ve got questions.
Carlos: Yeah, so I think we ultimately got there, and the thought was, in terms of setting that up, we have the users that have to be defined. I mean, that’s very similar to our row-level security. One of the challenges is that ultimately this is going to get applied at runtime, if you will. And so that, then presents some unique challenges. I guess you talked a little bit about best practices. So, row-level security sounds extremely intriguing. I don’t want to say it’s an easy button, but it is a way to say, “I’m trying to reuse the same report and allow two groups of people to see it.” Are there instances in which you’re like, “maybe it would just be better for me to create two different reports”?
Eugene: Well, I mean, so definitely one is whenever you would have a poor user experience because some of the data’s blank.
Carlos: Sure, like, “this one group is never going to have access to that, and so they just need a bunch of–”
Eugene: Right, right, and so maybe you want to have that single combined report, but you could easily run into situations where you have visuals that are just completely blank and so that becomes a training/communication issue and may not be worth the effort. I would definitely say, if you’re doing complex dynamic security, especially with hierarchies, I mean, you can do it, but if you’re trying to do something where it’s like, “okay, I joined Channel Advisor and Kevin Feasel’s my boss and we want it so he can see these certain pieces of information, but also anything that I can see.” So, if you want some sort of hierarchical kind of row-level security, that can get a bit gnarly, for sure. So, I mean, those are two main areas that come to mind. But like, if it’s something where your logic– and remember, this logic’s all implemented and implied in DAX, but if your logic’s something where you can just say, “okay, if you’re in this role, then vertical equals 4,” or whatever, then that’s not bad. I wouldn’t expect that would be bad from a performance perspective or anything like that. It’s whenever you start getting into like complex logic, so you’re adding conditionals, you’re doing dynamic stuff, you’re doing it for a lot of different tables, that’s when I would start to get a little bit nervous.
Carlos: Gotcha. Oh, I had another question and it’s gone.
Kevin: I’ll give you an easy question.
Carlos: Here we go.
Kevin: Can I limit the columns that are displayed? So maybe I don’t want to show you all of the measures, I just want to show you some of them?
Eugene: Not on a dynamic basis. So, you can certainly hide stuff from the report view, but no, there’s no way to say, “okay, I don’t want you to see these columns unless you’re the CFO or something.”
Carlos: Gotcha, so it is row and not column. We mentioned it earlier, or in the beginning, that this is not the same row-level security as SQL Server, and I feel like we’ve had this conversation enough times that it takes a while, but you have to repeat that, and I know we’re on a podcast, but potentially even show them why those two things won’t work together. Because it feels natural, like it should. Now I guess my question is now, again, you’re going to put your hand on the crystal ball, here, ala PTI, right, like see the future. Do you see them being able to integrate the row-level security in SQL Server and Power BI any time soon?
Eugene: I mean, I don’t think it’s a priority. The question is, and this is probably a better question for someone who knows like SQL internals well, but is there anything equivalent to effective username like there is in SSAS? Because they’re already doing it with SSAS, today. It basically says, “hey, I’m logging in with this admin account, but go ahead and pretend that I’m this user account.” And so, if you can do something similar with SQL, it should be possible. But I just don’t see it a priority, because in my mind, the only way that it would work is if you’re using Direct Query, which is intended for a very narrow set of use cases.
Carlos: Right, focused.
Eugene: Right, whereas, most of the time, it’s expected that you’re going to be importing the data, and so SQL row-level security wouldn’t help you. It only would matter for cases where you’re live querying the database.
Carlos: Gotcha. Other thoughts, Kevin?
Kevin: Yeah, my big takeaway question.
Kevin: Is it useful? Like, after everything we’ve said here, does it generally provide value? Does it provide value in a very limited set of cases, or would you just say, “nah, not worth the cost”?
Eugene: I think that when you’re an organization of a certain size, so if you’ve got potentially hundreds of people that are going to be using Power BI in your organization, then I think it’s definitely useful, because at that point you need some of that additional governance, and you don’t have a lot of other options. I think whenever you have like 20 people who are using Power BI in your organization, you might honestly just be better off segregating the data with different workspaces and Power Query parameters to say what data you’re pulling in. So yeah, I’d say at a certain size it’s worth it. Two other things I want to mention that we kind of touched on, but points of emphasis. With SSAS, you’re accessing on-premises AD groups. With Power BI, you’re accessing Azure AD/Office 365 groups and information. And so, some of which way you go is going to depend a lot on where your security is managed. The other thing is that all of these formulas and things are written in DAX, and so that means you have to know DAX. Which, hopefully you do, if you’re making reports, but that can be another hurdle.
Carlos: Yeah, limitation.
Kevin: Makes sense.
Carlos: Yes, it sounds like it may not be quite as approachable, but useful in certain scenarios.
Eugene: Yeah. It’s a narrow but useful tool, ideal for large organizations.
Carlos: Okay, compañeros, that’s going to do it for today’s episode. Thanks again, for tuning in. And as always, we like it when you reach out to us. We had quite a few this week, and we do appreciate the suggestions for future episodes, or connections. If you want to connect with us, Eugene?
Eugene: Yeah, you can find me on Twitter @sqlgene or sqlgene.com.
Kevin: You could find me, but that row got filtered out.
Carlos: That row got filtered out. There we go. And you’ve made it all the way to the end of this episode, compañeros, so congratulations.
Carlos: That’s right. One of the things that we are working on is we’re actually doing some reports for QuickBooks Online users, so if this matches your organization or if you know someone that’s using QuickBooks Online and would be willing to lend us 30 minutes of their time for us to ask some questions, and show some things to them, let us know. We’d be very interested in that. Or, you know folks that are in the printing industry, we’d love to connect with them as well. Thanks again, compañeros, that’s going to do it for today’s episode. You can connect with me on LinkedIn. I am at Carlos L Chacon and we’ll see you on the SQL Trail.