Hello everyone. Welcome back to

Analytical Guy. In this video, we will

talk about a very important topic in

Power BI, that is security. So, if

you're creating reports and you're

publishing it and sharing with multiple

users, it is important for us to

understand how the data is being used.

Is it exposed to everyone? Do you want

to expose all the elements, whatever is

there in your semantic model, to

everyone? So, if the answer is no, then

you have to create a security layer. And

in security, we talk about RLS that is

row level security. Second is OLS, which

is object level security. So, there are

two ways and I will explain you both of

them in this particular video. Once this

manual setup is done, then I'll also

explain you how to utilize AI to do all

the security part for you. So, let's get

started. All right. So, let's start with

both the concepts. What is RLS and what

is OLS? Just briefly and then I'll

explain you in depth. So, first of all,

RLS is row level security. OLS is object

level security. So, you will use RLS

whenever you want to control which rows

a user can see. For example, if you have

this data, this is a dimension customers

table. You have all the customers over

here. So, you can see customer key, then

we have gender, then we have the

customer name, we have state, zip code,

country, continent, and birthday. For

example, now you want to create a

filter. For example, you have various

store managers and store managers are

maybe on the base of continent. So, you

want to have a user who can see only the

data which is related to Australia, only

the Australia customers. So, you have to

apply row level security. And now you

can apply same with Europe and same with

North America. Right? So, row level

security is straightforward. You You are

essentially filtering out the rows of a

particular table. Right? So, that's what

RLS means. Second is OLS, which is

object level security. In object, that

means it can control columns or tables

or both. Right? So, for example, you

feel birthday column is sensitive and

you don't want to showcase the age of a

customer to your store managers. So, you

can hide essentially this particular

column

with all the users. So, you can create a

separate role which hides a particular

column and then whenever you give that

permission, give that role to a

particular user, that particular user

won't be able to see this column and

would be able to see everything else.

Right? So, we will see we will

demonstrate everything on Power BI to

understand how it's done. All right. So,

before we go ahead, let me tell you what

all is covered in this video so that you

know what is coming next. So, first of

all, we'll talk about how to implement

row level security. So, there are

multiple methods I'll talk you walk you

through all of them. Then how to

implement OLS, that is object level

security. So, there are two ways, Tim

Dale method and tabular editor method.

So, we will demonstrate both of them.

Once these two setups are done, we will

talk about the challenges with these

manual methods and then we will talk

about how to utilize AI to overcome

these challenges. And finally, the key

takeaways and best practices when we are

talking about securities in Power BI.

All right. So, let's start with row

level security, that is RLS.

In manual setup,

before we deep dive, let me give you

quick glance of RLS. So, first is it

filters the data based on the user

context. That's what we discussed. If

you want to filter particular records,

you can filter that.

User see the same report but different

data. So, you have different users, they

will see the different data but the same

report. The report name and everything

will remain the same.

It uses the DAX logic plus lookup value.

We will demonstrate that in the setup.

And it is applied at the query time.

This is important. So, we will talk

about when OLS is applied, but this RLS

is applied at the query time. So, once

the query runs, it is applied at that

particular time. It is perfect for

regional organizational hierarchy. So,

if you have regional hierarchies and

then you can easily apply RLS.

All right. So, let's deep dive into the

setup. All right. So, to understand RLS,

we will talk about two methods. One is

static RLS. In this, we will talk about

how to create one role per store. So,

it's like if you have 100 stores, you

have to create 100 roles. So, one role

per store. This is quite challenging

because if you have multiple stores, if

it is growing, then you have to create

the roles again and again. Second method

is dynamic and that is better than what

we are going to do in this one. So, for

this one, let's go straight to Power BI

desktop and I'll explain you how it's

done. All right. So, now we are on Power

BI desktop file and before we start, let

me just show you the data set. So, we

have dimension customers, then we have

dimension date, dimension product. So,

if you see on the right-hand side, so we

have dimension customer, dimension date,

dimension products, dimension stores,

and then fact sales. So, you don't need

to worry about these two as of now. So,

currently we have four dimension tables

and one fact table. And if you go in

your modeling, you will see

there are four dimension tables and one

fact tables. And this is how you work

