“Pivot Table Operation failed. We cannot locate a server to load the workbook Data Model” error when applying filter in Excel workbook accessed through Power BI Report server
Scenario and Issue: -
After configuring (by following /en-us/power-bi/report-server/excel-oos article) Power BI Report Server to use Office Online Server (OOS) to host the Excel PowerPivot models, when you open the Excel workbook which has the PowerPivot model and try to apply filter or slicer, you get the below mentioned error.
Error: - Pivot Table Operation failed. We cannot locate a server to load the workbook Data Model.
Tested Environment: -
- Edition: Power BI Report Server - SQL Server Enterprise with Software Assurance
- Product Build: 0.600.434
- SSAS Version: SQL Server 2017
- PBIRS Version: Power BI Report Server 2017 Microsoft 1.1.6514.9163 (October 2017)
Cause: -
We looked at the ULS logs of the OOS at “C:\ProgramData\Microsoft\OfficeWebApps\Data\Logs\ULS” path and found that “Check Administrator Access (Server Name\POWERPIVOT): Fail” call failed. This indicates the network service account doesn’t have admin privileges on PowerPivot instance.
In my case, I was able to resolve the issue by providing administrator permission to NT AUTHORITY\NETWORK SERVICE account on my SSAS PowerPivot instance.
To do this: -
- Connect to Power Pivot instance of SSAS from SSMS.
- Right click on it and then click on properties.
- Select the security tab and then click on Add.
- Provide the NT AUTHORITY\NETWORK SERVICE account and then ok.
- Verify that, Network account is added successfully as shown below.
Author: Varun Kulkarni – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Sarath Babu Chidipothu – Support Escalation Engineer, SQL Server BI Developer team, Microsoft