Exago version 2016.3 comes with a new feature that can help improve the performance of report execution: Database Aggregation.
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, AggDistinctCount, AggMax, AggMin, and RunningSum.
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.
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>
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 aggregates are done in the application, not in the database.
One-to-One relations are joins where for each row in the left table, there is only one corresponding row in the right table (identified by the primary key). E.g. Orders > OrderDetails is a one-to-one relation:
Order 1 |
? |
Order 1 Details |
Order 2 |
? |
Order 2 Details |
Order 3 |
? |
Order 3 Details |
Order 4 |
? |
Order 4 Details |
One-to-Many relations are joins where for each row in the left table, there are one or more corresponding rows in the right table (identified by the primary key). E.g. Products > OrderDetails is a one-to-many relation:
Product 1 |
? |
Order 1 Details |
|
|
Order 2 Details |
Product 2 |
? |
Order 3 Details |
|
|
Order 4 Details |
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.
Database Aggregation will not work on every report:
NOTE. Reports created in previous versions of Exago had column sorts added by default. This is no longer the case.