one-to-many relationship. Right? So, now

we have this and let me show you the

columns in dimension customers because

we are going to utilize this even more.

So, you will see customer key. For each

key, we have the gender, customer name,

city, state code, state, zip code,

country, continent, birthday column, and

then customer key name. All right. So,

now that we know the columns inside each

table, let's understand how to implement

row level security. Method number one is

static. All right. For that, first of

all, you will go to the modeling tab.

So, once you click on modeling, then you

will see manage roles. So, once you

click on manage roles, you just need to

create a new role from here. So, you can

see the for you, once you open your

file, it will show you as empty. This

will be empty. There won't be any role.

These are the roles which I created.

Right? So, now you have to click on the

new button. So, once you click on new,

and it will click as an untitled. So,

you can mention it as, let's say, row

level security for

Western Australia.

Right? So, now I have created row level

security for Western Australia. So, what

I want,

if I go to a particular dim stores,

and so, there are two options. So, one

is a blank where you can just click on

the plus new icon and it will show you

all the columns and you can apply rules.

Or there is second option is switching

to DAX editor and you can

write your DAX query and it will work.

Right? So, for now, this is static and

it is straightforward. In dim stores,

I'll click on new and my WA Western

Australia is state. So, I'll just click

on state and it should be equal to

Western

Australia.

This is manual straightforward. So, what

it does is whenever the state is Western

Australia Australia, this will show only

those records. Click on save. And once

you click on save, it should show you

successfully applied rule changes. That

means the rule is correct and now you

can close this.

just see over here.

For example, you will see continent

Australia, Europe, North America. And

for Australia, you can see quantity is

3576. Right? And we have all the data

right now. But now, if I have to test if

my role has been created successfully,

on the top, I can click on view as. So,

if I click on view as and go to RLS_WA,

which is the row level security for

Western Australia only, then click on

okay.

Right? So, now you will see over here

everything changes. This will only show

for Australia and that to Western

Australia. So, remember, this is only

for Australia and quantity was somewhere

around 3,000, now it is 730 only. So,

all the details, everything is shown for

Western Australia. And now, to confirm

this, you can go to data set. And if I

go to dim stores, you will see only this

particular record. So, we had multiple

records, but now it will only show you

for state Western Australia. So, this is

how you can test and see if the role has

been created successfully or not. And

once it's done, you can click on this

stop viewing, so it will stop and it

will go back to the original one.

Simple. Right? So, this is a static RLS.

So, just imagine if I go back here and

in dim stores, you will see now all the

states are coming in. Right? So, now

imagine if you want to create

a role for each state. So, right now we

have somewhere around 60 plus states

over here. So, if you

create a role for each state, you will

have to do this 60 times. And this is

time consuming and this is not a best

practice. Right? So, if you have if you

are a small team and just two or three

users, this is good enough. But if

there is a scale, you're scaling and you

have multiple users using this kind of

report, then comes the second option,

which is dynamic RLS. All right. So, now

coming to method two, which is dynamic

RLS, which is more flexible and

scalable. So, if you have hundreds or

thousands of users and they have

different roles, you can create one role

for all. And then

you need a mapping table for this. So,

this is one extra step in setup, but it

is very straightforward. So, So, you

need is one role and mapping table.

That's all, right? And finally, this is

how your Tim Dale script will look like.

I will show you for method one as well.

So, this is a script that will come in.

So, it shows that what has been done.

For example, over here you will see one

role store manager has been created. It

says model permission is read. So,

that's good. So, what it does is for all

dim stores,

store key looks up into the mapping

table and it maps with the store key.

And finally, it gives you the manager

email ID for that particular store key.

And then, if you are logged in up with a

particular email ID, it will map

with this particular manager ID and it

if it is matches, then you will see all

the data for that particular

store key. Otherwise, you won't. So,

this is where we use user principal

name. And this is important in this

part. All right. So, in order to

implement the dynamic role level

security in Power BI, step number one is

to create the mapping table. So, now

that you know that for each store key,

you need to have a email ID which has to

be associated with the user. So, if I

log in with that user

email ID, I should be able to see those

particular store keys, right? So, if I

have that mapping table, then I can

easily create a DAX and then it will

seamlessly work dynamically. So, step

