Post by Rob Collie

***Update:  check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2:  a Free Version of Power Update is now available.  More info here.

***Update #3:  There is now a forum for Power Update questions, located here.

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint or Otherwise)

A brand-new software utility designed from the ground up as
a “Companion” to  Power Pivot, Power Query, and the entire Power BI stack.

Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Common Problems with Power Pivot and Power BI Scheduled Refresh

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

OK, a few things:

 

  1. The culmination of a multi-year quest for me.  Nearly three years ago, I realized that a desktop-driven, ultra-simple and ultra-flexible refresh agent would be a massive game changer.  My experience running the world’s first cloud Power Pivot service (at Pivotstr3am) had given me quite the unexpected education.  One day it struck me that something like this would solve basically…  everything.  My colleagues had other missions to pursue, though, and we never funded the development of it.  Eventually I left – to build a new hosting platform, equipped with just such a desktop agent.But MS soon shifted to their “cloud first” strategy that withheld important new features from the “boxed software” I needed, a move which put a strong “chill” on my desire to take funding for a new hosting service – and the agent itself was a bit too “small” for investor pitches.  By then, our training and consulting biz was doing quite well, and was a lot of fun, so we went that direction.That said, I still wanted to build the refresh agent.  It was just a matter of finding the right partner – a firm with a strong software development staff already in place.  That search took a long time, and I had basically given up…  but then THEY found ME.
  2. A collaboration between Power Planner and PowerPivotPro.  I get a lot of email.  Right now, there are at least 30 CRITICAL things – big opportunities – sitting in my inbox awaiting a reply.  Please don’t take it personally if you are the owner of one of those.But these Power Planner folks were persistent.  “Hey Rob we think there are some cool things we could do together.”  <mail got buried>  “Have you seen our latest planning software?” <mail got buried>  “Rob would you be interested in our prototype refresh agent?”  <fell out of my chair>.They are a FANTASTIC team, and I am very glad they persisted.  For the past few months we’ve been collaborating – refining and improving the agent to fit my experiences of what the Power Pivot / Power BI world needs.  And the Power Planner team addressed every single suggestion, request, and blue-sky wish I threw at them.  I’m pretty demanding – they deserve kudos for withstanding all of that with a smile.  The results are amazing.
  3. An application that you install on your desktop or other PC.  You then “tell” the application which workbook(s) you want to refresh, how frequently, and where you want them to be published when they are done.  It takes care of it from there – even when you are not sitting at the computer.  No more manual “refresh and publish.”

“Is there a free trial?”

Why yes, you simply ask the BEST questions.

Click for Power Update Trial Download

Click Image to Visit the Power Update Page and Get the Free Trial

Benefits of Power Update

The exact benefits depend on where you are with respect to having or not having a server.  Here are the six most common “server scenarios” today, and how Power Update benefits you in each:

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint on Premises)

Benefits of Power Update When Used with “On Premises” SharePoint

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

Benefits of Power Update When Used with Power BI v1

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v2 - TBD)

Benefits of Power Update When Used with Power BI v2 – Stay Tuned

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location ("Vanilla" SharePoint Online)

Benefits of Power Update When Used with “Vanilla” SharePoint Online (or OneDrive)

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Network File Share)

Benefits of Power Update When You Don’t Even Have a Server

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SSAS Tabular)

Benefits of Power Update When Used with SSAS Tabular

“Is there a demo?”

I also recorded a comprehensive demo video, which is why I did not go to bed Monday night.

For those of you who are video-inclined, please enjoy…

Time Lapse Update-Ography (from the demo above)

Here’s a sequence of screenshots taken from my Power BI Online (v1) site last night, at 8 PM (before I started running Power Update), then at 9:22 (first autorefresh), 9:52 (second), 10:24 (third), and then, MUCH later (at 3:40 am).

I did not manually trigger ANY of these.  They all ran on their own.

Furthermore:

  1. I have NEVER configured the DMG, which until now, was 100% necessary for Power BI v1 refresh.
  2. This workbook uses a CSV data source living on a web server that requires authentication.  CSV is not currently supported by the DMG, and definitely not for download from “foreign” servers like this.
  3. It uses Power Query AND Power View.  You know, so I can scream “Power BI Bingo!” at the top of my lungs 🙂

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (Power BI v1))

“Is there a free trial?”

Didn’t you ask this before?  Well OK, this post HAS gone kinda long, so let’s save you the scroll:

Click for Power Update Trial Download

Click Image to Visit the Power Update Page and Get the Free Trial

