shutterstock_184473665.jpg

Summit 7 Team Blogs

Bug Report: PowerPivot for SharePoint 2016 Thumbnails Not Generating

Overview 

While working with a customer's SharePoint 2016 farm, I discovered a significant bug when working with PowerPivot Gallery libraries (part of PowerPivot for SharePoint). Whereas normally the you'd get thumbnails of the objects in an Excel workbook (visible in the carousel view), we were getting no thumbnails at all. There were multiple objects in the carousel for the object, so it knew that something was supposed to be there, but the thumbnails wouldn't get created. 

After lots of analysis and troubleshooting, I eventually realized I found a bug in SharePoint 2016's Excel REST API. Long story short, Office Online Server was unable to create thumbnails for the PowerPivot Gallery due to a dependency on a localhost URL. This might not be an issue for you if you have a single web application, but if you have more than one and the PowerPivot gallery is in a web application that's not specifically configured in a certain way (and there can be only one configured this way), then the thumbnails will not generate. I believe this is a fairly significant bug for PowerPivot for SharePoint users who have more than one web application. 

Until this issue is resolved, I do not recommend using PowerPivot for SharePoint in SharePoint 2016 if you have more than one web application using the PowerPivot Gallery. If you do, do not expect to have thumbnails for the carousel. It is unknown if this issue extends to other aspects of Excel Services or PowerPivot for SharePoint.  

The Bug 

To demonstrate the situation, I created a single-server SharePoint 2016 environment (SP2016Full01). Office Online Server 2016 was deployed on a second machine (OOS01), and PowerPivot for SharePoint was installed in the first server. Three web applications were created: https://home.contoso.com, https://my.contoso.com, https://other.contoso.com, and each were created according to standard practices: SSL, host headers, and dedicated IP addresses (so as to not have a dependency on SNI). In IIS, the bindings for each web application was configured with a dedicated IP address, the host name (or host header), and the certificate.  (Click images to enlarge in a new tab.)

   PowerPivot_Thubmnail_Bug1.png

PowerPivot_Thubmnail_Bug2.png

The PowerPivot web application solution was deployed to each web application.  

A custom database (Data) was created with three tables, one per web application: Home, My, and Other. Each table had some throwaway data.  

In the root site collection of each web application, the PowerPivot site collection feature was activated, and a PowerPivot Gallery was created, each named according to its web application (HomeGallery, MyGallery, OtherGallery). An Excel workbook was created for each web application. The workbook included a data connection to the custom database and its table. For example, Other.xlsx queried the [Data].[dbo].[Other] table and displayed the data on the worksheet. A credential stored in the Secure Store Service would be used for data refresh, and the Target Application ID was added to the data connection configuration.  

PowerPivot_Thubmnail_Bug3.png 

The three workbooks (Home.xlsx, My.xlsx, and Other.xlsx) were uploaded to their respective PowerPivot galleries. The workbooks were fully editable in the browser, demonstrating that Office Online Server is fully configured and operational.  

 PowerPivot_Thubmnail_Bug4.png

Note: Except where specified, all sites and workbooks effectively work the same and thus their screenshots are interchangeable. As such, you can generally ignore which site/workbook is in view in the screenshots. 

Normally, Office Online Server would then generate a thumbnail for the workbook. Each object in the workbook would have its own thumbnail. As we see below, it did not create the thumbnails but instead shows the generic image with the hourglass (indicating it's waiting on thumbnail generation). 

PowerPivot_Thubmnail_Bug5.png 

Note that in SharePoint 2016, the thumbnails are generated using the Excel REST API instead of the old gallerysnapshot.exe and getsnapshot.exe processes. This is described in https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2017/01/20/changes-to-powerpivot-gallery-and-snapshots-in-sharepoint-2016/. The thumbnail generation is triggered on the Web Front End whenever the item is uploaded/modified.  

Upon further examination, the SharePoint ULS logs indicated the issue: 

Capturing Report Gallery snapshot information from the following URL:
https://localhost/_vti_bin/ExcelRest.aspx/HomeGallery/Home.xlsx/model/Sheets
Snapshot Exception: Unable to take snapshots or get details of the file:
https://home.contoso.com/HomeGallery/Home.xlsx from the uri: 
https://localhost/_vti_bin/ExcelRest.aspx/HomeGallery/Home.xlsx/model/Sheets.
 
Snapshot Exception: Ensure localhost are allowed.

In the above messages, note that "https://localhost/_vti_bin/ExcelRest.aspx" was used for the call to the Excel REST API. We know that it's aware of the actual, full URL of the workbook as evidenced in the second message. However, it appears that the REST API is simply taking the relative path ("/HomeGallery/Home.xlsx") and prepending it with the localhost Excel REST API endpoint ("https://localhost/_vti_bin/ExcelRest.aspx"). The true domain name ("https://home.contoso.com") is not used to load the data stream. 

 

When the localhost URL (https://localhost/_vti_bin/ExcelRest.aspx/HomeGallery/Home.xlsx/model/Sheets) is loaded on the SharePoint server, the page is not found. However, if you replace "localhost" with the actual domain name (making "https://home.contoso.com/_vti_bin/ExcelRest.aspx/HomeGallery/Home.xlsx/model/Sheets"), then the REST API works and you get the correct data feed for the workbook's sheets. This proves that the Excel REST API fundamentally works, but it just doesn't work with the localhostURL.  

Why is this happening? Remember, the web applications were created according to standard practice with a host header (home.contoso.com) and a dedicated IP address. When the call to localhost comes from the Excel REST API, IIS is unable to match it with any of its web applications. In order for it to work in a scenario with host headers and/or dedicated IP addresses, a web application would either have to have "localhost" as a host header or have a binding set to 127.0.0.1. That's not going to happen with a SharePoint web application. So, what do we do? 

The Workaround 

In order to solve the issue (really, I think it's a workaround), in IIS, I changed the binding of the home.contoso.com web application to no longer have the host header and the dedicated IP address. 

 PowerPivot_Thubmnail_Bug6.png

After doing this, I edited the workbook again. BTW, you can also trigger a thumbnail refresh by editing the item's properties and clicking OK. And guess what – I now have a thumbnail!  PowerPivot_Thubmnail_Bug7.png

This proves that, in order to generate a PowerPivot gallery thumbnail, there is a hard requirement for localhost to be able to resolve to a SharePoint web application. To put this another way, it is not possible to create a thumbnail on a web application that has a host header or a dedicated IP. 

It Gets Worse 

The workaround described above works fine if you have a single web application that uses PowerPivot galleries. But what about additional web applications? The answer won't thrill you. I did the following with the https://other.contoso.com web application. 

First, even after the workaround was implemented, the thumbnail still wasn't being generated in OtherGallery, the same as in HomeGallery before the fix.  

 PowerPivot_Thubmnail_Bug8.png

A change was made to the Other.xlsx workbook, but the thumbnail still didn't generate. Thumbnails are still generating in the home.contoso.com web application but not in other.contoso.com. 

The reason why the other.contoso.com web application doesn't generate thumbnails is due to the hard dependency on the https://localhost/_vti_bin/ExcelRest.aspx URL. Changing the host header and IP address worked with the home.contoso.com web app because it became interchangeable with localhost. The relative path (/HomeGallery/Home.xlsx/model/Sheets) works fine with localhost because it exists within that first web application. However, the relative path for Other.xlsx (/OtherGallery/Other.xlsx/model/Sheets) does not live in the home.contoso.com web application.

https://localhost/_vti_bin/ExcelRest.aspx/OtherGallery/Other.xlsx/model/Sheets will not work just the same as https://home.contoso.com/_vti_bin/ExcelRest.aspx/OtherGallery/Other.xlsx/model/Sheets won't work. 

The fact that the process still uses localhost with another web application is seen in the ULS logs.  

 PowerPivot_Thubmnail_Bug9.png

For example: 

Capturing Report Gallery snapshot information from the following URL: https://localhost/_vti_bin/ExcelRest.aspx/OtherGallery/Other.xlsx/model/Sheets 

Capturing Report Gallery snapshot information from the following URL: https://localhost/_vti_bin/ExcelRest.aspx/OtherGallery/Other.xlsx/model/Ranges 

etc. 

Interestingly, the following is in the log after calling the Excel REST API for each of the objects in the workbook: 

Capturing image from: https://other.contoso.com/_vti_bin/excelrest.aspx/othergallery/other.xlsx/model/sheets('sheet1')?$format=image 

Notice that it doesn't use the localhost URL. 

The IIS logs for the home.contoso.com web application also proves that the wrong web application is handling the call: 

 PowerPivot_Thubmnail_Bug10.png

Although the Office Online Server server may have potentially kicked off the request for the thumbnail, it appears that the call to the Excel REST API is being made from the SharePoint server, not Office Online Server. If this is really the case, then the bug exists in SharePoint. 

FYI, it appears that the ExcelRest.aspx calls in turn make calls to Office Online Server's /x/_layouts/xlrestinternal.aspx. For example, the following can be seen in its IIS log. It looks like there's a direct correlation between the ExcelRest.aspx calls and the calls below (even though the clocks aren't lined up perfectly). 

 PowerPivot_Thubmnail_Bug11.png

Conclusion 

From the evidence above, it is clear that there is a hard dependency in SharePoint 2016 on localhost when creating thumbnails in a PowerPivot Gallery library. The localhost URL must be able to resolve to an IIS web application and thus at least one web application must be configured appropriately (no host name and configured for all unassigned IP addresses). Even if this is the case, the PowerPivot Gallery must be in that one specific web application in order for the thumbnails to generate. It is not possible to have PowerPivot Gallery thumbnails in multiple web applications. 

This clearly is a bug that Microsoft needs to address in the very near future if they expect businesses to use PowerPivot for SharePoint and Office Online Server in SharePoint 2016.  

Note: This has been tested with Office Online Server running on Windows Server 2012 R2 as well as on Windows Server 2016. The thumbnail does not generate with either version of Windows.

SHARE THIS STORY | |