number one, creating the

table. So, currently I have created a

demo

small mapping table over here you can

see. So, I have store keys 1 2 3 4 5 6

and manager email ID. So, if you see

over here in dim stores, we have store

key from 1 going till 66, right? So, we

have a total 66 stores over here.

But, I have only created just for six

just for demonstration purpose. So, now

I have store key one associated with

manager email admin@analytical-guy.tech

and then all those dummy email IDs and

then just for testing purpose, I have

created store key six which is

atikant@analytical-guy.tech.

So, we will test with this email ID so

as to see if it is working or not. All

right. So, once you create this table

and if you are thinking how to create,

you can create an Excel and then import

this or if it is just five or six, you

can just click on enter data from top

and then enter all your records and then

it's created. So, essentially, you need

to create a table over here. You can go

in Power Query which is a better way

or using data flows. So, once it's done,

so you have this mapping table. So, once

you have mapping table, map this column

with dimension stores store key, right?

So, if I go to the model view and see on

the left hand side, so you will see

dimension stores this

one is connected to

roles mapping table, right? So, connect

currently it is many to many, but you

can make it one to many.

Generally, it is better, but it's fine.

But, the arrow should be from

roles mapping table to

dim stores. All right. So, now that we

have this relationship done,

the third step is to create the DAX.

Right? For DAX, I'll go to report view,

click on

Right? So, once you click on modeling,

then click on manage roles. So, once you

click on manage roles, click on the new

roles and here I'll create

store manager demo.

Right? So, in store manager demo, I will

click in

dim stores. So, I'll click here.

Right? So, once you click on dim stores,

click on switch to DAX editor. And over

here, you have to write your DAX. So,

just for simplicity, I'll go to my

existing store manager and this is what

we want to create. So, I'll just copy

this and go to store manager demo which

we created just now. I'll paste this.

Let me explain you now what this is

doing. So, what I'm saying is

in dim stores, we have a column name

store key. In my instance, my store key

was

six and the manager email ID was

atikant@analytical-guy.

tech. So, what I'm saying is so,

whenever the manager email ID is equal

to user principal name. So, if I log in

with my email ID in Power BI services,

if I log in with this, so according to

this rule, it will match this email ID

with this manager email ID for store key

is equal to six. So, that means so,

whenever it is matching, it will return

my store key which is equal to six. So,

that means in dim stores, it will filter

for store key is equal to six.

Otherwise, every other store key is

filtered out. I won't be able to see any

other store key, right? So, that's the

logic.

So, if I remove this and click on save,

it should give me successfully applied

and that's good. So, once this is done,

I'll click on close and I can easily

test over here. In modeling, go to view

as, click over here,

click on store manager demo. If I click

on okay, this will give you everything

blank because it is not able to

understand which user you're going for.

There are so many users in store manager

demo, right? So, I can click on stop

viewing, click on view as again,

go to store manager demo. But, in other

users, this is where I have to mention

my name. So, atikant@analytical-guy.tech

analytical-guy

.tech. So, once I click on okay, I

should be able to see the data only for

store key is equal to six.

So, once I click on okay, I'll see the

data only for store key is equal to six.

So, if if I add store key over here from

dim stores,

so you will see it is just six, right?

In visualization, let me add in the

right way

and you will see this is only

demonstrating the data for six. And six

means Western Australia in this

particular data set. And you will see in

dim store, everything else is filtered

out. I won't be able to see anything

else. This proves that my role level

security is working. And the best part

is

you can create this online. So, once you

publish this,

click on save and let me test this on

services as well. Right? So, I'll just

look for my security

workshop.

Replace.

So, once it's published, let me open

this. So, once you have published the

report, you will see a semantic model

over here. So, this is semantic model

and you will see semantic model has six

dots like this icon. And once you see

semantic model, click on three dots,

click on security.

So, once you click on security, you have

all these options, role level security

options.

AJ Birthday, AJ Birthday one. These are

all test roles which were created

earlier. So, this is the one we created

now, store manager demo. Right? And the

best part is you can click on three dots

and click on test as role and you can

test if it is working or not. So, this

is essentially a place where you need to

add different users. If there is a new

user user and you want to add the other

role

