tim laqua dot com Thoughts and Code from Tim Laqua

15Mar/122

Automating PowerPivot Data Refresh in Sharepoint 2010

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.

Comments (2) Trackbacks (1)
  1. Hi,
    We have 100 reports built in PowerPivot and the refresh is set. Sometimes it shows some errors such as
    == Error no 1 ==
    The data refresh job failed because it did not run within the time period allotted for running this schedule.
    == Error no 2 ==
    OLE DB or ODBC error: Login failed for user ‘domain\sp_farmadmin’.; 28000. A connection could not be made to the data source with the DataSourceID of ‘GUID’, Name of ‘SQLSERVER’. An error occurred while processing the ‘TABLE NAME’ table. The operation has been cancelled.
    ===
    I have 20 to 30 report fail to refresh on scheduled time, Is there any way to automate them or any tricks and tips or suggestions.

    Many thanks in advance!
    Regards
    Erkindunya

  2. Please can you give us the code for sharepoint 2013?


Leave a comment