Surya Enterprises—Streamlining Data Refresh Tracking

Executive Summary

Surya Enterprises, a leading distributor of grocery products, faced challenges in tracking live file refresh statuses across its database systems. The company’s operations involved uploading Excel files to SharePoint and running manual jobs on virtual machines to upload this data to an Azure SQL database. Other files were directly uploaded to Azure at various times throughout the day, including evening and midnight, which added complexity. Due to a lack of funds and technological expertise, the company struggled to manage these late-hour updates efficiently. This lack of a unified system for monitoring file refresh statuses created inefficiencies, as the refreshed data was critical for use by vendors and internal departments. Data Hub India addressed these issues by creating an automated solution involving log tables, scripts, and a Power BI dashboard, ensuring real-time visibility of the refresh process.

Client Background

Surya Enterprises distributes a wide range of grocery products to retailers and wholesalers. The company handles significant amounts of transactional and inventory data, which is crucial for operational decision-making. Its employees upload data through multiple channels, including:

• SharePoint (files processed manually via virtual machines).

• Direct uploads to Azure SQL at varying intervals during the day, including late hours such as evening and midnight.

Given the reliance on accurate and timely data, tracking file refresh statuses was essential for ensuring seamless operations.

Challenges

Key Issues Identified

• Inconsistent Monitoring: Files uploaded to SharePoint or Azure were processed at different times, making it difficult to track which files had been refreshed.

• Manual Processes: Manually running jobs to upload data created delays and increased the risk of errors.

• Late-Hour Refreshes: Files needing updates during evening or midnight hours could not be processed efficiently due to limited technological resources and employee availability.

• Lack of Visibility: No centralized system existed to track whether files were successfully refreshed or if a process had failed.

• Dependency on Refreshed Data: Vendors and internal departments required live updates, but delays in file refreshes caused operational bottlenecks.

Solution Provided by Data Hub India

Automation and Real-Time Monitoring

Data Hub India implemented a comprehensive solution to address these challenges:

1. Log Table Implementation:

   • A log table was created in Azure SQL to store metadata about file refresh activities.

   • Columns included:

     • File Name

     • Refresh Date and Time

     • Refresh Status (Success, Pending, or Failed)

     • Whether the job was run automatically or manually

     • On-Time Indicator (whether the refresh was completed as scheduled).

2. Automation Scripts:

   • Developed scripts to run on virtual machines and Azure Data Factory pipelines to automate the data upload process.

   • Files placed on SharePoint or local storage were automatically detected and uploaded to Azure SQL, including during evening and midnight hours.

3. Status Updates:

   • Scripts updated the log table upon successful refresh or job failure, ensuring that refresh timing and status were consistently tracked.

4. Power BI Dashboard:

   • Designed a real-time dashboard displaying file statuses:

     • Pending: Files scheduled for refresh but not yet updated.

     • Success: Files refreshed successfully with updated data.

     • Failed: Files where the refresh process encountered an error.

   • Included visualizations for:

     • Upcoming refresh schedules.

     • Historical performance metrics.

     • On-time vs delayed refresh statistics.

Example Use Case

A file scheduled for refresh at 1:00 PM but not updated by 12:00 PM would show as "Pending" in the dashboard. Once refreshed, its status would change to "Success." If the job failed, the log table and dashboard would display it as "Failed," allowing the team to take corrective action.

Results and Impact

Operational Efficiency

• Reduced Manual Intervention: Automation significantly decreased the time spent manually running jobs.

• Enhanced Accuracy: Automated scripts minimized errors in the data upload process.

• Evening and Midnight Refresh Capability: The solution enabled seamless updates during late hours without employee intervention.

Real-Time Visibility

• The Power BI dashboard provided a centralized view of file refresh statuses, ensuring timely updates for vendors and departments.

Improved Decision-Making

• Vendors and internal teams could rely on up-to-date data, enabling faster and more informed decisions.

Key Metrics

• 100% Coverage: All files and their refresh statuses were tracked in real-time.

• Reduced Failures: Automated alerts helped minimize job failures by identifying issues promptly.

• Faster Turnaround: Average refresh time improved by 40%, ensuring data availability when needed.

Lessons Learned

1. Automation is Crucial: Replacing manual processes with automated solutions reduces errors and enhances efficiency.

2. Centralized Tracking Adds Value: A unified log table and dashboard ensure all stakeholders have access to accurate, real-time information.

3. Proactive Monitoring Saves Time: Real-time status tracking enables swift issue resolution, minimizing downtime.

4. Late-Hour Operations Require Innovation: Automation can address challenges associated with late-hour refreshes, reducing dependency on human intervention.

Conclusion

The solution implemented by Data Hub India transformed Surya Enterprises’ data management processes, enabling real-time tracking of file refresh statuses and improving operational efficiency. This case demonstrates the importance of automation and centralized monitoring in managing complex data workflows. By addressing late-hour refresh challenges, the success of this project positions Surya Enterprises for scalable and reliable data operations in the future.