Case Study: Loan-Level Capital Reporting Environment
A GSE and large mortgage securitizer maintained data from multiple work streams in several disparate systems, provided at different frequencies. Quarterly and ad-hoc data aggregation, consolidation, reporting and analytics required a significant amount of time and personnel hours.
The client desired configurable integration with source systems, automated acquisition of over 375 million records and performance improvements in report development.
- Reviewed system architecture, security protocol, user requirements and data dictionaries to determine feasibility and approach.
- Developed a user-configurable ETL Engine, developed in Python, to load data from different sources into a PostgreSQL data repository hosted on Linux server. The engine provides real-time status updates and error tracking.
- Developed the reporting module of the ETL Engine in Python to automatically generate client-defined Excel reports, reducing report development time from days to minutes
- Made raw and aggregated data available for internal users to connect virtually any reporting tool, including Python, R, Tableau and Excel
- Developed a user interface, leveraging the API exposed by the ETL Engine, allowing users to create and schedule jobs as well as stand up user-controlled reporting environments