Hi,

I have SharePoint 2010, which I have uploaded a PowerPivot model onto.

Currently it doesn't seem like I could setup the Data Refresh service to refresh my model more frequent than once a day. The Data Refresh configuration page looks like this:

Which doesn't show an option for anything more frequent than daily.

I have also tried to refresh the model's database directly on the Tabular SSAS instance (which SharePoint is using to store PowerPivot models) via SSIS or XMLA, but I get an error saying the tabular model is in "ReadOnly" mode, which I could potentially bypass (by detaching and re-attaching the model), but thats starting to sound abit too hacky.

Is there any way I could refresh my SharePoint uploaded PowerPivot model more than once daily?

Cheers

• Moved by Elvis LongMicrosoft contingent staff, Moderator Tuesday, September 03, 2013 8:24 AM better for support

You could use Powershell with a similar approach to that described here to update Office 365 workbooks with embedded PowerPivot models.

http://technet.microsoft.com/en-us/library/jj992650.aspx

HTH, Martin

Hi Martin,

Thanks for the suggestion.

Although this solution works well in Excel 2013 (I have tested it), in Excel 2010 (which is what I am using) the "Refresh" command does not extend to refreshing data in PowerPivot, and is only limited to data on Excel itself (for example PivotTable data). So it does not work in my scenario, even tho the MSFT document does say it works for 2010, I believe this is incorrect.

Reprocessing the cube on the SSAS server directly seems to be a no-go either (even if you get around the ReadOnly issue), as it seems SharePoint almost randomly drops and re-creates the cube, and it will take considerable code to circumvent SharePoint's normal operation.

I am currently considering making a POST call to the SharePoint Data Refresh page (shown in the image above), with the "Also refresh as soon as possible" checkbox ticked. This will kick off the reprocessing schedule immediately... but again, this is starting to feel really hacky.

Hi ToO_slK,

We can only configure PowerPivot data refresh schedule at daily level in SharePoint GUI, this behavior is by design. I recommend you refer to the connect site below:
PowerPivot 2008/2012 for SharePoint Data Refresh more than once a day: https://connect.microsoft.com/SQLServer/feedback/details/739949/powerpivot-2008-2012-for-sharepoint-data-refresh-more-than-once-a-day

Please vote the feedback, if the suggestion mentioned by customers for many times, the product team may consider to add the feature in the next SQL Server version. Thanks for your understanding.

In addition, here is a workaround for your reference. Please see:
Using a custom data refresh schedule in #PowerPivot for #SharePoint: http://smithicus.wordpress.com/2011/08/09/using-a-custom-data-refresh-schedule-in-powerpivot-for-sharepoint/

Best Regards,

I ended up converting the PowerPivot project into a BIDS one, which I then uploaded into a Tabular SSAS. From there I can have full control over how the cube refreshes.

I have also created a .BISM file in SharePoint that points to this new cube, and then have edited all my .RDLX files (PowerView) to point to the new .BISM connection.

• Marked as answer by ToO_sIK Tuesday, September 03, 2013 8:33 AM
• Unmarked as answer by ToO_sIK Tuesday, September 03, 2013 8:38 AM

Thanks for pointing out the Connect ticket Elvis!

That workaround using a custom data refresh schedule is actually really helpful, exactly what I was looking for... Cheers!

I have done this two different ways now with sql 2012 and sharepoint 2013 integrated. You can make a sql agent job that will change the time in the powerpivot sharepoint database. Just take all the current info in whatever job is there for the work book and only modify the time. I did an update or insert because when you do this the job will go away eventually for some reason since you are always pushing it to a different time than it is expecting. Here is my code for the job. This is not Microsoft recommended I suppose, but it works. Similar to the other answer about modifying the powerpivot database, but thought I would include it because the tables seemed to have changed in Sharepoint 2013.

UPDATE [DataRefresh].[WorkQ] SET StartDate = cast(getdate() as date), 
StartTime = cast(dateadd(mi, 5, getdate()) as time) WHERE ItemID='62FC4369-DBE3-4EBD-9A78-4D49333DD85F'

IF @@ROWCOUNT = 0

    INSERT INTO [DataRefresh].[WorkQ] VALUES 
('62FC4369-DBE3-4EBD-9A78-4D49333DD85F',
'http://prinbi01/sites/IFSPROD/PowerPivot%20Gallery/MRP_Data_Model.xlsx',
'',
cast(getdate() as date),
cast(dateadd(mi, 5, getdate()) as time),
null,
'<?xml version="1.0" encoding="utf-16"?><TimingDetails xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:type="OnSpecificDaysTimingDetails" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.AnalysisServices.SPAddin.DataRefresh"><customTimeOfDay><hour>11</hour><minute>15</minute></customTimeOfDay><days>Sunday Monday Tuesday Wednesday Thursday Friday Saturday</days></TimingDetails>',
'UB54wMO4M1rwpULFYtXOECplRZMBSgsncb2hv4jqQq8=',
0,
0)

I just took the 70-467 exam recently and failed it barely, but this was one of the questions. How to update a powerpivot data model hourly. One of the answer choices was to script a process command with an agent job. I looked it up and tried it on my PowerPivot instance and it seems to work. Create a job with type "SQL Server Analysis Services Command" and then put this in.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Type>ProcessFull</Type>
 <Object>  
  <DatabaseID>PowerPivotDBNameHere</DatabaseID> 
 </Object>
</Process>

This seems to be Microsoft recommended best practice since it is the answer choice on their expert track exam.

Not sure if that is the same as the XMLA being done by OP, but it worked for me. Kind of weird that it was in read only mode.

Hopefully one of these will help someone.

• Edited by BPFrenchak 1 hour 8 minutes ago