Automating Enterprise Reporting - A Case Study

Reporting plays a crucial role in the operations and success of organizations of any size or industry. It is critical for capturing the essence of the market trends and insights, the sales, finances and a variety of other business operations. By evaluating reports’ aggregated information, management can measure things and make strategic decisions.

The faster and the more thorough reporting is, the quicker an enterprise can react and take action. So, how can business intelligence and reporting be improved to the greatest extent in an enterprise?

In one of our projects, for the regional headquarters of a reputable financial institution, we achieved the almost impossible.

The Requirements

Starting with the requirements, we had to address the following:

• Processing massive amounts of data
• Gathering data across multiple and different data sources
• Providing users data querying and filtering tools to create, design and update reports on their own, as per their needs
• These interface tools should be powerful yet simple to use by non-technical users
• Producing all kinds of reports regardless their complexity, and with aggregated data from different queries
• Allow reusing common query and filter definitions in order to build other more complex queries on top of them
• Parameterize queries and reports (e.g. run the same report in a different month)
• Auto-generate periodic reports (e.g. generate on the first day of each month)

The Solution

The approach taken was multi-level, consisting of the following fundamental modules:

• Data gathering automation engine
• Powerful math expression engine
• Powerful query engine and editor
• Powerful filtering engine and editor
• Powerful reporting engine and designer
• Report scheduling engine

Data gathering automation engine: The engine connected to multiple RDBMS to collect and process data at an initial aggregation level. Data was available to all next modules either in raw or aggregated form.

Math expression engine: The engine was designed to parse complex expressions, extract tokens (such as mathematical formulas, variables, parameters, queries, filters and sub-expressions) that were found inside the expression and process them.

Query engine and editor: The query editor allowed users to drag & drop tables, columns, other queries and filters, using a higher level syntax that was visualized and easy to understand by non-technical people. Joining between tables was done automatically based on referential integrity, plus custom criteria as needed. Nesting queries and defining complex calculations were simple thanks to the expression engine’s power.

Filtering engine and editor: The filtering engine was also based upon the expression engine. The editor allowed users to drag & drop ready-made filters directly from the Chart of Accounts, as data was financial information. Nesting filters was also possible.

Reporting engine and designer: To meet enterprise reporting needs, two different solutions were combined. For simple, mostly tabular, reports, the system integrated a ready-made report designer solution, like those available on the market. However, for highly complex reports (such as those used for reporting to the bank’s global headquarters or to the central bank), which had to present different aggregated information coming from tens of different queries, a custom Excel report engine exceeded the demands. The engine was designed to allow mapping a query on each cell or range of cells in Excel, hence feeding at once Excel cells with data coming from tens or hundreds of different data sources. The solution covered generating reports of extreme complexity with an unmatched automation level.

Report scheduling engine: After having all the necessary reports defined in the system, automating enterprise reporting was a breeze. The scheduling engine allowed users to specify the periodicity of their reports, and generated them automatically on users’ behalf.

The Results

The grand result was that many hundreds of working hours were completely eliminated, freeing users from manual repetitive tasks and from the stress of preparing on-time and with absolute accuracy such complex and important regulatory reports on a monthly basis.

Epilogue

Enterprise reporting and its automation are critical to every organization. We delivered this solution to the reputable financial institution a long time ago, with tremendous success, breaking project’s owners’ initial doubts that there can ever be a software to cope with this level of complexity.

With today’s technologies and the evolution of ETL & message queue tools, we are now able to raise the bar much higher. Incorporating real-time data streaming, data analysis, data quality controls, conditional notifications/alerting and other functions, we can deliver unmatched enterprise reporting & BI solutions with stunning real-time dashboards to large organizations, such as banks, telecommunication providers, and governmental entities, with the same success.