Dynamic row-level security enables report authors to filter data based on the user roles. This prevents developers from having to maintain separate security matrices. This also avoids the complexity of maintaining users under different security roles. Since most organizations have the required information about users in their databases, implementing dynamic-row level severity will be an easy task.
About the demonstration
In this blog post, I will demonstrate a scenario where we filter data for different levels in the sales hierarchy of an organization. For example, a sales manager can be given access to view records of all sales representatives, whereas sales representatives will be given limited access so they can only see their personal records.
This post will cover how to secure a data model based on the organizational hierarchy in a parent-child relationship, a relationship also observed in the DimEmployee table under AdventureWorksDW2014 database.
The following diagram shows a tabular model of the AdventureWorksDW sample database.
1. Setting up the data model
1.1 Adding the tables to the data model You need to add the following tables to your data model.
- DimSalesTerritory
- DimDate
- DimProduct
- DimEmployee (This table contains the organization structure)
- FactInternetSales
To keep this demonstration simple, I have only added few columns from these tables. 1.2 Creating a flat structure Creating a flat structure will make it easier to evaluate the organization hierarchy from the EmployeeKey and the ParentEmployeeKey structures, which exist in the DimEmployee table. It is also important to note that SSAS Tabular projects do not support self-referential relationships as seen in the DimEmployee table. When importing a table which has a foreign key constraint, SSDT throws an error. This can be ignored. To support self-referential relationships, the DAX language provides a PATH function.
The PATH function simply accepts two parameters: the [Id] column and the [parent id] column. In this case, the column function will look like this: =PATH(DimEmployee[EmployeeKey],[ParentEmployeeKey]) Create a calculated column in the model as shown in Figure 02, and you will see it creates a delimited value with the key combination going up to the highest level in the structure. This column will be used to validate whether the logged in user has access to the relevant data. 1.2.1 Creating a user role Now that we have created a model, we need to create the relevant user role to which we should give access and apply the filtering rule. To create a user, right-click the Roles folder in the Tabula Model explorer and set the role name, and set the permission to ‘Read.’
To enable user filtering, add the following DAX command in the DAX filter section of the DimEmployee table. =PATHCONTAINS(DimEmployee[Org Hierarchy],LOOKUPVALUE(DimEmployee[EmployeeKey],DimEmployee[LoginID],USERNAME()))
It is important to note that the DAX filter expression for a role always needs to resolve to a Boolean result, i.e., True or False. To achieve this, you need to configure the following functions when adding the DAX command.
USERNAME – Returns the domain username for the user browsing the model
LOOKUPVALUE – Similar to a VLOOKUP in Excel, returns a value. In this case, the [EmployeeKey] for the row that meets all criteria specified by the search_columnName, in this case, the [LoginId] and search_value. The search_value is the USERNAME function mentioned above.
PATHCONTAINS – Returns a Boolean result by evaluating an item ([EmployeeKey] from the above LOOKUPVALUE) in a PATH ([Org hierarchy] created column above) if it exists.
2. Testing the data model
To test the data model, we need to deploy this model to an SSAS Tabular instance which is on a Windows Active Directory domain. Once deployed, you can test this by browsing the data model from an SQL Server Management Studio (SSMS) connecting as a different user. To demonstrate, I have updated the [LoginId] column in the [DimEmployee] table of AdventureWorksDW database to match users in my own domain, as shown below.
When browsing the model from SSMS as myself, who I assigned to the ‘North American Sales Manager’ role as shown in the above model (Figure 05), I only values for North America.
When browsing the model as a ’European Sales Manager,’ SSMS only shows European sales values.
When I browse the model as the ‘Vice President of Sales’, who is the manager of both North American and European sales managers, I can see sales values for all regions including Europe, and North America.
Author: Stefan Outschoorn Consultant-Project Delivery
