Scenario:
You install PowerPivot for SharePoint 2016 (or PowerBI Report Server) and use SQL 2017 as the data model server (Analysis Services in SharePoint mode). You have configured Office Online Server and all of the other dependencies to properly render workbooks and slice data, but you still receive the following error when you attempt the action: "We cannot locate a server to load the workbook Data Model"
Analysis:
In the ULS logs on the Office Online Server, you will see errors similar to the following while reproducing the error:
00/00/2018 11:11:11.94 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Monitorable Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: There are no servers available or actively being initialized. at <<TRUNCATED STACK FOR LENGTH>><<CORRELATION ID>> 00/00/2018 11:11:11.94 w3wp.exe (0x0000) 0x0000 Excel Online External Data by6b8 Medium OLEDBConnection::InitConnection: There are no servers available or actively being initialized. <<CORRELATION ID>> 00/00/2018 11:11:11.97 w3wp.exe (0x0000) 0x0000 Excel Online Excel Calculation Services akdn Verbose OperationSite.PrepareComplete: Caught Exception when running operation Microsoft.Office.Excel.Server.CalculationServer.Operations.ApplySlicerSelectionOperation, WebMethod: ApplySlicerSelection. Error was Id=NoStreamingServers; Microsoft.Office.Excel.Server.CalculationServer.OperationFailedException: We cannot locate a server to load the workbook Data Model. at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args) <<CORRELATION ID>>
Unfortunately, these errors will not help very much with the diagnosis other than the fact that it is telling you that data models cannot be created on the PowerPivot Analysis Services server. To find a root cause, a deeper look at the log is required and some background on Excel Services is needed.
When Excel Services boots up for the first time, it performs a series of checks against the defined PowerPivot instances prior to initialization. If one fails, that server is taken out of the pool of available servers. After the 3 main checks, it does a query for available memory. This is based on the performance counters applied to the server during the SSAS install. Because PowerPivot is so reliant on performance based counters, if the counters are missing and the memory check fails, the initialization of the server will also fail. This goes back to one of the errors we saw above: "There are no servers available or actively being initialized."
So, you may or may not see this list of checks in the logs at any given time, but if you run a restart-service wacsm command and are logging during the restart you will see the following (you may need to engage Excel somehow to get this to pop up):
00/00/2018 00:00:00.69 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium Checking Server Configuration (SERVERNAMEPOWERPIVOT) 00/00/2018 00:00:00.69 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium --> Check Administrator Access (SERVERNAMEPOWERPIVOT): Pass. 00/00/2018 00:00:00.69 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium --> Check Server Version (SERVERNAMEPOWERPIVOT): Pass (14.0.1.439 >= 11.0.2800.0). 00/00/2018 00:00:00.69 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium --> Check Deployment Mode (SERVERNAMEPOWERPIVOT): Pass. 00/00/2018 00:00:00.69 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium Check Server Configuration (SERVERNAMEPOWERPIVOT): Pass. 00/00/2018 00:00:00.73 w3wp.exe (0x0000) 0x0000 Excel Online Data Model 27 Medium SSPM: Initialization failed on server SERVERNAMEPOWERPIVOT: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The 'MSOLAP$POWERPIVOT:MemoryMemory Limit High KB' performance counter could not be found. System error -1073738824 <<TRUNCATED STACK>>
Cause:
The missing performance counters is a known product issue with SQL Analysis Services 2017 RTM, CU1 or CU2 if you install it in Tabular mode. Subsequently, if you install another instance on top of it or even uninstall/reinstall, the performance counters will still not be installed on the server.
Resolution:
This issue was resolved in SQL 2017 CU3. Our recommendation is just to update to the latest CU to bypass this issue all together:
https://www.microsoft.com/en-us/download/details.aspx?id=56128
Here is the official post from the SQL PG regarding the issue for more info:
Note: This article applies ONLY to the above mentioned products in this specific scenario. This product issue was found and resolved with the above mentioned patch and does not apply to any other versions of Office Online, PowerPivot or SQL Analysis Services. You may experience the same error in other forms for other reasons.
from TechNet Blogs https://ift.tt/2HtlQe5
No comments:
Post a Comment