Exago BI v2019.1 contains the following enhancements aimed at improving performance:
Free improvements that require no additional development or configuration.
Report and folder management
Caching was implemented for the report tree. Many interactions with reports and folders will no longer query the storage service. Long-term data is cached on the web server and refreshed on a timed basis or as needed. This results in improvements when using all methods of report and folder storage.
In our testing, reports and dashboards loaded much more quickly in the user interface. Improvements of up to 7.5x were seen in launching dashboards with large numbers of reports.
In our testing, pages in ExpressView, when swapping between them, were observed to render up to 30% faster.
Dashboard interactive filters
Reports on Dashboards with pre-set interactive filter values now skip an unnecessary step when executing. Loading and executing these Dashboards will now be quicker and look visually smoother.
In our testing, Dashboard reports with pre-set filters were observed to run up to 75% faster.
Optimizations were made to the way reports are processed in memory, especially with regard to formulas, parameters, cell references, formatting, and row height. This should result in across-the-board performance improvements when executing reports with these items.
In our testing, some reports with the aforementioned items were observed to run 7-15% faster.
Greater impact but require a small amount of configuration.
Formulas in the database
Most formula functions now have SQL translations for the following databases: MySQL, SQL Server, Oracle, PostgreSQL. Reports with filter and sort formulas, using data from a supported database, can now execute these formulas in that database instead of in the web server by translating the formulas to SQL. The database is far more optimized for these types of executions, so this will result in significant performance gains for these executions.
To enable this behavior, a configuration setting Convert Filter and Sort Formulas to SQL must be set to True. Administrators can add support for custom functions and other databases by adding the SQL translations to the configuration.
See this article for more information about database formulas.
The web server can now cache configuration data that will not be modified at runtime through the API. This will allow for quicker session initialization, a smaller memory footprint, and improvements to frontend performance with large configurations.
Administrators can enable this behavior by splitting the base configuration into a static cached parent file and one or more dynamic modifiable child files. The static configuration file should contain the majority of data objects and column metadata. Using column metadata for most data objects is now recommended and will be a significant net gain in the responsiveness of the frontend.
See this article for more information about configuration caching.
Require some work to set up but have the potential for the greatest impact.
ETL, short for "Extract, Transform, Load", refers to the process of transforming and storing data into a format and storage environment more suitable for retrieval. ETL tools are often used to create data warehouses that are cleaned, pre-processed, consolidated, and reorganized to be more performant for reporting. Exago BI v2019.1 includes a built-in ETL feature that uses the familiar and powerful Advanced Report designer and the Exago Scheduler services to make it easy to build new performant data objects.
The following examples are some performance bottlenecks that can be addressed using ETL.
Structural optimizations (no difference in data between sources and warehouse)
- Joined tables => one table (i.e. denormalization): Joining is an expensive database procedure that scales poorly with large numbers of tables. Data warehouses are often denormalized to reduce the number of on-the-fly joins necessary for data retrieval operations.
- Multiple databases => one database: While Exago BI can join data from multiple different data sources, it is generally not recommended as this is a significantly non-performant process. Cross-source joined objects should be pre-created and warehoused to eliminate this processing as a report execution bottleneck.
- Runtime formulas => preprocessed formulas: Often data fields are transformed using formulas to be better suited for reporting. New transformed fields can be pre-created and warehoused to reduce the amount of on-the-fly processing necessary when running reports.
- Transactional database => Warehouse: Transactional database servers are often under substantial load which can slow operations. Scheduled copying of data to a warehouse can split the load between the two databases. The frequency of retrieval operations on the transactional database will be reduced and reports will be run using the less-stressed warehouse.
- ODBC/NoSQL database => SQL database: Reporting from a database using ODBC to connect to Exago is less performant because certain queries such as filtering and sorting cannot be run in the database and will run on the web server instead. Copying data to a standard SQL warehouse will allow reports to run all supported queries in the database.
Data optimizations (creating smaller, targeted data objects to reduce rows needed for report executions)
- Pre-aggregation: Data tables with large quantities of granular (often time-based) data are generally not suitable for reporting, at least at the row-level. Creating pre-aggregated tables based on broader categories or ranges allows summary reports to run much quicker than if the report needed to process every row individually. This is especially useful for reports using aggregations that are not supported in the database.
- Sampling: Another technique useful for large granular tables is to strategically sample rows based on a pattern, such as every five rows, and create a new table with a fraction of the row quantity that is still useful for some types of reporting.
- Pre-filtering: It may be useful to create multiple tables based on ranges of a larger parent table, so that reports with a smaller range of data can target a specific table and be quicker to run.
Note: ETL is not included in the base contract and comes with an additional cost. Please speak to your Customer Success Representative or file a support ticket for more information.
See this article for more information about ETL.
Coming in future releases, both minor (maintenance) and major versions.
The tool used to render charts, maps, and gauges into images when exporting to static formats will be swapped out for a more performant one.
In our preliminary testing, visualizations were observed to render up to 20x faster.
The library used to export reports to Excel will be updated and optimized.
In our preliminary testing, reports were observed to export to Excel up to 84% faster.
More formulas in the database
Database formula support will be extended to aggregate, group, and top N filter formulas.