Efficiency Tidy up question

[masplin]

I started learning PP with a massive project that continues to grow. Despite a 64bit OS and Office and tons of RAM I often find it just crashes or takes forever to update the PP tables. I suspect this is partially caused by the simplictic way I have built up my many PP columns and measures so planning an overhaul. Before I do so I want some advice on whether it is like to be really effective. I have about 100,000 items in one table and 30,000 in another and bunch of smaller ones.

1. in order to follow the calculations as I built them up and spot errors I have say 3 columns in my PP table
Column1 = A-B
Column2 = C/D
Column3= Column1*Column2

I could replace this with Column3=(A-B)*(C/D)

Some of these will be using long related or filter functions. Will this make much difference or is it the same amount of computation?

2. I may be able to remove some of my calcualted columns in PP and create a measure in the Pivot Table instead. As I understand it this measure would only be calculated for the pivot table in question. I'm guessing that if a measure is used in many pivot tables it may be more efficient to create it as calculated column in PP, whereas if it only appears once it may be better as a pivot table measure. Again am I going gain much by moving from PP column to pivot table measure?

Obviously going to be a painful exercise doing all this and don't want to undertake it if no real upside.

Thanks for any advice

Mike

 

[Laurent C]

2. If you create a PowerPivot calculated measure, it will be availabe for all pivot tables based on your PP model. The main difference between a calculated measure and a calculated column, is that the calculated measure will be evaluated within the current filter context. In other words, calculated columns are evaluated independantly of any filters or groupings applied by the user, they behave as raw data do.

 

[masplin]

The question is whether there are efficiency gains to be made by using measures over columns?

 

[Laurent C]

I meant calculated measures and calculated columns behave differently and have different purposes, so the criterium to choose one or the other is not performance.

Loosely speaking: If you want to calculate then aggregate / group / filter, calculated columns are the way to go. If you want to aggregate / group / filter then calculate, then measures are what you need.

Of course, if you have created several calculated columns to do something that one measure could have done, then yes, you might observe a performance improvement by re-designing your model.

 

[masplin]

yes its the latter. I learnt about measures after I'd created lots of columns! Sounds liek it isn't a clear advantage though. Thanks for your advice.