Forensic SQL Process Reengineering
99% reduction in execution times (from hours to <1 minute) for the materials explosion process (MRP), unblocking the complete migration to Azure and revealing hidden overproduction in the business.
The Problem
The materials explosion calculation process (MRP) was a strategic bottleneck blocking the Azure migration, forcing an on-premise server to be maintained. It took hours to execute, generated inaccurate results with high variation, and business data changes required re-executions that took days, causing undetected overproduction.
The Solution
I applied exhaustive forensic analysis of SQL code (Performance Tuning) to identify bottlenecks, investigated hidden business processes causing data variations, and executed radical SQL process reengineering. Created a simple web interface to empower the end user, encapsulating technical complexity.
Key Results
Time reduction (hours → <1 min)
Azure migration completed
Tech Stack
Case Study
The Materials Calculation 'Monster'
The Challenge: Strategic and Operational Bottleneck
Cloud Migration Blockage
After migrating to Azure, this process (which took hours in the on-premise environment) became an obstacle, forcing a local server to be maintained for its execution, preventing cloud infrastructure consolidation and cost optimization.
Critical Business Impact
The process generated results with high variation and inaccuracies. Business data changes required a full re-execution that took days, affecting decision-making and leading to manual corrections months later, causing potential undetected overproduction.
The Forensic Diagnosis and Architectural Intervention
Root Cause Investigation (SQL)
I performed exhaustive analysis of the underlying SQL code (Performance Tuning) to identify bottlenecks that were consuming resources inefficiently.
Business Process Mapping
Beyond queries, I investigated hidden business processes influencing the data, discovering the variations causing calculation inaccuracy and consequent overproduction.
Radical Reengineering
I optimized the SQL process, achieving a 99% time reduction (from hours to less than 1 minute). This eliminated the need to run the process on-premise and enabled its functional migration to cloud infrastructure.
The Strategic Impact and User Empowerment
Financial/Strategic Impact
The forensic findings were presented to the business, revealing underlying overproduction and data variations. This enabled management to make informed decisions to correct processes and reduce inventory waste.
Operational Empowerment
I created a simple web interface that encapsulated the SQL logic complexity and database connectivity. This allowed the process owner (without being a SQL or SAP expert) to execute the calculation multiple times daily for testing or immediate re-executions, ensuring real-time accurate data.
Closing the Cloud Gap
By optimizing the process, the on-premise server dependency was eliminated, fully consolidating the infrastructure in Azure and achieving the original migration objective.