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?