the user in that particular role. For

example, you have RLSWA. So, there is a

new user and you need to give him access

to just Western Australia.

Right? So, add that particular user in

this one. So, if you click on this one,

go to this one and add that particular

email ID. All done. And once you add,

don't forget to click on save because

sometimes people don't save it and feel

like they have already added that

particular member in RLS.

All right. So, now this is a complete

setup for role level security. All

right. So, in role level security, I

just want to show you one more thing.

So, if you click on the TMDL icon on the

left hand side, click on Tim Dale and

over here on the right hand side, you

will see the semantic model.

Under semantic model, you have options

like cultures, then measures,

expressions, functions, everything. Then

you will see roles as an option, right?

So, this is what we are looking for. So,

if you expand roles, you will see a

particular role that we created just now

which is store manager demo, right? So,

if you select store manager demo, just

drag this one over here.

Anyway, so we can drag in the new one to

make it easier.

All right. So, this is the one we

created, right? So, this is the script

we created using

the

manager roles, right? So, once you

create, it will go in the semantic model

and this is how it is captured. So, what

it's saying is create or replace. So, we

are creating this role. So, if it was

already there, it will replace. So, we

are creating store manager demo. The

model permission is read. So, on the

table, so there is another

parameter that is table permission. So,

in table permission, we are saying in

the table dim stores, this logic should

be applied.

So, store key should be equal to the

store key of mapping table and on the

basis of whenever the manager email is

equal to the

user principal name. So, once this is

done and this will stay in your

file. So,

so whenever you download this as PBIP

and you will see this semantic model

comes in. That's also the reason when we

connect with the cloud, you connect with

PBIP and it has all the semantic model.

So, this Tim Dale is always there. So,

it has the complete context of your

semantic model.

>> [snorts]

>> All right. Same with the any other one.

So, if you create any other role, you

can see how it's done over here. If you

are liking this video so far, I would

really appreciate if you can subscribe,

like, and comment on this video, and

this will help me to create even more.

All right. So, coming back, so real

scenario, understanding how these uh RLS

works. For example,

So, let's compare static versus dynamic.

First example is

user Atikant moves from store 6 to 5.

So, if this is the scenario, what will

happen if you are using static roles?

Step one is you have to remove user

Atikant from store 6 role, then you have

to add Atikant in store 5 role. Step

three would be once this is done, you

have to publish the changes, then you

have to refresh the data set, then you

have to notify Atikant as a user. Once

it's done, this will take you 25 to 30

minutes, and this is just for one user.

Right? Method two, dynamic RLS. In this

one, what you have to do is just go to

your mapping table, update uh the user

Atikant. So, it is mapped to six, just

change it to five. That's it. So, once

this is done, just refresh your data

set, it's all done.

The user will automatically see the new

store. So, that's the beauty of working

on dynamic RLS. And once you have this,

you just need to update your mapping

table whenever there are multiple

changes in your users, they are shifting

from one store to another, or new users

are coming in. And this will literally

take you less than 5 minutes. So, this

is the shift. And this is what makes

dynamic RLS really good. All right. So,

now let's talk about the second scenario

where the dynamic roles

make it even better. So, if you have to

add new store

100 in

Bali, for example. So, in static roles,

first of all, you have to create a new

role, the way we created for WA, right?

So, you'll create something like store

{underscore} 100 {underscore} Bali.

Then you will write a DAX filter, or you

will apply whenever the state is equal

to Bali or whatever the name is. Then

you will publish to service, then you

will map that particular email ID to

this particular user role, and then you

have to test and validate. Definitely,

it will take 50 to 60 minutes. Right?

Whereas dynamic RLS is so good, you just

need to add one more row in that mapping

table. So, you will understand that

dynamic RLS is so good that you just

need to work on your mapping table, and

that's it. So, add a new role where

store key is equal to 100, and email ID

is the one that you want to give the

access to.

Once this is done, that's it. You just

need to refresh, and it's done.

The user will automatically has the

access. This will take less than 5

minutes. You can see the difference. So,

this is where dynamic RLS is really

nice. All right. So, now we are done

with row-level security, let's start

with object-level security, and this is

very interesting as well.

All right. First of all, quick glance.

