PowerPivot

All About SharePoint Power Pivot Service Application...

Power Pivot Installation and Configuration for SharePoint 2013 & Sql Server 2014

Steps:

Make sure Excel Service application and secure store Service application is up.

Make sure excel service application unattended service account added under Secure Store Service application as following with correct account added,

Prerequisites:

svc.spd.excel account must have read access to source database from where you are pulling content to power pivot file.

Follow step 1 here to install PowerPivot https://msdn.microsoft.com/en-us/library/jj219067.aspx#InstallSQL using SP admin account.

Here SQL server analysis service account is svc.sqlddev.an.bi added for SQL service analysis Service while installing Analysis service on web front server dspwfe1.

Note: need to be added following accounts while installing Analysis Service on DSPWFE1

All service applications that require access to the Analysis Services server instance need to have Analysis Services Administrative permissions. For example, add the service accounts for Excel Services, Power View, and Performance Point Services. Also, add the SharePoint farm account, which is used as the identity of the web application that hosts Central Administration.

Then step 2 to configure basic analysis services SharePoint integration using dsp13wfe/PowerPivot instance https://msdn.microsoft.com/en-us/library/jj219067.aspx#bkmk_config

-          Added dev service pp.pool account here.

Then used step 3 to verify installation https://msdn.microsoft.com/en-us/library/jj219067.aspx#bkmk_verify
Then used step 4 to install PowerPivot for SharePoint add-in and Deploy SharePoint solutions files with PowerPivot for SharePoint 2013 Configuration tool.

https://msdn.microsoft.com/en-us/library/jj218792.aspx

Then step 5 to activate PowerPivot Integration Feature in CA under manage farm features.

Make sure PowerPivot service application unattended service account added under Secure Store Service application as following with correct account added,

We created powerpivotunattendedaccountest here,

Now set credentials by clicking on PowerPivot unattended account as following,

Use:

Credential owner account is svc.spd.admin and All Users (windows) account here. (Note All Users Accounts should be fine here)

Windows User Name account is svc.spd.excel or any account which should have at least read permission from databases. If any errors occurs, make sure user mapping for this account under database server and assign enough read permission.

Then step 6 to create new site collection using Business Intelligence template and create new PowerPivot library and make sure PowerPivot feature is active for that site.

Then step 7 to create a excel sheet containing power pivot content and make changes as following,

-          Select Use a stored account under connection properties tab and provide PowerPivot application ID.

-          Open ThisworkbookDataModel connection and make sure as following,

Click on authentication settings and select Use the authenticated users account as following,

Click on and Save it.

Note: we don’t need export connection file to local drive or SharePoint site here. It will work automatically with help of SharePoint power pivot add-in.

Go into library, upload file and select file to manage schedule refresh as following,
change following and add power pivot account

Click ok and go back to library and open file and refresh it connection for data refresh.

Make sure about added library path into trusted file locations and trusted data connection library under excel service application under CA.

-          trusted file locations

-          Data connection libraries

Note: PowerPivot should be installed on multiple servers including Web front end server and application servers as Microsoft best recommendations practices for redundancy approach but it is not mandatory that have to be installed on each server.

You don’t need SQL Server Instance on any SharePoint Server box.

You better install PowerPivot Application on any one of Application servers.

That’s all…it should work…Cheers…J