Excel Services & PowerPivot for SharePointhttps://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog
Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.Wed, 06 Sep 2017 22:57:42 +0000en-UShourly1PowerPivot for SharePoint 2016 Gallery Snapshots Fail for SSL Host Header Siteshttps://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2017/06/09/powerpivot-for-sharepoint-2016-gallery-snapshots-fail-for-ssl-host-header-sites/
https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2017/06/09/powerpivot-for-sharepoint-2016-gallery-snapshots-fail-for-ssl-host-header-sites/#respondFri, 09 Jun 2017 14:32:19 +0000https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/?p=1395If you have been following the multiple threads on issues and changes to the PowerPivot for SharePoint 2016 Gallery Snapshots, you may be aware of an issue where gallery snapshots do not function properly (infinite "hourglass") for SSL sites when there are multiple sites bound to the same port. While not limited to port 443 we will use a generic port 443 site as an example here as it is the most common usage.

As part of the above call the full URL of the document is sent with the request to OOS on behalf of the user that uploaded the document. (Obviously "localhost" is not a valid URL for OOS, so we also send the full URL as part of the request so OOS knows where to go to retrieve the snapshots)

Office Online Server loads the workbook, retrieves the snapshots, and returns them to the SharePoint WFE server.

The snapshots are applied to the document.

The Issue:

The new snapshot process causes an issue for SSL sites that share a port and use unique certificates. When you combine multiple unique URLs each with their own unique SSL certificates and localhost calls, you wind up with IIS getting very confused on what certificate to pin to the request (since it is localhost, it takes the first one in line generally). In an effort to avoid an authentication double hop in a scenario where more than one SharePoint web front end (WFE) is involved, the product team opted to utilize localhost. If this was not in place, Kerberos Constrained Delegation (KCD) would need to be configured between the SharePoint WFE servers to avoid an overcomplicated configuration just for gallery snapshots to function properly.

In the SharePoint ULS logs, you will see a set of entries similar to the following detailing the failure:

During my multiple attempts to narrow this down, I could see the snapshot process attempting to load the first site bound certificate in my list (for my "secondary" SSL site) instead of the certificate for ssl.contoso.com. Because of the localhost call, IIS is confused (obviously) about what certificate to hand out for the request. This issue is specific to farms where everything else is configured properly and functional, but it is not limited to just this scenario. This just happens to be the most common (and basic) configuration.

The product team has determined this is by design and will not be changed due to product limitations with SharePoint and Office Online Server.

"The Workaround"

You can use a wildcard certificate. This is the only possible workaround we have found so far. With a wildcard certificate for your sites, you can utilize multiple SSL host header sites and your gallery snapshots will function (this has been tested and confirmed working). Again, this is assuming that everything else is configured properly for snapshots to function. We realize this is not viable for everyone, but at this point it is only option to keep sites SSL and have functional snapshots.

Note: Be sure that you research all aspects of using Wildcard certificates before implementing this solution so you are fully informed of the risks and rewards of using them.

Other Options

You can restrict your usage to one SSL site with host header and certificate and no IP specific bindings (site specific SSL certs and IP specific bindings usually go hand-in-hand).

Stop using the PowerPivot Gallery all together. You can schedule data refreshes for PowerPivot workbooks in any document library as long as the PowerPivot solution is deployed to the web app and the features are activated on the site.

Convert your SSL sites to HTTP. (Maybe even just the sites that host workbooks?)

Set "All Documents" to the default view in the PowerPivot Gallery.

Navigate to the list a site owner.

Click "Library" > "Library Settings" from the ribbon menu.

Scroll down to the "Views" section and click "All Documents"

Check the "Make this the default view" box and click "OK"

You can take the above a step further by deleting the Silverlight views in the PowerPivot Gallery.

Delete ScriptNOTE: For your current PowerPivot Galleries, they will continue to attempt to take snapshots even if you disable/delete the views. The only way to stop them from attempting to create snapshots is to replace the gallery with a normal document library. As long as you keep the PowerPivot features deployed to the site, you will still be able to schedule data refreshes as usual. We STRONGLY recommend that you test these scripts extensively in a development environment before using them on a farm that matters.

Something else to consider here is that PowerBI Report Server is being released soonish (Q2 this year is the current timeline). You can check out more info and try out the preview version already here: https://powerbi.microsoft.com/en-us/report-server/

This article covers an issue specific to PowerPivot Gallery Snapshots in SharePoint 2016 and does not refer to or reference any other products. If you see similar errors in other versions of SharePoint, the solutions/recommendations list here will not help you and are not relevant to your issue. The PowerShell scripts provided in this article are provided as samples for testing with no guarantee or warrantee by Microsoft.

