Unlocking the value in utilities

Independent testing and validation of a utility management system

Extensive product testing impacted product engineering activities

The client is a global energy and sustainability management company. By leveraging an extensive data warehouse that spans thousands of utilities, hundreds of thousands of business sites and millions of households, the client provides technology optimized solutions for saving resources.

Given the complexity and high volume (1 terabyte) of data, the client's proprietary energy management software required in-depth verification at various levels. The client also has solutions for advanced segmentation and rapid energy modeling which required rigorous testing for producing accurate analysis.

Such extensive testing requirements necessitated a need to independently test the product and combat key challenges such as:

  • Complexity of transformations applied on a huge volume of data (1 terabyte).
  • Lack of referential integrity at the source database and unavailability of mapping information for source to target databases at certain instances.
  • Validation of business intelligence (BI) reports using complex SQL queries and testing them on multiple browsers.
  • Achieving an optimal response time for ETL procedures and BI reports.
  • Handling resource flexibility and scalability with specialized knowledge in tools such as SSIS and Informatics.read more...

Dedicated test center for independent testing

Testing strategy: Nagarro was selected to independently test and validate the new system and ensure zero loss to business. After understanding the client's requirements and the complexity of data transformation, Nagarro set up a team of testers to articulate a strategy for seamless ETL transformation and BI report testing with minimum defects and maximum coverage of data scenarios.

Test data preparation: Based on the transformation logic, a test harness was created for verification. Test data was collected from the harness, flat files, and other disparate sources (databases with different schemas) and was loaded on the staging location. Further, the data was analyzed for any constraints such as lack of referential integrity within databases. All observations were shared with the client and resolved prior to the data transformation activity.

Data loading and transformation: Informatica was used to transform the test data according to the new data mapping defined in the tool and was loaded into the new destination database.

Independent testing and validation: Comprehensive data validation was performed between the source files and destination databases. This stage was crucial especially, since the volume of data was in terabytes and complex transformations were applied.

Extensive testing of dynamic BI reports: Once the data was loaded to the destination warehouse and validated, it displayed business intelligence (BI) reports. These reports were used for analytics and required extensive testing as there were large number of grouping and aggregations, based on certain filters. An independent team of manual testers was deployed to test the reports' layouts, data, and calculations.

Report testing with automated framework: An automation framework was developed with multi-browser feature to test these BI reports in all possible combinations of browsers. This framework was also used to automate most of the regression scenarios to reduce delivery time and improve quality.

Performance testing of the complete system: The data was loaded in the destination data warehouse within prescribed and expected time frames to ensure enhanced performance and scalability. The performance of each BI report was also analyzed and monitored for enhanced user experience.

High quality, near zero defect application

Data integrity: Independent, comprehensive testing with 100% functional coverage.

Shorter regression cycle: Automated ETL testing helped reduce the regression cycle by 40%-50%.

Lower defect leakage: Achieved defect leakage of 3.31% which is considerably lower than the industry average of 5%.

Cost savings: Centralized team ensured optimal resource utilization that lead to measurable cost savings.read less...

“Team Nagarro received appreciation from the client for their knowledge and experience in ETL testing. Our strong ETL testing capabilities helped combat the critical data transformation challenges, which were adversely affecting the data integrity related to utilities consumption across millions of households. After performing an independent testing and validation, a near zero defect and superior quality energy management software was successfully delivered to the client.”