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

Workaround for lack of PowerPivot API in Excel

A few days ago a post about VSTO-based PowerPivot Workbook in the PowerPivot Team Blog shown a workaround for the lack of PowerPivot API in Excel. In reality, I don’t like this workaround at all, because it relies on displaying message boxes to the end user in order to click “Update” button in the PowerPivot Ribbon. I understand the reasons why Microsoft probably is not publishing such API: doing that, they should support it for many years and probably they want the freedom to change it in the upcoming releases. I don’t know whether they already did it between v1 (2008 R2) and v2 (Denali), but my speculation is that a future version of Excel might integrate PowerPivot in a stronger way and the logical consequence is that its API would become part of the Excel object model. As I said, this is only my hypothesis and I don’t have any data to support it.

That said, the problem is that the lack of a PowerPivot API is really a missed opportunity today. There are companies, like Predixion, that take the risk of accessing to an undocumented and unsupported API to run their solutions. They generate PowerPivot workbooks from scratch with their Add-In, using PowerPivot compression to ease the data transfer from customer to their cloud infrastructure. They know they might have to rewrite part of their add-in for every new version of PowerPivot, and in case of any issue, they cannot rely on Microsoft support. It is a very expensive choice that cannot be justified in a large number of scenarios.

Between these two extremes, I would like a simple workaround to simply automate the PowerPivot Refresh operation from VBA/VSTO. Even in an unsupported way. Do you have the same hope?

Published Monday, August 08, 2011 5:22 PM 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

 

Jeff said:

Yes please!

August 8, 2011 11:27 AM
 

Hrvoje Piasevoli said:

Hi Marco, yes absolutely a missed oportunity that could have solved a lot of scenarios. Just today I have finished a concept of feeding the PP model from cube report cell - reading the server custom drill through action which by the way is easily constructable using the PivotTable object model. The only missing part is the refresh tables piece. But at least it allows me to use the multimillion molap dimension attributes (through the fact table) in the already prepared dashboards based on the power pivot model. It would be perfect if I could just update the PP table source query using this macro instead of teaching the users to either update PP with the linked drill through table or copy paste the drill through mdx in the query editor.

And if there was a way to union the results of two or more drillthroughs or incrementally add source rows I'd even solve the multiselect problem easily. Calculated cells and utility dimensions are not a problem when you go the macro path.

Best regards

August 8, 2011 1:10 PM
 

Marco Russo (SQLBI) said:

Thanks for the feedback Hrvoje.

Just a question: do you create a drillthrough MDX query on the fly and fill an Excel table that you use as a Linked Table in PP? Just to understand how your solution works.

Marco

August 8, 2011 1:22 PM
 

Eugene Asahara said:

I would go wild with the capability to intergrate into PowerPivot.

August 8, 2011 2:19 PM
 

m-b said:

I have the same hope as well. I've read somewhere though that in v2 linked tables are automatically updated when the Excel data changes? If that's the case you could update the Excel data using VBA and then the PowerPivot data would be refreshed as well. That's what I tried as a workaround in v1 but it didn't work, would be great if it does in v2 (haven't been able to test it yet).

A question though; doesn't having your data in both Excel and PowerPivot 'double' your data in de workbook?

August 8, 2011 3:33 PM
 

Marco Russo (SQLBI) said:

I haven't seen any difference between v1 and v2 regarding linked tables refresh. Where did you read that?

Marco

August 9, 2011 11:26 AM
 

m-b said:

I read it in a comment on PowerPivotPro:

http://powerpivotpro.com/2011/07/12/powerpivot-v2-ctp3/

Shame if it isn't true...

August 9, 2011 12:04 PM
 

Marco Russo (SQLBI) said:

I read it, but the Automatic feature was *already* on v1.

Now we can discuss what "Automatic" should mean reagarding to Linked Table updates, but the v1 and v2 behavior are the same to this regard.

Marco

August 9, 2011 12:15 PM
 

m-b said:

Ah ok. If I remember correctly the 'Automatic' feature only updates the linked table when you manually activate the PowerPivot window. In my opinion it should mean that when the Excel data changes the PowerPivot data will instantly change as well. If that was the case you could use VBA to pull the data into Excel (which is linked to PowerPivot) and then refresh the PowerPivot data source and the pivot tables. That way you could sort of automate PowerPivot but you would be limited to the 1 million row limit of 'regular' Excel...

August 9, 2011 1:11 PM
 

Marco Russo (SQLBI) said:

I know but this (waiting for PowerPivot window activation) is an expected behavior. Consider that every refresh requires the whole table to be read and moved to a PowerPivot table. If this would happen for every single cell change, it would be very slow. There could be a better way to handle that in an automatic way (i.e. waiting for Idle and then refresh PowerPivot) but that's the actual implementation today.

Marco

August 9, 2011 1:17 PM
 

m-b said:

Thanks for the clarification, I guess it makes sense in the current implementation. Shame there isn't some sort of workaround but I reckon Microsoft rather wants you to invest in SharePoint then enabling you to automate PowerPivot in other ways...

Which reminds me; is there a way you can update the PowerPivot data based on parameters within cells in Excel? That's another thing I'd love to see a workaround for.

August 9, 2011 1:31 PM
 

p vc said:

Marco - since we (you and I and these other folks) are only looking for the refresh functionality, can we not use the same undocumented API that Predixion is using?  Really just one function call is all we need for now - such a simple thing.  I would absolutely be willing to use an undocumented and unsupported function - if it changes, then so be it, but in the meantime it will certainly help me automate something that is just a big annoyance for my customers - and frankly quite embarrassing as it requires numerous manual steps that harken back to another century.

Does anyone out there know the dll/api/ whatever predixion with their intimate connections to MS used?  Surely if an unaffiliated company can get this info then so can some of the MS loyal customers or other ex-employees (like the Predixion folks).  Anyone?

August 10, 2011 2:23 PM
 

Marco Russo (SQLBI) said:

Most of the PowerPivot user interface code is written in .NET. The internal engine is a C++ DLL and the communication between these two layers is handled by using OLEDB for OLAP connection, I believe. Thus, there should be at least two ways to implement the refresh:

1) open the OLEDB connection with the internale engine (SSAS) and request a process operation by sending a XMLA statement

