The reporting development team for a client company in the fund management administration industry needed to for a variety of clients and have these reports automatically delivered.
Shortfalls of Microsoft Reporting Services
The team had traditionally used Microsoft Reporting Services to both host and develop these reports. They became frustrated by the many shortfalls within the service’s ability to both produce and automate the delivery of these reports in the varying client specific formats required. The result was that the team were faced with extensive lead times to compensate for the inability of the Microsoft Reporting architecture to automate this final part of the reporting process.
THE CHALLENGE: The Reporting Process is a Resource Intensive Initiative
The challenge was to quickly setup a system to handle the issues experienced by the report development team as this final step of the reporting process, which appeared to be the least complicated, had quickly become a resource intensive initiative.
Generate Reports in Multiple File Formats
Any solution would first need to be able to generate reports in multiple file formats using either SSRS or custom SQL scripts as a data source. The original system had struggled when exporting reports to CSV from SSRS and would result in multiple blank header rows and column names that were not suitable for the CSV format being introduced into the exported file.
Configuring the Architecture for File Manipulation
The original architecture was also not configurable enough to perform manipulation operations on these files as required. Options such as removing specific rows or searching and replacing header names would also ideally be addressed as plugins. These could then be dynamically added to the solution when needed, with minimal changes required to the core solution.
Report Scheduling & Delivery
The solution also needed to schedule the generation and delivery of reports as defined by the user, as well as being able to support the delivery of these reports over multiple channels including email, FTP and file share delivery.
Change Control Management & Process Management Applications
Configurations created on the system would need to follow SDLC rules as well as a change control management process. Full auditing of the approval process would also need to be required before any configuration could be made Live.
Finally, any process configured on the system needed to be able to be invoked externally from other systems either directly from other applications, or with process management applications such as Control M.
A Proof of Concept Solution
A generic solution was needed which not only had the ability to reuse the underlying architecture required to manipulate and deliver the reports, but also required no changes to be implemented at the source. The client company approached Digiata for a proof of concept solution.
Digiata engaged with the client as well as the teams impacted by the reporting shortfalls and quickly determined several key issues that needed to be resolved.
The proposed solution needed to be:
- Easy to configure
- Be able to integrate easily with external systems
- Needed to comply with governance and risk
This meant that each configuration needed to undergo the necessary change control procedures before being allowed to run.
Digiata proposed a solution that gave the report developer the ability to configure a process on the systems front end GUI which in turn allowed them to create a report in a desired file format from either SSRS or a custom SQL script. The user could then setup manipulation criteria for the process before finally delivering the reports using several channels.
Twenty57 Tools, Linx & Stadium
The solution was developed using the Twenty57 tools, Linx and Stadium. It was designed to be modular so that manipulation and delivery plugins could easily be added in the future with minor system enhancements as per the client’s needs.
All of the back end processing systems were created on the Linx architecture which offered not only rapid application design capabilities, but also supported all of the integration points used with the external systems.
Stadium further created the GUI elements which were hosted on the client’s intranet to allow the system to be accessible to all the users, including the report developers responsible for creating the configurations and the administrators who would approve and release these configurations.
Parallel Processing across Multiple Servers
The solution had been designed from inception to support parallel processing across multiple servers in order to handle the high volumes which would result from system gradually replacing all existing solutions. The goal was for the system to become the first choice for all new projects due to the expected efficiency gains in the project lifecycle.
After migrating to the new solution, the reporting developers were able to once again focus on their primary area of expertise. There is no longer a need to spend weeks developing custom applications to handle the automation of the reporting. Nor is there a need to create custom permutations of the same report in order for the export file formats to be compatible. All configurations can be implemented within an hour using the new solution, after which the change control process can be actioned within a few minutes if all the checkboxes have been ticked for approval by the administrators.
Due to the efficiency of the solution, the client is able to save on the resourcing hours required on the projects. This is a significant time saving which is passed directly on to their clients through lower project costs and shorter lead times. The incorporation of the SDLC lifecycle as well as reusing the underlying architecture also reduces the risk by offering consistency across the solutions.