Forum Discussion
Workflow best practices - dataflows, and datasets, and RLS, oh my!
The approach you described makes sense. Creating golden datasets is a good way to achieve a self-service model (see article below). Dataflows are an excellent tool for harvesting and transforming data, and offer numerous advantages including reusability. Keep in mind that not all connectors in PBI Desktop are available in PBI Service, and in some cases the PBI Desktop connectors are more robust than those in PBI Service (e.g., in the PBI Service Snowflake connector, there's no gear icon to edit custom SQL). The workaround is to create the M code in PBI Desktop, copy it, and paste it into a blank query in PBI Service.
It's recommended to do report development in PBI Desktop. You can develop reports in PBI Service (based on a published dataset), but if the dataset gets deleted, reports created in PBI Service become permanently broken (see article below). PBI Service is a great option for ad hoc reports, but enterprise reports should be developed in PBI Desktop.
Row-level security can be implemented in various ways. The article below describes how to implement it dynamically, with a solution to the many-to-many relationship that may exist.
These are great discussion points, and I look forward to hearing from the community. 🙂
https://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/
https://nisorness.com/data/2020/11/19/how-to-prevent-the-deletion-of-datasets-in-power-bi
https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi
Hello! Thanks for your thoughtful and detailed reply.
The linked article on golden datasets does seem to be the route I would like to adopt. Long-term I think having datasets that users can re-use where we already have the tables modeled correctly and some well-formulated measures premade is a great way to ensure data is reported correctly across departments.
One thing I am struggling with in adopting this model is permissions. Given the size of our university, we could have hundreds to thousands of employees trying to access reports/dashboards/apps based on these underlying datasets. What is the best practice is setting up these permissions? I had thought it would be to:
- Have a workspace housing the golden datasets. The only people with access to this workspace would be our IT data folks creating/edit those.
- Have workspaces for functional teams
- Reports would be created for those functional teams that use the underlying dataset (published across workspaces)
- Users are unable to access these reports because it says they don't have permissions on the underlying dataset
Is the only way to ensure access to reports is to allow access to all? Is there an easy way to do this without opening the workspace up to everyone?
Having a golden workspace and multiple workspaces for functional teams is the approach I use. You'll need to grant Build permission to users so they can access datasets in the golden workspace.
https://docs.microsoft.com/en-US/power-bi/connect-data/service-datasets-build-permissions