So, we have already talked about what is

OLS. So, it hides columns as well as

tables from the users. So, in case you

want to hide, for example, birthday

column from the table dim customers,

then you can easily hide that, and user

won't be able to see those sensitive

data. So, in case you have finance data,

and you want to hide salaries from the

other employees, so you can easily work

on object-level security, and it's all

done. So, in fact, data is not filtered

over here. It is redacted. So, in it's

not like we are filtering the data set,

and you're able to see the less records.

You would be able to see all the

records, but less of the columns.

Users see the same structure, but

sensitive columns don't appear.

>> [snorts]

>> No DAX is needed, applied before query

execution. Now you understand, so when

we talk about row-level security, it is

applied at the time of query execution.

All right. So, let's talk about the

method number one to implement

object-level security. Method one is

using the external tool, that is Tabular

Editor. So, if you have worked on Power

BI since long, you must be aware of this

particular tool. If not, I'll just tell

you how to install it. It's very

straightforward.

So, once you install, I'll explain you.

This is the interface part. So, if you

see over here, you will see this is the

menu icon, and then on the left-hand

side, we have the model, whatever the

data is there, we will see the roles as

well, all the tables, translations. And

once you have selected a particular

object in this model, it will translate

and show you all the

metadata over here. Right? So, this is

how the structure looks like in Tabular

Editor. So, first of all, let me show

you how Tabular Editor is installed in

Power BI, and then we'll see how we can

uh define OLS inside Tabular Editor. All

right. So, now we are on Tabular Editor

website. So, if you go over here, you

will have this option, downloading

Tabular Editor three. So, my suggestion,

don't go for Tabular Editor three, that

is a paid version. So, if you click on

this arrow, downward arrow, click on

there, go to Tabular Editor two, which

is open source. Once you click on

Tabular Editor two open source, you can

just click here, and it will download.

So, once you download, it is

straightforward the way you install any

other software. So, once it's installed,

it will straightaway come in your Power

BI app.

All right. So, now if I open my Power BI

and go back to the report view, and just

need to click on the external tools over

here.

So, if you click on external tools, you

will see the

Tabular Editor Editor shown over here.

All right. So, now in

order to create OLS, so first of all,

I'll go to modeling.

Right? So, click on modeling, click on

manager roles. Once I have clicked on

manager roles, I'll click on new, and

let's say I want to create object-level

security on birthday.

Right? So, I'll just mention OLS

{underscore} birthday. Right? So, I've

created this OLS {underscore} birthday.

You don't need to worry about anything

else, just click on save from here.

So, that means this has already applied

successfully. So, we have created this

role, but there is no rule for this, so

it won't work for now, but we have this

rule.

Right? So, now once you have created

this role, click on external tools,

click on Tabular Editor. So, once you

have clicked on Tabular Editor,

there will be a new window popped up,

and this is Tabular Editor. On the

left-hand side, you will see roles, you

will expand that. So, once you expand

this, you will look for the role that

you created just now, which is OLS

{underscore} birthday. Right? So, if you

click on OLS {underscore} birthday, you

will see the new information coming in

over here. You will see metadata, then

you have annotations, then you have

securities. So, this is what we are

looking for. So, if you go to security,

you have row-level security. We don't

need to worry about row-level security,

we are looking for object-level

security, that is table permission. So,

you will expand this, and in customers,

currently it's default, so I want to

have

dim customers as none.

Right? So, this will ensure, or I can do

this as read, so the user can read this

particular table, but now inside this

particular table, I will create a

particular rule. So, once I have done

this none, so you will see on the

left-hand side,

this dim customers has been created. So,

if you click on here, again on the

right-hand side in the window, you will

have

this one.

Security OLS column permissions. So,

this is what we are looking for. In

columns, in birthday, currently it's

default, so make it none.

So, once you have created none, so that

means this particular role can see

all the objects over here. For example,

in table, you will see all the objects,

and dim customer is on read, while in

dim customers,

if you go further in the columns,

everything is default, so I would be

able to see everything for this

particular role, except birthday column,

which is none. So, once you are done

with this, click on save on

this icon. So, once you click on save,

this is saved. You can close this one,

and you can go to modeling, and click on

view as. So, if you click on view as,

