Cache money: which calculation results Qlik reuses across objects

A few people have recently asked me whether Qlik still sees the same expression with cosmetic syntax variations (capitalization, spacing) as different expressions, for the purpose of reusing the cached calculation results across objects. You may recognize this condition in the Qlik Sense Document Analyzer as the "Expression syntax consistency" flag.

Sum(Sales) <> Sum( Sales ) <> SUM(Sales)

This was the case before, but I haven't retested the scenario in ages to see whether the engine has changed, so I took a shot over a few evenings at Qonnections 2019, in Dallas.

Testing

Here is a sample data generation script and the main two expressions I used -- enough data and complex enough expressions to tell whether the results were instantaneous (from the cache) or took a few seconds.

 Sample:
 LOAD
  'C' & Left(RowNo(), 1) as Country,
  Left(RowNo(), 4) as OrderID,
  Round(Rand() * 500) as Sales,
  Round(Rand() * 30) as UnitCost,
  Round(Rand() * 10) as Quantity
 AUTOGENERATE 10000000;

  • Expression 1: Avg(Aggr(Sum(Sales),OrderID))
  • Expression 2: Avg(Aggr(Sum(Sales),OrderID)) / Avg(TOTAL Aggr(Sum(Sales),OrderID))
I always started with this table/sheet, then created a number of variations and checked whether the results were instantaneous or required time to calculate when I opened those sheets.


Results

Testing in Qlik Sense Desktop April 2019, here is what I found.

Where cache was reused

  • Copy of the original table, but represented as a combo chart. This actually didn't work when I tested it in QlikView 11.2 in 2014, so that's an improvement.
  • Copy of the original table, but with variables containing the original expression formulas.
  • Copy of the original table, but with Master Measures containing the original expression formulas.

Where cache was not reused

  • Copy of the original table, with some capitalization and spacing changes in the expressions. This is the same as original question and the flag in the Qlik Sense Document Analyzer, and it's still an issue.
  • Copy of the original table, but with different number format settings in the expressions. This was surprising! Given this, I would love to see a default number format in the Master Measure settings.
  • Copy of the original table, with the order of the two expressions switched. You can see this even if you just drag an expression to change the order of columns in an existing chart.
  • Copy of the original table, but with the second expression removed. It didn't recognize that it had already calculated that expression grouped by the same dimension.
  • The two expressions from the original table, but stored in KPI objects. I was curious whether it would have awareness that it had already computed these amounts in the "total" row of the original table
To make sure there wasn't something unique about the Aggr function and the calculation engine, I also tried simpler expressions, Sum(Sales) and Count(DISTINCT OrderID), increased the row count, and increased the number of distinct Country values, with the same results.

Conclusions

The cache only seems to be used if an object has the same dimensions and identical expressions, in the same order, with the same formatting, regardless of the chart type -- i.e. any property of the hypercube. Even though there is one improvement since I last tested this, my conclusion is the same: it is very unlikely that cache ever gets reused across objects, no matter how careful a developer is to be consistent, although it still would get reused across sessions for the same objects.

My other conclusion is that there is an opportunity for Qlik to improve the performance of its engine and reduce RAM consumption by getting smarter about cache reuse.

Contact Form

Name

Email *

Message *