PowerPivot Scheduled Refresh Pt 3: Thin Workbooks

 
PowerPivot Thin and Core Workbooks

What is a “Thin” Workbook?

At Pivotstream, we came up with the term “thin workbook” to describe an Excel workbook that does not contain a PowerPivot model, but that DOES connect to another workbook that does, which we call a “core” workbook.  In other words, thin workbooks use core workbooks as their data source.  Others have called this “hub and spoke,” and I may even like that phrasing better, but hey, we’ve been using “thin and core” at Pivotstream for nearly a year and it’s hard to change.

So, a thin workbook contains pivots, charts, cube formulas, etc. – it’s a report-only workbook.  And whereas our core workbooks may be 200 MB or more, our thins are often 5 MB or less.

Why create thin workbooks?

Here are a few reasons why you may want to use a thin workbook approach.

1) To avoid duplicating data.  If you want to create multiple workbooks that offer different views of the same data, which we do all the time, you probably don’t want to duplicate the PowerPivot data across all of those workbooks.  That duplication needlessly burns RAM on the server, takes extra time to run scheduled refreshes, and also increases your maintenance work (ex:  if you want to modify a measure, you have to do it multiple places instead of one).  I *highly* recommend you never duplicate PowerPivot models like that, and thin workbooks are a good alternative.

2) To lock down data.  In many ways this is another flavor of #1.  At Pivotstream we often create one workbook that contains a full set of measures, and then another that omits certain measures that are particularly sensitive.  Both can use the same core workbook as a data source, but if you properly lock down your permissions on SharePoint, you can give one set of users a different level of data than another set.

3) To avoid the refresh on open performance problem.  In the last post about refresh, I pointed out that the PowerPivot refresh service on SharePoint kinda “cheats” a little bit – it refreshes the data in the PowerPivot model, but the data in the pivots (the data in the Excel sheets themselves) is untouched.  To work around that, PowerPivot for SharePoint sets the “refresh on open” flag, forcing Excel to refresh all pivots whenever the workbook is opened.

That can be slow.  In many cases in our work at Pivotstream, it is VERY slow.  Since the PowerPivot refresh service does NOT touch the thin workbooks, “refresh on open” never gets set.

Of course, that creates another problem doesn’t it?  The data in the Excel sheets then remains stale, with no automatic way to refresh it.  That’s no good.

But we are busy little beavers at Pivotstream and we don’t give up easy :)  We started working on this problem in March 2010.  More on this later.

How do you create thin workbooks?

The first step is to publish your “Core” workbook to SharePoint.  You CANNOT connect a thin to a core sitting on your desktop – the desktop version of the PowerPivot engine does not support cross-workbook connections like that.

Once you have your Core published, there are two primary ways to create a thin workbook:  Start from Scratch, or Hack like Mad.

Boring:  Start from Scratch

In this method, all you do is go to the PowerPivot Gallery and click the highlighted button:

Creating a Thin PowerPivot Workbook from the Gallery 
…and that gets you started in Excel, in a new workbook, with a blank pivot connected to the Core model.

More Fun:  Hack Like Mad!

For those of you who prefer to color outside the lines, here’s another technique that I often find helpful.  You’ve created a single Core workbook, and it has lots of report sheets in it.  You don’t want to start from scratch and throw all of those report sheets away, right?

So here’s what you do:

  1. Create a copy of the workbook.  Just copy/paste the file in Windows Explorer.  Name one file something like Core.xlsx and the other Thin.xlsx
  2. Delete pivots from the Core workbook.  Two reasons for this.  One, it reduces confusion – when you open the Core, you know it’s the core because you don’t see any report sheets.  But deleting pivots also makes the scheduled refresh process on SharePoint run faster – after PowerPivot refreshes the model on the server, it then needs to refresh the thumbnails in the gallery.  To do that, it asks Excel Services to open the workbook, which triggers “refresh on open.”  Why pay extra time and CPU penalties on reports no one is going to see?
  3. Publish the Core workbook to SharePoint.
  4. Change the connection string in the Thin workbook.  Open it up in Excel, and in Connection Properties change the “$Embedded$” string to be the full URL to the core workbook on SharePoint.  Example:

Changing the Connection String to Point to a Published PowerPivot Workbook

When you click OK, Excel performs a full refresh against the new source.  Be patient.  Once that is done, your thin no longer connects to the PowerPivot data embedded in its own file, it now queries the server copy instead. 

Of course, now you probably want to make the file smaller and get rid of all that unused PowerPivot embedded data.  Remember where that lives?  This post explains in more detail, but here’s the image of the thin workbook renamed from XLSX to ZIP:

Embedded Data - One Reason Why PowerPivot Will Not Work With Excel 2007