Scenario:

You create a PowerPivot workbook with one or more data sources and upload it to SharePoint 2016. You then schedule the workbook to refresh and the refresh fails with the following error: "The operation has timed out".

Cause:

This issue is caused by a change to the design of the product in SharePoint 2016 that limits a single PowerPivot data source to a refresh duration of 100 seconds (or 1:40). Note that this can also be caused by slow or underperforming data sources if you think your data should be refreshing in under 100 seconds. If you are pulling a small amount or data from a complex data source that takes a long time to query, you may consider alternative workarounds as increasing the timeout may not be the best option.

Symptoms:

If you expand the refresh history, you will notice that the data source of which refresh failed on ran for exactly 1:40 (100 seconds).

You may notice that some of your data sources actually succeed, or that the refresh succeeds intermittently if all of the data sources refresh in less than 100 seconds.

In the SharePoint ULS logs, you will see the actual error reported, but it will not seem to correlate to anything meaningful as far as a cause (because it doesn't....)

You will likely see a correlating event in the Application Event logs (EventID: 5214).

If you have multiple workbooks pulling from the same source, and one workbook is slowing the data source down causing the other queries to wait or slow (past the 100s), you may see multiple workbooks fail that do not normally fail.

Potential Workarounds:

Optimize your queries to run faster.

Query less data.

Add hardware to your data source to process queries faster.

Use PowerShell to increase the default timeout setting.

From a SharePoint 2016 Administrator enabled PowerShell prompt:

$farm = Get-SPFarm
#The timeout value is in milliseconds, so be very careful to not set it too low!!!$farm.Properties.Add("WopiProxyRequestTimeout", <new timeout value>);
$farm.Update();
#to double check the setting
$farm.properties

Disclaimer: Increase this timeout value at your own risk! We realize that the new default timeout is very low, but you should also be aware of the amount of data that you are pulling vs the time it should take to pull that data. Setting this timeout too high and allowing users to pull large amounts of data can cause performance issues for PowerPivot, SharePoint and Office Online Server. You will also be limited by the default timeouts for SharePoint, SQL and your external data sources. This timeout value may not always be the answer. There are more workarounds and optimization options depending on the type of data you are pulling.

NOTE: This blog applies SPECIFICALLY to the issue listed above and ONLY to PowerPivot for SharePoint 2016 Scheduled Data Refresh. This may not be the only context that you see this error and you could also run into it in other versions of the product (but it will not be for the same reason!!).

If you already installed the SSAS patch but not installed the spPowerPivot.msi, the patch can be backed out or Analysis Services can be reinstalled without much downtime. If you installed the MSI and ran the PowerPivot configuration tool, you will be required to reinstall or use the below workaround until this gets patched. Remember, the goal is to keep your msi and PowerPivot AS versions at the same level.

A recent product issue was introduced that causes users to be presented with an error when they attempt to schedule or modify a refresh schedule for a PowerPivot workbook, they will be presented with the following error:

Note that this is specific to PowerPivot for SharePoint 2013 and the SQL CUs listed above. This issue should not affect any other products and/or Analysis Services stand-alone installations. The issue appears to be limited to the PowerPivot add-in for SharePoint (sppowerpivot.msi) which happens to be included with the CU installer.

The PowerPivot Gallery has had some functionality and supportability changes between SharePoint 2013/2010 and SharePoint 2016. This is due to the fact that Excel Services functionality has moved to Office Online Server. While the basic premise of the gallery remains the same and some of the same rules apply, there are some changes that you should be aware of that will require your attention while troubleshooting or configuring a new farm. This article is purely informative and is not meant to include any troubleshooting steps. We will publish more information on a case-by-case basis for relevant issues.

What is new?

Gallery Snapshots no-longer support PowerView in any form (this includes Power View reports imbedded in Excel/PowerPivot workbooks AND .RDLX files built from PowerPivot data models stored in the gallery). Here is an example of what you will see if you load a workbook with Power View sheets included:

The event receiver that detects the file upload has been modified to accommodate for this. When it detects an XLSX file, it flags "false" and when it detects anything else, it flags true per the below ULS entry examples:

We recommend loading your RDLX files into a regular document library to avoid any confusion with users.

Also, we recommend publishing PowerPivot workbooks with PowerView sheets to PowerPivot galleries by sheet to avoid snapshots being taken of the erroneous sheets (see the following blog for this "workaround").

You will now see charts and sheets have separate snapshots in the snapshot ribbon, for instance a sheet with multiple charts will be shown, then each chart will have its own snapshot preview.

Gallery Snapshots are now handled by Office Online Server. The old gallerysnapshot.exe and getsnapshot.exe processes that used to take the snapshots no longer exist. Office Online Server utilizes the Excel REST API to render and load the snapshots to the documents when they are uploaded to the library or modified. Also, it is done in the context of the user that performs the action rather than a service account.

For administrators: You will find log entries for these actions on the SharePoint Web Front End and Office Online Server ULS logs during the upload or update of the PowerPivot file in question.

If a VALID .xlsx document is uploaded, you will see an entry similar to the following ULS entry as the first call to the "ExcelRest.aspx" as the users login:

Because Gallery Snapshots are now taken in the context of the user that performs the update action to the document, it is a REQUIREMENT that the SharePoint User Profile Service and User Profile Sync is running and fully functional. It also must be associated with the web application of which the PowerPivot Gallery is hosted in.

It also requires that the Claims to Windows Token Services be running on your SharePoint Web Front End servers, but if you are in MinRole configuration, this should be running anyway.

Gallery Snapshots do not function for SSL host header sites when they are on the same port and do not share a certificate (wildcard). See the following blog post for more details and workarounds:

What is the same?

The gallery continues to only have full support for PowerPivot documents.

The same classic views are still included with the gallery.

The PowerPivot Gallery (and all supporting features) are still only supported with Windows Authentication (SAML and Anonymous are not supported under ANY circumstances).

You can still add BISM content types and create Power View reports from PowerPivot data models and BISM connections directly from the PowerPivot Gallery GUI.

The Scheduled Data Refresh interface remains the same and you access and interact with it the same way as in SharePoint 2010 and 2013 via the PowerPivot Gallery.

This article is specific to PowerPivot in SharePoint 2016 On-Premise with Office Online Server. If you are looking for information on Gallery Snapshots for SharePoint 2013 or SharePoint 2010 with PowerPivot, please see our other blog posts on this subject:

You configure Office Online Server with SharePoint 2013 or SharePoint 2016 and have configured Kerberos Constrained Delegation to a data warehouse to pull business intelligence data for your users. You have two Active Directory domains in separate forests in a 2-way transitive trust configuration (We will call them DomainA in Forest 1 and DomainB in Forest 2). The SharePoint farm, data source and Office Online Server sit in Domain A, while only a specific set of users reside in Domain B. Users have no issues logging into the site and interacting with SharePoint.

Users that sit in DomainA configure Excel workbooks and are able to load them into SharePoint, render them in the browser and refresh without any issues. This proves KCD is working. Users from DomainB come over to the SharePoint site and attempt to refresh the same workbooks and receive the following failure:"The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh: XXXXXXXX"

The first assumption is that there is an issue communicating between domains because we have proven that the KCD is functional. You may have also found that this worked (or still works) with Excel Services in SharePoint 2013. You will find (through multiple means) that you can query users from the trusted domain without issues. All diagnosis and troubleshooting efforts will show that communication seems to be correct.

Digging Deeper:

If you dig deeper into this issue and pull Office Online Server logs, you will see the following chain in this scenario:

You will also notice that there are no calls to the other forest from the Office Online server in network traces (at least in relation to this stack).

Diagnosis and Workarounds:

To perform any data refresh action, Office Online Server requires the Windows Identity of the user which is gathered by the Claims to Windows Token Service. You will notice in the logs above that Office Online Server did not even try to query DomainB. The product team confirmed that this is a product limitation. At the moment, Office Online Server will only attempt to query domains located within the same forestas itself.

From the domain level, you can workaround this issue by moving both domains into the same forest. From the Excel Online level, you can refresh data using the Secure Store instead of the Authenticated Users account. This will effectively bypass the need for Office Online Server to retrieve user credentials from the other domain.

NOTE: This post is specific to Office Online Server on-premise and does not reflect on or speak to the capabilities of Office Online, Office 365 or SharePoint Online.

You are installing PowerPivot for SharePoint 2016 (or 2013) using SQL 2016 and you notice that you are no-longer restricted to having the instance name "POWERPIVOT" to make it function. As such, you decide to install PowerPivot Analysis Services on the default instance for the sake of simplicity. You continue with your Office Online Server and SharePoint configuration and proceed with testing your first PowerPivot workbook. The workbook opens without issues, but as soon as you attempt to interact with the PowerPivot workbook, you receive the following error: "Cannot locate a server to load the workbook data model".

Problem:

At the moment, there is a known issue causing issues with SSPM streaming between OOS/Excel Services and PowerPivot when it is installed on the default instance. There is no known fix date for this issue.

You will notice a few symptoms that are user facing and on the back end. First, as stated above you will be unable to refresh (browser or scheduled) or interact with Excel workbooks that contain PowerPivot data models. On the back end, you will notice that no data models are being created inside of the PowerPivot Analysis Services instance, but all connectivity to the instance will seem without flaw as you will have no issues connecting with SQL Management Studio or PowerShell.

Workaround:

For now, you will be required to install PowerPivot Analysis Services on a named instance of SQL. The name of the instance is not relevant as long as it is within the constraints of the current naming limitations for all SQL named instances. Installing on a named instance causes no security issues with test or production deployments. When this issue is resolved, if you wish to migrate your PowerPivot Analysis Services installation to the default instance, you can do a side-by-side installation change your configuration at that time.

]]>https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/10/14/sql-2016-powerpivot-analysis-services-fails-on-default-instance/feed/0Third Party Data Refresh Tools and Negative Interaction with PowerPivot for SharePointhttps://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/10/10/third-party-data-refresh-tools-and-negative-interaction-with-powerpivot-for-sharepoint/
https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/10/10/third-party-data-refresh-tools-and-negative-interaction-with-powerpivot-for-sharepoint/#commentsMon, 10 Oct 2016 16:44:37 +0000https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/?p=1267Recently we have seen multiple instances where custom 3rd party tools/scripts are being used to bypass the standard daily scheduled data refresh limit for PowerPivot workbooks. The main reason we have seen these instances is because these tools have been causing performance issues for SharePoint Web Front End servers that were not accounted for in their design and/or implementation into the subject farms. I first want to note that we do not support these tools in any way and highly recommend against using them at all. If you need to refresh your PowerPivot workbooks more than once a day, then you likely need to find a different BI solution to display your data. Excel Services and PowerPivot for SharePoint are not meant to be a "live feed" of data.

