Analytical List Page and Aggregated CAP View crashes with Out-Of-Memory in Browser

[ckuczera]

Dear Experts,

we are currently facing an Issue with Anylical List Page and CAP Aggregation View.

We are consuming the aggregated data within the Analyical List Page in Visual Filters, Chart and Table. The Visual Filter and Table are working. But in some cases the Chart crashes with an out of memory message in the Browser:

We have already investigated but until now I can't find the reason why the chart is crashing. We have already copied the aggregated data to a local test. The test succeded in the local environment:

The CDS View is build as described here:

// Costs Analytics View
@readonly
@cds.redirection.target : true
@Aggregation.ApplySupported.PropertyRestrictions : true 
define view CostsAnalytics as select from db.XXXCosts { 
key ID,
@Analytics.Dimension : true 
perfDate,
@Analytics.Dimension : true
distributionDate, 
... 
@title: '{i18n>NetCost}'<br>@Analytics.Measure : true 
@Aggregation.default : #SUM round( netCost, 2 ) as netCost : Decimal(15, 2), 
@title: '{i18n>TaxCost}' 
@Analytics.Measure : true 
@Aggregation.default : #SUM round( taxCost, 2 ) as taxCost : Decimal(15, 2),
@title: '{i18n>GrossCost}' 
@Analytics.Measure : true 
@Aggregation.default : #SUM round( grossCost, 2 ) as grossCost : Decimal(15, 2) 
};

The only difference is that the CAP Aggregation View is sending an inline count containing all entries from the database:

The count does not reflect the entries resulting from the select parameter. For Example: an select on Field costYearMonth and netCosts with a Filter set to Year 2021 is returning 12 Entries with aggregated Costs per Month in 2021, but contains an inline Count of all Entries in 2021 (49.802.503 Entries).

This is also reflected in the fixed Value List of all Visual Filters where Paging is tried for approx 60 Bil entries even the whole aggregated list does only contain 47 entries!

We are using the v2 proxy for the CAP View due to the fact that our UI5 Version dies not support v4 yet.

Hope anyone can help me on that issue.

BR & Thanks for any hint

[OlenaT · Accepted solution]

This issue was fixed in cds@5.7.3

[ckuczera]

We figured out that the logic to calculate the count is located in the file @sap\cds\libx\_runtime\db\query\read.js Method _createCountQuery:

function _createCountQuery(query) {
  const _query = JSON.parse(JSON.stringify(query)) // REVISIT: Use query.clone() instead
  _query.SELECT.columns = [{ func: 'count', args: [{val: 1 }], as: '$count' }]
  delete _query.SELECT.groupBy
  delete _query.SELECT.limit
  delete _query.SELECT.orderBy // not necessary to keep that
  // Also change columns in sub queries
  if (_query.SELECT.from.SET) {
    _query.SELECT.from.SET.args.forEach(subCountQuery => {
      subCountQuery.SELECT.columns = [{ val: 1 }]
    })
  }
  if (query.SELECT._4odata) _query.SELECT._4odata = true
  return _query
}

We changed the logic to check for columns with functions (sum, avg aso.) and using the countdistinct function to calculate the correct $count as described here:

function _createCountQuery(query) {
  const _query = JSON.parse(JSON.stringify(query)) // REVISIT: Use query.clone() instead
  // ************* Fix for Counting Bug in Aanlytical Aggregation Views
  if(_query.SELECT.columns && _query.SELECT.columns.find(oColumn => oColumn.func)) {
    if(_query.SELECT.groupBy) {
      _query.SELECT.columns = [{ func: 'countdistinct', args: _query.SELECT.groupBy, as: '$count' }]
    } else {
      _query.SELECT.columns = [{ val: 1, as: '$count' }]
    }
  } else {
    _query.SELECT.columns = [{ func: 'count', args: [{val: 1 }], as: '$count' }]
  }
  // ********* End Fix for Counting Bug in Aanlytical Aggregation Views
  delete _query.SELECT.groupBy
  delete _query.SELECT.limit
  delete _query.SELECT.orderBy // not necessary to keep that
  // Also change columns in sub queries
  if (_query.SELECT.from.SET) {
    _query.SELECT.from.SET.args.forEach(subCountQuery => {
      subCountQuery.SELECT.columns = [{ val: 1 }]
    })
  }
  if (query.SELECT._4odata) _query.SELECT._4odata = true
  return _query
}

Furthermore we are using a local sqlite database to test our application which has a different logic in countdistinct function and requires a second change in file @sap\cds\libx\_runtime\sqlite\customBuilder\CustomFunctionBuilder.js. In this file an explicit function fur countdistinct was necessary which add the counting fields seperated by || and not ,

  _handleFunction() {
    const functionName = this._functionName()
    const args = this._functionArgs()
    if (dateTimeFunctions.has(functionName)) {
      this._timeFunction(functionName, args)
    } else if (standadFunctions.includes(functionName)) {
      this._standardFunction(functionName, args)
    } else if (functionName === 'seconds_between') {
      this._secondsBetweenFunction(args)
    } else if (functionName === 'countdistinct') {
      this._executeCountDistinct(args)
    } else {
      super._handleFunction()
    }
  }
  _executeCountDistinct(args) {
    this._outputObj.sql.push('count', '(', 'DISTINCT')
    if (typeof args === 'string') this._outputObj.sql.push(args)
    else this._addFunctionArgs(args, true, ' || ')
    this._outputObj.sql.push(')')
  }

gregorw: thank you for the hint, we'll open an OSS Message with the details of this post

[ckuczera]

Dear CAP Community,

it seems that there is an Error located in the V4 Logic:

Example odata V4 Request (grouped by costYearMon and filtered on Year 2021):

/srv_api/aggregated-costs/CostsAnalytics?$top=100&$count=true&$apply=filter((((costYear eq '2021'))))/groupby((costYearMon),aggregate(taxCost with sum as __AGGREGATION__taxCost,netCost with sum as __AGGREGATION__netCost,grossCost with sum as __AGGREGATION__grossCost))

Returns an odata count of "@odata.count": 67461606,

In fact, the result countains 12 entries (one per month).

BR

Christoph

[gregorw]

Have you filed an issue via SAP Support?

[david_kunz2]

Hi ckuczera ,

Thanks a lot for this nice write-up, highly appreciated! We'll have a look into it!

Best regards,

David