ENGINEERED EFFICIENT ETL DATA PIPELINE FOR NEAR REAL-TIME SELF-SERVICE REPORTING
CASE STUDY
A DATA ENGINEERING & BUSINESS ANALYTICS CASE STUDY
A leading consumer finance company with over 500 branches in twenty-two states throughout the United States wanted an automated master business operations dashboards down to every single branch to improve agility and efficiency in resource allocation.
The client’s objective was to re-engineer the in-house data architecture for a futuristic solution that provides 360-degree business visibility for quick decision-making.
A DATA ENGINEERING & BUSINESS ANALYTICS CASE STUDY
A leading consumer finance company with over 500 branches in twenty-two states throughout the United States wanted an automated master business operations dashboards down to every single branch to improve agility and efficiency in resource allocation.
The client’s objective was to re-engineer the in-house data architecture for a futuristic solution that provides 360-degree business visibility for quick decision-making.
Our client is a finance company founded in 2002 to provide customers with creative, flexible, and convenient lending options. It provides personal loans, auto repair financing, credit cards, and other financial services to customers nationwide.
The company is known for its fast access to funds, transparent terms, and outstanding customer service.
ABOUT CUSTOMER
ABOUT CUSTOMER
Our client is a finance company founded in 2002 to provide customers with creative, flexible, and convenient lending options. It provides personal loans, auto repair financing, credit cards, and other financial services to customers nationwide.
The company is known for its fast access to funds, transparent terms, and outstanding customer service.
PROBLEM STATEMENT
The client wanted to improve its ability to provide customers with creative, flexible, and convenient lending options. To do that, it needed to innovate in two ways: first, by developing its digital channels, and second, by creating a holistic view of customer data from those digital channels that would enable it to develop better products and services.
But two problems were standing in the way of this goal.
The first was latency issues:
The existing process of generating reports was extremely manual-intensive and took a few hours to generate. As a result, data was updated only once in 30 to 60 days, limiting them to getting the maximum value from the campaign insight.
PROBLEM STATEMENT
The client wanted to improve its ability to provide customers with creative, flexible, and convenient lending options. To do that, it needed to innovate in two ways: first, by developing its digital channels, and second, by creating a holistic view of customer data from those digital channels that would enable it to develop better products and services.
But two problems were standing in the way of this goal.
The first was latency issues:
The existing process of generating reports was extremely manual-intensive and took a few hours to generate. As a result, data was updated only once in 30 to 60 days, limiting them to getting the maximum value from the campaign insight.
The second was performance issues:
When refreshing pages or processing real-time data on existing dashboards and reports, the client found that the system was not fast enough for the high pace requirements for competitive decision-making. This issue was caused by our client’s use of a NoSQL database (MongoDB), which uses JSON-like documents with optional schemas. A solution that is useful for storing data but difficult when it comes to build business dashboards and reporting layers on top of it.
Our client knew they needed a better solution that would allow them to meet their customers’ needs while also maintaining agility in their reporting capabilities. They required a highly effective data infrastructure for a real-time unified business analytics platform to generate business and customer insights.
To overcome these challenges, they turned to Zuci Systems, an intelligent automation solutions provider that helps businesses transform digitally through smart data engineering solutions.
PROBLEM STATEMENT
PROBLEM STATEMENT
The second was performance issues:
When refreshing pages or processing real-time data on existing dashboards and reports, the client found that the system was not fast enough for the high pace requirements for competitive decision-making. This issue was caused by our client’s use of a NoSQL database (MongoDB), which uses JSON-like documents with optional schemas. A solution that is useful for storing data but difficult when it comes to build business dashboards and reporting layers on top of it.
Our client knew they needed a better solution that would allow them to meet their customers’ needs while also maintaining agility in their reporting capabilities. They required a highly effective data infrastructure for a real-time unified business analytics platform to generate business and customer insights.
To overcome these challenges, they turned to Zuci Systems, an intelligent automation solutions provider that helps businesses transform digitally through smart data engineering solutions.
BUSINESS GOALS
Centralized data architecture for faster, near real-time analytics and automated reporting.
Develop a single source of truth for monitoring and diagnosis of all branch functions.
Daily, monthly, quarterly, and yearly analysis of the overall business process in the form of easy to understand visualizations.
Drilldown filters for resource tracking, compliance, and performance analysis based on loan processing events.
Automated data ingestion from multiple sources and faster time to insights.
BUSINESS GOALS
Centralized data architecture for faster, near real-time analytics and automated reporting.
Develop a single source of truth for monitoring and diagnosis of all branch functions.
Daily, monthly, quarterly, and yearly analysis of the overall business process in the form of easy to understand visualizations.
Drilldown filters for resource tracking, compliance, and performance analysis based on loan processing events.
Automated data ingestion from multiple sources and faster time to insights.
When the client approached Zuci Systems, they had latency and performance issues. Real-time data processing was delaying their reports. The NoSQL database they were using—MongoDB, which uses JSON-like documents with optional schemas—made it difficult to build business dashboards and reports.
Zuci Systems understood the customer’s needs and developed a bridge between MongoDB and SQL server using our in-house enterprise data hub solution ZIO. The ETL data pipelines helped to park JSON format data into a row and column format and dump it into the SQL server for high-speed dashboarding and faster query processing time. Leveraging ZIO, we automated data ingestion, integrated multiple data sources with APIs, and scaled the reporting and forecasting layers into Power BI for near real-time insights.
This allowed the client to reduce latency by 90% and quickly access accurate data about their loan performance, increasing client retention rates and improved growth rates by 11%.
SOLUTION
SOLUTION
When the client approached Zuci Systems, they had latency and performance issues. Real-time data processing was delaying their reports. The NoSQL database they were using—MongoDB, which uses JSON-like documents with optional schemas—made it difficult to build business dashboards and reports.
Zuci Systems understood the customer’s needs and developed a bridge between MongoDB and SQL server using our in-house enterprise data hub solution ZIO. The ETL data pipelines helped to park JSON format data into a row and column format and dump it into the SQL server for high-speed dashboarding and faster query processing time. Leveraging ZIO, we automated data ingestion, integrated multiple data sources with APIs, and scaled the reporting and forecasting layers into Power BI for near real-time insights.
This allowed the client to reduce latency by 90% and quickly access accurate data about their loan performance, increasing client retention rates and improved growth rates by 11%.
SOLUTION BLOCK DIAGRAM
SOLUTION BLOCK DIAGRAM
Studied the in-house dashboarding system design and challenges and drafted the overall BRD (Business Requirement Document).
After understanding the data workflow and systems, our team analyzed the existing data infrastructure for providing the optimal solution and prepared a timeline for deploying the solution.
Finalized technical requirements on the development needs and third-party tools for the project.
Post the overall analysis phase, our project team started with the overall design and architecture for the project, which is easy to use, secure and scalable.
HOW ZUCI SYSTEMS HELPED
HOW ZUCI SYSTEMS HELPED
Studied the in-house dashboarding system design and challenges and drafted the overall BRD (Business Requirement Document).
After understanding the data workflow and systems, our team analyzed the existing data infrastructure for providing the optimal solution and prepared a timeline for deploying the solution.
Finalized technical requirements on the development needs and third-party tools for the project.
Post the overall analysis phase, our project team started with the overall design and architecture for the project, which is easy to use, secure and scalable.
HOW ZUCI SYSTEMS HELPED
Given the data was stored in MongoDB, a NoSQL database, we built a bridge (Data pipelines) between MongoDB to SQL server using our in-house enterprise data hub solution, ZIO.
Moved the entire data from an unstructured database to an SQL server.
Leveraging ZIO, we integrated multiple data sources with APIs and automated data ingestion for near real-time insights.
Connected SQL database to Power BI and built easily understandable dashboards with a cumulative effort from the customer and Zuci's business intelligence team.
HOW ZUCI SYSTEMS HELPED
Given the data was stored in MongoDB, a NoSQL database, we built a bridge (Data pipelines) between MongoDB to SQL server using our in-house enterprise data hub solution, ZIO.
Moved the entire data from an unstructured database to an SQL server.
Leveraging ZIO, we integrated multiple data sources with APIs and automated data ingestion for near real-time insights.
Connected SQL database to Power BI and built easily understandable dashboards with a cumulative effort from the customer and Zuci's business intelligence team.
Created a set of 12 executive dashboards for analysis of applications, loans, issuance, and payments on a daily, monthly, quarterly, and yearly basis. Track primary branch functions, resource tracking, and more for different business teams.
Deployed and tested the solution on the client's staging environment and handed over the application guidelines.
Post acceptance of stakeholders, our team moved to production, followed by ongoing support for new requirements with workshops and Q&A sessions.
HOW ZUCI SYSTEMS HELPED
HOW ZUCI SYSTEMS HELPED
Created a set of 12 executive dashboards for analysis of applications, loans, issuance, and payments on a daily, monthly, quarterly, and yearly basis. Track primary branch functions, resource tracking, and more for different business teams.
Deployed and tested the solution on the client's staging environment and handed over the application guidelines.
Post acceptance of stakeholders, our team moved to production, followed by ongoing support for new requirements with workshops and Q&A sessions.
BUSINESS OUTCOME
Near real-time visibility to
Operational Metrics and KPIs
BUSINESS OUTCOME
Near real-time visibility to
Operational Metrics and KPIs
TECH STACK
TECH STACK
