The ability to implement Row Level Security is one of the most important features of Power BI data modeling. There are multiple ways to achieve it depending on the situation and how the data is available.
The simplest form of Row Level Security (RLS) is defining the rules manually in the Role definitions. This is called Static Row Level Security.
In the above Role named "California", all the users who are part of this Role (added in Power BI Service), will see only the data related to California and nothing else.
This is very simple and straight-forward. However, it is seldom the case that life throws such tasks at you. :)
Typically, one may need to dynamically enforce security based on pre-defined associations with underlying data. Users may be associated with geography or some other aspect in a dataset, which can then be used in the model to dynamically filter the data based on who accesses the report. This is called Dynamic Row Level Security. We can implement this in different ways.
The simplest way is to leverage user information from within the existing table in the model. Say, in our Adventure Works example, in the SalesTerritory table, if we have a Sales Rep column with email information.
The Role can then be defined with a filter on SalesTerritory table:
The user login is passed through the built-in function USERPRINCIPALNAME(), which is compared with the Sales Rep email, resulting in the appropriately filtered data in the visuals.
This seems easy too. However, often the access information is maintained in a separate dataset.
Consider the example of Sales Reps being assigned Sales Territory Groups in a separate table. This table can be added to the data model as shown below.
The Role is then defined by adding a filter to the SalesRep table on the email column. This is the login of the user accessing the Power BI Report.
This performs in the similar way as above. Easy peasy.
But, what if there are multiple security tables that we need to use together. In the case of Adventure Works, say we need to control access based on Product Color (Huh!) in addition to Sales Territory.
Notice that the list of users is not the same as that in the Sales Rep table, though there are some overlaps.
Get the Product Color access table into the data model and add the filter condition to the Role.
Now, viewing the report as Adam results in the below, with the combination of Color and Geography that he has access to.
Robin, who is present only in the Access By Color table cannot see any data:
That is, the rules are applied together as an AND operation. Well and good if this is what we want. But what if we want to apply the security in such a way that the users have the highest of the possible access based on multiple rules from different tables?
We can achieve that by using DAX directly on the fact table. No relationships to the security tables are required as we will be using the LOOKUPVALUE() function.
This is in addition to having the USERPRINCIPALNAME() filter on both the security tables.
In order for this to work, the fact table should directly have the actual columns that has the security set against, in this case Product Color and Sales Territory Group. This can be achieved by first adding calculated columns to the fact table using RELATED function. We are able to achieve the OR operation for the filters by bypassing the dimensions altogether.
Now, the results of applying the security for Adam and Robin look quite different. Adam can see data for all products for Australia and only Blue and non-colored products for rest of the countries.
Similarly, Robin can now see data for all countries of Red and multi-colored products.
The simplest form of Row Level Security (RLS) is defining the rules manually in the Role definitions. This is called Static Row Level Security.
In the above Role named "California", all the users who are part of this Role (added in Power BI Service), will see only the data related to California and nothing else.
This is very simple and straight-forward. However, it is seldom the case that life throws such tasks at you. :)
Typically, one may need to dynamically enforce security based on pre-defined associations with underlying data. Users may be associated with geography or some other aspect in a dataset, which can then be used in the model to dynamically filter the data based on who accesses the report. This is called Dynamic Row Level Security. We can implement this in different ways.
The simplest way is to leverage user information from within the existing table in the model. Say, in our Adventure Works example, in the SalesTerritory table, if we have a Sales Rep column with email information.
The Role can then be defined with a filter on SalesTerritory table:
The user login is passed through the built-in function USERPRINCIPALNAME(), which is compared with the Sales Rep email, resulting in the appropriately filtered data in the visuals.
This seems easy too. However, often the access information is maintained in a separate dataset.
Consider the example of Sales Reps being assigned Sales Territory Groups in a separate table. This table can be added to the data model as shown below.
The Role is then defined by adding a filter to the SalesRep table on the email column. This is the login of the user accessing the Power BI Report.
But, what if there are multiple security tables that we need to use together. In the case of Adventure Works, say we need to control access based on Product Color (Huh!) in addition to Sales Territory.
Notice that the list of users is not the same as that in the Sales Rep table, though there are some overlaps.
Get the Product Color access table into the data model and add the filter condition to the Role.
Now, viewing the report as Adam results in the below, with the combination of Color and Geography that he has access to.
Robin, who is present only in the Access By Color table cannot see any data:
That is, the rules are applied together as an AND operation. Well and good if this is what we want. But what if we want to apply the security in such a way that the users have the highest of the possible access based on multiple rules from different tables?
We can achieve that by using DAX directly on the fact table. No relationships to the security tables are required as we will be using the LOOKUPVALUE() function.
OR(
'FactInternetSales'[ProductColor]=LOOKUPVALUE('AccessByColor'[ProductColor],
'AccessByColor'[User],USERPRINCIPALNAME(),'AccessByColor'[ProductColor],'FactInternetSales'[ProductColor])
,
'FactInternetSales'[SalesTerritoryGroup]=LOOKUPVALUE('SalesRep'[SalesTerritoryGroup],
'SalesRep'[SalesRep],USERPRINCIPALNAME(),'SalesRep'[SalesTerritoryGroup],'FactInternetSales'[SalesTerritoryGroup])
)
This is in addition to having the USERPRINCIPALNAME() filter on both the security tables.
In order for this to work, the fact table should directly have the actual columns that has the security set against, in this case Product Color and Sales Territory Group. This can be achieved by first adding calculated columns to the fact table using RELATED function. We are able to achieve the OR operation for the filters by bypassing the dimensions altogether.
Now, the results of applying the security for Adam and Robin look quite different. Adam can see data for all products for Australia and only Blue and non-colored products for rest of the countries.
Similarly, Robin can now see data for all countries of Red and multi-colored products.
MMORPG OYUNLARI
ReplyDeleteinstagram takipçi satın al
Tiktok jeton hilesi
tiktok jeton hilesi
antalya saç ekimi
referans kimliği nedir
İNSTAGRAM TAKİPÇİ SATIN AL
İnstagram takipçi
MT2 PVP SERVERLAR
smm panel
ReplyDeletesmm panel
İş ilanları
İnstagram takipçi satın al
hirdavatci
beyazesyateknikservisi.com.tr
SERVİS
Tiktok jeton hilesi indir