skip to Main Content

 
When You Are With the Right Organization You Don't Need to Wait on Fresh Pivots

“People looked at me differently.  They knew I was with somebody. 

I didn’t have to wait in line at the bakery on Sunday morning for fresh pivots.”

-Henry “DAX” Hill

 

 
That Second Refresh Can Really Bite You

Back before the great distraction known as Donald Farmer hijacked this blog for a few days, I was talking about Scheduled Data Refresh.  Specifically, I left off talking about how refresh is really TWO refreshes – PowerPivot and Excel.  Here’s the illustration again:

powerpivotrefreshvsexcelrefresh

OK, so the PowerPivot refresh service has one primary mission in life, and that is to refresh the PowerPivot model.  Which brings us to…

Surprise #1: PowerPivot refresh service does NOT refresh the Pivots!

That’s right, it leaves the pivots in the workbook alone.  So by default, those pivots (and/or cube formulas) still contain stale data!  I’m 100% serious.

“But wait, Rob, you’re wrong!” you say.  “I’ve tested this feature out, and I have NEVER seen stale data in my pivots when I view them in Excel Services!”

That’s right, you DON’T see stale data do you?  That PowerPivot refresh service is one resourceful beast, and it’s playing a clever little trick…

Surprise #2:  PowerPivot refresh service sets “Refresh on Open”

Have you ever seen this feature in Excel?  It’s buried pretty deep:

Refresh On Open Setting in Excel is Used By PowerPivot Scheduled Refresh 
Refresh on Open Setting in Excel 

Yep, click Connections on the ribbon, select a connection (typically named “PowerPivot Data” in our case), then click Properties.  On the resulting dialog you will see the checkbox “Refresh data when opening the file.”

By default that checkbox is NOT set.  You can try this out to see what I’m talking about:  take a PowerPivot workbook, open in Excel client and verify the checkbox is not set.  Then upload to SharePoint, schedule a refresh.  When complete, download the workbook and look at this setting again.  It will be checked now.

Side Topic:  How Does PowerPivot Refresh DO THAT???

I’m 99% certain that there is no API on the server for doing this.  I don’t think Excel Services “helps” PowerPivot at all.  I’m pretty sure PowerPivot modifies the workbook directly, via the file format.  The Open XML File Format, to be precise…  which happens to be what Office uses – XLSX, DOCX, PPTX – these are all Open XML files.

You can do pretty astounding things with that format, so if you are a developer type, I suggest playing around with it.  I can tell you that OUR developers at Pivotstream had a heart attack when they saw that SDK 🙂

How This Can Bite You:  Refresh Sometimes Takes Awhile

HOURGLASS So far so good.  PowerPivot refresh does NOT refresh the pivots, but it DOES set the refresh on open flag.  So when you open it in Excel Services, the first thing Excel Services does, before it shows you any workbook content, is refresh the pivots.  So you never see stale data.  Ever.

In many cases, that’s the end of the story, everything is happy.  But in other cases, it can be painful.

Remember the post I did awhile back on the differences between Update and Refresh?  The overall theme of that blog post was basically:  Refresh Can Be Very Slow.

Why is that?  Short version:  Refresh refreshes EVERY pivot in the workbook.  AND refresh does more work than a normal pivot query.

So…  if you’ve got a lot of pivots in your workbook, or a large data model, or complex measures, or perhaps a combination of those, refresh can take awhile.

Other Considerations

The worst part, of course, is that when a user opens a report in the browser, they have to wait awhile before they see any data.  Sometimes that’s just an extra second or so, which is why you may not have noticed.

In other cases though, it can be as much as 1-2 minutes!

That happens for every user.  In fact, if you open a browser and hit a report, wait through the refresh, and then close the browser, even if you immediately return to the report in a new browser window…  you have to wait out the refresh again.

Imagine what that can do to the CPU(s) on the PowerPivot server(s).  Of course, if you planned your hardware around data refresh as the peak load scenario, then you likely have enough CPU, but still…  a lot of users doing this at once will only magnify their wait time.

“Give me the good news!”

First of all, I brought this to the attention of my former colleagues at Microsoft last time I was in Seattle.  They’re working on solutions that don’t involve “Refresh on Open” but it’s not yet known when or how those solutions will be made available.  Kudos to them for their responsiveness on the matter – I will keep you posted.

In the meantime, there are obvious things you can experiment with.  Keeping the number of pivots smaller is one.  Cutting back on cross-filtering in slicers is another.  But fundamentally, if you have a reporting/modeling/analysis scenario that involves heavy lifting, you can’t exactly just remove those needs from your workbook.