In these cases, we found that the tools and scripts update the workbooks from another server/machine outside of the farm and re-upload them to SharePoint. As in most cases where PowerPivot is used, the library where the documents were stored was a PowerPivot Gallery as it provides the best user experience visually and functionally. Due to the fact that the documents were stored in a gallery, the massive front end load was caused from the PowerPivot gallery snapshots being triggered on the workbooks. Every time a user (program, script or otherwise) updates or uploads a workbook to a gallery, this snapshot process is triggered. The process engages the workbook after upload and literally creates images for every supported sheet in the workbook and uploads them to the metadata of the file.

Now, if we take this as an example: If I have a PowerPivot gallery with fifty 30MB workbooks in it and I use my script to refresh all of them and then re-upload them to the site, not only did I just slam a single WFE with 1.5 GB of uploads, but I have potentially caused 50 getsnapshot processes to launch at the same time and start processing. Each of those processes reaches out to the site at the same time to create an image file and then upload it to the document. You can also see how this can affect more than one WFE at the same time.

The other difficult part about this is that a solution like this can be implemented by any user that has access to the farm. Administrators may not even know it exists until it is too late. Most of these update solutions can be run and triggered from a CLIENT machine. They do not require servers or code level access to function. They generally access the site via the same interface that other software (like Office for instance) would use.

