1

How to Use RLS Roles and Filter Expressions for Dynamic User-Based Vis

 2 years ago
source link: https://datafloq.com/read/how-use-rls-roles-filter-expressions-dynamic-user-based-visibility-power-bi/18812
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Data security, wherein users or groups of users are prevented from viewing a portion of a dataset, is often a top requirement in Power BI deployments.

This article is a part of a larger chapter around dynamic user-based filter context techniques from the book, Microsoft Power BI Cookbook, Second Edition by Greg Deckler and Brett Powell. This cookbook helps you build effective analytical data models, reports, and dashboards using the advanced features of Power BI.

Technical Requirements

The following are required to complete the recipes in this article:

Defining RLS Roles and Filter Expressions

Data security should always be top of mind and BI teams and developers should strive to ensure that users are never granted greater access or permission than is necessary to perform a legitimate business function.

In the context of Power BI projects, the question "Who should be able to see what?" should be well defined and documented before any reports are published or read access to the dataset is granted. Additionally, there should be a well-defined process for creating and managing the security groups used in security implementations.

The data security of Power BI models comprises security roles defined within the model, with each role containing a unique set of one or more DAX filter expressions. Roles and their associated filter expressions are created in Power BI Desktop, and users or groups are mapped to security roles in the Power BI service. A single DAX filter expression can be applied to each table of a model within a given security role, with users having the option of being mapped to multiple security roles. The filter expressions applied to tables within a security role also filter other tables in the model via relationships defined in the model, like the filters applied to Power BI reports, and are applied to all queries submitted by the security role member.

This recipe contains an end-to-end example of configuring, deploying, and validating RLS roles, applicable to both Import and DirectQuery data models. Additional guidance on a consolidated security role table that improves the manageability of changing security requirements is also included.

Getting ready

To prepare for this recipe, follow these steps:

  1. Open a Power BI Desktop file locally and access the Power Query Editor by clicking on Transform Data in the ribbon of the Home tab

  2. Create a query named AdWorksDW similar to the following:

    let 
        Source = Sql.Database(“localhost\MSSQLSERVERDEV”, “AdventureWorksDW2019”) 
    in 
        Source

  3. Disable load on the AdWorksDW query and place it into a Data Sources query group

  4. Right-click the AdWorksDW query and choose Reference

  5. Rename the query Internet Sales and choose the FactInternetSales table

  6. Repeat steps 4 and 5, creating a Resellers query that points to the DimResellers table, a Reseller Sales query that points to the FactResellerSales query, a Sales Territories query that points to the DimSalesTerritory table, and a Dates query that points to the DimDate table

  7. Create a Customers query using the following code:

    let 
        Source = AdWorksDW, 
        dbo_DimCustomer = Source{[Schema=“dbo”,Item=“DimCustomer”]}[Data], 
        DimGeography = Table.ExpandRecordColumn( 
            dbo_DimCustomer, “DimGeography”,  
            {“DimSalesTerritory”}, {“DimSalesTerritory”}), 
        SalesTerritory = Table.ExpandRecordColumn( 
            DimGeography, “DimSalesTerritory”,  
            {“SalesTerritoryGroup”}, {“SalesTerritoryGroup”}) 
    in 
        SalesTerritory

  8. Create a Products query using the following code:

    let 
        Source = AdWorksDW, 
        Navigation = Source{[Schema=“dbo”,Item=“DimProduct”]}[Data], 
        Subcategory =  
            Table.ExpandRecordColumn( 
                Navigation, “DimProductSubcategory”,  
                {“EnglishProductSubcategoryName”, “DimProductCategory”},  
                {“Product Subcategory”, “DimProductCategory”} 
            ), 
        Category =  
            Table.ExpandRecordColumn( 
                Subcategory, “DimProductCategory”,  
                {“EnglishProductCategoryName”}, {“Product Category”} 
            ) 
    in 
       Category

  9. Move the Internet Sales and Reseller Sales queries to a Facts query group and the Sales Territories, Dates, Customers, Resellers, and Products queries to a Dimensions query group

  10. In the Other Queries group, create a Blank query called Calculations.

  11. Select Close & Apply from the ribbon of the Home tab

  12. Switch to the Model view and create a relationship between the SalesTerritoryKey columns in the Internet Sales and Sales Territories tables

  13. Create a relationship between the SalesTerritoryKey columns in the Reseller Sales and Sales Territories tables

  14. Create a relationship between the ProductKey columns in the Reseller Sales and Products tables

  15. Create a relationship between the ProductKey columns in the Internet Sales and Products tables

  16. Create a relationship between the DateKey column in the Dates table and the OrderDateKey in the Internet Sales table

  17. Create a relationship between the DateKey column in the Dates table and the OrderDateKey in the Reseller Sales table

  18. Create a relationship between the CustomerKey columns in the Internet Sales and Customers tables

  19. Create a relationship between the ResellerKey columns in the Reseller Sales and Resellers tables

  20. Save your work

