We currently have about 20 reports planned for our first Power BI project.
Managing these all in one pbix file is manageable, however, some of our other projects could see dozens of datasets and hundreds of reports.
Wondering what you guys have done from the point of management for larger datasets (or ones that are growing)?
I know we can split the dataset/report into two files (linking the dataset file to the report file) to split the logic/viz.
While that's useful to some degree, our challenge is mostly related to RLS.
How would we ensure proper access via RLS with the entire dataset when our RLS has so many conditions.
For RLS:
- We have clients (via a Client -* Contacts table relationship) that limits them to only their client data.
- We have client managers (via Client *-* Users [type=manager] table relationship) that limits them to only their clients' portfolios
- We have other user roles in the company that permit supervision of specific client managers (Client *-* Users [type=manager][get:supervisor])
- We may also limit users by offices (Client *-* Users [get:office])
- We may also limit users by company as we own multiple companies (Company - Client *-* User -* Company)
- Or not limit (full portfolio)
Trying to reuse the RLS above and ensure filters are properly handled for all reports is especially challenging.
Keep in mind this doesn't cover all our RLS conditions, and some of them may be stacked (e.g. user works for company X and is in office Y).
We're working with an archaic system, so there isn't opportunity for us to improve the db structure.
Just trying to find a way to work with it to pull improved analytics.
The idea here would be use have one project that handles RLS + filters for our web app to tie into.
Then reuse that RLS dataset for other datasets to ensure proper access is granted to the user and perform appropriate calculations based on their access.
