Business intelligence depends on the ability to provide the information necessary to make informed decisions. But not all information is meant for all eyes. The ability to control or restrict access to sensitive information in data models and reports is hugely important.
It allows you to answer questions such as how do I secure access to published reports? How do I restrict visibility of data so that my CEO can see the whole picture, but a department head or regional sales managers will only see results pertaining to their department or regions? How do I ensure that published data conforms with legal or regulatory requirements?
Row-level security is one technique Power BI modelers and report writers can use to resolve these questions. By correctly structuring datasets and using Active Directory (AD) security groups, row-level security can enforce data protection at the BI tool level.
In this on-demand webinar, we discuss and demo how to implement row-level security and restrict access for given users. You will learn
- How to set up static role-level security with groups
-
How to set up dynamic role-level security using DAX
Data is a valuable and sensitive resource, learn how to protect it.
Presenter
Stephen Wullschleger
Vice President
Senturus, Inc.
Stephen’s background in BI, operations and finance spans 30 years. During that time, he has led numerous business analytics initiatives using Microsoft data tools and has watched the platform evolve from his initial use of version 1 of SQL Server and Microsoft Access. He brings a deep familiarity with the various capabilities and components of the Microsoft BI platform and understanding of how to best leverage them.
Read moreMachine transcript
0:07
Greetings and welcome to this latest installation of the Senturus Knowledge Series today. We’re pleased to be presenting the topic of how to set up row-level security in Power BI featuring a demo and discussion and of static and dynamic security implementation in Power BI before we get into the core content a few housekeeping items.
0:30
Please feel free to use the GoToWebinar control panel to make this session interactive while we do mute all the Phones out of consideration of our presenters. We do encourage you to enter questions in the question section of the control panel.
0:46
Similarly, you can minimize or restore the control panel to gain access to it using that orange box and while we do try to respond to all of our questions while the webinars and progress if we are unable to for whatever reason we will post it via a response document on senturus.com, which leads naturally me to the next question that we get frequently is can I get a copy of today’s deck and the answer is absolutely it will be available at the URL. You see there at senturus.com where if you go to the resources tab and select the resources Library, you will find the presentation as well as links to our fabulous other resources hundreds of other webinars tips and tricks and valuable free content.
1:33
So make sure you bookmark that likewise the link is also in the chat window if you want to go get it right now.
1:40
Our agenda today after some quick introductions will get into the meat of the presentation featuring row-level security in the context of an overall Power BI deployment will feature some demos showing off static and dynamic row-level security as well as cover some additional security considerations after which stick around we’ll do a brief Senturus overview for those of you who may not be familiar with what we offer give you some great additional free resources and then we’ll wrap up at the end with the Q&A. I’m pleased to be joined today by my colleague Stephen Wullschleger vice president here at Senturus.
2:20
He has an extensive background in BI operations and finance that spans 30 years during which time he’s led numerous business analytics issue initiatives using Microsoft data tools and it’s watch the platform evolved from his initial use of version 1 of SQL server and Microsoft access various capabilities and components of the Microsoft BI platform and thinking how to best leverage them is also responsible for having curated and actually delivering the preponderance of our Power BI training content, which is excellent. My name is Mike Weinhauer. I’m a director here at Senturus serve a number of different roles. But one of them is the MC of these of the knowledge series webinars. I’m pleased to be here with you today with that. I’ll hand the floor over to Mr. Weinhauer for the main part of the presentation.
3:24
Yes, thank you.
3:25
Mike and welcome everyone to today’s topic on Power BI row-level security as Mike said, today I’ll give first sort of the context of how row-level security fits within kind of likes overall Power BI security framework and then do some demos of how to setup row level security both in a sort of static level or do a sort of night more Dynamic version and then we’ll kind of Step back and think about some of the sort of how think about security or sort of broader sort of security planning. So with that first point to make is if you think about Power BI security recognize that Power BI is an online SAS offering built on Azure and it’s got basically two clusters like a web front end and a back end.
4:16
So whenever you go to powerbi.com to login, you actually are connecting to their instance of Power BI, Yeah, and if you choose the premium service, then you can actually purchase dedicated provisioned partitions instances of as your so you can have better control over your performance and those sorts of things and you know, that’s not the security around that is not really a subject of this but certainly something to think about like, how are you securing the communication pipe those sorts of things, you know, when is data encrypted when isn’t all important?
4:54
The other important point to make about the infrastructure is it uses a user active directory for account authentication so soon as using your Office 365 account and so all the things related to this user account security groups distribution list supply and can be used when you’re setting up row-level security and there’s a good white paper.
5:24
Or Microsoft published here.
5:25
There’s a link at the bottom will have it other resources the kind of goes through some of these sort of basic security elements of Power BI, second thing to think about when you think that security is how the data is stored and moved. So what we’re talking about here is a Power BI data set that you’re publishing into a workspace on the service. And there’s this concept you can actually either import data into your data set or you can use direct query which is basically a connection back to your original.
6:03
Original source and whenever you’re presenting a visualization, it’s going to actually query the original SQL database or whatever the source is to present the information. So the data in that case isn’t residing locally within the Power BI data set. It’s actually in the source and this kind of like two ways again thinking about security more broadly. You’ve got data. That’s at rest meaning where it’s being stored or it’s in process like in the middle of doing a query or something like that.
6:33
Again, there’s sorts of aspects of think about alright encryption and that sort of thing but specific to row-level security were focusing really on the data at rest and whether it’s important direct query the information about the table structure is always stored in that PBX file.
6:52
It’s just that the actual data, which may not may or may not be and as I mentioned, we use the Power BI uses active directory the service uses active directory. So when you’re connecting to data sources when you’re setting up the security around that it uses whatever sort of the connection or login is to that, you know original source system. We are connecting to the data once you actually then say share it and push the Power BI and do this row-level security.
7:27
You have to break it out between there’s some data sources that don’t support roles carrying there some that are capable. So like the data sets everything you import that supports it and for the most part direct query supports it and then if you use the on premise gateway, then that can also sometimes enforce row-level security on sources like SQL server and stuff like that. So we’ll focus today on some of the role-level security.
7:57
I’ve been mentioning row-level security but is worth stepping back and saying well what row-level security is? What does that mean?
8:09
And it’s a security mechanism that filters the records from a table based upon the what authorization the user has what permission is the user has so the best example is if I’ve got like a simple table here with five records, and I’ve got a username got territories associate it so Mike here has you know USA and Mexico and John has just USA actually has Brazil as well and K as Canada and that’s what all the records are. But if I log in as Mike, it’s automatically going to filter the records on that table to only show the records that Mike has permissions to see or the Mike’s associated with or if I log in as John.
8:57
Then it’s going to filter the records so that I can only see those records of data or similarly K can only see her Canada record and that’s being done at the in this was basically introduced in Microsoft SQL server in 2015 and kind of gives that sort of fine-grained sort of Access Control that you might want to give to specific users and it’s transparent in the sense that it’s built into the That the application layer is not done at the application layer. It’s kind of built at the done at the database level. So if you’ve got other applications built on top you can it’ll all can enforce the same row level security and it gives you a chance to do some centralizing of the logic around which you want to set up your security table or your security permissions.
9:50
So within that there’s this notion of roles in Power BI and again this all goes back for all you’ve been doing active directory for years of the notion of role-based security instead of roll security groups and you know, the best practice is to not do it in like an individual email address like in the example I was showing you but it set up a group and then that group has plays a role in your organization, so it could be that this group has permissions on particular region, or this group has a permissions on a particular function whether its sales or operations or something like that. So yeah, it is people play roles and then you can set up your security. So they’ll filter based on those roles and Power BI has this special filter built in which we will see called the roll filter where you can associate people with those roles.
10:45
So, you know an example is project management and then you might have people play more than one role. That’s okay. You can associate with then with more than one if you want to give like a manager permission over things overall.
11:01
So we talked about this term static roll filters and I think the best way really is to dive into an example. So, let’s see here. If I go to this, okay, so first just to level set our demonstration we set this up using the adventure works data set that’s common.
11:27
You know, it’s Microsoft publicity has been used a lot for demos and stuff like that, but it’s simple and clear in well set up to support these sort of examples and probably a lot of people might be familiar with it already and my data set I set up here is just two tables if it look at my model view, if I look at my data view, you see I’ve got an internet sales table.
11:50
So basically got sales order records and quantity, the order amount of the cost and the sales order amount and some dates and then I’ve also got a sales territory and focus on the sales territory group got North America Europe or Pacific and then if you look at my data model, we set up a relationship between those two tables. And so and it’s basically doing a join on sales territory key to join them together.
12:29
And what that means if I was to report then I could it would filter the records on internet sales. If I were say put a slicer on my port and filter by silt sales territory group.
12:41
So let’s just go through it here.
12:47
If I were to go into the report view and says wanted to create like a little stacked bar chart, so I’ll click on stacked bar chart and I’m going to expand it. So it’s more visible. I’m just going to add my sales amount of create a measure for sales amount here. So add that and then I’ll also add the sales territory group that I was mentioning.
13:09
So now you see I’ve got a report gives me sales by North America by Pacific by Europe and The next thing you’d want to do then is say to several level security. There’s several ways to get to it. But if I’m in my report view and I go to modeling there’s a section here called manage roles and security similarly. If I were in the data view here, it’s actually on the home tab. This is a security section so you can get to it from several places if I just go in here and go to manage roles.
13:49
And I can create a new role. I’m just going to call this one a pack for Asian Pacific.
13:56
And then you can basically set your rule which is I’m going to say that sales territory group.
14:07
Equals quote Pacific and then that should then set and this is basically the standard DAX expression. So this is a very simple one and I’m going to hit save.
14:24
Let’s see here sales territory group.
14:32
Equals quote Pacific and that’s because I spelled sells territory group wrong.
14:46
Actually, we’ll see that is a good example. I actually tried to write this expression on fact internet sales. Well that’s actually wrong because that’s not a field in that table. So really what I want to do is put in dim sales territory, which is where that field exists click on the check mark now you’ll see it accept it and now I can actually create two more. So I’ll create a new one called EU and similar sort of thing and I’ll call this year.
15:16
Threatened Pacific and I’ll create one more and I’ll call it North America and for North America and I’ll call it North America.
15:33
And hit save so now just go back and look at their again. So I’ve got three different roles that I’ve set up one for each ton of region. It’s basically specify this sort of DAX expression. They’ll apply a filter to my data.
15:52
And now also within Power BI desktop, which is what we’re doing all this work actually row level security. I should mention I have to be set up in Power BI desktop and then you publish it to the workspace. So this is all done in desktop and then published out.
16:06
So if I click on view as X if I go back first to my report and now if I go to the modeling I click view as it’ll allow you to see what it would look like if I logged in as a particular role, so if everything worked, well if Login as a pack here, you’ll see now it’s actually going to filter that report. So anyone who’s been associate with a pack roll. This is what they’re going to see similarly. If I did the EU roll. I’m going to see that they just see the Europe data and it actually good for checking to make sure you set up your filters correctly and they’re operating the way that you want them to so if I do North America that one worked as well, so now I’ve got my security setup.
16:51
Up and working in in Power BI desktop. So now I’ve done that next up I want to do is publish this out so I can start sharing it. So to do that. It’s just standard publish. So if I go to my home tab I click on publish I save my changes.
17:13
And then I’m logged in and it’s going to give you a list of all the workspaces that I have permissions to publish to and we’re just going to do it to our lab environment.
17:25
And now it’s just goanna should be pretty quick because a small file it’s going to publish that out into the Power BI service.
17:34
The next step is to go into the Power BI service. I’m logged in if my instructor account which is what I published as and you actually you see that here it is. Here’s the new data set that we just created and also here’s the report and since I’m the instructor I can see I have actually permissions on all three if you go back into the workspace here to the data.
18:04
At the next step in setting up the securities and go associate users with those roles that you created. So I’ll go here and I’ll click there’s a little lips. He’s here next to the data set and there’s a section called security and under a pack. I can click on a pack and I can choose a user who’s you got to count on our ad service in your Office 365 account. So I got PB I dot student.
18:34
Oh and I’ll add and save.
18:40
And then let’s add student O2.
18:45
To Europe add and save and student 3 to North America and hit save and now those are there now also it’s so heart faint and hard to see but there’s actually the same sort of view as option here. These little lips. He’s so I can test as role and now it’s going to show me now with the report looks like if I had that role and so I can actually switch toggle here and see what it looks like is Europe and see what it look like.
19:20
It was north so now I see that I’ve got it set up. I also see that I’ve also now associated with particular right users. And so that’s it now. It’s all set up and if I were actually to go over and I’ve got another machine here where I’m logged in as PBI student. Oh one actually might ask me make me log out first. So let’s see here.
19:52
Yeah, there it is. And I go to content. First of all, this is the next point to make I’ll show you this first, but I’ll make a point so you see I’m student. Oh one and I logged in as student. No one and this is the report gave me just the Pacific region.
20:09
There’s one other little nuance here though is when you’re setting up your workspaces.
20:15
And so this is like the workspace you grant access to people to the workspace and here I’ve made Student 1 and student to I’ve given them viewer permissions on the workspace and you have other options here, but the point to note is that the role of securities only applied for people who have viewer permissions. So if you’ve got people in his workspace that have higher level permissions like contributor member admin, they’re going to be able to see all of the data even if you tried to set so if of roles and associated with the roles, so that’s actually something to keep in mind.
20:52
This is a very much suck targeted towards those sort of viewer consumer roles within Power BI and you also didn’t have to be careful because if you give people permission to download the dataset, I can download the PBX file or analyzing Excel or download the file, then they’ll be able to get to a WC all of the data. So again, you got to make sure when you’re setting up this the broader security and access privileges you set that up the way you want.
21:23
Okay, so and then the next point to make is we’ve done this now and you look at that. You say gosh that’s actually a lot of Maintenance. You know, I’ve have to manually set up the rolls. I’m Manuel Associated them in the service, you know, that’s but I’ve already got some other source security infrastructure in place. Is there a kind of maybe a more efficient way to do it so pretty quickly? We recommend moving from the sort of static example.
21:58
Like more of a dynamic example, I’m calling it where you’ve got a security table set up to do the joins in the filtering. So and that and that if you guys have already got installations around your organization, you’ve been doing data and data analytics and you’ve already set these security tables up you can want probably going to want to make use of them for this as well. So and that case there’s a couple DAX.
22:28
Functions to be aware of that you’ll make use of theirs called they’re called there’s called user name and user principal name and they both kind of basically R has returned the username of the person who’s logged in and I used to it in different formats, depending on how you want to use it. So it’s either username is domain / user name or user principal name is just their email address and again since whenever someone in order to access our bad Power BI service I have to login.
22:58
Power BI knows the user name of everyone who’s logged in and who’s trying to view the report and this function then is available for you. Not only for rural level security, but other things you might want to use that for it with your DAX and all of your reporting. So let’s look an example of that.
23:18
Mmm so here I basically it’s the same thing. I’ve got the same sales territory are net sales tables, but I also added another table here called the user territory where I’ve associated users with the territories. And again the primary key you I’m doing a join on my territory table is this sales territory key. The icy North America is associated with one two, three four.
23:51
Eyes and so on so forth Europe, so did the similar sort of set up here for the associate my users. So I basically so C8 student one with those and student to with I think that was Europe and student 3 with Asia Pacific and then in my model, I need to add that sort of join to the relationships. I need to add that relationship and now with that.
24:21
Now I have that relationship then it’ll apply these filters all the way across. So whenever I want to on my report filter on it on a particular user account is going to filter all the data and you can see that as an example. I sever report here and here’s just a list of all the records in that sales territory table and like which student I associate with.
24:45
I didn’t overlap any students kind of like one to one, but you could like I said have multiple risk associated with multiple territories if you wanted and there’s like I said, there’s this DAX expression here and I created one called current user principal name is equal to the user principal name that function and that’s actually what’s displayed on this card and I’m logged in current with my machine and I’ve got a big interest in Roman history so liberates and freedom. So that’s kind of like the user name them I logged into but when we published that the Power BI this will show us the username of what was logged in.
25:27
And then, you know you I can filter it you can see if I got PBI student. This is what they’re going to see and so on and so forth now say a pro level security in this case is actually very similar as basically the same as we did before our I’m going to create one role here are very similar in terms of making use of the DAX. Let me just make sure I get my formula correct here.
25:56
Okay, so I’m going to create a new role and I’m going to call it user territory.
26:06
You if I could spell user territory.
26:10
And I’m going to put it on this table. I’m going to say where to go.
26:22
User account was the name of the field.
26:27
Equals user principal name let’s just check that work.
26:40
Okay, so I’ve got my field user account and basically what this is saying is based upon who’s logged in if I’m logged in as student one that I’m going to filter the all the records to whatever permissions in my security table that person has permissions to see and just make sure it’s all right. Okay, so same thing now I’m going to go ahead and go out and publish this.
27:11
To the Power BI service to our lab.
27:26
Okay, and now if I go back there?
27:31
We should fight refresh this Source should be now.
27:38
It added this sort of another my data set again here and the same thing. I’m going to go into security to set the permissions. I’ve got that role that I created called user territory. I mentioned that you could use email addresses, but you can also use Power BI groups. So we actually have a group here called the PBI class and all of our students are member of that class.
28:02
So and that’s really best practice and you know ad 100 Basics right as Roles and groups rather than individual email addresses when you’re doing this. So I’ve set up which of course if you’re working a large organization means you probably have to collaborate with whoever the administrators are of your Office 365 account and stuff like that to set this up my set that and then I go to my lips is test his role.
28:34
Now I select a person student 1.
28:42
It should apply the filter across so there you see it. So if I if it’s student one, you know, the only records are being placed on this table are there’s those others aren’t even visible and my chart here is being filtered to only show North America still only all my slicers are also filtered. So the whole thing all the data is being filtered because of those relationships that we set up here in our model between user.
29:12
Ori and these tables so now like I said if I were too actually to go over to my other machine where I’m logged in as student one.
29:25
And refresh the data and I look at this report and again notice this personally has viewer capabilities. So I click on data sets those data sets. I published aren’t even visible that person is even get doesn’t see them. All they have permissions on is as a viewer is the reports. So now if I go into my Dynamic one, if everything worked, well, there you go student 1 similarly. I’ve got my other machine here you can see student too.
29:58
Here another Greek goddess or Roman goddess, if I go to this and refresh the data?
30:09
And I go I’m now logged in and this one is student to and you can see what this person sees.
30:17
So I think one of the questions just got posted was can use ad security groups and the absolute we just demonstrated. Absolutely. You can’t that’s what I just did because if you noticed when I set my role here back here if I go back to on the data set when I set it up here on security. Why is my computer freezing?
30:46
On security. This is a group. This is actually an Office 365 group, but it could be an ad Security Group as well. So and that is actually the recommended approach like I was saying so those are two examples of row-level security.
31:10
Now odds are if you’re a larger organization, you have a more complex hierarchy you’re trying to implement between that is besides those sort of Basics and the principles are exactly the same. It really comes down to a more complex DAX function for your filter and they’re sort of different examples that we could do.
31:38
Around that which I don’t think we are still need to go through your because we always will trade the comp and but you can imagine a DAX function that has an or there’s also even if you’ve got your table set up this kind of recursive. So like it’s got, you know a person, you know this salesperson and they report to this man. It says reports to and the same people as another record with the manager and that person reports up.
32:04
So you’ve kind of built the hierarchy In it, you can make use of What’s called the data that the path function was in DAX to trace through that hierarchy so there and I’ve seen you know, six seven levels of hierarchy people trying to do with this or even more than just user there could be more Dimensions by which you want to apply your filter and all that comes back to how you kind of construct this sort of function.
32:34
So with that you’ve probably already seen some of the limitations of row-level security, right? It doesn’t necessarily answer the question who can see which reports right? You kind of controlling that through the access in the workspace and that sort of thing.
32:55
So this is really just about how it’s applying filter to the data within those Arts, and as I mentioned it’s kind of Applied really to space if you have the viewer sort of roll and also, you know, if you have to be careful because if you have access to the data set or they downloaded or access a different way, they can potentially get around it.
33:20
And the sort of whether it’s this approach or any sort of security you’re doing here, you know on your platform. There’s always this question. How can I maintain that those permissions are my security table and keep it up to date and accurate and that’s you know, partly an operational sort of question. But definitely a reason to go to having some sort of security table this sort of Dynamics or reproach supposed to Sir.
33:46
So our simple set you may have a simple solution for static or Pretty quickly, you’re going to want to get to sort of Maintenance and to the extent that your organization supports it, you know again pushing that into the active directory groups is a good way to go.
34:05
And then there’s things to think about, you know, rollover squeeze a piece of your overall sort of security framework and when you excite of planning this and thinking it through mean first steps are okay. Well which users need access to what information so what are your security roles? What are you trying to and which the information is critical and how critical is it that we prevent the know?
34:35
This information so certainly in the planning design phase and thinking it through those are sort of fundamental sort of questions and it fits into what’s my what’s your overall data governance approach to Power BI wasn’t just security when we talking about. Where is the data going to reside and we’re going to present you know, how we’re going to architect our overall sort of data structure.
34:59
And you know, how are we going to certify data sets and make sure that we’ve got a single source of truth and all sorts of things this is kind of piece of that sort of broader planning design puzzle to be thinking through as well as there’s a lot of performance sort of impacts right and things to think about and that gets back to import versus direct query and how you’re having the data.
35:24
And then how do you optimize for performance and I’ve set up direct query then you’ve got all sorts of things that can affect it like Network latency and those sorts of things and you want to make sure Then that’s your query that gets sent to the sources a restricted data set a smaller data set and things like that. So need to think Beyond just security to overall sort of performance design optimization as well and that gets back to where we’ll Implement security. We’re doing example here where it’s all in Power BI but a lot of you have data warehouses where you’ve got security already built into the backend or you’re using analysis services and maybe row level security isn’t the right way to go.
36:05
So maybe you want to make use of those in your design instead and like I said all these things comes back to operational once I built it, how can I make sure that’s easy to maintain and control and update and those sorts of things all important considerations when you think about your overall security and data governance planning.
36:30
With that what kind of overview kind of just give it talk about some additional resources and then hand it over to Mike and maybe have a few questions, but I see our kind of popping up on the chat. So just point out that this is just one in a series Senturus offers training courses and kind of like the entire piece of the platform Power BI we also offer mentoring sessions. If you want to have follow-up questions.
36:57
You just want to do a one-on-one sort of quick mentoring session often with security it gets into very specific questions about a particular sort of things people need and so it kind of moves yawns these sort of general discussions need to dive into a sort of specific scenario that you’re trying to wrestle with but to assume the sort of DAX formation and how to it also gets back to how I structure my data model optimally to support this sort of filtering so modeling is important piece of this as well and continue to publish articles on our website references sources you can go to and like I said as you’re thinking broader by your Enterprise roll out and sort of add ministration how you’re going to do it. Also we’re there to help and talk to those sorts of questions. So with that I will hand it back over to Mike great. Thanks. Steve got stick around everyone. There’s a lot of great questions in the question log that we’re going to get to you after just a couple of slides here.
37:57
So hang up as it were and Steve maybe while you’re while you’re driving the deck are you can peruse that real quick. It looks like you’ve been keeping an eye on it. And there’s one also in the chat window by the way, but a few quick words about Senturus in case you’re unfamiliar with us. We are the authority and business intelligence. All we do is concentrate our expertise on BI all day every day with a depth of knowledge across the entire bi stack. We’re known for providing Clarity from the chaos of complex business requirements just for data.
38:29
We made a name for ourselves because of our strength and Bridging the Gap between it and business users. We deliver solutions that give organizations access to reliable analysis ready data across the organization so they can quickly and easily get answers at the point of impact in the decisions.
38:48
They make and the actions they take we offer a full spectrum of BI services as Steve alluded to before everything from creating dashboards reports and visualizations to back-end data preparation and modern data warehousing handling hybrid bi environments like migration security complex security and cloud versus on-premise. We even offer software to enable bimodal BI and platform migrations and we support we have expertise on demand for you as well as complete training and mentoring across Cognos.
39:26
Power BI and Tableau. We’re so confident in our team and our methodology that we back our projects with a hundred percent money back guarantee that’s unique in the industry. We’ve also been doing this for a while coming up on two decades. We work across the spectrum from Fortune 500 clients to the mid-market many of those logos. You’ll no doubt recognize solving problems across many Industries and functional areas ranging from the office of finance to sales and marketing manufacturing operations.
39:55
HR and ITR team is both large enough to meet all of your business analytics needs its small enough to provide personalized attention.
40:06
We mentioned earlier that if you head over to if you want to flip the slide there Steve if you visit senturus.com/resources, you will find all of the great webinars that we have out there in addition to a ton of other great information, and I was remiss in stating that leaving out that there will be a recording of this webinar that we always post along with a copy of the presentation great upcoming events. You can always go to senturus.com events and there’s Cognos Framework Manager versus data modules event. That will be Thursday June 25th. So same time in a couple of weeks here go ahead and register for that. We do offer complete BI training across the three different platforms. I mentioned before Cognos.
40:55
Power BI and Tableau, everything from corporate offerings that combine the various modalities and offerings to tailored group sessions individual to small group mentoring instructor-led online virtual courses and self-paced e-learning. So talk to us about how we can help your organization meet all of your training needs. And then finally before we get to the Q&A we again recommend that you head over to our site for hundreds of different great free resources.
41:25
Going to go to the next slide Steve including unbiased product reviews great tech tips, our fabulous blog which provides kind of top of mind up-to-the-minute very easily digestible information in the business analytics space product demos and the aforementioned upcoming events. And with that we can take the remaining time that we have and dive into some of the questions. So, I don’t know if you picked up one or two that you want to start.
41:55
Yeah. Alright so several questions here. So let me just pull up my example.
42:04
Okay. So first question was will we share recording as well answers? Yes, someone pointed out to me that I was setting up to the wrong table. I fixed question is can you do this for a par very report server? The answer is yes the same concepts apply and as several questions or around ad and Power BI groups. And so yes, you can use this group.
42:32
So I say but a lot of people like said. Sometimes that’s not so easy to implement because maybe it restricted or something like that. You can also get more sophisticated in how you set up this user table.
42:49
So even though I was doing it to link to the group’s I could make a more I could do it all through more sophisticated security table here instead and I could even add columns to create a hierarchy so I could actually really could have had another the other column here which is you know, group 1 or group 2 or some things like that. Similarly. There’s you know, the sort of question about hierarchy similar sort of thing.
43:21
You can I could have a hierarchy if I had another table here that had say a hierarchy of users and like employees say and it had Who that person reports to is part of the table and then basically therefore recursive and iterative then I could use the DAX function the path function to actually build a path for each user each employee and then I could use that as part of my filtering. Let’s see what other sort of questions are there.
43:58
If you give users viewer access to the data set but have to test it on some of these I have to really testing to make sure I gave you the proper definitive answer. And again, if you give them access to the data set to create reports, I think it even at that point they’re going to be able to get around the low level security but it’s something to be easy for you to test other questions here. I’m just going through list is row level security.
44:28
Available only for premium the answer is no it’s doesn’t require premium license. It works with the pro license as well. Do you have to have at least Pro in order to be able to share your reports that you created in Power BI desktop with others. We had a question on the chat log about if you pull if you import the data using a SQL query instead of individual tables, is it possible to apply RLS to that?
45:00
Yes, well if you import data using SQL search query absolutely because it’s basically the same, you know, you go to get data and I actually imported from Excel by could import from each SQL Server just as easily as exactly the same. I’ve imported the data.
45:18
I’ve got a local instance and row-level security will go to apply and in addition if I use Direct query to connect that SQL Server since it supports row-level security you should be able to apply rule level security as well through that. However, you know as you’re probably aware, you know, if I publish it to the service you also and I’ve got my own on-premise SQL Server. You also need to set up the gateway to set that secure communication pipe between the service and the SQL Server.
45:56
So it’s able to do those sort of freeze it updates that sort of thing. But row-level security is supported in that instance. And what if the there’s existing security groups in your enterprise LDAP/80s. Is that something that you can map Genesis?
46:13
Yes, you can map to those if you noticed when I was doing my mapping here and I started typing it basically gave us gave me every person but also every security group and its distribution group that’s in our organization. And I was able to choose any of them if there’s any that are existing.
46:36
If you don’t have access to that like that’s controlled by a different organization and you know, you know, that’s not something you want to have touch. I said then it gets back to how you’re designing your tables here. So you would build all of it into your sort of like security tables that way and then you wouldn’t you did be much more maintenance right or you’d have to do your own maintenance but even have able to have your own kind of control over it.
47:06
As opposed to necessarily being reliant on over as manager your ad environment.
47:22
Yeah, I think there were there was another question about the hierarchical table and I think you talked. Yeah that and then looked a little bit about that one. I probably didn’t really show any great examples. That’s probably one or be happy to follow up with you to talk about your specific question and more and happy to have a chat about it that sort of thing on somebody sort of hierarchy ones because they get very specific to the your particular.
47:50
Sort of wave different ways you need to filter it. I said the general concept is to make use of that sort of path function or something like that.
48:06
And then there’s some question about downloading the data. Like if you have a user with viewer access and you allow export to that those subsets of the data know if they download oh if they actually download the data, that’s a good question. I think if that’s what I would want to follow up and actually test to tell you the truth. I’m not sure in theory if you’ve got a report here.
48:35
With my report here right if in theory if I’ve got my report here and you give them on the ability here to say choose to export data that it would only give the data that they have permissions on so that should flow through the row-level security, but I would want to test it before I said definitively or I would recommend that you test that to make sure that that works and you can actually see there’s a setting here whether or not you can get When you create your report, you actually have a setting here whether or not you can give people just access to the summarized data, which was a DDA for this visualization or the underlying records as well.
49:42
Yeah, I guess the other point there is you may not have an enterprise ad but when you set up your Power BI space app at powerbi.com for your company, you know that is tied to behind-the-scenes active directory in some way for people to be logging in so you would still be managing their logins and made behind the covers. It doesn’t feel like a d and you’re just kind of managing at the user level. I like I said set up your own server security tables.
50:16
The Power BI service when a person logs in is being honest is using that as its authentication mechanism for the person is logging in.
50:36
Great. I hope everyone has been surviving all the shelter in place and not getting to stir crazy and everyone is healthy most important and all and all of your groups and organizations. Like I said, we’re here I’m happy to have conversations with anyone who wants to talk about them sort of thing and I looking forward to it. Yeah, that sounds great. See if you want to toggle back to the last slide.
51:04
In that vein. We thank you for joining us today. First of all, thank our presenter. Mr. Wullschuler for an excellent presentation. If you do have follow-up questions or wants to talk to us about your specific challenges in your environment.
51:21
Please feel free to reach out to us using any of the methods below through our website through the triple-eight number or simply email us at [email protected], and we look forward to seeing you on another one of our knowledge series events. Thank you very much. Stay safe, and have a great rest of your day bye now.
Read more
