Get In Touch
221 River Street, 9th Floor,
Hoboken, New Jersey 07030
United States of America
Work Inquiries

Career Inquiries

Managing Data for Business Intelligence: A Case Study

The client

A fast-growing investment company in the business of purchasing home loans from banks, and then working with loan servicing companies, who managed the relationship with the borrowers.  

The client hired Tricon Infotech to replace their laborious and inefficient spreadsheet-based intake system with an automated platform for loan originators to submit loans for consideration. This allowed the client to quickly evaluate and purchase the best loans ahead of their competitors.  

The client’s business was so successful that soon it was purchasing loans from over 80 banks to the tune of tens of thousands of loans

The problem

The client also worked with a half-dozen loan servicing companies to manage the homeowners’ payments and avoid defaults. Each servicer would submit status updates regularly via Excel or CSV files that contained up to 200 columns each.  

In order to evaluate the loans properly, the client was going to need to combine the data from the originators and the servicers. That meant processing nearly a hundred unique data sources, each with hundreds of columns of data, all of it arriving on a daily basis.   

How was the client to manage this tsunami of data? 

The goal

Produce a system that would automatically process the data and deliver meaningful reports so the client could properly understand how its loans were being serviced. 

The solution

Tricon Infotech’s architects knew that the first step was to consolidate all the information into a single centralized data warehouse, and then shrink it into smaller warehouses until it had a small enough dataset to then run the requirements reports efficiently. 

Separate platforms combine the incoming data from the loan originators and loan servicers, and then consolidate the data into a single database, dubbed Bronze DB. This is the master list, so new reports in the future can reference historical data. 

From there, the system automatically selects only those columns that are necessary for reporting over to a smaller database, Silver DB. 

The final database aggregates data from separate tables into the smallest, manageable source, Gold DB, which has about 30% of the columns that are in the Bronze DB. 

Now the system can run predetermined reports and display them in Tableau, all refreshed on an hourly basis thanks to the manageable database size.

Illustration of how the complex data is processed through three databases to generate the BI Report

The result

The client now is well-positioned to continue its work thanks to the automated intake from loan originators, updated information from loan servicers, and a new suite of reports. Additionally, the new system maintains the life history of all the loans, from origination to purchase and closing, which allows the client to study overall loan behavior and optimize their decision-making. Tricon Infotech continues to manage the project with a dedicated team that has grown from 10 to 75 engineers. 

Share Post:
Case study

Accelerated loan processing

A loan servicing company was losing business due to a slow Excel + Dropbox workflow. Tricon automated the process and added real-time analytics to keep them ahead of the competition.
Case study

Automated laboratories

The King Abdullah University of Science and Technology (KAUST) automated its laboratory management system with help from Tricon Infotech.
Case study

Publisher sampling

A major educational publisher wanted to implement Salesforce into its Oracle database but lacked a sampling solution. With help from Tricon Infotech, they accomplished everything.
Case study

Gutenberg meets iPad

One of the world’s most prestigious scholarly publishers automated its entire workflow with help from Tricon Infotech. The journey begins on their sales representatives’ iPads.