PowerPivot - Am I asking too much?

[Twi78]

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?

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

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))

Am I pushing it?



Your views are appreciated.
Thanks
Garry​

 

[JavierGuillen]

Hi Garry

It is true that PowerPivot's engine (xVelocity) can be very fast when computing aggregates. However, you must be mindful of the execution process of your DAX formula or performance will be affected. By default, there are two execution modes: row-by-row or bulk (filter context) mode. Many functions trigger an iterative row-by-row execution, so you must be careful when using them, particularly when you nest them.

In your case, there are three nested iterating functions: SUMX ( MAXX ( FILTER () ) ). Over bigger datasets, this will certainly have a performance impact. For example, I see you are comparing the [Event History Key] with the result of the MAXX, but you most likely don't need to iterate over the Movements table in order to generate this max value. As an example, you could replace MAXX with:

CALCULATE(
MAX(Movements[Event History Key]),
FILTER(
Movements,
Movements[MeasuresDate Time]<=FIRSTDATE(Month_Table[DateTimeFrom])&&
Movements[To Process]<>"DESPATCHED" &&
Movements[ToProcess]<>"SCRAPPED"
) )

Doing so will eliminate one nested iteration.