Lastly, if the PowerPivot gallery is configured with versioning, this can cause some really bad issues with version limits. You may find these documents becoming quickly unusable or causing corruption in your content database if too many versions are created. They also cause massive storage issues on the database side when they get out of hand.

So, some things to think about:

Do not use custom solutions to update your PowerPivot workbooks more than once per day. (It is not supported)

If you do, do not store the workbooks in a PowerPivot Gallery.

Do not configure versioning on the library where the workbooks are stored (or limit the versioning severely).

You could "upsize" your data models. Effectively this involves moving a data models created in PowerPivot to Analysis Services. You can then use SQL jobs to update the data as frequently as you wish. After that is configured, you could create Excel workbooks to hit the new model and "Refresh on Open". This would effectively give you "fresh" data every time you open the workbook. Again, it would not be a live stream, but it would give you a supported method to retrieve data on a more frequent basis.

Note: This workaround may not make sense if you have very small workbooks.

If the data source is SQL Analysis Services, you could use flat out use Excel rather than PowerPivot and again configure the workbook to "Refresh on Open". This solution will likely cause a little more load on the application servers as well as the Analysis Services data sources, but it is fully supported and a much more reliable method to retrieve data "on the fly".

Consider other products that integrate with SharePoint such as SQL Reporting Services or PerformancePoint to display the data.

