PowerPivot Data Refresh, what settings to use and why

I was planning on writing a blog post on how data refresh worked and what each setting is and means. At the last moment i remembered a blog post by Dave Wickert from last year PowerPivot Data Refreshwhere he takes a look at the data refresh facility in PowerPivot for SharePoint and show you how to use it for maximum benefit. I couldn’t do it better than him so please read this blog to see what Data resfreh is all about.

What i would like to talk about today is what settings i would recommend when setting up Data refresh.

Before we start Dave makes two very good points in his article which i would like to repeat:

• For data to be refreshed it has to available to the server, which means when you use a txt file from your desktop. This file will never be refreshable from the server.
• Another strong point: just because you have the data in a workbook doesn’t mean you have to refresh it. Use auto refresh with thought, use it only when you really would need it.

There are two specific setting i would like to discuss, first the time a refresh should run:

In PowerPivot we have a new setting “After business hours”. The start and end time you can specify at the PowerPivot part of the Central admin.  What this setting does (thanks to Denny Lee) when you select it:

Basically the job runs “after business hours” in the interval that is set in the timer job – by default this is every minute.  So basically, the PowerPivot System Service starts after business hour and every minute spawns off threads to begin the process of each individual workbook model.  The system service is constantly checking what models have been processed, uploaded, etc. to ensure that the system isn’t overloaded as well – so it’s not firing off each workbook altogether but taking into account of the individual workbook data refresh times (that each user sets), “builds the map”, and the fires the next one in the queue.

So this will make sure you won’t run multiple refresh jobs at the same time and will make sure it runs at the first time available. I would recommend using this settings.

The second and most interesting part is of course the security setting, which user should you fill in.

Quoting Dave Wickerts explaination on what each setting is

• Who should the user be? There is one Windows user regardless of how many schedules are specified in this job. In this field you specify “Who” the user is that will run at 2am in the morning. It must be a Windows user. You have several options here, the end result of each one is an NT account:
  • “Use the data refresh account configured by the administrator” – in this case, the system will use the unattended execution account that is specified for the PowerPivot for SharePoint service application. Typically this is a low permissions account that has no trusted access to the various data sources. In this case, the data refresh facility will use the non-Windows authentication (i.e. sql logins) that have been specified on the connection string in the data sources.
  • “Connect using the following Windows user credentials” – The user can enter in a Windows username and password. We will store these credentials in the SharePoint Secure Store facility for the user. At 2am the data refresh facility will pull the credentials out and do a Windows logon using them – and the data sources can then make trusted connections.
  • “Connect using the credentials saved in Secure Store Service” – if the user has the ability to enter in their own Secure Credentials the data refresh facility will use them at 2am for the logon. If using this approach, the application ID must be a Secure Store group that includes the service account used by the PowerPivot service application (so we can read the credentials).

NOTE: These options are important because they establish the Windows environment for the job. If there are any trusted connections used in the workbook’s data sources, then this is the Windows user that will be logged on for the job; the credentials in the data source are used for non-Windows authentication..

I would recommend using “Connect using the following Windows user credentials” where the you enter the credentials of the person who created the workbook, most of the times this is the person who will set up the refresh anyway. I recommend this setting because this user always has access to all the data sources, databases, file shares or sharepoint documents he used for the PowerPivot docuement. The other two are uncertain to have access. Besides this being a self service BI solution this setting is the most easy for end users to understand.

Ow and please leave a comment if you think differently about these settings, i’m just a developer with my view of things 🙂