3

I've been setting up a kind of a BI solution in SharePoint with very limited SP experience, and even though it now kind of works, I'm rather confused about how it actually works. Let me elaborate on the set-up:

  • We have a SharePoint 2013 site to be used by clients
  • There's a SQL Server 2012 instance which has a database and data from a DW
  • We create reports in Excel 2013, utilising PowerPivot, so that there's a data connection to the SQL database
  • The Excel reports are then uploaded to the site
  • End-users open the reports in their browsers and should be able to refresh the reports with data from the SQL database.

In addition, since this is actually a test setup, the actual SQL database with the data and all the SharePoint internal databases reside in the same instance, and everything is inside a single server.

Now, I've managed to configure and install the following:

  • an Analysis Service instance in "PowerPivot for Sharepoint" -mode
  • Power Pivot for Sharepoint with all the service applications and application pools and whatnot
  • Excel Services
  • Secure Store with target applications for both Power Pivot and Excel Services
  • unattended accounts for both Power Pivot and Excel Services

and now when I upload reports into Sharepoint, open and refresh, it works, by utilising the "none" option in the Excel workbook connection authentication (I believe this means it goes all the way to the SQL database using the unattended account credentials).

But I'm confused: what does Excel Services do, and what does PowerPivot do here? Why do I have two Secure Store target applications, and why do they both have unattended accounts? It feels like I've set up two different ways of refreshing data, but I'm not sure because I've been really just making it up as I go. There's lots of documentation on either Excel Services or PowerPivot, but they don't make it very clear how they work in unison (or are they even both necessary).

The question, thusly, is: do I have unnecessary parts here? Perhaps the two unattended accounts?

Improve this question
0

I think you've got it about right. Last time I set one up we used one unattended account (SP_Unattended). Remember that Excel Services and Power Pivot are two separate products. Excel Services was around before SharePoint and was capable of running a workbook connected to a data source. Power Pivot, using the xVelocity Engine is a much more recent addition. If you build a SSAS tabular cube you are using the same service and a similar user interface to Power Pivot, it's all xVelocity underneath. Excel and Excel Services connect to Power Pivot differently, in that Excel accesses the Engine directly and Excel Services instantiates an SSAS Cube and talks to that. I'm not saying I'm an expert on this, I'm a Data Analyst. It sure is an interesting architecture, congratulations on your achievement :-), been there, and it was much the same for me.

Improve this answer
  • Thanks, though personally I could've used slightly stronger words than "interesting" about the architecture ;) I'm still not sure how or why I'm using both Excel Services and PowerPivot for SharePoint, when it seems that both are separately capable of connecting to a data source from a workbook that has been uploaded to a SharePoint site? – Juha K Jul 6 '16 at 5:41
  • Yeah but Excel can't connect to it's own PowerPivot in Excel Services like it does on the desktop, it uses a separate service, so it talks to that. It is fairly convoluted. Power Pivot is like a plug-in and Excel services doesn't do plug-ins. Like the way is doesn't do VBA either. Excel services can get it own data without PowerPivot if the Workbook say gets it's data straight from SQL Server without PowerPivot, but you'd lose all the PowerPivot functionality and have to load the data straight to a table on a sheet – MikeAinOz Jul 6 '16 at 9:48
  • Ah, I think I see now what you mean. I'm getting the PowerPivot functionality in the workbook in SharePoint through the PowerPivot service thing. Sure feels like they've just hacked it together over a long period of time, could use some serious refactoring... but hey, at least it works. Thank you! – Juha K Jul 7 '16 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.