- October 9, 2022
Making every transaction count!!
Consume Finance | U.S.A.
About the Client:
One of the largest privately held retail consumer finance companies in the United States. With a customer-centric focus, they offer an innovative mix of FinTech and brick-and-mortar financial products and services through an online customer portal as well as through 230+ retail locations in 3 states under the same brand.
Some key facts about the company:
• Healthy Balance sheet
• Positioned to grow
• Locations are 100% company owned – no franchises
• Named multiple times to the Inc. 5000 list of America’s fastest-growing private companies
• Named multiple times to the Dallas 100 list of the fastest-growing private companies in North Texas.
Challenge:
Erroneous outputs while extracting data from generation and consumption process largely due to network issues and resource contention.
What We Did :
Step 1:Â Bug Identification in the existing process including
- Few transactions were missed between the Point of Sale database and data warehouse
- Queries using linked servers in SQL Server Stored procedures were failing due to network issues.
Step 2:Â Critical discovery step found that the process was scheduled to run at 2:30 AM but the transactions were being posted at 4 AM.
Step 3:Â Optimising and customising of the SSIS packages and SQL queries in the SQL Server Job Agent to accomplish the extraction of generation and consumption processes on the schedule.
Step 4:Â Truncation and loading of staging tables instead of using linked servers in SQL stored procedures leading to the benefit of no resource contention at the Point-of-Sale databases.
Step 5:Â Recommendation and Implementation of beneficial Azure Data Factory pipelines to generate and consume the extracts hence the extracts in the storage accounts could be used in an external table on Azure Synapse.
Step 6:Â The job was then scheduled to trigger at 5:50 AM which meant that the transactions would not be missed between the 2 databases.
Success achieved:
Motivity Labs was able to accomplish 100 % successful execution of these jobs without any failures and the client was pleased with the end results. The missing transactions count was zero. The Data Warehouse was shifted from an On-Premise SQL Server instance to Azure Synapse.