{"id":1556,"date":"2013-11-04T06:56:19","date_gmt":"2013-11-04T06:56:19","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1556"},"modified":"2024-04-12T05:16:15","modified_gmt":"2024-04-12T05:16:15","slug":"getting-powerpivot-working-in-sharepoint","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/","title":{"rendered":"Getting PowerPivot working in SharePoint"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_65 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#PowerPivot_with_SharePoint_Diagnostics\" title=\"PowerPivot with SharePoint Diagnostics\">PowerPivot with SharePoint Diagnostics<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#No_associated_Service_Application\" title=\"No associated Service Application\">No associated Service Application<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Data_Connection_Library_trust\" title=\"Data Connection Library trust\">Data Connection Library trust<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Trusted_file_location\" title=\"Trusted file location\">Trusted file location<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Install_the_appropriate_SSAS_OLAP_library\" title=\"Install the appropriate SSAS OLAP library\">Install the appropriate SSAS OLAP library<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_SSAS_is_running_under_a_domain_account\" title=\"Ensure SSAS is running under a domain account\">Ensure SSAS is running under a domain account<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_OLE_DB_providers_are_available\" title=\"Ensure OLE DB providers are available\">Ensure OLE DB providers are available<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_the_OLE_DB_provider_is_trusted_by_Excel_Services\" title=\"Ensure the OLE DB provider is trusted by Excel Services\">Ensure the OLE DB provider is trusted by Excel Services<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_CTWTS_is_started\" title=\"Ensure CTWTS is started\">Ensure CTWTS is started<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_CTWTS_is_running_as_LOCAL\" title=\"Ensure CTWTS is running as LOCAL\">Ensure CTWTS is running as LOCAL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_Secure_Store_Service_is_configured\" title=\"Ensure Secure Store Service is configured\">Ensure Secure Store Service is configured<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_Excel_Services_is_running_and_associated\" title=\"Ensure Excel Services is running and associated\">Ensure Excel Services is running and associated<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_XLSX_renders_in_Excel_Services\" title=\"Ensure XLSX renders in Excel Services\">Ensure XLSX renders in Excel Services<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Ensure_Analysis_Services_Account_can_act_as_OS\" title=\"Ensure Analysis Services Account can act as O\/S\">Ensure Analysis Services Account can act as O\/S<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/getting-powerpivot-working-in-sharepoint\/#Check_the_ULS\" title=\"Check the ULS\">Check the ULS<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"PowerPivot_with_SharePoint_Diagnostics\"><\/span>PowerPivot with SharePoint Diagnostics<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>There are a lot of moving parts to PowerPivot. Here are some common causes of PowerPivot not working within SharePoint:<\/p>\n<h2><span class=\"ez-toc-section\" id=\"No_associated_Service_Application\"><\/span>No associated Service Application<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>I list this first, because I spent way too much time finding this one. \u00a0 There is a Service Application created in support of PowerPivot. \u00a0If your web application doesn\u2019t have a proxy association to this Service Application, you will get a generation connection error when trying to view a PowerPivot spreadsheet within Excel Services.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Data_Connection_Library_trust\"><\/span>Data Connection Library trust<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you use an external (ie ODC) data connection in a library, that library needs to be trusted.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Trusted_file_location\"><\/span>Trusted file location<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The location where the PowerPivot spreadsheet is located needs to be trusted.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Install_the_appropriate_SSAS_OLAP_library\"><\/span>Install the appropriate SSAS OLAP library<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>For SQL Server 2008 R2 SP1, the correct library is MSOLAP.4, for SQL Server 2012, the correct library is is MSOLAP.5. \u00a0If you open the ODC data connection referenced by your PowerPivot spreadsheet, at the beginning you will find a reference to this library.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_SSAS_is_running_under_a_domain_account\"><\/span>Ensure SSAS is running under a domain account<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>There are two solutions, and both need to be deployed. Here\u2019s the PowerShell:<\/p>\n<pre lang=\"php\">Add-SPSolution \u2013LiteralPath \u201cC:Program FilesMicrosoft SQL Server110ToolsPowerPivotToolsConfigurationToolResourcesPowerPivotFarm.wsp\u201d\r\nInstall-SPSolution \u2013Identity PowerPivotFarm.wsp \u2013GACDeployment -Force\r\n \r\n \r\n$centralAdmin = $(Get-SPWebApplication -IncludeCentralAdministration | Where { $_.IsAdministrationWebApplication -eq $TRUE})\r\nAdd-SPSolution \u2013LiteralPath \u201cC:Program FilesMicrosoft SQL Server110ToolsPowerPivotToolsConfigurationToolResourcesPowerPivotWebApp.wsp\u201d\r\nInstall-SPSolution -Identity PowerPivotWebApp.wsp -GACDeployment -Force -WebApplication $centralAdmin\r\n<\/pre>\n<p>If you want to check the deployment status, try this command:<\/p>\n<pre lang=\"php\">Get-SPSolution \"powerpivotfarm.wsp\" | Format-List\r\n<\/pre>\n<p>As background, there is a deployment job triggered with deploying the solution. This job is managed under the Timer Job Service, which you can check on:<\/p>\n<pre lang=\"php\">Get-SPTimerJob -Type Microsoft.SharePoint.Administration.SPSolutionDeploymentJobDefinition | Format-List\r\n<\/pre>\n<p>Next, go into Farm Solutions in Central Admin, and deploy both solutions. What do these solutions do?<\/p>\n<p>The Powerpivotfarm.wsp solution does the following:<\/p>\n<p>Adds Microsoft.AnalysisServices.SharePoint.Integration.dll to the global assembly.<br \/>\nAdds Microsoft.AnalysisServices.ChannelTransport.dll to the global assembly.<br \/>\nInstalls features and resources files, and registers content types.<br \/>\nAdds library templates for PowerPivot Gallery and Data Feed libraries.<br \/>\nAdds application pages for service application configuration, PowerPivot Management Dashboard, data refresh, and PowerPivot Gallery.<br \/>\nThe Powerpivotwebapp.wsp solution does the following:<\/p>\n<p>Adds Microsoft.AnalysisServices.SharePoint.Integration.dll resources files to the web server extensions folder on the Web front-end.<br \/>\nAdds PowerPivot Web service to the Web-front end.<br \/>\nAdds thumbnail image generation for PowerPivot Gallery.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_OLE_DB_providers_are_available\"><\/span>Ensure OLE DB providers are available<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you are running SQL Server 2008 R2 SP, then the OLE DB provider should have been installed when SharePoint was installed. \u00a0You can find it in the GAC called \u201cmicrosoft.analysisservices.xmla.dll\u201d. \u00a0GAC is in C:windowsassembly<\/p>\n<p>SQL Server 2008 R2: MSOLAP100.dll, within the data connection string it is called MSOLAP.4, and the version (when you examine properties of the file in the GAC) is 10.50.1600 or later)<\/p>\n<p>SQL Server 2012: MSOLAP110.dll, within the data connection string it is called MSOLAP.5, and the version (when you examine properties of the file in the GAC) is 11.00.0000 or later)<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_the_OLE_DB_provider_is_trusted_by_Excel_Services\"><\/span>Ensure the OLE DB provider is trusted by Excel Services<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Check within Central Admin, Manage Service Applications, Excel Services, Trusted Data Providers lists MSOLAP.4 \u00a0and\/or MSOLAP.5, depending on which version of SQL Server you are using.<\/p>\n<p>If it is not listed, click Add Trusted Data Provider, in the Provider ID, type MSOLAP.5.<br role=\"presentation\" data-uw-rm-sr=\"\" \/>For Provider Type, ensure that OLE DB is selected. In Provider Description, type Microsoft OLE DB Provider for OLAP Services 11.0.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_CTWTS_is_started\"><\/span>Ensure CTWTS is started<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The Claims to Windows Token Service is required to communicate and authenticate seamlessly with SQL Server. \u00a0This service has to be started. \u00a0In Central Admin, go to Services on Server, and start it up.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_CTWTS_is_running_as_LOCAL\"><\/span>Ensure CTWTS is running as LOCAL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The Claims to Windows Token Service needs to run using the LOCAL builtin account. \u00a0You can see this by starting services.msc on each SharePoint server.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_Secure_Store_Service_is_configured\"><\/span>Ensure Secure Store Service is configured<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This Service Application needs to be configured (it has its own database), running, associated with your web application, with an Application ID defined that maps to an AD account. \u00a0I prefer configuring as a \u201cGroup\u201d entry. \u00a0You\u2019ll need to enter credentials for this Application ID, and also grant permissions to the users who will reference the Secure Store Application ID, which is set within the data connection for the Authentication configuration.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_Excel_Services_is_running_and_associated\"><\/span>Ensure Excel Services is running and associated<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>PowerPivot makes use of Excel Services. \u00a0This Service Application must be configured, and associated with your web application. \u00a0The data connection library (if used) and file location must be trusted. \u00a0Excel Services should also be configured to allow PowerPivot workbooks of sufficient size to render. \u00a0Note I have seen a bug in early versions of SharePoint 2013 where increasing the maximum workbook size does not allow larger workbooks to render.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_XLSX_renders_in_Excel_Services\"><\/span>Ensure XLSX renders in Excel Services<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This one is a real gotcha\u201d if you are running SharePoint 2013 with an Office Web Apps farm.\u00a0 By default Office Web Apps renders spreadsheets, and cannot handle external data connections.\u00a0 if the URL contains \u201c_layouts\/15\/WopiFrame.aspx\u201d then the spreadsheet is being rendered by Office Web Apps.\u00a0 The tricky part is that other than the URL, Office Web Apps rendered spreadsheets look virtually identical to the Excel Services rendered equivalent.\u00a0 Under Excel Services the URL would instead contain \u201c_layouts\/15\/xlviewer.aspx\u201d.\u00a0 The fix for this is a single PowerShell command:<\/p>\n<pre lang=\"php\">New-SPWOPISuppressionSetting -Extension XLSX -Action View<\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Ensure_Analysis_Services_Account_can_act_as_OS\"><\/span>Ensure Analysis Services Account can act as O\/S<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The Service account running the PowerPivot dedicated Analysis Services on the SharePoint server can fail to refresh unless the following steps are taken:<\/p>\n<ul>\n<li>Run \u201csecpol.msc\u201d<\/li>\n<li>Click Local Security Policy, then click Local policies, and then click User rights assignment.<\/li>\n<li>Add the service account.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Check_the_ULS\"><\/span>Check the ULS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When all fails, the ULS can be a lifesaver. \u00a0While often cryptic or hyperventilating about some minutia, it still can very clearly indicate the source of a problem<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PowerPivot with SharePoint Diagnostics There are a lot of moving parts to PowerPivot. Here are some common causes of PowerPivot not working within SharePoint: No associated Service Application I list this first, because I spent way too much time finding this one. \u00a0 There is a Service Application created in support of PowerPivot. \u00a0If your [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":1557,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-1556","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-services"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1556"}],"version-history":[{"count":5,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1556\/revisions"}],"predecessor-version":[{"id":1563,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1556\/revisions\/1563"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1557"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1556"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}