Row Level Security in an On-Premises Analysis Services tabular model in Power BI
Using a sample dataset to work through the steps below, to implement row level security in an on-premises ?Analysis Services Tabular Model?and use it in a Power BI report.
For implementing this as an example we require Adventureworks Database
Step 1: Create the user security table and define data relationship
You can find many articles describing how to define row-level dynamic security with the?SQL Server Analysis Services (SSAS) tabular?model. For our sample, we use Implement Dynamic Security by Using Row Filters.
The steps here require using the Adventureworks relational database.
Once you create and save the table, you need to establish the relationship between the?DimUserSecurity?table's?SalesTerritoryID?column and the?DimSalesTerritory?table's?SalesTerritoryKey?column, as shown below.
In SSMS, right-click?DimUserSecurity, and select?Design. Then select?Table Designer?>?Relationships.... When done, save the table.
Add users to the table. Right-click?DimUserSecurity?and select?Edit Top 200 Rows. Once you've added users, the?DimUserSecurity?table should appear similar to the following example:
Next, do an?inner join?with the?DimSalesTerritory?table, which shows the user associated region details. The SQL code here does the inner join, and the image shows how the table then appears.
SQL: select b.SalesTerritoryCountry, b.SalesTerritoryRegion, a.EmployeeID, a.FirstName, a.LastName, a.UserName from [dbo].[DimUserSecurity] as a join [dbo].[DimSalesTerritory] as b on a.[SalesTerritoryID] = b.[SalesTerritoryKey]
The joined table shows who is responsible for each sales region, thanks to the relationship created in Step 2. For example, you can see that?Rita Santos?is responsible for?Australia.
Step 2: Create the tabular model with facts and dimension tables
Once your relational data warehouse is in place, you need to define the tabular model. You can create the model using SQL Server Data Tools(SSDT).
2. Once you've imported the necessary tables, you need to define a role called?SalesTerritoryUsers?with Read permission. Select the?Model?menu in SQL Server Data Tools, and then select?Roles. In?Role Manager, select?New.
3. Under?Members?in the?Role Manager, add the users that you defined in the?DimUserSecurity?table in Step 1
4. Next, add the proper functions for both?DimSalesTerritory?and?DimUserSecurity?tables, as shown below under?Row Filters?tab.
5. The?LOOKUPVALUE?function returns values for a column in which the Windows user name matches the one the?USERNAME?function returns. You can then restrict queries to where the?LOOKUPVALUE?returned values match ones in the same or related table. In the?DAX Filter?column, type the following formula:
DAX: =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
In this formula, the?LOOKUPVALUE?function returns all values for the?DimUserSecurity[SalesTerritoryID]?column, where the?DimUserSecurity[UserName]?is the same as the current logged on Windows user name, and?DimUserSecurity[SalesTerritoryID]?is the same as the?DimSalesTerritory[SalesTerritoryKey].
The set of Sales?SalesTerritoryKey's?LOOKUPVALUE?returns is then used to restrict the rows shown in the?DimSalesTerritory. Only rows where the?SalesTerritoryKey?value is in the IDs that the?LOOKUPVALUE?function returns are displayed.
领英推荐
6. For the?DimUserSecurity?table, in the?DAX Filter?column, add the following formula:
DAX: =FALSE()
This formula specifies that all columns resolve to?false; meaning?DimUserSecurity?table columns can't be queried.
Step 3: Add Data Sources within your On-premises data gateway
Once your tabular model is deployed and ready for consumption, you need to add a data source connection to your on-premises Analysis Services tabular server.
With this procedure complete, the gateway is configured and ready to interact with your on-premises Analysis Services data source.
Step 4: Create report based on analysis services tabular model using Power BI desktop
3. Fill in your Analysis Services tabular instance details and select?Connect live. Then select?OK.
With Power BI, dynamic security works only with a live connection.
4. You can see that the deployed model is in the Analysis Services instance. Select the respective model and then select?OK.
Power BI Desktop now displays all the available fields, to the right of the canvas in the?Fields?pane.
5. In the?Fields?pane, select the?SalesAmount?measure from the?FactInternetSales?table and the?SalesTerritoryRegion?dimension from the?SalesTerritory?table.
6. To keep this report simple, we won't add any more columns right now. To have a more meaningful data representation, change the visualization to?Donut chart.
7. Once your report is ready, you can directly publish it to the Power BI portal. From the?Home?ribbon in Power BI Desktop, select?Publish.
Step 5: Create and share a dashboard
You've created the report and published it to the?Power BI?service. Now you can use the example created in previous steps to demonstrate the model security scenario.
In the role as?Sales Manager, the user Grace can see data from all the different sales regions. Grace creates this report and publishes it to the Power BI service. This report was created in the previous tasks.
Once Grace publishes the report, the next step is to create a dashboard in the Power BI service called?TabularDynamicSec?based on that report. In the following image, notice that Grace can see the data corresponding to all the sales region.
Now Grace shares the dashboard with a colleague, Rita, who is responsible for the Australia region sales.
When Rita logs in to the Power BI service and views the shared dashboard that Grace created, only sales from the Australia region are visible.
Congratulations! The Power BI service shows the dynamic row-level security defined in the on-premises Analysis Services tabular model. Power BI uses the?EffectiveUserName?property to send the current Power BI user credential to the on-premises data source to run the queries.