Forum Discussion
Use report without access to underlying data?
Hi FlorisMK , Thank you for reaching out to the Microsoft Community Forum.
In Power BI Desktop, load the full HR exports as “Source” queries. Create separate “Aggregated” queries that transform and summarize the data, removing any sensitive fields. Use only these aggregated queries in your visuals. Right-click each source query and uncheck “Enable Load” to keep them out of the data model. Then, under File -> Options -> Report Settings, disable export and copy options to prevent users from accessing data behind the visuals.
Publish the report to a separate Power BI workspace, not your HR team’s. Share it via a Power BI App or give users the Viewer role, this lets them see the report but not access the dataset or source files.
For more information:
Export the data that was used to create a visualization
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi BhavinVyas3003 , thanks for your quick response. I'm afraid it's a bit too high-level for me though. Most of your bullets evoke a "yes, but how" response in me.
Most importantly, I do not understand how to do your first bullet, and as I read it, it seems to be more a rephrasing of my question than a solution. Yes, I want a report with only the aggregated data accessible to users, but HOW do I achieve that? After all, the source data is required to aggregate the aggregated data.
The source data is direct exports from the HR systems, and I have no control over which attributes are exported. And some sensitive attributes (such as birth dates) are indispensable for creating the demographics.
To be entirely clear, I want to avoid having to edit the source exports, because that would add an inconvenient and time-consuming additional step.
Here's what I'm thinking/hoping would be possible:
- Base the report on the HR exports, which contain all the necessary atomic data --> source queries
- Build reporting queries on the source queries, and measures on the reporting queries, to aggregate the source data
- Use the reporting queries and measures in the visuals of the report
- Make the report and visuals available to my users
- Disallow drilling down to the underlying reporting queries of the visuals
- Disallow accessing the reporting queries directly
- Disallow accessing the source queries directly
The setup is already mostly in place: the report has "Source" queries that read the exports, and reporting queries that Table.Combine the source queries and contain the required measures. None of the "Source" queries are used directly in the visuals.
I guess what I'm also hoping is that it's possible to allow Power BI to read the source exports, without my users needing permission.
Hi FlorisMK , Thank you for reaching out to the Microsoft Community Forum.
In Power BI Desktop, load the full HR exports as “Source” queries. Create separate “Aggregated” queries that transform and summarize the data, removing any sensitive fields. Use only these aggregated queries in your visuals. Right-click each source query and uncheck “Enable Load” to keep them out of the data model. Then, under File -> Options -> Report Settings, disable export and copy options to prevent users from accessing data behind the visuals.
Publish the report to a separate Power BI workspace, not your HR team’s. Share it via a Power BI App or give users the Viewer role, this lets them see the report but not access the dataset or source files.
For more information:
Export the data that was used to create a visualization
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Thanks! I'll dive into your suggestions asap, hopefully later today, otherwise it'll have to be next week.
Hi FlorisMK , Thanks for the update. Please do check when possible and share your thoughts and results. Also, If any of the responses here helped, please consider marking them "Accept as Solution".
Thank you.Finally an opportunity to look into this! Your breakdown of the necessary steps prompts a question. If I uncheck 'Enable load', would that mean that my report users cannot update the report? In other words: would that mean I'd be publishing a static copy of data for them?
Ideally, I'd want my users to be able to access the report and see the latest aggregated data. My ideal process would be that HR staff perform regular exports, and the report stays up-to-date for everyone without additional effort.