What is the workbook doing when it says "Reading Data"?

[masplin]

I've got a workbook containing several million lines of data extracted by SQL query. I've written some measures that clearly require a lot of calculation. What I am struggling to understand is if I have some pivot tables with some of these big measures already existing and I create a new pivot table, with some very simple measure (like sum of a field,) it takes hours to populate this new pivot table with "Reading data" showing in the bottom corner. The implication is that it is recalculating all the hard measures even though they already exist. Even if I use slicers to pre select a small sub-set for the new simple pivot table it takes just as long.

Is this the behavior I should expect where the time to do any calculation is dependent on the slowest calculation in the workbook. If so seems a nuts implementation. I know 2013 is rubbish because it recalculates everything whenever you change data in the powerpivot window unless you use the OLAP extension to disable the auto refresh (who designed that!!!) . So is this what is happening and it is refreshing everything any time you make the slightest change to any pivottable? If so it is basically unusable.

Appreciate any clarity on how the calculations are performed in case i'm being stupid.

Mike

 

[Matt Allington]

Yes, it's not very good/fast when changing the model. But it is the pivot tables and slicers that take the time. To test this out, take a full copy of the workbook and save it with a new name. delete every sheet and add a single pivot. See op how this varies to the original.

How many calc columns do you have? These can take up valuable memory.
Are you using a star schema design? This is normally optimal
have you absolutely minimised the columns in your data table? This is best practice
turn off visualisation settings in slicers - this saves refresh time
afe you using 64bit? That will help a lot if you are not, or try the memory patch

 

[masplin]

Hi Matt

This is is a pretty new model and hopefully I have learnt a lot of lesson from previous models. My question is not actually the processing speed as i expect it to be slow when I add a really complex measure. My question is:

1. create a pivot with complex measure takes hours to complete
2. add a new pivot with a very simple measure. also takes hours to complete
3. Delete the complex pivot so only have the simple pivot and calculates very quickly

The implication is that it is recalculating EVERYTHING in the workbook even though it doesn't need to to a pivot 1 hasn't changed. This wouldn't surprise me as like many have had terrible issues with changing say the date format in the powerpivot window and found it refreshing the whole workbook unnecessarily. The cure for that is the OLAPExtensions add-in so you can disable the pivot table refresh while you build your model. So is this the same issue i'm experiencing as clearly makes it impossible to build a workbook if it always refreshes everything so speed is determined by the slowest part of any pivot. Equally it is illogical to have to delete all the slow pivots every time you wan tot make a new pivot.

I'd just like to know that this is expected behavior because of the idiotic way 2013 has been built or it is not expected so it is something I have done?

Mike