Automating PowerPivot Refresh operation from VBA – The Code

In my last post I explained how I ‘d managed to automate the refresh of a PowerPivot model from within VBA.

Download the example workbook from here …  UPDATE: New version of code available here … (This ‘trick’ will not work in Excel 2013, that’s the bad news, the good news is it’s no longer needed as PowerPivot (in 2013 known as the Data Model) is now fully supported directly from the Excel Object Model. I’ve added some code to check if 2013+, if so, use this new functionality).

 

Advertisements

78 responses to “Automating PowerPivot Refresh operation from VBA – The Code

  1. Pingback: Automating PowerPivot Refresh operation from VBA | Gobán Saor

  2. I tried it and it just works also in PowerPivot v1!
    It would be useful to recognize when the refresh in PowerPivot is completed so that you can refresh other objects in Excel depending on PowerPivot data.
    Good job!

    • Excellent.

      Might be possible using DMV meta data http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ to determine if a cube has been refreshed, need to look at it further. Another alternative is an old-fashioned check-sum sent along with the source data.

      The other nice-to-have would be the ability to refresh a single table, but I’m having no luck with that so far.

    • Hi Marco, I am new to this site and I just tried the script to refresh PowerPivot v1 and I am getting Error 1004 – Application or Object Defined Error. I can refresh the powerpivot table manually. Can you please help.

  3. To refresh a single table you have to send a Process command specifying a dimension – it becomes the process of a dimension, after all. The only issue is to retrieve the dimension name, which is the name of the table followed by a GUID. You can retrieve these commands by generating the processing script from SSMS in Denali. Here is an example (I’m not sure, but it might be working on PowerPivot v1, too):

    ProcessDefault

    TabularProject4
    Query_1e7e0dfe-e9c1-49a3-b43c-c6017c60e838

  4. Will give that a try, can see the DimensionIDs in the DMV query I use to get the DatabaseID GUID. I’m guessing a DimensionID property below DatabaseID within Object tab?

    I’ve never looked at SSAS (Denali or otherwise), my big iron BI experience was all Oracle and Essbase, guess I should one of these days 🙂

  5. Nice – let me know when you solve the problem to avoid the sleep before doing a Refresh of the PivotTable in Excel

    • @Marco,

      Now that I can separately refresh individual tables, one option would be to specify (or deduce) the tables to be refreshed; enclose in a Parallel Tag to take advantage of parallel loading (assuming local cube AS engine can take advantage of this?); finishing off the refresh with a “metadata table” outside the Parallel Tag (to ensure it runs in sequence after “business data” loads). This “metadata table” could provide the end user with useful stats and visual confirmation of a successful update and could also be used by VBA code to automate such checks (traditional DW star-schemas often used junk dimensions or Data Quality dimensions to provide similar service).

  6. Pingback: PowerPivot Refresh thru VBA at last! « PowerPivotPro

  7. Consider that parallel is not supported – even if you request parallel, data is processed sequentially. Both for PowerPivot v1 and Denali.

    • Makes it even easier then; specify the tables to be refreshed in sequence, check the DMV last update of the last table’s “dimension” (assuming last_update is only updated after, not during or before, refresh is complete).

    • When I added a large data set to my test model to check out what DMVs would work best for identifying a completed refresh (discover_sessions and discover_commands were the hot favourites) I discovered that the ADO call blocks while refreshing. So whatever I saw when testing (late at night) with my initial small sample, the process command isn’t asynchronous. Maybe a small 1 or 2 second delay to allow proper re-sync between Excel and the AS engine is all that’s required.

      Does bring up another problem with the connection timing out for very large datasets (defaults to 30 seconds I think, and can’t be changed once the connection is established) but I can get around that by using an ADODB.Command object (which has its own TimeOut property) instead of an execute call directly against the default connection.

  8. This is awesome. Fantastic job!

  9. Pingback: Automating PowerPivot Refresh operation from VBA – The Code « Extended Results, Inc.

  10. Pingback: SQLBI - Marco Russo : Automate PowerPivot Data Refresh in Excel

  11. As I wrote in my blog, this technique is completely unsupported by Microsoft and might break your Excel file (I don’t have more information to describe in which conditions this might happen).
    Just to warn some future BI developer regarding that!

  12. As Marco pointed out on his blog http://bit.ly/pp0UXz, a corrupted workbook could be very expensive (but then, PowerPivot itself has been known to do that without the help of any VBA hacks), but then again it could also be very cheap, such being the nature of spreadsheets.

    Backups, more backups and still more backups is the solution (and so easy to do in VBA).

    If people did end up corrupting and phoning the Excel help desk in mass complaint, then perhaps MS would get the message that the current refresh process is sub-optimal. I would actually be happy with a pure ribbon approach i.e. a button for “Update External PP Tables & Refresh Pivots”, “Update Linked Tables & Refresh Pivots”, “Update All Tables & Refresh Pivots”.

    Unlike the DLL approach , this method is documented (if not supported) in that the same steps could be used to update a normal SSAS database. However, in the case of PowerPivot there’s obviously some sort of com automation feedback into Excel (as I found out when I attempted to refresh from within a .NET add-in in UDF mode – automation calls are not allowed from UDFs). There’s obviously something been written back to the workbook which, if the refresh fails (an ADO timeout on a very large dataset would be an obvious fail-point), might result in corruption.

    If only the embedded database ends up corrupted, that shouldn’t be a problem as the meta data held in the workbook’s CustomXML parts seems to work well as a recovery method. But if the CustomXML parts themselves also end up zapped, then a return to previous backup would be the only option (unless a bit of XML hackery is something you enjoy).

    If anybody out there comes across any such problems please report them to this blog post or email or twitter me,

  13. I agree with you – I asked to Microsoft whether this approach would safe enough and they pointed me out that they don’t have a full test of this scenario and for this reason it is unsupported. My speculation is that an issue can happen in particular condition (i.e. an error in XMLA?) but this is the reason I wanted to highlight the potential issue.
    That said, this is better than nothing and I just warned people about doing their backup – it’s easy, after all, just copy a file .)

  14. Pingback: HAMMERing away at Automated PowerPivot Refresh | Gobán Saor

  15. Tom,
    You are a genius – finally Powerpivot without Sharepoint becomes a usable tool for BI authors.
    Thank you!

  16. Pingback: PowerPivot automatikus frissítése - Kővári Attila szakmai blogja - TechNetKlub

  17. I am unable to get to square one in your code (either version) because I get stopped on the

    Set cnn = ActiveWorkbook.Connections(cnnName).OLEDBConnection.ADOConnection

    line of code. “cnnName” is equal to “PowerPivot Data”.

    It gets past the initial Refresh but this gives me an Error 1004 – Application or Object Defined Error.

    This happens with cnn set as either an Object or as an ADODB.Connection

    I tried to play with the References and tried the ActiveX Ojects 2.8 Library. OI am running this on Win7 SP1.with CPT3 Denali PP.

    Any thoughts?

    Thanx
    Dick

    • Hi Dick,

      The line before …

      ActiveWorkbook.Connections(cnnName).Refresh

      … activates the connection (manually fetching something from the cube will do likewise). It this doesn’t happen, then the connection will not exist and you’ll get that error. I can simulate the error by commenting out the Refresh line (before I touch any PP pivot tables).

      If you’re getting the error after the a successful refresh that would be worrying. Try it after you’ve played with a PP pivot for a bit.

      The fact that this is the only way to get a handle on the connection is a worry, as MS could easily nobble it, by closing the connection between refreshes (as would happen with a normal data connection linked to say an Excel Table).

      I’m using XP SP3 and Denali, and it works okay.

      Tom

      • Thanx for your prompt response.

        I’m afraid I DO run the refresh from your code before this fails… I will try recalcing a CUBE Formula as well. I am using Win 7, Office without SAP1, PP CTP3 Denali with SQL Server 2008 R2.
        I’ll let you know if that works for me.
        Thanx again
        Dick

      • OK – I created a Pivot Table from the PP data and I refreshed that pivot table inside my code and THEN the ADOConnection worked. Now let’s see if the rest works for me 😉 .. I’m sure that this is going to be a major asset for me. I’ll let you know if it works and if it does I’ll owe you forever ;-).

        Dick

  18. Yes please do, and any help you need let me know. What’s “without SAP1” mean? And I wonder if the presence of SQL Server 2008 R2 has anything to do with the problem?

    Tom

  19. Sorry … I noticed the error after I posted … I meant “SP1” Oops

  20. That’s great Dick.

    Yes, you would need at least one PP PIvotTable for the Refresh to work. Forcing a cube formula to fire might also do the trick, but be aware CUBE formulas are asynchronous.

    • Thanks very much Gab. This is the first time I am visiting this site and I just followed the steps, could refresh the powerpivot without any issues for one table (assigned tablename to lTable). How do i loop through all the tables? Thanks again.

    • Or Can I refresh tables in powerpivot in one shot ?

    • Sorry. Typo. I mean to say ALL tables in one shot. Thanks.

  21. OK – got the DatabaseID, injected it into the XMLA code and when I try to run it it gives me “Runtime error -2147467259 – No cube specified – A cube must be specified for this command to work”.

    The Table in my PP database is a linked Access Table. Could it be that the source needs to be an actual SSAS OLAP Cube to work? Hope not.

    Thoughts?
    Thanx
    Dick

  22. No, MS Access works just fine, only table type you can’t refresh are linked (i.e. same workbook) Excel tables. I’m guessing you’ve modified the code? Send it on to me, with the workbook if possible, and I’ll take a look at it. (send to tom@gobansaor.com

  23. I’m using PP v1 with refresh working in one report, whereas in another I get the runtime error -2147467259 as mentioned by Dick. I have no linked tables, only tables and queries from/against SQL Server.

  24. Hi Jesper,

    Dick’s error was due to bugs introduced when re-writing the code. Which version of my code are you using? Have you modified it?

    Tom

  25. Hi Tom,
    I’m not sure where to look for version – timestamps on ppRefresh.zip content appears to be from 9. sep. 18.13. I haven’t modified the code – just exported the PPRefresh module and imported it in my Excel PP v1 file and hooked it up to a button in the ribbon. There seems to be some interference between reports as the first report which worked just before now fails with an error referencing the file name of the second report. Going into debug, it fails at comm.Execute in doXMLA. Can there be any caching across files/sessions even with only one file open at a time ?

    Will the code handle reports with multiple pp tables in it ?

    • Jesper,

      Forget last reply, you do seem to be using latest version. The major change in that version was to handle the situation when multiple PP cubes where open in the same Excel session. Is that what’s happening to you?

      Tom

  26. Hi Tom,
    sorry for disturbing the force 🙂 Found out that the refresh failed due to timeout. I have the refresh code hooked up to a custom button on in a custom group on the ribbon and got confused by a seemingly application-wide macro reference that continues to launch the last report in which I have hooked this up when I refresh a report in which I have hooked this up earlier on even if I have imported the code in both. After extending the timeout, it works, and so now I’m just looking for a way reference the “Refresh” macro in the local file, in stead of within the file in which the button was hooked up on the ribbon.

  27. No problem Jesper. You could move the Refresh code and button logic to a VBA addin perhaps.

  28. Hello Tom,

    I (like many others) wanted to try your code.The error I am getting is in the xmla execute and it says: . PivotTable1 is a Pivot table i created based on the PowerPivot Data. Any ideas for me lease? Thanks,Nala

  29. Sorry, the error was left out when posting: Run-time error ‘-2147467259 (80004005)’: Either the dimension with the ID of ‘PivotTable1’ does not exist in the database with the ID of ‘xxxx..’, or the user does not have permissions to access the object. Thanks!

    • Nala,

      Looks like you’re passing in the Pivot Table name as the table to refresh. This variable can be set to a source PP model table for those cases where you wish to only refresh a single source table. Normally you leave this blank to refresh every source table within the model.

      Tom

  30. Thank you Tom, but then I will get the run-time error: The operation has been cancelled. I followed the suggestions ahead and I increased the timeout to 5000 for the xmla, still not working…

  31. I see that the cnn is set to: l Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=2;Optimize Response=3;Cell Error Mode=TextValue”. Is this OK?

  32. Thank you Tom, setting the timeout to zero yield the same result: Run-time error ‘-2147467259 (80004005)’: The operation has been cancelled. Any other ideeas for me to try? I really appreciate your quick response 🙂

  33. Not likely to be a problem per se with the refresh code. Most likely something to do with the source datasets. What database(s) are you connecting to.

    Give me a much detail as can, versions of PowerPivot etc, does the refresh work okay manually, have you tried against a very simple refresh scenario?

  34. Thank you Tom. The file I need the refresh to work on has an embeded power pivot. I tried the code on a clean file with a small (100 rows) PP table collected from a SQL server database. It worked. So at least we rulled out the refresh code as a problem 🙂 The embeded datasource is about 100K rows. I have no control about it, any idea of what can be wrong with it? It is built from the same server as the table I tried on. I did not design the embeded data source but I have to work with it… And yes, the refresh works manually on the embeded PP file. I work on a Windows 7 professional machine, Office 2010, VBA 7.0.1625.

  35. How long does the manual refresh take?

  36. It takes 100sec to refresh the power pivot (70K rows) and another 40 sec to Refresh All (under Data tab). Thank you.

  37. It still sounds like a timeout issue; where did you apply the timeout? Should be at line ..

    If doXMLA(cnn, lDatabaseID, lDimensionID, yourTimeOutInSeconds) = “OK”

    Send me your workbook to tom@gobansaor.com

    • Error -2147467259 (80004005)’ is an ADO error which when raised by the Refresh code can be caused by a number of issues.

      Most likely either:

      (a) No PowerPivot backed PivotTable created in Workbook (hence no connection to tap into) – solution 1st create a PivotTable based on the PP model.

      Or (b) Authentication problems with accessing source data – solution make sure you have access and that all credentials are stored correctly within the connection i.e. prompts for username/passwords are not handled.

      Or (c) A timeout – increase timeout lenght or set to 0 (no timeout).

      If the above appears to be not the problem then see this article http://support.microsoft.com/kb/167957 for a guide to getting back more information from ADO about the specific error.

      Tom

  38. got the same error as Nala, incrasing timeout value doeasn’t help :/

    • Dear Thesubneo, or whatever your name is:

      The -2147467259 error is a generic VB runtime error, it can be caused by almost anything. The number in brackets afterwards (and any text) is the important bit. Please provide as much information as possible (version of PP, type of data connection, size of data source, whether it works manually, whether you’ve initially “practised” using the example workbook and it worked in your environment; have you changed the code, if so what etc.).

      Also be ready to send me both your workbook and data source (the original or, if issues of confidentially, a similar dataset that also causes the same problem).

      The code has been downloaded 1000+ times, has been used successfully by at least 50 of those to my knowledge (some people actually email me to say thanks and share their successfully experience – imagine that), so it does work, stick with it, think of it as a learning experience, not a handout.

      And please do not pepper the site with the same comment on multiple blog posts, if you do you’re likely to trigger the auto-spam logic and/or my indifference.

      Oh, it’s also polite to provide a name so we can have a proper conversation.

      Tom

      • Dear Tom,
        Thank you for your answer,
        First of all : sorry for my laconic post .

        I know that your code works – i use it to update another pp table, i just have problems with this one

        Here are details :

        I have excel 2010 32 bit + PowerPivot V2 (polish versions)
        The error is :-2147467259 (80004005)’ , The message could be translated (from polish) as something like that :
        ” The operation has been cancelled beacuse execution of other operation in the transaction has failed” – don’t know the exact english text 😦

        I try to update a single table , beacuse when i update all tables(without specyfing the name of the table in the code) i recive some memory error (message about considering switching my excel to 64bit or adding some RAM)

        As i’ve mentioned above – the code works with other table (same workbook)

        If i try tu refresh manually all tables i also receive memory error, if i refresh single table manually it works.

        Sources for both tables are excel files – the working one is 15MB file, the other is 45MB
        The 45MB file has about 160k rows, but i try to import about 60k rows (by column filters in PP window ->table properties)

        I start to think it can be a memory issue, but it’s strange that it works in manual mode and doesn’t work when using code.

        Thank you,
        Marcin

  39. Marcin,

    It’s a memory issue pure and simple; internally the add-in likely uses a different approach to refreshing and undoubtedly manages memory aggressively. PowerPivot needs all the memory you can throw at it and ideally a 64bit machine.

    Tom

  40. Donald Parish

    Another way to get the -2147467259 error is to forget to change the Data Source for the CSV files to their location on my machine (as per the instructions). This looks great. Should suit my needs for update well.

  41. Pingback: PowerPivot VBA Refresh Code – Bug Fix | Gobán Saor

  42. Thanks for this great code! i did find a small bug. On the line, xmla = Replace(xmla, “<<>>”, databaseID), there is not a “<<>>” referenced in the above XMLA so there is nothing to replace. Same with <<>>

  43. Thanks for posting the code! I seem to be having trouble with the refresh freezing on PowerPivot tables that are sourced from Excel files located on a network share and a SharePoint document library. Is the code designed to handle those types of data sources or will I need to modify it?

  44. so, reading through the code, you open an excel workbook and will that refresh any powerpivot workbok? does the powerpivot workbook need to be opened.
    i am trying to come up with a solution for a powerpivot workbook that is sourced via a sql table. i do not want to open the powerpivot work book at all and handle inside of code. powershell or c#. does this code give the abiltity to do that task and/or help with the code to potentially do so.

    robert

  45. Robert,

    Excel needs to be loaded and the workbook containing the model to be refreshed must also be loaded.

    In this version of PP (but not 2013) would be possible to refresh the model directly in SQL Server, export and then insert via the XML SDK into one or more workbooks without opening the said workbooks in Excel (so could be done on an Excel-free server).

    Tom

  46. thanks,
    so, i could perform my tasks without technically opening excel at all. using the openformat or other techniques to get at the core workbook.

    i guess this is why people put pp up on sharepoint and then put a refresh on sharepoint. then with ssas 2012 and the tabular format this would go away as well and the excel pivot tables i currently have hitting an excel powerpivot workbook will just sit on the ssas 2012 tabular data format and work like any other odbc connection.

    is that accurate?

  47. Yes, you could say, develop he model in Excel, then import the model into SSAS Tabular and then either modify the original workbook to now look at the server(via openformat manipulation) or, as you saidm just create a new set of workbooks using pivots based on SSAS ADO connection. This works better in Excel 2013 than 2010 as the local and server-side pivot control dialog have been unified in Excel 2013.

  48. Pingback: Macro PowerPivot Refresh

  49. Hi,

    Am Using a excel workbook as source to the powerpivot. and am using the code you have done. Its refreshing the powerpivot without any problem.

    But i wanted to restrict the no. of rows to be processed by applying filter on some columns from the source. How can i specify filter while processing the cube, using xmla.

    Please help me to solve this problem.

    Thanks a lot!!!!!!!

  50. Thanks !!!
    Please guide me with some good resource for .NET & AMO so that i can achieve my requirement.