Exago version 2016.3 comes with a new feature that can help improve the performance of report execution: Database Aggregation.
Background
Aggregates are calculations performed on a group of data rows. For example, counting the number of rows in a group is an aggregate calculation. In Exago, this is done by using an aggregate formula in a Group Footer or Report Footer section.
Supported aggregate formulas are AggCount, AggSum, AggAvg, AggMax, and AggMin.
Exago calculates aggregates by individually pulling each detail row from the database, and then applying a formula. For small reports, this is usually acceptable, but in reports with large numbers of row, where you don't need to see the row data, performance can be reduced.
Most databases have the ability to calculate aggregates themselves. This tends to be much faster than performing the calculations at the application level. The Database Aggregation feature allows Exago to push these calculations to the database. This allows Exago to retrieve only one row from the database, which contains the query, instead of all the individual data rows. This can be a significant performance boon, especially for large reports.
Enabling Database Aggregation
Admins can enable database aggregation by toggling the following Admin Console setting to True, or by editing the config file:
( Database Settings Aggregate and Group in Database ) <aggregateandgroupindatabase>
WARNING: You must see the following section to ensure that aggregates for One-to-Many joins are calculated correctly.
Identifying One-to-Many Joins
All One-to-Many joins must be identified as such in the application configuration. Exago calculates aggregates based on the unique key relationship between categories. But some databases calculate aggregates by simply counting the rows. This means Exago must know which joins are one-to-many, so that it can decide whether it is acceptable to push the calculation to the database, or whether it must do the calculation in the application.
NOTE. The Data Source Metadata Discovery tool cannot identify whether joins are One-to-Many. By default it sets all joins as One-to-One. If you used this tool to autofill your Objects and Joins, you must take these precautions before enabling Database Aggregation.
Admin Console
To set your joins using the Admin console, expand Data > Joins, and then double-click on the Join, or select the Join and press Edit. This will open the Joins tab.
In the Relation Type drop-down menu, select One To Many. Then press Apply or OK to save your changes. Do this for every relevant Join.
Config File
To set your joins by editing the config file, open your config file in a text or xml editor. Each join is a <join> element. In each relevant join element, locate the <relation_type> field and change the value from 11 (one-to-one) to 1M (one-to-many). Then save the config and restart the web server.
Exceptions
Database aggregation will not work on every report. In the following cases, the application will perform the aggregation instead of the database, even if Database Aggregation is enabled:
- ExpressViews (prior to v2017.3)
- Reports with more than one data source
- Reports with a One-to-Many join or a Cross Join
- Reports with a visible Detail section or cells referencing a hidden Detail section
- Reports with Formula Sorts or Custom Columns
- Aggregate formulas with a non-data field argument, such as another formula
- Reports with one or more Interactive Filters, Column Sorts, Group Min/Max Filters, Top N Filters, Linked Report Formulas, Crosstabs, or Transformed Vertical Tables