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 🙂

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 38 Comments

  1. 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 🙂

  2. 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?

  3. 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?

  4. 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?

  5. 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.

  6. 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.

  7. 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
    and APPID
    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?

  8. 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.

  9. 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.

  10. 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?

  11. 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!

  12. 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.

  13. 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.

  14. I stumbled upon this article years after the initial publication. However, a really interesting topic and I was eager to test this after reading the article. Got the setup working after some issues (seems it’s not working in same way to change connection string in Excel 2013). Anyhow, core and thin versions now complete but I’m now stuck where this article ends… Keeping the thin books and its pviots/charts refreshed and accurate after core book has completed its refresh.

    So, is there any best practice for this particular moment?
    Would really appreciate help on this one,

    Thanks for a great website and well written articles,


  15. I’m in same position as Otter above, Desktop PowerPivot but no Server side version
    The core workbook, thin clients idea is an approach I’ve been working on. I have a core workbook that pulls together my disparate data sources to produce what I would call report tables in PP
    My work around is to have the core export the contents of its report tables in the form of csv data files to a designated folder where they can be collected by the thin client workbooks
    I’m keen to be the PowerPivot Che Guevera in my current residency but any conversations with IT regarding Power BI have been met with blank faces. Nobody realised PowerBI features were available in their deployment of 2013 until I pointed it out!
    I’m wondering if the workaround I’ve come up with is the best arrangement I can hope for?

  16. Hi, does Thin/Core work at all in Excel 2013?
    I just can’t seem to find a way to re-point the connection string for “ThisWorkbookDataModel” to the core workbook – I have done a bit of research around forums but could not find a way. The field is just not editable.
    When I go to Connection Properties/Definition, the field Connection string is totally blank and greyed out.
    Has anyone found a way in Excel 2013, or has this been totally phased out?
    Pity, as it would be really cool.

Leave a Comment or Question