A plumbing, heating and industrial supplies company implements automated QA validation process for their modern data warehousing solution using Azure Synapse Analytics & Power BI

Overview

VNB Consulting assisted major Industrial Supply and Wholesale distribution corporation automate their process for validating and auditing their Statement of Operations (SOP) through the financial year end validations where the SOP is based on data that is sent from the General Ledger and HR systems to the Enterprise Data Warehouse.

Customer Profile

Customer is a leading wholesale distributor and industrial supplier that specializes in plumbing, heating and air-conditioning, swimming pool and waterwork services, and various construction services. It has presence across all over US and each location work in a decentralized environment.

Customer Situation

Customer had recently moved to a cloud DW solution built around Azure Synapse and other Azure services. The financial reports generated from the new system requires a whole lot of manual efforts to compare data from reports generated via legacy systems. This involved a lot of manual intervention to compare data for every month which in turn created a big delay in the whole financial data validation process. Moreover, the solution was not generalized and robust, had too much manual dependency, limited to test data for a single month at a given time and was not scalable to be used across other domains for data validations. Validations were done by exporting data in excels, building logics in Microsoft Access and comparison in another excel workbook.

Our Solution Offering

Our team started with the understanding of the data validation process, the challenges and limitations, and the future use cases which to be catered. Post the discovery sessions, the team proposed a dynamic data validation built in Azure Synapse and digitization of the excel reports using Power BI. All the data exports challenges including 1 month at a time exports, validation logics and rules, and output data ready for comparison was created and driven with the help of metadata tables and SQL stored procedures. The process was generalized to add future cases and was simplified for ease of maintenance and support. The process execution was handled by ADF and email notifications were set up using Azure Logic Apps. Validation reports were created in Power BI to automate and digitize the existing reports and data comparisons were made available to the users for all cases. During the whole project, our team provided all appropriate deliverables including Solution Design, Development logics, unit testing, UAT test case and results, performance and regression testing, user trainings and manuals at appropriate stages of the project.

Customer Benefits 

  • Cost savings by reducing all manual efforts and automation of the whole process.
  • Significant reduction in delays in the data validation process which allowed users to have timely closures of financial transactions.
  • No longer limitation of comparing only 1 month at a given time which allowed users to navigate across validation results between different months, quarters, and years.
  • Simplified, scalable and robust solution which allowed users to incorporate the solution across different domains.

Related Case Studies

Microsoft Fabric Case Study

Housing and Urban Infrastructure Solutions Company Automates their Payroll Allocation and Archival using Microsoft Fabric

VNB Consulting collaborated with a Housing and Urban Infrastructure Solutions company automate their Payroll Allocation and Archival process using Microsoft Fabric.

VIEW DETAILS

Empowering a Utilities Company with Seamless OBIEE to Power BI Migration for Amplified Analytical Advantages

VNB Consulting helped a Utilities company migrate from OBIEE to Power BI, resulting in significant cost savings while also improving their analytics capabilities. 

VIEW DETAILS