This large enterprise client operates multiple plants, each producing various materials with associated wastage or recycled products. Monthly data is collected and analyzed to manage and reduce wastage, increasing profitability. The ESG (Environmental, Social, and Governance) report is crucial for auditing and financial departments to monitor losses and sustainability efforts.
Data from each plant supervisor/assistant is collected monthly, stored in Excel files, and shared via One Drive. The data is organized in a hierarchical folder structure – State > Plant > Department Head > Report Head > Excel File. With over 80 files in different shared folders, generating a consolidated monthly report becomes challenging. Data updates are not fixed, causing delays, and reminders need to be sent on the 5th, 10th , 15th , 20th , 25th day of each month until data is updated. Tracking and reporting on user data updates are essential.
The proposed solution involves using Alteryx for ETL (Extract, Transform, Load) processes, automation, and Power BI for visualization.
- Alteryx fetches data from all One Drive folders using a single input tool.
- Data is merged and segregated based on materials and plant-wise.
- Files are processed based on their updating date.
- Data is split into two categories: updated and non-updated.
- Addressing formatting issues, errors, and wrong entries using Alteryx.
- For updated data, Alteryx generates the ESG report.
- Reminder emails are sent for non-updated data after every 5 day of each month.
Monitoring and Analysis:
- Alteryx outputs a Power BI data extract for KPI monitoring.
- Power BI dashboard tracks updated and pending data, material quantities, top/bottom 5 plants, and other relevant metrics.
- Inconsistent report formats and incorrect data entries.
- Alteryx-level challenges in handling diverse data formats.
- Ongoing user education to minimize errors in master data entries.
The proposed solution is in the Proof of Concept (POC) phase, where all requirements have been fulfilled using Alteryx. Challenges are being addressed, and ongoing user education is reducing unnecessary data entry errors.
Continue refining the solution based on user feedback, fully integrate Alteryx processes, and deploy the automated ESG reporting system for wider use across all plants. Regular updates and optimizations will be implemented to enhance efficiency and accuracy.