Speeding up Aggr by reusing cache

[nafi_qlikview]

Hi,

I've spent a lot of time on this already, without any success. Maybe you guys can help.

In my data model, I have a list of quote requests and corresponding quotes for each request:

QUOTEREQUESTS:

QUOTEREQUESTID, CREATED

QUOTES:

QUOTEREQUESTID, RANK, PRODUCTID, PRICE, ...

I need to do a number of calculations on the best quote per quote request. E.g. average PRICE of the best quote.

Depending on who uses the dashboard, the view on products is restricted so that I can't just filter on RANK = 1 or pre-aggregate, but instead have to dynamically aggregate based on min RANK.

In order to calculate the average PRICE for the best quote, as mentioned in my example, I use something like:

Avg(Aggr(FirstSortedValue(PRICE, RANK), QUOTEREQUESTID))

Due to the data volume in my application this is quite slow for this one expression already. Since I have to calculate a number of other metrics based on other fields than PRICE, calculation times for each expression add up.

Now, I was wondering if anyone has an idea how I could improve this. One idea I had was to try to somehow dynamically cache the information for the best quote (so that it's only aggregated once) and then calcualte everything from the cached results. However, I couldn't work out how to do this since Aggr can always only store one value. Another idea was to store (aggregate) the best PRODUCTID per QUOTEREQUEST and then somehow use set analysis on this, but couldn't work out how to do this either.

Also, this whole Aggr operation seems to be single threaded. Any idea why? Wouldn't logically expect that.

I would appreciate your input!

Martin

[marcus_sommer]

Maybe you could it put in one table so that there isn't any hop necessary - maybe aggregated in an extra table to a higher level and linked with an autonumber-key to your other data. And yes your notice about the file-size is correct - these kind of optimization should be improve the calculations within the gui but led to disadvantages by other things like app/ram size, load-time, complexity ... maybe there are futher possibilities to improve the overall performance. Will be all these indexes really needed, by the keys are this autonumber ... http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/11/the-importance-of-being-distinct

I think your caching-thoughts won't fit with the methods which qv used, unfortunately ...

- Marcus

[nafi_qlikview]

OK, found something that could work although it doesn't look like QlikView is reusing the cache at the moment (it's still slightly faster though because I can use NODISTINCT in the Aggr):

Avg(If(PRODUCTID = Aggr(FirstSortedValue(PRODUCTID, RANK), QUOTEREQUESTID), ...))

This way I'm using the same Aggr expression for all my metrics. For some reason this is still not quick enough though which leads me to think that QV isn't reusing the results. Any suggestions how I could force it to do so?

Thanks,

Martin

[marcus_sommer]

QV caches calculations only if they are absolutely identical - see here the last paragraph from http://community.qlik.com/blogs/qlikviewdesignblog/2014/04/14/the-qlikview-cache

- Marcus

[nafi_qlikview]

Hi Marcus,

I'm aware of this. However, I was hoping that this would also apply to parts of a formula as the Aggr bit is a completely separate calculation.

Can you think of a way how I could still have the Aggr part cached (e.g. moving it to a variable, although I'm not sure if results on an Aggr can be stored in a variable)?

Thanks,

Martin