At Pivotstream, we found ourselves in precisely that situation.  And I am happy to say that we have beaten the problem, but the answer is complex – deserving of its own series of posts in fact.  Plus, there are some things here that I am not yet ready to talk about publicly, muhaha…  so let’s put that on the back burner for now.

Next up in this series:  “thin” and “core” workbooks, aka, “hub and spoke.”

Click Here for Part Three >>

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 17 Comments
  1. As a Finance Analyst with no formal IT training administering a SharePoint server(old laptop) to auto-refresh my daily PowerPivot operating models (~20) any hint as to where to begin automating the excel refresh would drastically improve my day to day work life. Learning OpenXML development probably isn’t in the cards for me, but at least it’s somewhere to start.

    I know I have my fingers crossed every time I read an update on the many PowerPivot blogs that the key to the automatic-refresh has been found(and shared Rob). Having relied on Task Scheduler/Windows Batch Files/VBA to automate my reporting, it is the only think preventing me from embracing DAX, Cube formulas, and PowerPivot for every aspect of my financial modeling.

    1. Hi Derek. Just to clarify, you are looking for a client-side method for automatic refresh correct? That is NOT what we have done at Pivotstream, so I’m not witholding secrets on that front 🙂

      To your point, someone else has been pestering me to look at one of their projects and I have sadly been ignoring them until now. I’m gonna take a look today if possible and if there is news to share on that front, you will hear about it ASAP on the blog I promise 🙂

  2. Hi I spent 2 days looking for a solution online and trying this out but I can’t seem to find a solution. Is there a way to loop through the items on the pagefield on a PowerPivot?

  3. Left this out, I was able to use a for each loop referencing the items located on another worksheet but was wondering if there was a way to do so similiar to a regular pivot for each pi in pf.pivotitems then currentpage=pi.name ie….

    1. Yes and no. Yes, you can upload an XLSM and it will render in Excel Services just fine. But no, the macros won’t run on SharePoint.

  4. Hey All…………

    I have a requirement….

    User will select the producttype and click the apply button… the the data gets changes in the excel sheet… with two columns names,price. and the corresponding pivot table should be created with a pivot chart with a slicer(date).

    Upto this it wnt fine but if the user selects another product type and clicks apply he has to mannually goto power pivot window and update the data and come back to excel window again has to refresh the data sheet so that the changes will gets affected in the chart as well s data sheet.

    Iss there any way to make the process automated with out making user to performing updating every time.

  5. I don’t want the ‘refresh data on opening file’ checkbox to be checked after opening the document downloaded from sharepoint. Any way to achieve this?

  6. I am not using SharePoint. I have a 2010 Excel file with a PivotTable built on PowerPivot tables. I want to deploy copies of the file to the desktops of co-workers. Their expertise in Excel is limited to opening a file. How can refreshing the PowerPivot tables and PivotTable be automated to perform on open or give the user a single icon to click?

  7. why do i keep getting this message when i try to alter the sql within Table Properties?

    OLE DB or ODBC error: [OLE DB Provider for Teradata] Not enough information to log on; 28000.
    A connection could not be made to the data source with the DataSourceID of ‘aac0de51-27ea-4aa7-936b-9a22b5918ce9’, Name of ‘Teradata TDPROD 16’.
    An error occurred while processing table ‘Earned7’.
    The current operation was cancelled because another operation in the transaction failed.

  8. The concept that you covered is very useful, but I’m having an issue that it unfortunately doesn’t seem to fix. When I refresh my data I can see that the tables in the Data Model are updated (new dates added) and I can even see the new dates in my pivot table when I use the Column Label pulldown, but the new data is not shown in the pivot table itself until I check and uncheck a date in the pulldown. Another way I can get it updated is if I toggle the checkbox for autofit columns. It’s as if the pivot table has a 3rd level of refresh that’s only activated when I make changes to the pivot table. Have you ever come across this?

      1. I don’t know whether this is a universal fix but it seems to be working for me. I go to Data Connections and select ThisWorkBookDataModel and hit Refresh All. When it’s done I hit Refresh and it updates the pivot tables

        1. Thanks so much for getting back to me. For me, I have my worksheets protected that the pivot tables are on. To resolve this I had to write a macro that unprotects the worksheet, refreshes the data then protects the worksheet again.

  9. I checked the refresh data when opening file, but it does not work. I have a connection and can see the new information in the data source of my Excel file – yet I need to manually refresh all to update the pivot tables. How do I resolve?

Leave a Comment or Question