and look for object-level security

{underscore} birthday. So, once you

click on this, click on okay.

So, see, if you see over here,

this visual is

giving me errors, because this had

If you click on this visual, this had a

particular column, which was birthday.

So, I won't be able to see over here,

because this is hidden by default.

Right? So, if I go to stop viewing, it

will go back come back again, and this

was over here, you can see in values.

Right? So, if I go to view again, sorry,

modeling again, click on view as, and

click on

object-level [snorts]

security {underscore} birthday. So, now

it is hidden, and now let me see this

confirmation in the data set. So, if I

go to dim date, everything is showing

up, dim products, everything is showing

up, dim stores, everything is showing

up. Now, if you go to dim customers,

everything is showing up. Just it won't

show you birthday. And this is some

somehow not showing you up, but state,

zip code, everything should be shown.

You can see everything is shown. If you

click on birthday, nothing will be

shown. You will see it is all blank.

So, this shows that you can apply object

level security, and this way you won't

be able to expose what is what should

not be exposed to the regular users.

Right? And now you can confirm this over

here as well. So, if I just remove this,

go to rules, and I can also search OLS_

birthday, and drag this over here. So,

what it does is it gives you read

permission, and then again we have table

permission, we have column permissions.

In table permissions, it says

for dim customers, read option, that's

great. So, I can read the table. In

column permission for birthday, it is

none.

Right? So, I cannot read uh I cannot see

this particular column. Same, if I have

multiple columns, I can just add uh more

columns here equal to none, and it will

not show in my data set. That's how you

can uh save, or you can say have

sensitive data, still it won't be

exposed to the desired users.

Right? So, this is method one on how to

apply object level security. All right.

So, let's talk about the method two for

applying OLS, that is Tim Dale.

Tim Dale is TMDL, that is tabular model

definition language, and this is where

you create some scripts, and this goes

in semantic model, and is understood by

the complete model. So, what it does is

first of all, you have to write create

or replace, and then create a rule, and

what is the name of that particular

rule, then what is the model permission,

then what is the table permission, and

what is the column permission, and

that's how you can create the Tim Dale.

So, let's go ahead, and let's try to

create this. All right. So, now that we

are on Power BI desktop file, and I have

opened the Tim Dale tab. So, in Tim

Dale, currently there is the role

OLS_birthday that we created earlier.

So, in this one, I'll just create using

this, I'll create a new role. For

example, for object level security,

birthday_ let's say continent.

So, what I'm saying is

or you can say birthday_gender.

So, this role,

what I want is I want

allow particular users to see the

birthday as well as gender of a

particular customer. [clears throat]

Right? So, the model permission will say

remains same, that is read. Table

permission, this is the dim customers

read, that's good. In column permission,

birthday is equal to none, that's great.

I'll just add one more column permission

for gender

equal to none.

Right?

And that's it. So, you you can remove

this annotation because this is already

done, or you can change the annotation

PVI ID, and that will work. So, once

this is done, click on apply.

Uh you will exit the current view,

that's fine.

And now you'll see

changes applied to the model. Right? So,

this particular role,

OLS_birthday_gender,

I won't be able to see birthday as well

as gender. Right? So, if I want to test

this, I'll go here in modeling, click on

view as, and go to

OLS_birthday_gender,

click on okay.

So, see this has been hidden, that's

good. And now if I go to my table view

in dim customers,

I should not be able to see gender.

Currently shown because it takes the

default state, but now if I click on

here, it won't show you. Right? So, if I

go here, it will show you everything. If

I go back to dim products, it will show

you everything. Come back to dim

customers, now gender is gone. Right?

Under gender, you will see this. So,

gender column is hidden anyways, and

same with birthday. So, you will see

birthday as a header, but it won't show

you the values underneath.

Right? So, this is the beauty of how we

can apply the permissions, object level

security, and you can

uh literally define which user users can

see what data

and schema. Right? So, this is the

beauty of OLS. So, same goes So, for

example, if you don't want So, let's

create a new one. So, I don't want this

particular user to see customer table

itself.

So, I'm applying role OLS_customer

table. So, I'm saying table permission

dim customers, for this one, it should

be none.

And I can remove other two columns.

