Sue Bayes
February 3, 2022
A best practise within Power BI is to separate the dataset from the Report. There are many reasons for this but primarily it allows you to use the model to build different reports for different users with the same business logic applied to the imported data. In essence you build a “golden dataset” with consistent measures and dimensions.
For more information on this I recommend reading this post by Melissa Coates from Coates Data Strategies . If you are interested in learning more about Governance and Security I can thoroughly recommend this course which talks you through the different options for managing and governing data within Power BI.
There are additional considerations that need to be thought about when using one dataset to feed and create many different “thin” reports. (Called thin because the reports attach to the Power BI dataset that is sat in the Power BI Service. The data is not held in the thin report, it is therefore “thinner”).
One key aspect is how to manage Row Level Security and Object Level Security for each report.
Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. Click here for more information.
Object-level security (OLS) enables model authors to secure sensitive tables or columns from report viewers– sensitive data, for example includes personally identifiable information (PII). Click here for more information.
When you set RLS, you apply a DAX measure to a dimension within Power BI desktop to specify how to filter the report for that particular user, for example, you could set the report to only show information for a specific user or department. There are many blogs and videos to choose from, links below for my particular favourites.
Once the report is published to the Service, for RLS to be effective the RLS or OLS role needs to be mapped to the user or security group within the Security settings of Power BI service at the data set level.
The User / Security Group must have either:
This gives access to the dataset which gives access to the data. The preferred option is to give ‘Read’ access at the dataset level within the the dataset workspace for a .
You also need to consider how your users are now going to view the data. You need to give permission in two places – once to access the data at dataset level and then again to access the report / app. There are two permission setting activities. So if you store your dataset in one workspace and your report in another then you need to ensure access has been defined for both areas.
Whether you chose an App or a Report, you need to assign Viewer access to your User or Security Group to allow them to access the report and to ensure that RLS or OLS is applied.
Once you have defined access to the data, access to the report /app, you then need to add someone as a member to a specific RLS or OLS role using the Security menu. RLS or OLS narrows down which row or which Object a member of that role can see given that they already have Read permission on the dataset.
If RLS or OLS exists, the user would see nothing (for the secured content) unless they have been given access from the Security menu to that Role. If RLS or OLS does not exist, with Read permission the User would see everything in the dataset.
RLS or OLS is set for every Report at the dataset level. One dataset, therefore one set of RLS or OLS with DAX measures used to add complexity as required. Reza Rad has a fantastic book on how to use DAX to build hierarchical RLS and I can also recommend this presentation by Mathias Halkjaer that I saw at SQLBITs but what happens if you want to use the dataset but don’t want to use RLS for a specific Report? You may need RLS for one set of measures within a report but perhaps want to show totals of something else within another report for example.
There are a few options that you can consider:
One of which would be to have another copy of dimension tables that don’t have RLS added to them within the model and for the reports that don’t require RLS, you use those dimensions. However, this could be difficult to manage and would perhaps be confusing to set up for Self Service BI users.
Another option could be to have aggregated fact tables that aren’t attached to the dimension tables, and are therefore unable to give a lower level of granularity. This would give the ability to have RLS and have totals from the same dataset, but could bulk out your model and could be confusing when deciding which measure to use.
At time of writing, my preferred choice would be to publish the same dataset twice, to two separate workspaces and have one Workspace with a set of permissions as Viewer for the RLS dataset, for the other Workspace and dataset (from the same source) set the permissions to be that of a Member.
To ensure that RLS is applied, the Users / Groups need to be set as Viewers. If User / Groups are set as Member, RLS is not applied. There is only one dataset to manage at pbix level.
Any questions, happy to help,
Sue
Thank you so much to Melissa and Dan for helping me wrap my head around this in detail.
February 3, 2022
