Friday, February 10, 2012

Hub'n'Spoke in PowerPivot - The Core and Thin Workbooks

I'm still working on the Management Dashboard I blogged earlier on when I was Implementing Parent / Child Hierarchy and dealing with the issue with AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE).

I am now dealing with some performance issues, specially when opening and refreshing the Excel file. Another point occured when setting the auto refresh option in SharePoint. The refreshing itself is pretty fast, but when you open the file afterwards, it takes ages to load.. This has to do with the first (PP) and second (Excel) refresh of the file as Rob (PowerPivotPro) explains in his post.
When searching for solutions I came across some posts of Rob, the Core and Thin workbooks posts (1 and 2). The implementation of this hub'n'spoke model for PowerPivot is quite straight-forward with the help of Rob's posts.

In short, you have to get a copy of your workbook, change the connection from embedded to the location of the core workbook on Sharepoint. Then you can rename your thin workbook to .zip and copy an empty item1.data in xl\customData. Rename back to xlsx and your thin workbook is created.



Another performance issue I have is when opening an auto-refresh file in Sharepoint, it takes ages for the file to load, due to the second refresh where Excel has to refresh all the formulas and pivot tables. Everytime the file is opened this refresh takes place. With the Core-Thin concept this is not totally solved, because the thin workbook still has to be refreshed..
Still, I can now auto-refresh the core workbook in Sharepoint (this sets the autorefresh on open mark in the file). But the core never gets opened by the user, the thin only consults the data inside the core, so that's not a problem.
The only issue I now have is keeping the thin workbook up to date. Rob (or PivotStream) seems to have a solution for this problem, so I think patiently waiting is the only option.. :)