Managing data for Business Intelligence: A case study

4 min read

Listen to the latest articles and insights from our experts.

Listen to the latest articles and insights from our experts.

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? 

A diagram showing data from 80 loan originators and 5 to 7 loan servicers flowing from spreadsheets to a data warehouse and into Tableau reporting.

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 Business Intelligence 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: