Exago version 2016.3 comes with a new feature that can help improve the performance of report execution: Database Aggregation.
What are Aggregate Formulas?
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, we would do this by putting the formula AggCount in a Group Footer or Report Footer section.
Other supported aggregate formulas are AggSum, AggAvg, AggMax, and AggMin.
What is Database Aggregation?
In previous versions of Exago, or with this feature turned off, the Exago application 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 rows which don't need to see row data, performance can be reduced.
Most databases have the ability to do aggregate calculations on the back-end, and this feature allows Exago to query the database to do those calculations automatically. This way, Exago only pulls one row from the database, instead of many rows. This can lead to a significant performance improvement in large reports.
When will Database Aggregation work?
Database Aggregation will not work on every report:
- Reports must use only one data source.
- Reports cannot have One-to-Many joins.
- Reports which have a visible Detail section gain no performance benefit from database aggregation, since all the data rows are already being queried individually.
- Reports which use Formula Sorts or Custom Columns cannot use database aggregation, since the calculations are being performed on rows that do not exist in the database.
- Each aggregate formula must reference a single unmodified data field. It will not work with formulas or conditional data.
- Reports cannot have Interactive Filters, Column Sorts, Group Min/Max Filters, or a Linked-report Conditional.
NOTE. Reports created in previous versions of Exago had column sorts added by default. This is no longer the case.
Enabling Database Aggregation
Admins can enable this feature on an application-wide basis by toggling the following Admin Console setting to True, or by editing the config file:
( Database Settings Aggregate and Group in Database ) <aggregateandgroupindatabase>
WARNING: To ensure that incorrect data will not be returned, see the following section on One-to-Many joins.
Identifying One-to-Many Joins
You must ensure that all One-to-Many joins in your configuration are properly identified. One-to-Many joins are incompatible with Database Aggregation. If Exago does not recognize the joins as such, the database will return incorrect values. The joins must be set correctly to ensure that the application does not use database aggregation for these fields.
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 the following precautions before enabling Database Aggregation.
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.
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.