PowerPivot - Is it really any good?

Hi,

I just wanted to gauge people opinions of powerpivot. Reading various articles and reviews, its the best thing since sliced bread and have noticed there are a lot of consultancies out there providing a powerpivot service and earning a pretty penny so I'm thinking it must be good.

However, I have used and received some basic training on powerpivot, and quite easily managed to grind this to a halt. In my eyes producing a stock report from a dataset that no where near pushes the boundaries of the data handling limits it boasts should be a simple task, or applying fairly detailed DAX formulas to select data should be part and parcel within its capabilities.

So I guess what I am asking is, is it me? am I expecting too much? or is there a problem with the initial data source etc etc?

Your views are appreciated.
Thanks
Garry

Originally Posted by JosephP

impossible to say without knowing what you did but I can say that it is easily possible to write inefficient measures and calculated columns that will cause problems. powerpivot has its strengths and weaknesses and is not a panacea for all pivot table woes-for many situations it is complete overkill and even unhelpful. it also depends on the capabilities of the machine you are using it on :-)

Let me try and give you an example of the data. There are 240,000 rows and 20 columns. Within the 240,000 rows are 60,000 prodcuts each having 4 processes. The date range is 1 year, therefore 5000 products are processed each month. The aim was to produce a pivot table stock report to list the 5000 products and the process it was in at the end of each month (where the months are the slicer values).

Am I pushing it?

Thanks

Originally Posted by JosephP

that doesn't really tell me what you did but in terms of data that should not be an issue at all for pp or for a standard pivot table for that matter

This is my measure. It checks each part number to return the last event prior to the stock report date (selected from the slicer), except where the process is despatched or scrapped to show me what is in stock. It works, but is painfully slow and I can see excel is struggling with it.

=SUMX(Movements,IF(CALCULATE(MAXX(FILTER(Movements,Movements[MeasuresDate Time]<=FIRSTDATE(Month_Table[DateTimeFrom])&&Movements[To Process]<>"DESPATCHED" && Movements[ToProcess]<>"SCRAPPED"),Movements[Event History Key]), ALLEXCEPT(Movements,Movements[PartNumber]))=Movements[Event History Key],Movements[MeasuresCurrent Weight],0))