Define and document the security role requirements to be implemented, and the members or groups of these roles. Use the bus matrix diagrams to help communicate what data is currently stored in the model. Validate that role security is indeed required (not report or model filters), given the risk or sensitivity of the data.

Do not confuse security role filters with the various other forms of filters in Power BI, such as report-, page-, and visual-level filters, as well as filter logic in DAX measures. RLS role filters are applied to all queries of security role members, effectively producing a virtual subset of the data model for the given role at query time. Given the performance implications of compounding security role filters with report query filters, all user experience and analytical filters should be implemented outside of the security role filters. Security filters should be exclusively used for securing sensitive data.

Figure 1: Model for RLS roles and filter expressions

How to Define RLS Roles and Filter Expressions

To implement this recipe, use the following steps:

  1. In the Report view, create the following measures in the Calculations table:

    Customers Row Count = COUNTROWS(‘Customers’) 

    Internet Sales Row Count = COUNTROWS(‘Internet Sales’) 

    Products Row Count = COUNTROWS(‘Products’) 

    Reseller Sales Row Count = COUNTROWS(‘Reseller Sales’) 

    Resellers Row Count = COUNTROWS(‘Resellers’) 

    Sales Territories Row Count = COUNTROWS(‘Sales Territories’) 

    Dates Row Count = COUNTROWS(‘Dates’)

  2. Create a simple Table visualization using the measures created in the previous step. An example is shown in Figure 2.

    Figure 2: Row count measures in a table visual of the Power BI data model

  3. Hide the Calculations column in the Calculations table.

  4. Click Manage roles in the ribbon of the Modeling tab.

  5. Click the Create button and name the new role United States Online Bike Sales.

  6. Create the following four table filter DAX expressions on the Customers, Products, Resellers, and Sales Territories tables, respectively:

    • [SalesTerritoryGroup] = “North America” 
    • [Product Category] = “Bikes” 
    • FALSE() 
    • [SalesTerritoryCountry] = “United States”

    Figure 3: Role security definitions for United States Online Bike Sales

  7. Click Create and name the new role Europe Reseller Mountain and Touring.

  8. Create the following three table filter DAX expressions on the Customers, Resellers, and Sales Territories tables, respectively:

    • FALSE()
    • [ProductLine] IN {“Mountain”,”Touring”}
    • [SalesTerritoryGroup] = “Europe”

  9. Click the Save button.

  10. Click View as in the ribbon of the Modeling tab, choose the United States Online Bike Sales role, click the OK button, and note that the row counts in the Table visualization change, with the Reseller Sales Row Count and Reseller Row Count measures being blank.Figure 4: Viewing the row count measures as a member of the United States Online Bike Sales role

  11. Click View as in the ribbon of the Modeling tab, choose the Europe Reseller Mountain and Touring role and uncheck the United States Online Bike Sales role, click the OK button, and note that the row counts in the Table visualization change with the Internet Sales Row Count and Customers Row Count measures being blank.Figure 5: Viewing the row count measures as a member of the Europe Reseller Mountain and Touring role
  12. Save the file, click Publish in the ribbon of the Home tab, and deploy to the Power BI service.

  13. Log in to the Power BI service and navigate to the workspace of the published report.

  14. Click the vertical ellipses next to the dataset and select Security.

    Figure 6: Opening the security settings for a published Power BI dataset in an app workspace

  15. Members for each role can be added by entering the email addresses of the users and clicking the Add button.

          Figure 7: Row-Level Security dialog

