1. Project Title

Pharma Manufacturing Process Analytics using SQL Server

2. TAGLINE

Optimizing Production Efficiency and Reducing Waste with Data-Driven SQL Analytics

Domain: Pharmaceutical Manufacturing (Tablets Production)
Project status: Completed

3. Problem Statement

Pharmaceutical companies face challenges in monitoring efficiency, tracking quality deviations, and maintaining environmental conditions during critical production stages like Granulation, Compression, and Coating. Even minor material losses, unexpected temperature or humidity spikes, and issues with machines or operators can significantly increase costs and create compliance risks.

4. Solution Approach

• Centralized Data: Consolidated production data into a single SQL Server database.
• Data Cleaning: Handled missing information, such as estimating unrecorded humidity levels using stage averages.
• Advanced Analysis: Used T-SQL to calculate material loss, assess yield percentages, and evaluate the performance of both operators and machines.
• Business Views: Created specific Key Performance Indicator (KPI) views and a ready-to-use data format for future Machine Learning applications.
• Reporting: Summarized all findings into clear, actionable business recommendations.

Project Workflow: End-to-end analytics process from raw data ingestion to dashboard reporting.

5. Tech Stack

• Database Management: SQL Server (SSMS)
• Data Analysis: Advanced T-SQL (CTEs, Joins, Window Functions, Views)
• Data Visualization: Power BI

6. Key Features

• Comprehensive data quality checks and handling of missing values.
• Accurate calculations for yield loss and stage-by-stage material waste.
• Analysis of how environmental factors (like humidity) impact production.
• Rankings and performance evaluations for individual operators and machines.
• Automated detection of anomalies and ready-to-use reporting dashboards for business leadership.

7. Impact & Results

• Cost Savings: Identified $106,000 per year in potential savings by addressing material loss and improving humidity controls.
• Machine Efficiency: Pinpointed specific machines (CT-04 and G-03) needing maintenance, projected to save $18,000 annually.
• Workforce Optimization: Recommended customized operator training, which could reduce waste by $30,000 each year.
• Process Improvement: Established a target to improve overall production yield by 3% across all tablet products.

Power BI Dashboard: KPI summary showing Average Yield %, Average Weight Loss, and stage-wise performance breakdown.

---

Created by Parth B Mistry