Openlink’s Report Builder product is an excellent tool to create a report quickly and easily, often without the need for support from IT. However, the simplicity of creating a report can easily lead to problems, some of which are familiar to users of data warehouses and data lakes.
In this paper we present observations on a decade of the global Findur community’s development of Report Builder definitions, including:
- How to avoid getting into trouble;
- How to identify the troubling conditions; and
- How to get out of trouble if your environment is already in a tough spot.
Report Builder maintenance is part of the reason that we recommend users engage a strategy that will derive value from your Findur/Endur platform for years to come.
Most reporting problems we see begin with governance that needs to be tightened. The Findur/Endur product has powerful security features. A good implementation must include the use of security and user roles for the creation and maintenance of Report Builder definitions. And yet, a substantial portion of the implementations we see have gaps in security.
Nobody should create a report manually in Production; they should all be promoted after the completion of user acceptance testing in a lower environment. Ensure that only a small number of administrative staff have access to deploy reports to Production. An important gate for UAT acceptance should be that non-administrative users cannot update the report’s definition after it is deployed.
Some organizations have accumulated hundreds of report definitions since Report Builder was released in version 10.2. It is worth maintaining an ownership list that shows who owns the report and when it was last run. Tight security surrounding Report Builder definitions is your most effective method and first line of defense against a lot of the challenges covered in this article.
Report Builder definitions may not require OpenJVS or OpenComponents code, but they are still members of the customization family.
Once deployed to Production, we see there are report definitions that are rarely used, if ever. Staff turnover sometimes leads to reports becoming ‘abandonware’. Reports that are run rarely are more likely to suffer from declining quality as operations change over time; they can even be the subject of a future showstopper. The more likely outcome will be a user mistakes a report in Production for a Production-ready report.
As with any customization, first ensure that there is a justification for its need at a sophisticated, senior level. Sometimes we see an organization committed to a customization on the basis of an assertion by a junior member of staff, or a user with a relatively narrow focus of attention in the application.
Make sure no important operational decisions are ever made on the basis of a report of questionable quality. We can help review report usage including how often the definition was executed historically. Reports that have not been run for two years should be reviewed for removal. Some reports are year-end reports, and expected to only run at year-end, so do not set too short a time horizon on abandonware analysis.
Reports sometimes have low risk and minor impact on other areas of the application. However, some reports exist to drive portfolio decision making, back-office settlements, accounting, reconciliation, or regulatory reporting. These are business critical reports! Make sure all reports adhere to a high standard of quality.
Even if a report definition does not require IT to create, we recommend having a solution architect, or someone with a similar, sophisticated and broad perspective of the application, included during the report development lifecycle. The goal of this role is to ensure design quality and consistency, and to avoid repetition, redundancy and performance issues. It is an opportunity to improve quality throughout the application, reduce bugs, improve solution agility, ease upgrades, and reduce total cost of ownership.
The solution architect must be familiar with Openlink architecture, the functional requirements, and on the matter of solution design, be strongly opinionated and able to effectively represent the technology view to business counterparts.
Performance testing should be part of UAT; however, even after passing these tests, performance of a report can decline over time with the accumulation of data. Poorly performing reports frustrate end-users and threaten end-of-day and month-end SLAs.
There are several potential remedies to poorly performing report definitions including distributing processing. There is sometimes a way to easily reconfigure a report’s query or joins to improve its performance. We can help review report performance including historical trend analysis. We include this analysis in our environment health check.
Superfluous Historical Data
Report Builder has features that can archive the content of executed reports. Sometimes it is helpful or even essential to retain historical report content in the database. However, sometimes this feature was enabled unintentionally and there is data from hundreds, perhaps thousands of historical reports in the database. Users may not even be aware that this historical report data is cluttering their database.
We can help diagnose this historical data, disable the feature to reduce the accumulation of unnecessary data, prepare a strategy to retain the essential data, and operationalize a regular task to purge the superfluous data. The benefits include application performance for end-users and IT personnel (e.g. the duration to take a backup and create a dev/test environment), and safeguarding the security of potentially-sensitive data.
Repetition and Duplication
We see repetition in many reporting solutions, which has a significant cost in terms of report maintenance. Sometimes a Report Builder definition is duplicated in order to extract a report with a different set of columns, to extract to multiple file paths, or to multiple file formats. It is not necessary to duplicate a report definition to handle these requirements.
In addition, the complexity of the Openlink data model often leads the designer to duplicate components required by multiple report definitions. For example, FX transactions are a frequent origin of customizations as standard fields are stored in multiple tables.
The choice during design is to implement the same customization in each report or, better yet, to create a reusable component that will be shared by the reports. The latter is the preferred approach because bug fixes or enhancements to support additional FX types (such as swaps, NDFs or options) will be deployed to the shared component, with fewer changes (if any) to dependent report definitions.
The problem of repetition is not limited to Report Builder definitions. Guard against repetition of structures in other components such as TPM, user-defined simulation results, operation services, and pre-Report Builder reports (e.g. Crystal). With straight-forward technical customization, it is possible to share components across all these solutions in Findur.
We would be happy to demonstrate how we have reused components across solutions, including how to use automated unit tests to improve solution quality.
Managing Change Through Upgrades
Changes to the Openlink environment from one version to the next will often expose the problems described in this paper. Clients using treasury solutions such as cash forecasting, SWIFT messaging, cash reconciliation, and more, need to know there were big changes starting in version 17 that could affect their reports.
Organizations can improve the accuracy of their Findur ‘like-for-like’ upgrade project timeline by performing some of these maintenance tasks in advance of the upgrade. Do not wait until the upgrade begins to review Report Builder definitions. Waiting until the upgrade can mean that the timeline presented to project steering committee will underestimate the scope or complexity of changes required to existing Report Builder definitions.
If you need help with your Openlink Report Builder maintenance, please reach out!