Business Challenge
Our customer is a global financial advisory company that provides many services for its clients in diverse areas of the financial world. Although business logic can vary widely between different clients and projects, they all require a way to represent their work’s results in a convenient and powerful format. They also need to analyze the data received and process it for better decision making. All of this requires a complex, powerful and customizable reporting solution in order to react to business requests quickly and keep pace with dynamic financial processes.
Technologies
- Microsoft SQL Server 2012
- SQL Server Reporting Services
- SQL Server Integration Services
- Microsoft .Net Framework (MVC4 technology)
- PowerShell
Solution
The solution is based on the Microsoft technologies stack, which ensures compatibility between all parts of the application. The data is stored in databases that are deliberately designed to ensure high-speed data access and prevent any data redundancy. With dedicated data marts for reporting, not only can we conveniently store the data, but can implement the necessary complex business logic for reporting by using stored procedures, functions and views–all while not affecting application business users’ work. Complex financial formulas are easily maintained in the system on the database level by using .Net integration to create and deploy custom aggregate functions to the database. This ensures that the calculations are performed quickly and accurately.
A large number of internal tools in the reporting engine and customizability through the .Net technology allows us to consistently meet the client’s needs for report design. Extending the original report engine’s functionality using .Net helped us to automate complex design logic with dynamic sizing and scaling, which is not supported by traditional engines. A powerful Web API and Web Forms Control let us easily embed reports into our web application and take full control over report preparation and generation from the web applications code. As a result, we can provide numerous ways to deliver the generated report to the final users, including showing it in the application either as part of the web page or as a PDF document, exporting it to multiple formats, delivering reports to email, scheduling reports to be delivered by email or shared folders, and so on. Separate logic has been implemented to support easily maintainable ad hoc reports, which allows us to also provide clients all types of data for short-term analysis.
The data flows between the internal systems and external data sources are automated using numerous ETL processes. They ensure the data is consistent and correct in all phases of the processes, and the elaborate error handling assures data issues don’t pass unnoticed. The processes are designed not only to strengthen data consistency and durability, but also to guarantee data protection on all sides of the data flows. The entire solution for the client consists of the three separate projects, each having the Web Application as an access point, the interface for the business users to input some data, ETL process as a way to import some data into the system and the reporting as a main output of the system. The systems have some shared modules and data storages, and are intended to be incorporated into one system with the single access point eventually.
- The first system is a residential loan application that provides support for contemporary residential mortgage due diligence and litigation support activities. It supports many clients and business projects in a multi-tenanted architecture. This means that we need to process the data from some external data sources, provide users a way to analyze that data in the Web Application and input some analysis results in a structured form that will be used for reporting. For this, we developed a solution that allows managers to define the custom data structure from the interface with various relations and conditions between data points. This allows us to make project-specific questionnaire forms that are completely based on meta-data, eliminating the need for any additional development for different clients. From the reporting perspective, we offer numerous reports to help business users analyze the income data and intermediate results of their work. Additionally, we have reports for client deliverables, which are actually the main output of the system. Each client has their own requirements for their final reports, which is why the Client Deliverables reports are almost completely based on the meta-data. This allows managers to configure the look and feel of the report and change some logic without involving a development team.
- The second system is an asset management and loan servicer monitoring and reporting application for portfolio managers. This application is intended to help analyze loan portfolios without making any changes to them. The system’s data comes from numerous data sources on a daily basis and is processed by a huge ETL system. For the ETL system, we developed a separate structure to store mappings between external and internal data points, including complex relations, calculated points, and more. This helps us add the new data sources by simply adding the configurations and eliminates the need to develop separate ETL processes for them. As an output, the system has a dozen reports to help managers understand profitability of the portfolios. The distinctive feature of this system’s reports is a common approach to the report design, which allows us to store some unified parts of the reports on the report server. This ensures that reports’ designs are consistent.
- The third system was initially developed to automate Funds of Funds reporting, but has evolved as the universal reporting solution for automating any investments portfolios reporting. This application’s clients are different companies with different business models; that’s why we needed a way to store data in a convenient-for-anybody manner. To meet this need, we designed a flexible database that lets us configure the separate data points for each client and stores historical data. To meet clients’ particular report design expectations, we developed special features that control font sizes, tables, and label heights that are automatically based on the given data. Some aspects of these features are also controlled by meta-data that managers can change to improve look and feel of the reports.