THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Automate PowerPivot Data Refresh in Excel

In many workshops and sessions about PowerPivot the most frequently asked question has always been: how can I automate the PowerPivot Data Refresh in Excel? I talked about that a few weeks ago.

The sad answer is that this feature is not supported in PowerPivot v1. And from what I can see in CTP3, it will still be unsupported in PowerPivot v2, too. However, once you understand that what I’m going to cover is completely unsupported and might damage your PowerPivot workbook making it unreadable (so please make a copy of your original workbook before starting), I think it is a good idea to make a few consideration about that.

The first consideration is about what Excel can do. By using VBA or VSTO you can access to the Excel object model and you can force the Refresh of a PivotTable. When you refresh a PivotTable connected to a PowerPivot data model, you are simply instructing Excel to request data from an external engine. PowerPivot is considered an external engine, even if, from a technical point of view, it runs inside the same Windows process of Excel. The communication between Excel and the PowerPivot engine is made through an OLEDB connection, which is used to transfer XMLA commands by using the same syntax used to communicate with a regular Analysis Services instance. Thus, a first approach is to inject an XMLA command on that connection requesting the PowerPivot engine to refresh a table of your PowerPivot data model.

A second approach might be to reverse engineer a few .NET DLLs that have been written to implement the PowerPivot Excel AddIn. With some hacking you might access this API within your Excel workbook and then call some internal undocumented API.

Unfortunately, none of these approaches is really safe. The .NET DLLs reverse engineering is not easy, you have to interact with a large number of undocumented APIs and you might be in trouble looking for a safe way to call them (threading model is just one of the possible issues to consider). The XMLA approach is somewhat more documented. However, Microsoft explicitly says that this type of approach is not supported in PowerPivot for Excel (it will be different with Denali BISM Tabular models, but it’s out of scope in this discussion).The unsupported statement I mentioned has an important consequence: “This may appear to work but it is a dangerous solution and has the potential to break/damage your workbook unexpectedly. The recommendation is to stick with the PowerPivot refresh mechanisms despite the fact that they do not let you automate things.” Thus, it seems that doing these operations outside from the PowerPivot AddIn might break the Excel file, producing an unreadable workbook. If you think about that, losing the Excel file in this way could be very expensive, so plan a backup before starting and after every major release.

However, in case you are willing to continue after this warning, you should know that a 100% VBA solution is available on Tom Gleeson’s blog.  I have to repeat this again: don’t call Microsoft support in case you break your Excel files by using this technique. They would bounce you (and blame me for the link in this post <grin/>).

Published Thursday, September 08, 2011 2:58 AM by Marco Russo (SQLBI)
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Steve Bartalos said:

Check out PowerPlanner a New Planning, Budgeting, Forecasting tool for PowerPivot.

It supports Writeback, Top-down planning, Goal-seeking and Row-level security for PowerPivot.

September 15, 2011 4:08 PM
 

Bosky said:

Dear Marco, I am reading your book " powerpivot for excel 2010" and still on a learning curve and hence thought of taking your help. I am using an excel sheet to load data in power pivot. Data in excel sheet is auto refreshed by excel 2010 built in function.However, powerpivot table shows some empty rows on the top even if there are no empty rows in the connected sheet. Model worked fine initially but after some days started giving this problem. Tried everywhere but no help so request you to please help ur student :)

May 11, 2012 5:36 AM
 

Marco Russo (SQLBI) said:

Bosky, the empty rows you see at the beginning might be rows at the bottom of the table in Excel. Sort order of rows in PowerPivot may not be the same as in Excel. If you are importing data from external Excel files, check there are no "empty data" (cells where you wrote something and then deleted, you should remove the whole row in this case to be sure). If you're using a LInked Table, check that the definition of the table in Excel doesn't include empty rows at the bottom.

May 11, 2012 5:49 AM
 

Adding a "schedule once record" to the PowerPivot database instead? said:

