Forum Discussion
Refresh Excel based Reports of Semantic Model - Viewer/ Read Access Permissions
I have a premium capacity workspace to which I am planning to deploy a semantic model (dataset) and reports based on this data. I intend to give certain SMEs the ability to connect to the data to build further Power BI reports and use analyze in Excel feature (Build permissions on the dataset). These SMEs intend to connect to the model and then build/ feed Excel reporting, much like they used to with Cube (SSAS) models using pivot tables, which are then shared with various viewers from around the organisation.
I want the viewers of the Excel based reports to be able to see the reports and Excel files the SMEs build using the Semantic Model and to be able to refresh the data in Excel/ see updated data based on the Import schedule in Power BI but I do not want them to be able to build their own reports based on the semantic model.
Are there Power BI permissions levels which would allow this to work? Essentially, SMEs to have Build permissions with the dataset and viewers to see refreshed data in any Excel files the SMEs develop for them.
Hi henry_gonsalves ,
Thank you for reaching out to Microsoft Fabric Community Forum.
In Power BI, when using import mode and Excel reports connected to a Power BI dataset, there are a few key things to note regarding Viewer access and refresh ability:
-
Viewers cannot refresh imported data in Excel unless they have Build permission on the dataset.
-
Even if a scheduled refresh is set up in the Power BI Service, Excel files connected via import mode do not auto-refresh; they contain a static snapshot unless refreshed manually.
-
In Excel, the Refresh button works only if the user has permission to access the data source i.e Power BI dataset.
-
Without Build permissions, Excel will throw an error when a Viewer tries to refresh the connection.
- To allow users to refresh the dataset in Excel, ensure they are granted Build permissions on the dataset. This is required whether they are connecting through Analyze in Excel or through a live connection
Regards,
Chaithanya.
-
12 Replies
Yes, this is possible for semantic models in Power BI premium workspaces
- Semantic Model Permissions
In Power BI:
- Grant Build permission on the dataset only to the SMEs.
This allows them to: - Use Analyze in Excel
- Create reports (including Excel pivot tables connected to the model)
- DO NOT grant Build permission to the viewers.
This prevents them from: - Opening a live connection to the dataset
- Building their own reports or Excel pivot tables
- SME-Developed Excel Reports
- SMEs build Excel files that connect to the semantic model (PBIX dataset) using Analyze in Excel or direct Power BI dataset connections.
- These Excel files can be:
- Published to OneDrive or SharePoint Online
- Shared directly via links with View permission only
- Viewer Experience
- Viewers open the Excel files via browser or desktop.
- Since the Excel file contains a pivot table backed by a Power BI dataset, the data can refresh upon open (if connection is preserved).
- Viewers will be prompted to authenticate, but since they don’t have Build permissions, they cannot change the model or use it in other contexts.
- Viewers can see refreshed data based on the dataset's import/refresh schedule, but they can’t alter the pivot table structure.
Please mark this post as solution if it helps you. Appreciate Kudos.
Thank you for your response. This is exactly the capability that I want.
Would I need to set anything up Excel side for the excel data to refresh if it is connected to the Power BI semantic model which would have a scheduled refresh (import mode)?
Would the viewers be able to click "Refresh" as normal as per other data sources/ pivot tables?
Hi henry_gonsalves ,
Just to clarify, if you want users to open an Excel file that’s connected to a Power BI dataset and actually refresh the data (using the Refresh button in Excel), they have to have Build permission on the dataset. If they only have Read or Viewer permission, they’ll be able to see the last saved data in the file, but they won’t be able to refresh, it’ll give a permissions error.
This applies whether you’re using Premium or Pro workspaces, and there isn’t a workaround at the moment. So for any scenario where viewers need to refresh Excel reports connected to a semantic model, make sure they have Build permission on the dataset.
Thank you, this seems to be the functionality I need: Builders can build and Viewers can view them.
I just wanted to make sure that viewers would be able to read reports produced in Excel using the dataset and could refresh them themselves without needing Build permissions too. So if the report has a scheduled refresh on import mode, I assume the Viewers would be to click Refresh in Excel as per other data sources and pivot tables to get the latest data that's in the service?
Many thanks
Hi henry_gonsalves ,
Thank you for reaching out to Microsoft Fabric Community Forum.
In Power BI, when using import mode and Excel reports connected to a Power BI dataset, there are a few key things to note regarding Viewer access and refresh ability:
-
Viewers cannot refresh imported data in Excel unless they have Build permission on the dataset.
-
Even if a scheduled refresh is set up in the Power BI Service, Excel files connected via import mode do not auto-refresh; they contain a static snapshot unless refreshed manually.
-
In Excel, the Refresh button works only if the user has permission to access the data source i.e Power BI dataset.
-
Without Build permissions, Excel will throw an error when a Viewer tries to refresh the connection.
- To allow users to refresh the dataset in Excel, ensure they are granted Build permissions on the dataset. This is required whether they are connecting through Analyze in Excel or through a live connection
Regards,
Chaithanya.
-
Hi. You can work with that. However, the permission won't let them do exactly as you think. Build permission is required for analyze in excel. Doc specifing that: https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel#prerequisites
That means that the users analyzing in excel will also be able to connect the semantic model with power bi desktop. They might not be able to publish a report to share with others (unless they have a pro license), but they can connect using the tool onpremise.
That's the only consideration to keep in mind that I can think about now.
There might be some policy to prevent Desktop to login and connect to data. That's a research topic to keep in mind too.
I hope that helps,
If some users have Viewer permission to the report/ dataset and others have Build permission to the dataset to build any Excel based reports using the model, will the viewers be able to view the Excel reports built by the SMEs and refresh the data? Thank you
That's tricky. I guess a viewer with non build permission that open the excel might be able to view it, but won't be able refresh it. Of course it depends on where you store your excel files.
I hope that make sense
Hi henry_gonsalves ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @henry_gonsalves ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @henry_gonsalves ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
