Of course we want to do this, it's a fundamental requirement. Dear Sharepoint, please refresh my PowerPivot when the data is ready, not on some arbitrary schedule. Until this functionality is built-in, people will continue to hack away at it to figure out how to make this happen. Is this supported? Certainly not - so continue at your own risk.
So the method we'll be using here is just to mimic what Sharepoint does when you check the box to "Also refresh as soon as possible" in the schedule configuration page. To accomplish this we open the profiler, connect it to whatever instance our Sharepoint PowerPivot database is hosted on and filter you TextData to "%Schedule%" or filter to just the Sharepoint PowerPivot database (SP2010_PowerPivot_Service_Application in our case) - then open up your test PowerPivot schedule configuration, check the box, click OK, wait for the schedule history to come back up and then stop the trace. Now you know you've got what you need, you just have to find it:
So here we have Sharepoint asking for information about the schedule (identified by the ItemID from DataRefresh.Items) and then passing the data it received to PersistSchedule. If you take a close look at the PersistSchedule parameters, the last one is @RunNow = 1. So clearly, this is what we're after. We went the most tedious route and manually looked up the ItemID in DataRefresh.Items:
SET NOCOUNT ON DECLARE @ItemID UNIQUEIDENTIFIER = '30D39B0E-DA8B-489B-A713-3B29D208B51F' DECLARE @ItemName [nvarchar](200), @SPSiteID [uniqueidentifier], @SPWebID [uniqueidentifier], @LastModifiedBy [nvarchar](450), @ScheduleLastUpdatedBy [nvarchar](450), @UserIdentity [nvarchar](450), @EmailNotification [bit], @EmailList [nvarchar](450), @SecurityConfiguration [int], @SSApplicationID [nvarchar](256), @ProcessAllDataSources [bit], @RV [bigint], -- Schedule info @Enabled [bit], -- Schedule details @NextProcessDate [datetime], @ScheduleStartDate [datetime], @FrequencyKey [nchar](1), @RepeatFrequency [int], @Sunday [bit], @Monday [bit], @Tuesday [bit], @Wednesday [bit], @Thursday [bit], @Friday [bit], @Saturday [bit], @Weekday [bit], @WeekendDay [bit], @Day [bit], @MonthlyPeriod [tinyint], @MonthlySpecificDay [int], @ProcessAfterBusinessHours [bit], @SpecificTime [datetime], @RunNow [bit] DECLARE @Schedule TABLE ( [ItemID] [uniqueidentifier] NOT NULL, [Enabled] [bit] NOT NULL, [RV] [bigint] NULL, [ItemName] [nvarchar](200) NOT NULL, [SPSiteID] [uniqueidentifier] NOT NULL, [SPWebID] [uniqueidentifier] NOT NULL, [UserIdentity] [nvarchar](450) NULL, [LastModifiedBy] [nvarchar](450) NOT NULL, [EmailNotification] [bit] NOT NULL, [EmailList] [nvarchar](450) NULL, [SecurityConfiguration] [int] NOT NULL, [SSApplicationID] [nvarchar](256) NULL, [ProcessAllDataSources] [bit] NOT NULL, [ScheduleDetailID] [uniqueidentifier] NOT NULL, [ScheduleStartDate] [datetime] NOT NULL, [FrequencyKey] [nchar](1) NOT NULL, [RepeatFrequency] [int] NOT NULL, [Sunday] [bit] NOT NULL, [Monday] [bit] NOT NULL, [Tuesday] [bit] NOT NULL, [Wednesday] [bit] NOT NULL, [Thursday] [bit] NOT NULL, [Friday] [bit] NOT NULL, [Saturday] [bit] NOT NULL, [WeekDay] [bit] NOT NULL, [WeekendDay] [bit] NOT NULL, [Day] [bit] NOT NULL, [MonthlyPeriod] [tinyint] NOT NULL, [MonthlySpecificDay] [int] NOT NULL, [ProcessAfterBusinessHours] [bit] NOT NULL, [SpecificTime] [datetime] NULL ) INSERT INTO @Schedule EXEC DataRefresh.GetSchedule @ItemID SELECT @ItemName = [ItemName] ,@SPSiteID = [SPSiteID] ,@SPWebID = [SPWebID] ,@LastModifiedBy = [LastModifiedBy] ,@ScheduleLastUpdatedBy = N'DOMAIN\tlaqua' ,@UserIdentity = [UserIdentity] ,@EmailNotification = [EmailNotification] ,@EmailList = [EmailList] ,@SecurityConfiguration = [SecurityConfiguration] ,@SSApplicationID = [SSApplicationID] ,@ProcessAllDataSources = [ProcessAllDataSources] ,@RV = [RV] -- Schedule info ,@Enabled = [Enabled] -- Schedule details ,@NextProcessDate = CAST(GETDATE() AS DATE) ,@ScheduleStartDate = [ScheduleStartDate] ,@FrequencyKey = [FrequencyKey] ,@RepeatFrequency = [RepeatFrequency] ,@Sunday = [Sunday] ,@Monday = [Monday] ,@Tuesday = [Tuesday] ,@Wednesday = [Wednesday] ,@Thursday = [Thursday] ,@Friday = [Friday] ,@Saturday = [Saturday] ,@Weekday = [Weekday] ,@WeekendDay = [WeekendDay] ,@Day = [Day] ,@MonthlyPeriod = [MonthlyPeriod] ,@MonthlySpecificDay = [MonthlySpecificDay] ,@ProcessAfterBusinessHours = [ProcessAfterBusinessHours] ,@SpecificTime = [SpecificTime] ,@RunNow = 1 FROM @Schedule EXEC DataRefresh.PersistSchedule @ItemID = @ItemID ,@ItemName = @ItemName ,@SPSiteID = @SPSiteID ,@SPWebID = @SPWebID ,@LastModifiedBy = @LastModifiedBy ,@ScheduleLastUpdatedBy = @ScheduleLastUpdatedBy ,@UserIdentity = @UserIdentity ,@EmailNotification = @EmailNotification ,@EmailList = @EmailList ,@SecurityConfiguration = @SecurityConfiguration ,@SSApplicationID = @SSApplicationID ,@ProcessAllDataSources = @ProcessAllDataSources ,@RV = @RV -- Schedule info ,@Enabled = @Enabled -- Schedule details ,@NextProcessDate = @NextProcessDate ,@ScheduleStartDate = @ScheduleStartDate ,@FrequencyKey = @FrequencyKey ,@RepeatFrequency = @RepeatFrequency ,@Sunday = @Sunday ,@Monday = @Monday ,@Tuesday = @Tuesday ,@Wednesday = @Wednesday ,@Thursday = @Thursday ,@Friday = @Friday ,@Saturday = @Saturday ,@Weekday = @Weekday ,@WeekendDay = @WeekendDay ,@Day = @Day ,@MonthlyPeriod = @MonthlyPeriod ,@MonthlySpecificDay = @MonthlySpecificDay ,@ProcessAfterBusinessHours = @ProcessAfterBusinessHours ,@SpecificTime = @SpecificTime ,@RunNow = @RunNow
The only parameters that don't come from GetSchedule are NextRunDate which we set to midnight of today and RunNow which is, of course, 1 and ScheduleLastModifiedBy (I just tossed my username in there, I assume you could use the system identity as well).
And then we set it up to refresh every 10 minutes via Agent job to test:
Happy refreshing! Let me know if you have any questions.