“Has Anyone Else Been Using It?”

Yes – we’ve had more than a dozen “beta” customers kicking the tires for a month or so.  So while we won’t guarantee that we’ve found every little corner case lurking out there, you can feel pretty good that it’s working for a healthy population already.

“Anything I should be aware of?”

Two things in particular:

  1. It requires admin rights on your computer.  Not just to install, but to run as well.  If that’s not gonna fly on your desktop, you will need to find another computer, and/or beg IT to let you install this baby.
  2. It closes Excel when a scheduled refresh runs.  This takes some getting used to.  I’m still not 100% there yet.  So if you’re going to be refreshing every hour, you’re going to need to dedicate a second computer to Power Update – you won’t want it interfering with your regular work.

“Does it work with Power Pivot for Excel 2010?”

In any normal universe, the answer would be no.

But in our universe, the answer is YES.  Bizarrely so, but true.

“Can we provide feedback?”

Yes, absolutely.  And please do, we (myself included) will eagerly devour it.

[email protected]

Have fun!

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 68 Comments

    1. I have personally never used the sql agents for Power Pivot refresh, but I know people who have. And yes, I agree that Power Update is a promising alternative to that approach.

      I will wait for others to stroll by, like Scott 🙂

      1. Yes. On SP2010, you could force a refresh by writing to a table in the power pivot db. That solved a few problematic refresh cases. Power Update solves… every problematic refresh case. Seriously.

  1. Dear Mr Collie, amazing development. Do you support excel 2010 powerpivot model v2 + sql server as data source? Why? It seems that in v2 there is no simple way of making sql server password persistent. Does your tool work its way around that? Hope so!

      1. To work around the “Excel won’t store my sql creds” issue, you could use Power Query. Not something you could have done before if you are doing On Prem sharepoint, but… with Power Update, you can certainly consider that.

  2. Wow. I’ve done a lot with updating a master workbook, then copying the Data Model to other workbooks. But being able to take load off the SSAS Tabular is pretty amazing. And being able to use Power Query as a source will be VERY useful.

    1. So you’ve experienced the same thing I have: the machine that is servicing interactive users is basically the LAST place you want to run the data model refreshes. Ironic, and the world has worked that way until now for reasons that are almost 100% historical rather than logical. I like logic better. But it took 2+ years operating a shared cloud SSAS / Power Pivot server for the realization to dawn on me. Historic inertia is strong.

      And yeah, the fact that I can now recommend Power Query WITHOUT RESERVATION? Holy cow, that in itself gives me shivers.

  3. This has to be one of the best product announcements I have ever read! It has the vibe of a fun user guide and marketing materials, all in one.

    Rob, we love Power Update and have found it to be a critical part of our projects. Good work getting the team at Power Planner to refine this product!

    1. Hi Ken! I’m not even sure I knew you guys were on the preview, I was about to email you about it yesterday when I saw your post 🙂

      Thanks for the kind words on the announcement’s style. That said, one of these days I need to master the art of brevity.

  4. I am going to update the sh…t out of my team’s workbooks. It is a shame we(20k employee corporation) have 32bit excel which i think will make PowerUpdate less usefull for larger models, right?

    1. Hi Witek,

      My initial thought is to the contrary. I believe you could have one computer running Power Update, maybe on a 64 bit Excel for speed and size that is running the updates overnight. The users could then open the model(s) in the morning when they get in and not having to reprocess the entire model. It has been updated.

      Happy Power Pivot,

      Chris.

  5. Hi, Mike Dietterick sent me this nice email, and a couple of important questions that I am sure the community can chime in on:
    ————————————————————————————————————————–
    Great job with this and sorely needed to make Power Query useable in many applications!

    We have been having some challenges getting a combination of MySQL data from Rackspace servers and local file share data refreshed on our SharePoint online sites.

    I was able to get the first 2 files I tried refreshed without issue. One was stored locally and uploaded to a document library and the other was refreshed directly from the doc library where it was saved.

    One question…I noticed you specifically mention the ability to upload a workbook into on premises SSAS Tabular. Would it work for an SSAS instance hosted on an Azure VM?

    I was also curious to know if you guys had any additional details on Power BI v2. Given that v1 was directly integrated into SharePoint online/Office 365, it seems from what I have seen and read that v2 will be a standalone product. Does that mean there won’t be the option for the tight integration with 365. We have several solutions in place using a combination of workflows and web parts to display info from Power BI workbooks on various sites. I’m hoping the separation of Power BI into a standalone product does not mean we have to go back to the drawing board with all of these.

    Thanks,

    Mike Dietterick
    Director of Product Development

  6. You can do a scheduled refresh of a Excel File containing a PowerQuery Table
    1. Create a Schedule Task using Windows Taks Scheduler – Call the Task say “PQRefresh”
    2. Set the Frequency to 1 Hour or to whatever you want – once a week, once a month etc
    3. Ask the Task to Open a VBS File (TXT file with extension changed to VBS) Called PQRefresh

    The VBS file contains the following lines of code

    Option Explicit

    Dim xlApp, xlBook

    Set xlApp = CreateObject(“Excel.Application”)
    xlApp.Visible = TRUE
    Set xlBook = xlApp.Workbooks.Open(“C:\Data\PQ.xlsm”)
    xlApp.Run “RefreshQuery”
    xlBook.Close True
    xlApp.Quit

    Set xlBook = Nothing
    Set xlApp = Nothing

    WScript.Echo “Finished.”
    WScript.Quit

    4. The File PQ.xlsm contains the PowerQuery that you want to refresh and has a Sub
    Called

    Sub RefreshQuery()

    Sheet1.ListObjects(1).QueryTable.Refresh False
    Sheet1.Range(“E1”).Value2 = “Last Refreshed on ” & Now

    End Sub

    If you want to keep the PQ file macro free these lines of code can also be transferred into the the VBS file with minor changes

    1. Yep. And then you start adding script that handles the publish step, saving the file to a network share or sharepoint or restoring to SSAS. And code which handles the auth for O365 or power bi which likely doesn’t use your windows creds.

      All doable of course. Then you start adding error handling and maybe a ux to make this a bit friendlier for other people to use.

      This is one of the unexpected dirty little secrets of software. The core of the “actual work” that it does for you is usually simple. It’s all the convenience and polish work, and the testing and support, that consumes > 99% of development cost.

      I wouldn’t have sunk so much of my time into Power Update if I thought scripts were a sufficient solution for the general population. Heck, scripts aren’t even sufficient for ME. Much respect for you Sam, you definitely are one capable dude. There are a lot of people in the world UNlike you however – even if they rock at DAX, they aren’t cool with writing and maintaining their own suite of scripts.

      And that’s why I’ve long wanted a piece of polished software, despite the existence of scripts like these throughout that entire timeframe.

  7. Just to be sure I’m understanding well…this tool will allow to refresh Ecel workbooks that contain regular Pivot tables (not Powerpivot) based on SSAS cubes ? and will also give the option to automatically put a copy of this refreshed workbook on a specified network folder (with an indication of the datetime in the the name of the saved excel file) ? Tx for confirming or correcting !

    1. Hi Kurt! Yes, in addition to being able to refresh Power Pivot workbooks, it will ALSO refresh what I refer to as “thin” workbooks – workbooks that contain only reports/dashboards (typically pivots, but also constructed of cube formulas and maybe even DAX query tables) that are connected to SSAS servers – whether those servers are “old school multidimensional” or Tabular.

      In fact, this “thin” scenario is one that Scott (see above in the comments, or a link to his review at the very top of this post) has been running in great depth for the past month. (In his case, the SSAS server is actually a Power Pivot for SharePoint server, but that’s a detail – it’s still SSAS.)

      At the moment, we don’t have a “date/time stamp in the file name” feature, but I will add that to the list of future improvements.

  8. If using SharePoint online with the option “required check-out/check-in for documents”, would the update/refresh still function?

  9. is able to uddate the core powerpivot data. to avoid sharepoint use memory to handle the update ?. I only can refresh the the data in excel (pivots) not the source.

    1. We’re going to be talking about that functionality in a future post. For now, it’s enough that PU refreshes everything with no hassle or restrictions. A lot to digest before we reveal the next big win 🙂

  10. Works great!! Just tried to update PowerPivot model from a Power Query which is taking Oracle as source….. It works!! I’m ready to pay !!!

    1. Awesome Ruben. Let us know at some point if you’d like to write up your experiences with it. We’d love to post them, obviously 🙂

      1. Sure, would love to contribute…. Saludos desde Mexico!
        BTW,.. I hope you have in your plans California for some training, we are close to the border with San Diego, I’m sure you have lot of fans there.

        Regarding PowerBI I was able to update following your video for version1:
        Oracle data to Power Query and upload to sharepoint… all automatically with Power Update.

        I was also able to update Power BI for Version2 as follow:
        Oracle data to Power Query, save on One Drive sync folder and selected the “Folder or network” option in Power Update…. It works!!
        User just goes to Power BI V2, select refresh and reports are updated.

  11. Hi,

    This looks interesting!

    My scenario is automatically refreshing a workbook stored in a Power BI enabled Office 365 team site.

    Questions

    1) Does the refresh run from the PC where the task is created? Ie the machine where the tasks is created?
    2) When adding a task for an Office 365/SharePoint, where are the credentials I enter stored?
    3) Seems the source should be a local file. Should I sync down the source file locally eg using ODfB (One Drive for Business)?
    4) Is there any advantage to using SharePoint as target as opposed to local file if the destination folder is synced with ODfB.

    Thanks,
    Andreas

  12. I am attempting try out the trial of Power Update. Does the utility have the feature as within the SharePoint PowerPivot refresh setup where one can change the “Credentials” so as to run the refresh as a another account –> “Provide the credentials that will be used to refresh data on your behalf”

    When the PowerPivot data source is an SSAS Tabular model with a defined DAX filter within a defined Role, this “Row Level Security” produces results based on the specific user.

    With Power Update, the credentials option only applies to the upload of the updated workbook.

    Does Power Update provide this functionality?

    ~Ron

  13. Hi,
    I have a Power Pivot workbook that pulls from an Access database that pulls from the enterprises data mart (via a series of queries triggered by a macro). I would like to use Power Update so that all users of my Power Pivot workbook have the most up to date version every morning.
    To do this, I would need to first grab some VBA from the Internet to run the Access macro at, for example, 4AM. Then, use Power Update to refresh the Power Pivot workbook at, for example, 5AM.
    Is my understanding correct?
    This will be great, thank you!
    -Sean

  14. This is awesome. No more ‘refresh when opening’ in thin workbooks using PPIV or SSAS cube/tabular. YAY!! We definitely needed that.
    I have a question – if we have a job that processes tabular model/cube and if we want the data to be refreshed in a “thin” workbook, is there a way to add that dependency?

  15. I’m wading through the corporate forms to get them pushed into licensing this for multiple users. Is there an email contact I can use for more details on that? I’m at a 50k employee firm, and anticipate about 50 users across the company will want this within the next year or so. I can get my management to buy me a single person license, but really I already know of a dozen people in the company that would be happy to use this and my contacts aren’t that extensive in the BI arena.

  16. I just downloaded the application and set up my first task. My client has a workbook stored on an Office 365 SharePoint site shared folder that has been enabled for PowerBI. When I run the task it appears to download the file and run the refreshes. However, it does not appear to refresh the model data from the on premise SQL database. It updates the linked tables and the model to the reports but but not the source data. What am I missing?

  17. Hello,
    maybe I missed it…but do not find anything back on ‘locks’ on files …how does Power update handle this…it seems that when you try to update a file which is opened by a user that the update fails; also when the file to which you want to copy the updates xls to is opened that it fails .. how can we deal with that ?
    So I want to refresh an excel regularly during business day, but this file can be open by a user at certain moments.

    1. no feedback so far from your side ? We just did some tests ourself and a workaround seems to be to update it in a separate folder and to add an additional step each time that just copies the xls (so disable the datasource refresh : setting in power update task definition) to the location where the user acces the file…in this way you limit the time the xls will be locked and you can retry this copy step very often at failure (until lock is gone)

      1. Hi Kurt. The dev team monitors the forum but doesn’t always see comments on blog posts. I have sent them a note alerting them to your question/issue here.

      2. The workaround you proposed can be a way to overcome the issue of the target file being locked. We’ve also introduced the CopyRetryCount and CopyRetryWait (ms) settings that you can change in C:\Program Files\PowerPlanner\Power Update\PQRefresh.exe.config. This provides a built-in retry mechanism in case the source or target files are locked. You need to update to the latest version of Power Update by re-running pusetup.exe for these parameters to appear.

  18. Hi,
    Is there a way to export the configured jobs in PowerUpdate to another user profile ?
    We’re running this under one domain admin account but we’d like to move it to another new domain admin account.
    When we log in as the new account and open PowerUpdate all the jobs are gone. Jobs seem to be saved under the specific’ users’ profile.
    Thanks in advance. Vincent

  19. Hi guys,

    Thanks for putting up this great forum. I have a business case for which I have been use Power Update in conjunction with Power Query, Pivot & Excel for transaction reporting. Here’s the scenario;

    I have an Excel workbook, Summary.xlsx, that loads data from two other Excel workbooks, A & B through Power Query. Once the data is loaded into Summary.xlsx into sheets 1 & 2, these sheets are then sourced to Power Pivot model as a Linked Excel Table. The Summary workbook is then shared with end users using the centralised SharePoint portal.

    I have configured Power Update, and everything works except for the Power Pivot model, which is not updated as part of the task created in Power Update. End users have to manually open Power Pivot window, that is set to auto-update from linked tables, after which the final pivots are updated.

    I was expecting Power Pivot to get updated automatically, as part of the Power Update scheduled job/task. Have I misinterpreted the features?

    P.S: Thanks for putting this great platform together and keeping it alive, it’s certainly very, very useful.

    Thanks

    Kaz

    1. Hi,

      The issue can be caused by the following reasons:
      – The source workbook is not refreshed: Power Update doesn’t update the workbook used as the datasource only the workbook referenced in the Update Task. Solution: You have to update the source workbook externally, or using another Power Update task.
      – You are using Excel 2010: Refreshing linked tables in Excel 2010 is not yet implemented
      – In Excel 2013 the Linked tables connection is listed earlier in the list of connections than the PowerPivot model connection: Power Update refreshes connections in the same order as they appear in the Connections dialog in Excel (alphabetically). Solution: Rename the linked table connection so it appears last alphabetically (e.g. put x_ before it’s name or number connections).

      Charles

      1. interesting….I want to go a little bit deeper into the statement “Power Update refreshes connections in the same order as they appear in the Connections dialog in Excel (alphabetically). ” ….is this a general rule ? I Always understood (and confirmed by Chris Webb) that the standard behaviour of Powerquery is that it looks at dependencies…so when a powerquery 1 uses another powerquery 2, then it will execute first the powerquery 2 and then powerquery 1 … So if I understand it well, powerupdate would behave in a different way ??? Tx for giving feedback on this !

    2. In case of using a Power Query that’s dependent upon other Power Queries as a datasource in PowerPivot when Power Update refreshes the final query, Power Query will off course internally refresh the base Power Queries, but Power Update is not aware of the relationship between the various data connections / tables in the workbook, you need to specify the order to refresh connections (e.g. put a prefix number in front of the connection name in the Connections dialog in Excel).

  20. Hi Charles,

    Thanks for response. I am using Excel 2010 and my issue is relating to Power Pivot model not updating from the source Linked Excel tables, which are updating fine through Power Query. Here’s the overall data flow ;

    1 – Power Query loads data from source Excel files, cleans and transforms the data
    2 – Data is loaded into Excel tables from Power Query created in step 1.
    3 – Excel tables prepared in step 2 are used as Excel Linked Tables sourcing data to Power Pivot model
    4 – Power Pivot model has some measures/DAX.

    Using Power Query, step 1 & 2 work as expected. My issue lies in step 3, where numbers are not updated in Power Pivot model. I have to manually open the Power Pivot window, hat is set to auto-update from linked tables, after which the final pivots are updated.

    Thanks

    Kaz

  21. Can the file outputs automatically generated by Power Update and saved to a folder be exported and saved as csv files instead of xls files?

  22. Hello, it seems when the license expired and afterards you reactivate the license that a lot of tasks seem to be ‘incomplete’ … it seems that the reference to the files to update are removed; and you need to reselect them…in an environment with 50 or more tasks this is time consuming. Is this a normal behaviour or is there some way to recover the correct tasks definitions ?

  23. Hi Does any one have a solution for updating the Pivot tables and excel formulas in Excel 2010 once Power Update has finished running. I have tried various VBA code but none seems to really refresh the pivots. I understand this is available in 2013.

  24. Opening, refreshing, refreshing, and saving makes me grouchy and awkward to be in the office with. Thank you so much.

  25. Hello Rob

    Is the Power Update now support the auto schedule refresh the Power BI Desktop (.pbix) files? If so, can you share any documentation on this?

    Thanks
    Kishore

  26. Excellent post . I am thankful for the details , Does someone know where my business could possibly get access to a blank Req Tenant to Leave Premises example to use ?

  27. Hi … I am currently working on Power BI and there is a major requirement. We need a way where in whenever my refresh happens, the report gets converted into a PDF and gets emailed as an attachment to the necessary stakeholders . Is it possible with Power Update ?

      1. Thank you so much for that, but is there any tutorial I could refer to which can guide me on configuration of power update for emailing pdf after refresh from power bi ?

Leave a Comment or Question