2) call internal method of the PowerPivot DLLs by leveraging on Reflection in .NET

Even if the latter might seem better, the former is probably more robust.

I just don't have other time to spend on this! :)

Marco

August 11, 2011 11:39 AM
 

p vc said:

Thanks for pointing me in the right direction Marco!

Hmmmm.... that sounds pretty complicated :)  However I will poke around and see if I can get anything to work.  If I do I will post the how-to, and reply to you here with a link.

In the meantime, if anybody on the Microsoft team feels like posting unofficial how-to instructions somewhere then I would be eternally grateful! :)

can't wait for the official API to appear (eventually).

August 11, 2011 1:35 PM
 

Ron said:

Hi Marco,

I did some reflection. The only clear refresh-related methods I found was in a namespace called Microsoft.AnalysisServices.Modeler.DataRefreshWizard.

There is a class called DataRefreshProgressObject which has 2 methods: StartDataRefresh() and StartDataRefreshAll().

Ron

August 12, 2011 4:17 PM
 

Hrvoje Piasevoli said:

Hi Marco, sorry for the late response, I missed your question... "do you create a drillthrough MDX query on the fly and fill an Excel table that you use as a Linked Table in PP?"

I am supporting 2 scenarios, first being customized drill through and reusing the same result table. The second is copying the DT mdx in clipboard for the user to paste in the source query inside power pivot to overcome the 1M limitation and duplicate data. It's still not quite satisfying, and I'll have a look at what can be done using XMLA thanks for the hints.

Thanks to all for the useful and interesting discussion.

August 13, 2011 4:05 AM
 

Marco Russo (SQLBI) said:

Thank you Hrvoje!

August 13, 2011 6:04 AM
 

Marco Russo (SQLBI) said:

Ron,

to refresh table probably you should:

1) Get a connection to PowerPivot sandbox engine

2) Get the list of tables (look at the Tables property in the Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox class)

3) Call the Process method on each table

Otherwise, you should use XMLA connecting directly to the sandboxed engine.

Marco

August 13, 2011 10:13 AM
 

Ron said:

Marco,

for the DataModelingSandboxConnection I need a connectionStringInput. I've tried several options like:

string connstring = @"PROVIDER=MSOLAP;DATA SOURCE=C:\inetpub\wwwroot\test.xlsx";

string connstring = @"PROVIDER=MSOLAP;DATA SOURCE=http://localhost/test.xlsx";

With the datasource pointing at the file location I receive a {"The connection string is not valid."} error. When putting the xlsx on a webserver I receive a {"The remote server returned an error: (404) Not Found."} error, although when debugging the error I see the connectionstring is extended with some extra info. Under the hood the following is created:

Provider=MSOLAP;Data Source=http://localhost/test.xlsx;Locale Identifier=1033;SQLQueryMode=DataKeys

Any ideas?

Ron

August 13, 2011 4:06 PM
 

Marco Russo (SQLBI) said:

You have to call the API in-process with Excel.

Take a look at the embedded connection used by PowerPivot in any workbook:

Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

August 13, 2011 5:38 PM
 

Ron said:

I saw that connection string in the connections in Excel.  

I will see if we can use VSTO, but I guess that will be in a few weeks after my holiday (to Italy :))

If somebody else can find some time to take it from herer. I used Red-Gate reflector for examining the APIs.

Ron

August 14, 2011 1:33 PM
 

Marco Russo (SQLBI) said:

Have a nice time in Italy and, if you have time, don't miss Torino!

August 15, 2011 5:22 AM
 

Steve Bartalos said:

Check out PowerPlanner (www.power-planner.com), 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:13 PM
 

Rishi said:

Hi Marco,

Any idea how I can use VBA (Excel 2010) to update the powerpivot data connection location/file path please?

December 5, 2013 12:32 PM
 

Marco Russo (SQLBI) said:

December 6, 2013 5:38 AM
 

Navin Kumar said:

Hi Guys does any one have any script to do the refresh action in power pivort.

April 1, 2014 8:54 AM
 

Marco Russo (SQLBI) said:

Navin, follow the link in my previous comment.

April 1, 2014 2:23 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