Data connectivity issue when publishing PowerPivot Excel file to SharePoint, and how to work around it?

One of my customers is very impressed with the capabilities of PowerPivot, particularly the analysis capabilities but even more the publishing capabilities. With that I mean the ability to publish a dashboard to a SharePoint site, after which it can be experienced directly in the browser, including filtering and slicing for end-users.

As we publish our PowerPivot results to a SharePoint site, we get the following error for any action that triggers the data connection to refresh:

The data connection uses Windows Authentication and user credentials could not be delegated

I've done a lot of research on this one and it seems it is a configuration issue on the SharePoint side. Note though that we are using a cloud hosted SharePoint thus the environment is not under our control. In addition, even our own team mentions this to be a security restriction that will not be lifted.

Therefore, I'm not working on solving the above problem, rather on avoiding it alltogether:

My first experiment was to build a "normal" Excel file without PowerPivot. Same data and I managed to build the same pivots. Both the data and the pivots are in the same file, without a data connection. Publishing it works just fine. The error is not experienced this time, and even interacting with the report via slicers works.

As a second experiment, I wanted to follow the same scenario, but this time using PowerPivot. From data in an Excel sheet I created a so-called "linked table" in PowerPivot. Next, I created some pivots that make use of this table. The pivots are in the same Excel file as the original data. When I publish this file to SharePoint, I get the same error mentioned before when doing anything that refreshed the data connection. Even though the data and pivots are in the same file, it still pops up with this security error, which surprises me.

How can I work around this data connection issue when a PowerPivot is published? We'd like to have both the analytical power of PowerPivot as well as having the rich publishing options of Excel, without running into the data connectivity issue. Is it possible to "flatten" a PowerPivot file to "normal" Excel, since experiment #1 shows that this works fine. How can I remove the data connection from PowerPivot and tell it to just use the Excel data in the very same file?


ANSWERS:


Do you have PowerPivot for Sharepoint installed?


Is it Pivotstream providing the cloud service?



 MORE:


 ? Map Chart works in Power Map but does not work in the Power View Map
 ? How do I get Excel to use Formatted_value in SSAS cube
 ? How to enable user to edit Power Pivot lookup data
 ? What algorithms are suitable for this simple machine learning problem?
 ? What algorithms are suitable for this simple machine learning problem?
 ? What algorithms are suitable for this simple machine learning problem?
 ? What is the best technique/algorithm to compare trees changes using machine learning?
 ? What are units in neural network (backpropagation algorithm)
 ? line (travel path) clustering machine learning algorithm
 ? A easy learning machine program