Solution for Data Extraction and Transformation
Our client is a leading provider of analytical software and advisory services to whole loan and structured finance investors. Their analytic solution uses highly scalable cash flow and risk modelling to conduct visible and controlled investment analysis. Our client’s pricing solutions are an independent, transparent and systematic means to price, hold and manage risk of MBS and ABS and they bring expert knowledge to efforts managing mortgage data and analytics, vendor scoring, model risk and operational controls.
The client had huge amounts of financial loan level data. The data, running into millions of records, was given to them by their clients and vendors on a monthly basis. There did not seem to be any way to normalize the data sent by different companies into a single database, which could then be queried to retrieve meaningful reports.
There were two primary technical challenges. The first was the speed at which the data should be imported after they are received from the clients/vendors, extracted and reports generated. All of this needed to be to be 10x faster than the current system. The second was the level of user friendliness required. The users were not IT staff but financial analysts. Hence the user interface had to be simple and intuitive.
In experiments done by us and as reported by the client, the existing methods would take days for successful import after validations into the MySQL database. In addition, the user interface was not friendly for use by IT staff as well as non-IT financial analysts.
Web Access came up with a solution that consisted of two departures from traditional approach. The first was creating a web services application on a thick client, designed in .Net giving the analysts the simplicity and flexibility they could understand and easily use. The server end used Java technologies with a MySQL database.
The second departure was in the approach in which data was being handled. We changed the traditional extract transform load approach to an extract load transform load application. We introduced an intermediate load that accelerated the process by over 10x.
The transformation includes validations, as well as creation of new fields that are mathematical and / or logical derivatives of one or more existing fields in the database.
The extract load transform load application has the ability to load and transform several files of over a million records each in under an hour.
The application is currently being used to transform and normalize financial data from multiple sources quickly and easily. What used to take analysts weeks, now takes them minutes.