Have you heard of anyone taking the approach of using the "Run once" setting using, and althering the run time using a scheduled job using SQL Agent?  It appears that you could do something with [DataRefresh].[ScheduleDetails] to make this work.    

Clearly the feature to run the job "not before" the scheduled time has a potential to cause an issue if you keep scheduling the refresh evey X minutes and the scheduler decides not to run it based on performance.   You'd probably need to have some logic to check for an existing "run once" record and not to enter a second record, but it seems like there may be potential to update the time here and get the result you are looking for.

Essentially, I'm asking if you know of anyone using SQL Agent to update the value of the next run in the PowerPivot refresh tables so that they can schedule more than one run a day.  It seems pretty straightforward but I don't want to test this in my production farm :)

August 27, 2012 2:56 PM
 

Marco Russo (SQLBI) said:

I have to say that I remember having discussed this technique, but I don't know somebody who really made a test. It is of course not supported by Microsoft, but this doesn't mean it couldn't work.

Please keep me update if you make some test (best to do in a non-production environment first, anyway!).

August 27, 2012 3:07 PM
 

Henry Velez said:

Hi Marco, I wonder if you can help me, with a problem actually I have that is about this topic, in fact i'm using as reference the book Alberto Ferrary and you published "Microsoft Excel 2013: Building Data Models with Power Pivot", so what i did for now is create a new model from VBA using a procedure with parameters, after that I tried to refresh it but i Got an error so this is the code im using:

this code works fine and its used to create de model in power pivot but i dont now there is a way from code to change the tab's name into the power pivot?

Dim ConnString As Variant

Dim CommandText As Variant

ConnString = Array( _

       "OLEDB;Provider=SQLOLEDB.1", _

       "Integrated Security=SSPI", _

       "Initial Catalog=CIS_Incubation", _

       "Data Source=Co1ushgweb02;" _

       )

CommandText = QueryStrPP & " 'Platform_Report', " & Segment & ", " & SQLCAL & ", " & AssumptionToFilter & ", " & Upgrade & ", " & SQLBI & ", " & SQLSTD & ", " & UpAssump & ", " & SQLSTDSAPrice & ", " & SQLENTSAPrice & ", " & SQLCALSAPrice & ", " & SQLSTDPROCSAPrice & ", " & SQLENTPROCSAPrice & ", " & UpsellTarget & ", " & SQLENTPROCPrice & ", " & SQLENTPROCPriceSA & ", " & Procs & ", " & SQLSTD2Cores & ", " & SQLENT2Cores

ActiveWorkbook.Connections.Add2 _

   Name:="PowerPivot Quadrants Connection", _

   Description:="PowerPivot Quadrants Connection", _

   ConnectionString:=Join(ConnString, ";"), _

   lCmdtype:=2, _

   CommandText:=CommandText, _

   CreateModelConnection:=True, _

   ImportRelationships:=True

after i created the model i mark all the code as comment, and then I have another portion of code that tried to refresh the model using the sp with parameters

With ActiveWorkbook.Connections("PowerPivot Quadrants Connection").OLEDBConnection

   .BackgroundQuery = False

   ValuesPP = QueryStrPP & " Platform_Report" & Segment & ", " & SQLCAL & ", " & AssumptionToFilter & ", " & Upgrade & ", " & SQLBI & ", " & SQLSTD & ", " & UpAssump & ", " & SQLSTDSAPrice & ", " & SQLENTSAPrice & ", " & SQLCALSAPrice & ", " & SQLSTDPROCSAPrice & ", " & SQLENTPROCSAPrice & ", " & UpsellTarget & ", " & SQLENTPROCPrice & ", " & SQLENTPROCPriceSA & ", " & Procs & ", " & SQLSTD2Cores & ", " & SQLENT2Cores

   .CommandText = ValuesPP

   ActiveWorkbook.Connections("PowerPivot Quadrants Connection").Refresh

End With

this shows me a error 1004, Application-defined or object-defined error

Any comment will be helpfull.

Thanks :)

Henry

August 5, 2013 1:27 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement