User-Based Security Inside Power BI Dashboard
Kartheek T
August 8, 2016
With the wide adaption of Power BI, reports from the data extracted various data sources and was shown at an enterprise level, this gave an important need of reporting model to be at an enterprise level.
One of such enterprise level reporting features being providing data security among the users, if a particular region manager is given access to the report, we need to make sure the report shows data relevant to that region, instead of showing data for all the region to that user.
In order to solve this issue,
We need to have 2 things in place 1) SSAS Tabular Model & 2) UPN Suffix
Firstly, we need to add the data into the SSAS Tabular Model (SSAS Also supports a same variety of Data as Power BI), we need to add the data association with the user’s Windows ID into a table (Ex: Region and User ID into a particular table).
We will then add a (READ ONLY Access) Role with a DAX Expression in SSAS Tabular Model to evaluate that the given regions associated to the users are shown accordingly at SSAS Browser Level.
Now create a Personal / Enterprise Gateway for Power BI to show this Role-based Security Database on Power BI.com.
In case the Windows Login into the SSAS Tabular Mode is different from the 0365 user login in Power BI, you need to create a UPN Suffix to a Forest.
1) Open Active Directory Domains and Trusts.
2) Right-click Active Directory Domains and Trusts in the Tree window pane, and then click Properties.
3) On the UPN Suffixes tab, type the new UPN suffix that you would like to add to the forest.
4) Click Add, and then click OK.
5) Once the UPN Suffix is added we have chosen the UPN name for all the required accounts.
6) Open Active Directory Users and Computers.
7) Right-click on the user name and click on properties.
Note: Make sure User logon is same as office 365 account EX: if the Office 365 account is Firstname.LastName@Test.com then the User logon name should be as Firstname.LastName
8) Goto Account tab, under User logon name click on the drop down as shown below and choose the UPN suffix name added and Click Apply
9) Repeat the steps 7 to 8 for all the required users
Now create new Power BI report from Power BI Desktop, make sure you use Direct Query in the Power BI desktop while creating the reports, this will give advantage of putting role based security at PowerBI.com, as every time when users log in, the report will go back to SSAS Database via the mentioned gateway, check the corresponding regions associated to the user and get the corresponding data and show the same PowerBI.com
The extra added advantage of this approach is Power BI size limitation of 250MB will not be applicable, as the SSAS database data source will be queried live, hence the Power BI File will have only report designed oriented code, and not the data extract.
To conclude, with this approach you can implement user based security model at Power BI.com, which means every user will see data / Slicers/reports related to him alone in PowerBI.com.