Right? So, if I apply this,

you will exit, that's okay, and it will

say changes applied to the model. So,

for this role, I'll copy this, I'll go

here back to view as again, and I'll

look for

OLS_customer

table,

and click on okay. So, this will hide

these two visuals because we don't have

customer table anymore. So, if I go

here, and click on anywhere else, dim

data, dim products, and now if I click

on dim customers,

I should not be able to see that. So,

that's

strange. Let me see what's happening.

Yes. So, you don't see over here.

So, this is already loaded data, so

that's shown. But if I expand, if I

click on customer key, it is all

blank. If I click on gender, it is all

blank. Right? So, once you're done with

this, and you will refresh, publish it

in the services, then users won't be

able to see even this data. Right?

So, this is how you can apply the rules.

So, you can create rules through TMDL

scripts as well, and this is

straightforward. All right. So, now we

understand what is RLS, what is OLS, and

how to set up and do all the securities

in Power BI. But, why to do manually in

this AI era? Right? So, in this one, in

this section, we will talk about how you

can utilize AI to create these

securities in Power BI.

So, first of all, what AI can do in in

regards of Power BI security? So, first

of all, it can generate 50 row level

securities with just one prompt. So, for

example, if you have to do static RLS,

one RLS will take you 5-6 minutes,

right? And there are 50, that can take

you 2-3 hours, right? But, if you're

generating through Claude or any other

AI, and uh if you're

uh you have uh structured it very well,

it will take you less than 10 minutes,

and it's all done. Same with if you want

to generate the Tim Dale OLS for all the

customer for all the columns, the way we

did for dim customers, you can do this,

and it will take you literally 5

minutes. You can audit, you can fix all

your broken Tim Dale scripts using

Claude, and it will identify, it will

show you what are the errors, and then

you can fix it. Or you can connect

directly with MCP the way I did in the

last videos, and it will automatically

change in your semantic model.

Same, generate validation checklist. So,

there is a validation checklist when

you're talking about security. So, you

can see what are the checklist, and you

can test and do everything by yourself.

So, essentially, you're spending 20-30

minutes instead of 4-6 hours, this is

80% faster. So, when we talk about how

AI can be utilized by Power BI

developers, this is what we're talking

about. We still need to know the

concepts. You can't just rely on Claude

or any other AI tool that tell me how to

create RLS, OLS, what is RLS,

etc., etc. You need to know the concepts

that will help you

optimize it faster, and this is what the

people are looking for, make it fast.

All right. So, let's see some examples

on how AI can be utilized for this.

First of all, easy demo, I'll talk about

the easy prompting. If you have to

generate DAX, so you can just give this

prompt.

And then, it will give you this script,

and you don't need to know DAX for this,

for example. So, essentially, what we

want is we want to create RLS DAX rules

for three continents, for example,

Australia, Europe, and North America.

Uh you need to filter dimension

customers continent column based on the

logged in user email ID. So, for

example, if I'm logged in, I should be

only able to see data related to

Australia. I should not be able to see

data for Europe and North America. So,

you will use lookup value to match users

to dimension customer continent. Right?

So, this is the script.

The script is dim stores region is equal

to lookup value, dim users region, and

whenever the email of dim users equal to

user principal name, give me this

region, and this maps, and then it will

give me the desired output only. And

this is easy, I mean, straightforward,

so you can use Claude or any other AI

for this. All right. So, demo two, this

is again very easy prompting. So, if you

need to generate the Tim Dale script, so

the input is if you want to hide columns

from intern user role and external

partners, so you will say dim customers

birthday, and dim finance cost, margin,

all these columns should be hidden. So,

generate Tim Dale with permissions is

equal to none. So, it will give you the

role intern_access, model permission is

equal to read, table permission for dim

customers, column permissions birthday

is equal to none, and etc. So, this is

what we demonstrated, we created

ourselves, but if you uh want Claude to

be useful in these regards, just have a

good prompt, and it will do it for you.

Some of the other interesting use cases

of using AI for Power BI securities is

when we have dynamic hierarchical

security. For example, if you have

organization plus geography plus

exceptions, how to

tackle with this issue? The problem is

users belong to region, country, and

store, right? So, some users will see

