A Peek Inside: Updating data in your embedded PowerPivot workbook

Recently I have been seeing some threads about how data is updated in a PowerPivot workbook. As it turns out, this can be a pretty complicated technical situation, so lets start off with a general statement: There are only 2 ways to update the embedded data contained in a PowerPivot workbook:

1. Bring the workbook down to the client machine, use the PowerPivot Excel add-in and click on “refresh”, then republish the workbook back to SharePoint (which implies certain SharePoint rights to enable)
2. Use the build-in PowerPivot data refresh facility (which also implies certain SharePoint rights)

That is it.

But as you guess, there is more to the story. So let’s start a ‘geek session’ and drill down into the type of data source. The update story really depends on if the user has defined a data source using a trusted connection or a SQL login connection. So let’s take them one by one.

If using trusted Windows authentication against the data source, let’s look at this sequence of events:

1. User A creates a PowerPivot document that access a relational DB using user A’s interactive credentials.
2. User A publishes to SharePoint.
3. As User A has contributor rights against the document, she can set up a data refresh schedule. As part of the schedule, she specifies what Windows credentials to use for the data refresh. The creds are stored encrypted in SharePoint Secure Store. This is specified once for the entire job (as this is the Windows environment that the job runs in).
4. When data refresh runs at 2am, the data is updated using the schedule’s credentials. The Windows creds must have contributor rights to the file because we read and write the file using the job’s Windows cred’s SharePoint rights.
5. If User B is allowed to copy down the document (which he can do if he has READ rights, but he cannot do if he has VIEW ONLY rights) and he refreshes the connection using the PowerPivot Excel add-in the DB accessed with User B’s interactive credentials.

If using SQL logins against the data source, the sequence is slightly different:

1. User A creates a PowerPivot document that access a relational DB using the data source creds specified in the connection – it would likely NOT be User A’s Windows uid/pwd.
2. User A publishes to SharePoint.
3. As User A has contributor rights against the document, she can set up a data refresh schedule. As part of the schedule, she can specify the non-Windows data source credentials to use for the data refresh. The data source creds are stored encrypted in SharePoint Secure Store. This must be specified on a data source by data source basis. The Windows creds specified for the schedule must have contributor rights to the file because we read and write the file using the job’s Windows cred’s SharePoint rights.
4. When data refresh runs, the data is updated using the schedule credentials by modify the connect string to include uid/pwd.
5. If User B is allowed to copy down the document and he refreshes the contents using the PowerPivot Excel add-in, then he may or he may not be prompted for the password. The default setting is to re-prompt for the password, but if User A wanted to, she could have embedded the non-Windows auth password in the connection and User B would re-use it also.

I hope that clears things up a bit.

Enjoy.