Large Enterprise Dashboard : Ensuring Data Quality With Automated Verification

Client Background and Project Goals:

The client offers a platform that helps organizations become more flexible by giving them information about how their teams are performing, how their leaders are doing, and the overall health of the organization. This platform allows industries to check how adaptable they are at different levels—teams, projects, and leadership—and provides useful feedback to improve performance. It also helps teams stay focused on their goals, enabling them to work faster and more efficiently, which leads to better outcomes and continuous improvement.
The client has 500+ customers and subdomains, dealing with a real complex data load every day. Manually checking and maintaining data accuracy was nearly impossible. The reason for contacting Jignect is to ensure the data being fed into the dashboard is accurate in all situations. From a business standpoint, having correct data is crucial for making informed decisions about what next steps need to take in large enterprise management.

Business Challenges:

• Data Efficiency: Checking data directly in the database by hand was not efficient and slowed things down.
• Data Correctness: Making sure that the charts and graphs showed the right data needed a lot of careful checking.
• Dashboard Widget Checking: Each widget, like charts and graphs, had to be checked under every possible filter setting.
• Performance: Normal queries took longer to execute and display results when working with large datasets.
• Regression Testing: Manual regression testing was time-consuming and inefficient.
• Production Stability: Frequent data changes required continuous validation to maintain production stability.

Business Goals:

The main aim was to make the data checking process automatic to:

1. Reduce manual work and errors.
2. Make sure the charts and graphs are accurate.
3. Check the data fast and effectively in all scenarios.
4. Ensure data stability even with continuously incoming data.

Solution Provided By Jignect:

1. Stored Procedures for Consistent Validation: To streamline data validation across teams, projects, and leadership levels, reusable stored procedures were implemented using SQL Server Management Studio [SSMS] to standardize queries, enforce business logic, and optimize performance. Separate QA-specific stored procedures were maintained for validation, while development procedures remained independent for better modularity.This approach minimized direct table access, improved query efficiency using SQL Server’s caching, and reduced reliance on hardcoded SQL in automation scripts.

2. Automation using Stored Procedures: Wrote automated scripts to validate the accuracy and consistency of data presented in widgets against the database. Utilized automation tools, SQL queries, and stored procedures to systematically compare and verify calculated results in the dashboard with raw data from the database, ensuring data integrity and reliability in a large enterprise dashboard.

Planning and Execution:

1. Understanding the widgets requirements: Identifying calculation logic, database tables, and relationships.
2. Manual Testing: After development, test the widget logic manually once to make sure it’s working as expected.
3. Stored Procedures: Convert the queries to stored procedures using SSMS to ensure that the widget is working as per logic on a regular basis.
4. Scripting: Writing automation scripts to execute stored procedures and validate widget data.

Automation Approach: 

To ensure accurate and efficient data validation for the dashboard, stored procedures were integrated with SSMS. Selenium with C# were used to develop automation verification scripts, executing stored procedures and handling business logic.

Stored Procedures Steps:

1. Open SSMS & Connect to Database.
2. Navigate to Stored Procedures through Databases > Your Database > Programmability.
3. Click New Query and write the stored procedure using CREATE PROCEDURE.
4. Run the query (F5 or Execute button) to create the stored procedure in the database.
5. Call the Stored Procedure using EXEC command.

Automation Script Steps:

Set up the MSTest framework in C# for Selenium to create an automation script, connect DB and validate responses.

1. Stored Procedures Fetch Expected Data – The system queries processed and aggregated data for validation.
2. Automation Script Extracts Dashboard Data – Using UI automation, actual values are fetched from the dashboard.
3. Automated Verification Comparison is Performed – Data consistency is checked between expected (DB) and actual (UI).
4. Test Report Highlights Discrepancies – Any mismatch is logged for further analysis.

Pre- and Post-Automation Results:

Impact on Product Output:

• Speed: Automated verification significantly reduced the time required to verify data across various filters and conditions, saving manual effort by 70%.
• Accuracy: Automated scripts and stored procedures minimized manual errors, achieving 99% accuracy in dashboard data validation and enhancing overall data quality.
• Efficiency: Eliminated repetitive manual calculations, allowing the team to focus on other critical tasks.
• Error Detection: Automation quickly pointed out mistakes, helping to find and fix bugs or incorrect logic 50% faster.
• Reusability: Stored procedures provided a consistent framework for future validations, reducing regression testing efforts by 40%.
• Regression Testing: Automated verification lowered regression testing efforts and ensured consistent results.
• Production Stability: Continuous validation helped maintain the stability of the production environment by preventing 80% of data inconsistencies before they impacted users.

Conclusion:

By automating the data validation process and leveraging stored procedures, we successfully ensured that all dashboard widgets displayed accurate data under every condition. This approach saved significant time, reduced manual effort, and enhanced the overall reliability of the dashboard. Additionally, the reusability of stored procedures and automated scripts ensured long-term benefits for future testing. Most importantly, with this solution in place, the client now ensures high data accuracy and quality across all dashboards, which enables better decision-making and supports ongoing organizational improvement.

Witness how our meticulous approach and cutting-edge solutions elevated quality and performance to new heights. Begin your journey into the world of software testing excellence. To know more refer to Tools & Technologies & QA Services.

If you would like to learn more about the awesome services we provide,  be sure to reach out.

Happy Testing 🙂