Just to define what we mean when we say something "Is not supported". We at Microsoft will not provide support to fix custom solutions and/or scenarios that have been deemed "out of bounds" by our product teams. This does not mean that these solutions will not work, but if you run into a problem due to one of these solutions, we will not be able to assist beyond a best effort to get the farm back into a working state after the customizations have been removed and/or disabled.

If you question whether a solution you want to implement is supported, please contact support and we can take a look at let you know.

1. Make sure that the account running Excel Services is part of the groups WSS_WPG and WSS_ADMIN_WPG on the SharePoint servers running Excel Services.

2. Open the SharePoint Management Shell and run the following command:

psconfig -cmd secureresources

3. Run iisreset in the command prompt. This will reset the registry permissions so that the WSS_WPG and WSS_ADMIN_WPG groups have read access to the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\15.0

Excel Services should now run successfully without crashing.

]]>https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/07/01/excel-services-crashes-continuously/feed/0PowerPivot for Excel – “Memory error”https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/04/29/powerpivot-for-excel-memory-error/
https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/04/29/powerpivot-for-excel-memory-error/#commentsFri, 29 Apr 2016 02:40:39 +0000https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/?p=1195You may receive the following error when attempting a data refresh using PowerPivot for Excel:

Memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.

This error may occur when using the 32-bit version of Excel, which enables you to work with up to 2GB of data in memory when using PowerPivot for Excel. The PowerPivot add-in runs as an extension of Excel and the PowerPivot Vertipaq in-memory engine loads within the same process space. As 32-bit Excel is limited to a 2GB virtual address space, once you start adding up all of the uses of that virtual address space, including Excel, all of the add-ins, and the in-memory database itself, the largest PowerPivot workbook that you can create on a 32-bit machine may actually be much lower than 2GB.

Installing the 64-bit versions of Excel 2010 and PowerPivot for Excel 2010 would increase the 2GB limit to 4GB. Furthermore, if you use 64-bit Excel 2013, there is no imposed file size limit, so you would be able to fully utilize the amount of memory you have installed when using 64-bit PowerPivot for Excel 2013. Alternatively, you could try reducing the imported datasets and tables by applying filters in the Table Import wizard to avoid hitting the limit when using 32-bit Excel and PowerPivot.

]]>https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/04/29/powerpivot-for-excel-memory-error/feed/5PowerPivot Workbooks as a Data Sourcehttps://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/03/03/powerpivot-workbooks-as-a-data-source/
https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2016/03/03/powerpivot-workbooks-as-a-data-source/#commentsFri, 15 Feb 2013 13:53:00 +0000http://technet.qa.msdn.wdslab.com/excel_services__powerpivot_for_sharepoint_support_blog/2013/02/15/powerpivot-workbooks-as-a-data-source/We have been seeing some issues with customers using PowerPivot workbooks as a source file in new Excel PowerPivot files. There are a variety of errors that can arise from this process, and this blog is intended to provide you with some troubleshooting steps to resolve the issue.

You may or may not be aware, but it is possible to use a PowerPivot workbook as a data source for other data applications. For instance, one can use Excel and connect to a PowerPivot file on a SharePoint site as a PowerPivot data source. There may be a file on SharePoint site named BIFinance.xlsx. When creating a new PowerPivot workbook, we could specify our data source as the following:

http://sharepointsite/PowerPivot Gallery/BIFinance.xlsx

Fig 1: Using PowerPivot workbook as a data source

In a nutshell, when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook, the connection goes through a Redirector service on the SharePoint server and is ultimately routed to the SQL Server Analysis Services PowerPivot instance.

For more information on how to use PowerPivot workbooks as a Data Source, please see the following video:

2. The client machine making the call or the SharePoint servers do not have updated Microsoft SQL Server Analysis Server OLEDB and ADOMD.Net drivers. You may need to check with your SharePoint administrator to determine what version of PowerPivot is installed in your SharePoint environment.

4. The user that is making the connection needs to have read access the root of the web application. Go to Central Administration > Application Management > Manage web applications > User Policy and give read access to user.

7. If you see this error: "PowerPivot Web service returned an error", try the following:

a. The PowerPivot workbook being used as a data source needs to be in Trusted File Locations in the Excel Services settings. Go to Central Administration > Application Management > Manage service applications. Select the Excel Services application, click on Trusted File Locations, and add the location of the workbook.

b. The PowerPivot System Service account (PowerPivot application pool account) needs to be an administrator of the PowerPivot Analysis Services instance. Connect to the PowerPivot instance in SQL Server Management Studio, right-click on the instance, select Properties, select Security and add the account.

The steps above should resolve most of the errors that are seen when attempting to use a PowerPivot workbook as a data source within Excel PowerPivot.