PowerPivot, Data Models and Sharepoint. Validation of what's possible.

[alex1alex]

Hello,

I just spent an hour on the phone with Microsoft's Office 365 SharePoint Dedicated support trying to validate if Excel Services was configured on our SharePoint on-line systems.

I want to create an Excel worksheet, that has a data model and a Pivot Table and I want to upload it to SharePoint. Then, I want other users to be able to open up the worksheet via the Excel Web interface and be able to change the filters on the PivotTable. As is, they can open it up but when they try to change the Pivot Table filters they get this error:
PivotTable Operation Failed

An error occurred while working on the Data Model in the workbook. Please try again
​

The excel worksheet is being loaded in the the Excel Web App (which I know does not support interactions with the Data Models)

I want it to be loaded in the Excel Services (aka, SharePoint Server with Excel Web App (SharePoint view mode))

Because in this mode Sharepoint will: "View and interact with Power Pivot data models" (technet link )

He kept saying that my problem was that I was uploading a Workbook with PowerPivot and that is just not supported in Sharepoint. He confirmed that Excel services where running...but not now it was configured...(I couldn't find a technet article that explained how to look this up while we were on the phone)

I guess my question is, am I totally misunderstanding what's possible? Doesn't "View and interact with Power Pivot data models" mean "open up the worksheet via the Excel Web interface and be able to change change the filters on the PivotTable."?

I think our Sharepoint is not configured correctly and that the support person didn't know enough about BI on sharepoint to say one way or the other.

As is, he's sent me a follow up saying that i need to open a ticket to ask about a configuration change but I'd like to get my ducks n a row before doing that.

I would really appreciate some perspective here.

Kindest Regards,
Alex


(PS: obviously Microsoft's naming of things could be less confusing)

 

[Tianbas]

Iam not really an Expert in this but you are right, if a PowerPivot is running in an Excel Web Part (aka Excel Services) on a Sharepoint Enterprise Server you can use all the slicer and interactions with the data model.

If you say "loaded in the Excel Web App" what exactly do you mean, just uploaded into a sharepoint library or did you really insert a new web part on your sharepoint page (categorie = Business Data, Web part = Excel Web Access) that refers to a PowerPivot file in a sharepoint library ? As you talk about "opens" the workbook it sounds like the first. To have the interactions you need the second. This means the user goes on your sharepointpage and directly sees the report with slicers and so on and no excel file to open.

 

[alex1alex]

Hi Tianbas,

Re: "loaded in the Excel Web App" . That's where I upload the XLS to a library, and then you "view it on the web" and it looks like you open it in Excel but the Excel is just a web application (instead of the real Office Excel that you install on your laptop.).

Reading the description at the top of this...... link ..... I interpret that as not needing to do the "Web part" thing you mentioned.
Can you read that and tell me what you think?

Thanks,
Alex

 

[Tianbas]

I dont know if you can run Excelservices Reports other than with Web Parts. We use the Web Parts and its working Sharepoint is really complex and it took us a long time with our IT to get it running as we wanted it.

 

[scottsen]

You *can* display it in a web part, but that should be required. This really does sound to me like O365 is just suckin'. However... *maybe* this required a Power BI enabled O365 site? I didn't think so...

 

[alex1alex]

@scottsen, assuming that should be "but that should NOT be required"

I'm having trouble finding a succinct explanation that I can provide to the guy in my company who is the go-between to Microsoft.
This is the best that I've found so far.... Data models in Excel Services

And our offering has a yes next to Excel Services.... link

If we need PowerBI, then why is "Excel Services" written out separately? It's a bit confusing. Maybe the problem is just that MS isn't very clear on which configuration we're paying for.