Item1.data is the file you want to get rid of.  But if you delete it, you will corrupt the file.  So I just create a 0-byte Item1.data file on my desktop and copy it over the top of the file inside the ZIP.

Rename back to XLSX and you have a true “Thin” workbook with all of the original report sheets intact and functional!

Summary, and a hint at the next post

Thin workbooks are a VERY useful tool in a PowerPivot system.  For Pivotstream, they are absolutely essential.

The only trick, as mentioned above, has been getting those thin workbooks to always be up to date.  I’m happy to say we have that problem solved.

I’m pretty sure others are going to need this sort of thing, too, as well as some other internal tools and components we have built.  If you have interest in beta testing our PowerPivot Accelerators, as we are calling them, watch for the next post :)

34 Responses to PowerPivot Scheduled Refresh Pt 3: Thin Workbooks

  1. […] on powerpivotpro.com Rob has published several articles on handling PowerPivot under SharePoint.  One such article talked about a hub’n'spoke approach to PowerPivot deployment ( or Core and Thin workbooks as his team describes the approach). Lots of […]

    • Erik Olsson Dibbern says:

      Hi,

      When working with core and thin worksbooks on SharePoint, will the Pivot on the thin workbook be “handled” through the PowerPivot window? Or will it be more like working with a traditional data connection?

      Best/Erik

  2. Charles says:

    Refresh on open performance problem is my #1 issue with PowerPivot on Sharepoint right now.

    I have about 40 or 50 workbooks uploaded refreshing daily and the refresh on open is killing the performance (taking upwards of 60seconds to open 1 workbook.)

    It’s honestly better in my environment to disable after-hours data refresh and just refresh all the books manually in Excel, then re-upload them to avoid the hangs on opening the files.

    This thin workbook thing looks cool and all but it seems like a work around and I don’t think having 50 of these is an ideal solution… Please keep me posted the very day you guys figure out the refresh on open problem :)

  3. A.J. says:

    I work in a confined environment where I do not have rights to upload files larger than 5 MB to Sharepoint. Nevertheless, I need the core – thin mechanism to work for files stored on a desktop / server instead of sharepoint.

    I tried exporting the PowerPivot Data from the core workbook in the form of Microsoft Data Connections (.odc) file. I opened a new workbook, & without opening powerpivot, I tried adding this file as a data connection.

    Once done, I opened the PowerPivot window, the data just did not stop refreshing. When I switched to the excel, it showed me error that the power pivot data was corrupted.

    Any workarounds?

  4. OtterMBA says:

    My BI team has yet to deploy PowerPivot for sharepoint. I currently use PowerPivot for Excel CTP3. Everytime I need to use powerpivot for a report I have to re-build the model from scratch. I have tried exporting the powerpivot connection string and then connecting a new workbook to it but it crashes powerpivot. Is there away using the excel version to connect multiple workbooks to an existing powerpivot model yet with out sharepoint?

    • powerpivotpro says:

      No, there is not. And I don’t expect there ever will be. Free addin = no revenue for MS unless you use SharePoint.

  5. Nicky says:

    Hi Rob,

    at the end of your post you are saying you finally got the problem solved of “keeping those thin workbooks to always be up to date”. Any hints on how you got it to work?

  6. Bill says:

    The “core” & “thin” workbooks is a great idea that I couldn’t wait to put into use. Everything worked fine, but the “thin” workbooks need data filtering done. If the “core” holds the bulk of the data, how are you filtering it for many “thin” workbooks that do not need the entire kitchen sink of data?

    Need the filtering because some of the data is for only a few eyes, and not all.

    • powerpivotpro says:

      Yeah we do this all the time at Pivotstream. Two kinds of filtering:

      1) Just remove entire measures from the pivot. Since there is no field list in the browser, they can’t put the measure back.

      2) Put a field (like “Region”) on the Page (Report) filter zone of the pivot. Filter down to the list of regions that the user should see. Then hide the entire row of the worksheet that contains the report filter! :) The web user cannot unhide the row :)

      You can then even put Region on a slicer, and let the user slice by Region, but they will NOT be able to select regions that you filtered out on the page filter. It’s perfect.

  7. I have some problem when I create thin.xlsx. The problem is:
    1. We can’t create new pivot by Power Pivot tools becasue we need create new measurement in power pivot table report.
    2. We can “Pivot Table” tools only but not enough for us because we create relationship on Power Pivot but on “Pivot Table” tools not support

    Now we use Share Point for Power Pivot. The concept Core and Thin it very smart for us to implement Business Analytic.

    Please recommend for item #1 and #2.

  8. Derek says:

    The first approach only allows mt to doanload a ODC conenction file. The 2nd one also doesn’t work and errors show up to say :”URL cannot find” though if i paste my URL into my IE, it promptly pop-up the excel download window.

    Besides, when i check my event view, i can see some error msg there as below,

    The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
    {6DF8CB71-153B-4C66-8FC4-E59301B8011B}
    and APPID
    {961AD749-64E9-4BD5-BCC8-ECE8BA0E241F}
    to the user Domain\srvc_prodbi_sql SID (S-1-5-21-2222296782-158576315-1096482972-51048) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

    Any idea?

  9. Marty says:

    I tried the “hack” approach and got this error in Step 4: “A PivotTable, cube function or slicer using connection “myPPivot” has failed to refresh.”

    I’m not sure if this is security or driver related. I’m deploying on a multi-tier environment, in case it matters.

    Hope someone here can help.

    • powerpivotpro says:

      If you open the PowerPivot window in that workbook, what do you see?

      • Marty says:

        I can see Powerpivot data (embedded) on Thin.

        The Core, uploaded in Sharepoint, refreshes properly. When I downloaded a copy, I can see the correct PowerPivot data.

        Thanks.

        • Marty says:

          Sorry I just checked again. The downloaded Core from Sharepoint does not have any PowerPivot data.

          • Holly says:

            Marty, Did you find that this was a data availablity issue, or was it a security issue? How did you resolve this?
            Thanks.

  10. Kweks says:

    I also can’t seem to get core and thin workbooks to work. Does it work with PowerPivot v2?

  11. Mike Scarborough says:

    Regarding the semantics, how about “thin spokes” and a “core hub?”

  12. John Kelly says:

    Hey Rob – where is the follow up? Very keen to follow this story.

    • powerpivotpro says:

      What followup? Sorry, it’s been forever and don’t recall what was supposed to be next :)

      • John Kelly says:

        Hey Rob, sorry yes just at the bottom of this post there’s a hint at a future post about clever application of core and thin style workbooks. I’m very interested; it looks very clever and we have refresh issues with one particular report that this may resolve

  13. Ron Barrett says:

    Aside from using a core workbook in conjunction with SharePoint, is there a way to copy multiple measures from one PowerPivot workbook to another that has the same data connection? I tried the obvious, copy-paste measures from the PowerPivot window, but that didn’t work.

  14. Jesse Marshall says:

    We are in the same boat as you when you started — 60-70 workbooks that we have on a scheduled refresh to refresh every morning after the DW gets updated. However, the performance isn’t really an issue — each workbook refreshes in about 1-2 mins…but every day, we get at least 5-10 “call to Excel services failed” errors randomly (ie, workbook A will refresh successfully today but report this error tomorrow).
    From what I can tell it is an issue with Excel Services not being able to save the workbook back to the gallery (ie the last step in the process). Has anyone else run into this? MS told me that it was related to user permissions, but I know that’s not the case since it works one day and not the next, and the workbooks that fail are always random.
    Is there a maximum concurrent upload limitation I am running into with SharePoint? Or is there a setting I need to bump up somewhere?

  15. Collin Roloff says:

    Hi Rob, Is there any way to hide the pivot table field list within the SharePoint application? We have given the users Read only access to the file but they can still right click on a pivot table and pull up the list. The problem is that I have used a filter to lock down the data to specific departments, but since they can view the field this they are able to change the filter and modify the tables/charts. Any suggestions? Thanks for your help!

    • powerpivotpro says:

      There is a way to do this, because I begged MS to include some sort of “backdoor” way to suppress said field list in 2013 SharePoint, for precisely the reasons you specify.

      But I forget what the method is, let me ask around a bit.

      • Collin Roloff says:

        It appears there is an easy way to do this with a normal excel web part, but for some reason I can’t find a way to do this with the xlviewer.aspx. Any help or suggestions would be greatly appreciated. Thanks Rob!

  16. Alex says:

    Does this work with any Share Point Licence? Is Power Pivot for Share Point needed?

  17. Do you know if it’s possible to implement something similar in Power BI? I have been struggling with how to implement the concept of a shared data model in Power BI. Supposedly it’s not yet possible. Without this, I’m not sure how Power BI can be considered a viable enterprise solution.

  18. Peter says:

    Rob,
    Great Article.
    I am currently trying to implement this method.
    Having issues when trying to refresh the thin workbook in excel on my desktop.
    It seems to be an issue when the connection takes a longer time to complete.
    Eg: with a smaller file with no pivots and only slicers the file will update.
    But when multiple pivots and cross filtering slicers are added I am seeing an internal error, the multidimensional connection then pops up but pressing next here and it says request can no be redirected to sharepoint.
    Any suggestions will be of great help this issues has been bugging me for a while.
    Cheers,
    Pete

Leave a Comment or Question