entire region, others will only be able

to see the specific stores, some would

have some exceptions like extra access

and all. So, for this, you need to have

a more complex DAX. Right? So, for

example, the DAX will look like this.

So, if I'm saying the user principal

name is in values of security email,

calculate true filter the dimension

store where the region is in values of

securities and store keys in the values

of security store key. Right? So, if

this is true, then it will map. So, this

is what AI can help you with. Otherwise,

you have to write I mean, it's not

time-consuming if you know, but if you

don't know, then you can utilize Claude

for this. So, it's like generating

multi-layer row-level security logic. It

handles hierarchies, overrides, and

if there are edge cases, it will take

care of it. Similarly, there is one more

use case working on multi-role conflict

resolution. So, if you have multiple

roles like admin, manager, auditor,

which is generally the use case. So, the

problem is users belong to multiple

roles, admin which has full access,

manager will have limited, and auditor

will only have read-only subset. Right?

So, Power BI behavior will be like union

of all the rows roles, which can cause

overexposure. And this is what happens

if we have one particular user in

multiple roles, there will be some

conflict.

So, it will detect security leaks,

and it will suggest how to design. And

this is what we can create. So, we can

create a DAX which is like switching.

Whenever the role is admin, makes it

make it true, that means it's there. If

it is manager, then work on this

particular condition. If it is auditor,

then work on this condition. Right? So,

this is how we can utilize AI to make it

faster and optimized in terms of

security. All right. So, there are many

more ways of how AI can be utilized for

this particular

problem, but one most important thing is

documenting. So, if you want to document

your security architecture, it is very

useful. So, I've already created this

particular document in Notion. I'll just

open that. So, what it does is So, this

is the prompt which I created over here.

So, this is like creating a professional

well-structured PDF document for Power

BI security implementation. Document

should write a clear consulting style

format suitable for businesses. It It

should have executive summary. This

should explain

the purpose of document, importance of

data security using RLS and OLS. So, you

have to mention how many stores are

there in this particular continent,

role-based columns restrictions. What is

the architecture overview? This is

important. Then, what is your RLS

implementation in this particular Power

BI

project? What is the OLS implementation?

Then, the mapping table design. So, if

there is a new person coming in your

team, he should know what's happening

inside and how to

continue from there. And then,

deployment checklist and testing phase

and finally auditing and best practices.

So, I've already created this prompt.

I'll just copy this prompt in Claude or

ChatGPT, it will give me the document.

So, this is how the document looks like.

This is the PDF, and it is a four-page

PDF. And then, of course, you can modify

the the prompt and the way you want, and

it will show you that. So, it will show

you executive summary that is there,

then architecture overview,

then RLS implementation, OLS

implementation. It will show you the

mapping table. Then, it will also show

you the deployment checklist, audit

trail template, how it's done, and

finally the best practices and to be

taken care of for this particular

project. So, this is important in terms

of documenting whenever there is a large

team and you want to

do knowledge transfer and new person is

coming in, this is very useful. All

right. So, I'll just conclude this video

with some key takeaways. First of all,

RLS is row-level security. It filters

rows based on the user context. OLS, it

hides columns or tables from users. One

user is equal to one role, multiple

roles is unpredictable. So, that's what

I was saying. If you have multiple

users, so for example, if one user is in

multiple roles, that will give you some

conflict. So, we should not have that.

Always test on Power BI service. So,

whenever you're testing on desktop,

sometimes there's a caching problem or

something, then generally it might

give you wrong answers, but always test

on

Power BI services. So, once you are on

Power BI services, go to security and go

to all the roles and click on test as

role, and that should give you the best

answer.

Claude saves 80 to 90% of your time. So,

whenever I work on these aspects, I try

to utilize all of the AI tools and see

if

that is helping me reduce the time of

preparation. Right? Using Tim Bill for

complex OLS setups. This is really nice.

And documenting your security

architecture. This is what I explained

previously. And validate quarterly. So,

it is important for us to

check them regularly so as to see if

there are some changes and you want to

modify any business requirement changed,

then you need to modify all those

things. So, this is what this video was

for. If you like such videos, please

like, subscribe, and help me grow on

this channel. Thanks a lot for watching

this video.