How it works

The filter expressions in row-level security (RLS) roles are always enforced and override any filtering logic in connected reports or DAX measures. BI developers responsible for a model with RLS roles defined should be able to communicate how the RLS logic impacts common reports and measures to report authors and other stakeholders.

Filters applied in security roles traverse relationships just like filters in Power BI reports and filters applied in DAX measures. For example, a security filter on a product dimension table will flow from the Products table (the one side of a relationship) to the many side (Internet Sales), but will stop there, and neither will they flow to other tables related to Internet Sales unless bidirectional relationships have been enabled between Internet Sales and these other dimension tables. Note that when viewing by either role or no roles, the Dates Row Count measure remains the same, because there is no direct filter applied—nor are any of the DAX filter expressions propagated to the Dates table through relationships.

The data model contains both internet sales and reseller sales, but each role should be restricted to their specific business process (fact table). Additionally, the United States Online Bike Sales role should be able to view North America customer details (Canada and United States), but only sales for United States customers purchasing products in the bike category. The Sales Territories filter ensures that members will only see sales data associated with United States customers. The Customers table filter allows the security members the option to view Canada and United States customers. The FALSE function is used to filter every row of the Resellers table, which also filters the related Reseller Sales table.

The two reseller table measures return a blank value, given the FALSE security filter. The Internet Sales table is filtered by the Products filter (Bikes) and the Sales Territories filter (United States). The Internet Sales table is also filtered by the Customers filter, but the Sales Territories filter is more restrictive.

Even for experienced Power BI developers and for relatively simple requirements, it can be helpful to apply a single security filter at a time and to observe the impact on row counts. A standard testing report page with row counts, and possibly fact table measures, can help expedite the process.

For the European Reseller Mountain and Touring role, The Resellers filter makes only rows with a ProductLine of Mountain or Touring visible. The IN DAX operator is a more intuitive and sustainable expression than the || symbol used as a logical OR operator in older versions of the language. The Internet Sales and Customers tables are blank due to the FALSE expression for the Customers table. Customers has a one-to-many, single-direction relationship with Internet Sales. Therefore, filters on the Customers table impact Internet Sales, but not other tables.

The Sales Territories table has three rows remaining (France, Germany, and United Kingdom) due to the Europe filter. The Reseller Sales fact table is impacted by both the Sales Territories filter and the Reseller ProductLine filter (Mountain or Touring). The filters from the Resellers and Sales Territories tables flow to the Reseller Sales table, but stop there and do not impact other tables.

In gathering security requirements, and again in a testing or quality assurance (QA) phase, communicate which tables are not impacted by the security filters to stakeholders. Users may falsely believe that a Products table security filter will also filter another dimension table, such as a Stores dimension, since only certain products are sold in certain stores. However, if the Stores table is queried directly and there is not a bidirectional relationship between Stores and a Sales fact table, all the stores would be accessible. Only when a sales measure is used in a visual would stores with blank values (given the Products filter) be discarded by default, and even then, a user could access these stores via Show items with no data setting. To secure these tables and avoid bidirectional cross-filtering for these relationships, additional table-specific security filters may be needed.

Summary

This article dived deep into the details of RLS roles and filter expressions through practical examples. It involved building and deploying dynamic, user-based security for both import and DirectQuery datasets as well as examples of dynamic filter context functionality to enhance the user experience.

About the Authors

Greg Deckler is Vice President of Cloud Services at Fusion Alliance and has been a technology systems consultant for over 25 years. Internationally recognized as an expert in Power BI, Greg Deckler is a Microsoft MVP for Data Platform and an active member of the Power BI community.

Brett Powell is the owner of and business intelligence consultant at Frontline Analytics LLC, a data and analytics research and consulting firm and Microsoft Power BI partner. He has worked with Power BI technologies since they were first introduced as the SQL Server 2008R2 PowerPivot add-in for Excel 2010. 

Image Credit: iStockphoto.com/anyaberkut/879809148

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK