00:00:00
hello everyone welcome to details I hope
00:00:02
you are all doing fine in this video we
00:00:03
are going to learn about static and
00:00:05
dynamic RLS that is Rule level security
00:00:07
what we are going to do we are going to
00:00:09
apply static RLS and then I Dynamic RLS
00:00:12
on this report I will I'm going to show
00:00:14
how to apply this RLS that is static and
00:00:17
dynamic on this report and then publish
00:00:19
it to power Pi service and we are also
00:00:22
going to see how you are going to test
00:00:23
it like how whether your static RLS is
00:00:26
working fine or not or whether your
00:00:28
Dynamic RLS is working fine or not we
00:00:30
are going to see first implement the RLS
00:00:32
that is static and dynamic then we are
00:00:34
going to test whether these RLS are
00:00:37
working fine or not so without wasting
00:00:39
time let's get started with this video
00:00:40
so before starting the video I would
00:00:42
request you to search datables and power
00:00:45
bi on the Google and you can see there
00:00:47
are different links of from the
00:00:48
databools.com that is PLC Android exam
00:00:50
questions here we have compiled the most
00:00:52
important exam questions related to PL
00:00:54
300 exam and then top 50 power bi
00:00:57
interview questions which has asked in
00:00:59
interview
00:01:00
and there are multiple articles related
00:01:03
to Power Pin so please do visit
00:01:04
databools.com for powerpay related
00:01:07
videos and articles
00:01:09
so without wasting time let's get
00:01:10
started with our video
00:01:12
[Music]
00:01:16
so this is our dashboard where we have
00:01:18
country filter product filter and
00:01:19
financial aid filter and we are we are
00:01:22
showing here sales current units YTD
00:01:24
units previous here YTD units and sales
00:01:27
by country sales by product sales by
00:01:29
current year units by quarter and sales
00:01:31
by discount Trend so let's see how our
00:01:33
data looks or our model looks
00:01:36
so you can click on this model view so
00:01:38
this is our model view you can see we
00:01:40
have one Financial table this is our
00:01:41
fact table and we have three or four
00:01:43
dimension table that is dimension
00:01:45
country Dimension product date Dimension
00:01:48
and then security table which is
00:01:50
filtering entry table
00:01:52
so let's go to our report so let's see
00:01:55
what is the difference between static
00:01:57
and dynamic RLS so in static RLS what
00:01:59
you you are going to do let's say you
00:02:01
create a rule product based on the
00:02:03
country and the data will be friends the
00:02:05
country will be friends
00:02:07
so this you created a tool with country
00:02:09
equal to friends so people assigned to
00:02:11
that role will only be able to see the
00:02:13
data for Country friends whereas in
00:02:16
Dynamic RLS you will create a role based
00:02:18
on the country
00:02:19
but there will be another security table
00:02:21
where you will be having the mail IDs of
00:02:24
the persons and the country value for
00:02:27
which they are allowed to see the data
00:02:29
so what power bi is going to do power bi
00:02:32
is automatically dynamically will find
00:02:34
the mail IDs of the user who is logged
00:02:36
in in the powerpay by using user
00:02:38
principle name function then it is going
00:02:40
to filter out the report based on the
00:02:43
security table and and it will go to the
00:02:46
security table it will search for the
00:02:47
that mail ID and see like for which
00:02:50
country the user is allowed to see the
00:02:52
data and it will filter the report based
00:02:54
on that country
00:02:56
so this is the difference between static
00:02:58
RLS and dynamic RLS so let's create by
00:03:01
Static RLS rules first
00:03:04
we will go to this manage tool in
00:03:06
modeling tab
00:03:07
then we will click on this create rule
00:03:09
let's say we create a ruler
00:03:12
friends country like people in this role
00:03:16
will only be able to see the data for
00:03:17
Country friends so what we are going to
00:03:20
do we are going to do this dim Dimension
00:03:22
country we will go to this filter this
00:03:25
three dots then we'll click on the add
00:03:28
filter and the column name for which we
00:03:30
want to add filter this is the column
00:03:32
name is country we will click on this so
00:03:35
this this will be added like country
00:03:36
equal to Value so here we want to we
00:03:39
need to add the values for which we want
00:03:41
to apply the filter so we will apply
00:03:43
filter friends so what this rule is
00:03:46
going to do it it is going to filter the
00:03:48
Dem country based on the uh this Dax
00:03:51
function that is country equal to
00:03:53
friends so the data will be filtered for
00:03:55
only friends country so let's save this
00:03:58
rule
00:03:59
let's check this this is okay let's save
00:04:01
this rule now what we are going to use
00:04:05
we are going to test this rule so we are
00:04:07
going to click on this view as a rule
00:04:09
now we will click on this friends
00:04:11
country and check this role
00:04:15
now you can see the data is filtered for
00:04:17
friends
00:04:21
now stop viewing this rule let's create
00:04:23
new role where you have you are
00:04:26
filtering the country by friends and
00:04:28
product by let's say Montana
00:04:30
so we will click again on this one
00:04:33
manage tools create error countries
00:04:36
friends let's name it static
00:04:44
friends
00:04:45
and Montana
00:04:48
let's create this rule
00:04:51
now what we are going to do we are going
00:04:53
to set a filter on two tables like first
00:04:55
is country second is product so we'll
00:04:58
again go to this then click on country
00:05:01
column this is the column name of for
00:05:04
which we need to set the filter let's
00:05:06
put it friends
00:05:08
check this then go to this product table
00:05:10
click here add filter choose the column
00:05:13
on which you need to apply filter
00:05:15
and then put value Montana
00:05:19
save this rule
00:05:22
now
00:05:23
the rule is applied
00:05:26
now test this rule using this Vos column
00:05:28
now when you click on the static fence
00:05:30
in Montana
00:05:33
you you will see that the report is
00:05:36
filtered based on friends and Montana
00:05:38
now what you are going to do you you are
00:05:41
going to publish this report into Power
00:05:43
bi service
00:05:44
let's see if the changes now we what we
00:05:48
are going to see we are going to see how
00:05:49
to assign people to this these rules so
00:05:52
we will publish this report in my
00:05:53
workspace
00:05:54
so the report is published
00:05:57
let's check yes it is published
00:06:00
now go to my workspace we have the sales
00:06:03
dashboard
00:06:04
now we will go to click on this three
00:06:07
dots in this data set column of Cs code
00:06:10
to we'll click on the security
00:06:13
now you can see here are the rules which
00:06:15
we created in power bi desktop so what
00:06:18
you need to do let's say you want to
00:06:19
assign people to this role
00:06:21
you will enter their mail ID let's say
00:06:25
wait
00:06:27
so this is our mail ID let's enter and
00:06:30
we will add
00:06:31
now this this person is added to this
00:06:34
rule you can click here and test this
00:06:37
rule
00:06:38
save changes
00:06:45
now when I login this through this rule
00:06:48
you can see I'm only able to see the
00:06:50
data related to product friends and
00:06:52
Country friends and product Montana so
00:06:55
we have seen like how we can create
00:06:57
different role in static RLS
00:06:59
but the issue here is let's say you want
00:07:02
to make different rules like one for
00:07:04
friends one for Germany one for Canada
00:07:06
one for USA and then for Mexico so you
00:07:08
need to create five different rules and
00:07:10
let's say you need to create multi uh
00:07:13
same for the product also you need to
00:07:15
create different roles now this is a
00:07:18
problem to create five row let's say you
00:07:20
have 100 countries then you need to
00:07:22
create 100 rules so this problem solves
00:07:24
with Dynamic RLS where we just need to
00:07:27
create one rule and that will fulfill
00:07:29
our demand for filtering with different
00:07:32
countries so let's say how to achieve
00:07:34
this so for dynamic RLS we need another
00:07:37
table that is security table so let's
00:07:39
check what is there so in security table
00:07:42
we have email IDs of different users of
00:07:44
the report and what are the access they
00:07:46
have so you can see this XYZ has the
00:07:49
access of country Germany so he is going
00:07:51
to see the data for Germany country why
00:07:53
ZX is going to see the data from Mexico
00:07:55
and YX is going to see the data for
00:07:58
Germany so what we need to do we need to
00:08:01
filter uh the country table the country
00:08:04
Dimension table based on this table so
00:08:06
what what we are going to do we will
00:08:07
connect this security table with country
00:08:09
table and make the direction from
00:08:12
security table to Country table so in
00:08:15
RLS we will filter uh this security
00:08:18
table by mail ID so let's say we filter
00:08:20
it by XYZ so it will filter this table
00:08:24
with con Germany Rule and then in result
00:08:28
security table will filter Dimension
00:08:30
country with Germany and then Dem
00:08:32
country will filter Financial table with
00:08:35
Germany filter so a Germany filter will
00:08:37
be applied on the fact data and in
00:08:39
report you are going to see the data for
00:08:41
Germany only so let's see how to achieve
00:08:43
this
00:08:44
now let's create the dynamic roll on
00:08:46
Country so we'll click on this manage
00:08:48
tool
00:08:50
we will this is always created so let's
00:08:52
delete this first
00:08:55
so we will create a new rule
00:08:59
dynamic
00:09:01
on country
00:09:04
we'll create this rule
00:09:06
now go to this role Dynamic country and
00:09:09
we will filter the security table so
00:09:11
what we will do we are going to click on
00:09:13
this three dots
00:09:14
and we are what we are going to do we
00:09:16
are going to do email IDs and on based
00:09:20
on those email IDs country table country
00:09:22
Dimension table will be filtered so
00:09:24
we'll click on this email value ID value
00:09:27
then we will click on this value and we
00:09:30
will write user
00:09:32
principle
00:09:34
name
00:09:36
so user principal name is a Dax function
00:09:39
what it will do it will fetch the email
00:09:40
ID of the user who is logged into the
00:09:43
power bi
00:09:44
we will check this and then save this
00:09:48
so the rule is applying
00:09:51
now let's see our data so we can see
00:09:54
like XYZ at the datables.com is going to
00:09:57
able to see the data for
00:09:59
country Germany so let's test this rule
00:10:01
we will click on this view as
00:10:04
then we will click Dynamic country and
00:10:06
then the other user like for which we
00:10:09
are testing this data so we'll write his
00:10:13
email ID
00:10:16
and then click ok
00:10:19
so you can see
00:10:21
now viewing as Dynamic country Rule and
00:10:24
XYZ logged in so you can see he is able
00:10:27
to see the data for Country Germany and
00:10:29
but these are the different products
00:10:32
now let's check for different user
00:10:35
so we have another user here with izxi
00:10:39
and he is going to see the data from
00:10:41
Mexico
00:10:42
so let's click here view as
00:10:45
Dynamic country other user
00:10:49
yzx at the rate databools.com
00:10:55
click ok now we are going to we what we
00:10:58
are seeing we are showing a role as
00:11:00
Dynamic country and the user logged in
00:11:02
is yzx at 3 datables you can see that
00:11:05
the data is filtered for Mexico
00:11:08
then we will publish this report again
00:11:11
the report is published to our workspace
00:11:13
we will go to workspace
00:11:16
then we will click here on the three
00:11:18
dots on the data set then in security
00:11:22
you can see we have a new rule Dynamic
00:11:25
country so you can add people here you
00:11:27
just need to add people here and they
00:11:29
will be filtered based on the security
00:11:31
table so we have created a dynamic role
00:11:34
for country now let's say we need to
00:11:35
create a dynamic reel based on country
00:11:37
as well as product
00:11:40
now the problem here is right now we are
00:11:42
filtering this security table
00:11:44
dynamically and it is filtering this
00:11:45
country table and this country table is
00:11:48
filtering the perfect table now we want
00:11:50
to filter country table as well as
00:11:52
product table simultaneously because in
00:11:54
a single rule we need to filter country
00:11:57
and product but the problem is when we
00:12:00
are going to make a relationship between
00:12:01
country and product power bi is not
00:12:04
going to allow us to make this
00:12:05
relationship you can see this is a
00:12:07
dotted line 2 because both are filtering
00:12:10
this table so power bi will not allow to
00:12:12
do this so what we can do we will delete
00:12:14
both of these relationship
00:12:17
so we have deleted both our relationship
00:12:19
now security table is not connected to
00:12:21
any of these table now let's look at our
00:12:23
security table how our security table
00:12:25
looks like so what what we are doing
00:12:27
here we want to filter uh this email ID
00:12:31
bike country as well as product so when
00:12:34
x y z is logged in he is going to see
00:12:37
the data for this Germany country and
00:12:39
product Montana similarly for when y z x
00:12:41
is logged in he is going to see the data
00:12:42
for Mexico country and product ratio
00:12:45
now let's go to this manage tools
00:12:48
so we have already created one rule that
00:12:51
is dynamic rule let's change its name
00:12:56
dynamic
00:12:58
product and Country
00:13:00
[Music]
00:13:02
so what we are going to do we are going
00:13:04
to filter both country and product based
00:13:07
on this Dynamic tool so we had wrote
00:13:09
down one next statement so let's
00:13:11
understand what this text is going to do
00:13:13
so what what our objective is so when a
00:13:16
user logged in with a username Dex will
00:13:19
fetch its username and filter that and
00:13:21
filter this country based on the
00:13:24
username and the security table so let's
00:13:25
say XYZ is logged in so what text will
00:13:28
do Dex will fetch the user ID it's let's
00:13:31
say XYZ and mail our ID XYZ at the
00:13:34
readables.com it will search the
00:13:36
security table like for which country
00:13:39
this XYZ had permission to view the
00:13:41
report it will fetch the country name
00:13:43
and then with that country name it will
00:13:45
filter this country report so what we
00:13:47
are doing here we are fetching the
00:13:49
user ID and we have saved this user ID
00:13:53
in this user role now what we are doing
00:13:56
we are filtering this security table
00:13:58
this table based on the email ID we have
00:14:00
fetched that is username and then we are
00:14:03
fetching the value of country for which
00:14:06
this user is allowed in the security
00:14:07
table so through this in this RLS in
00:14:11
this RLS variable we have saved the
00:14:14
country details country name for which
00:14:17
this user is allowed now what we are
00:14:19
doing we are filtering this country
00:14:21
table with this country value
00:14:24
similarly for product we wrote down this
00:14:27
text we are uh we are filtering the
00:14:30
security table first then we are taking
00:14:33
out the detail of product for which this
00:14:35
you email ID has access and we are
00:14:38
saving this product detail in RLS value
00:14:39
and then this uh we are filtering this
00:14:43
product table based on this RLS value
00:14:46
so this is the dynamic axis when we want
00:14:48
to filter two tables simultaneously
00:14:50
so now let's check this rule
00:14:52
we will go to view as
00:14:56
the rule is saving first we will go to
00:14:58
this view as tab then we will click on
00:15:00
other users and dynamic country and
00:15:02
product we will type the name XYZ at the
00:15:05
rate dataverse.com
00:15:08
click OK here
00:15:12
now you can see the report is filtered
00:15:15
based on the two rules that is country
00:15:17
and product so XYZ it has the access of
00:15:20
Germany country and Montana product so
00:15:22
that's all for this video we have seen
00:15:24
three types of RLS here like first is
00:15:27
static RLS then we have created a
00:15:29
dynamic RLS based on the country then we
00:15:31
have created another Dynamic RLS based
00:15:34
on two uh two Dimension like one this
00:15:37
country and another is product so guys
00:15:38
that's all for this video If you find
00:15:40
this video insightful then please do
00:15:42
like this video And subscribe to our
00:15:43
Channel and feel free to ask anything in
00:15:46
comment section we will try our best to
00:15:47
answer all of them check us out on
00:15:49
different social media platform like
00:15:51
LinkedIn Facebook and Twitter and check
00:15:52
our website status datables.com so we'll
00:15:55
see you in our next video thank you
