End-Line Daily Fault Summary

1. Dataset Overview

Dataset Name: EndLineDailyFaultSummary
Description: This table stores the daily summarized faults identified at the end-line inspection process. It consolidates fault details (style, size, line, section, worker, operation, machine) into a single record with a fault count. FaultCount holds the total occurrences for each unique combination.
Owner/Maintainer: Manufacturing Reports Team / Production Analytics

2. Schema Definition

Database: SooperWizer
Server: T3600-SERVERPC\DEV2019
Schema: Reports

Table Structure

Column Name	Data Type	Constraints	Description
FaultSummaryID	INT	PRIMARY KEY, IDENTITY(1,1)	Unique summary record identifier
FaultID	INT	NOT NULL	Fault/defect identifier
FaultCode	VARCHAR(64)	NOT NULL	Fault code
FaultName	VARCHAR(256)	NOT NULL	Fault description
FaultCount	INT	DEFAULT(0)	Number of faults found
ColorID	INT	NOT NULL	Color identifier
ColorCode	VARCHAR(50)	NOT NULL	Color code for the product
ColorName	VARCHAR(100)	NULL	Color description
SizeID	INT	NOT NULL	Product size identifier
SizeCode	VARCHAR(50)	NOT NULL	Product size code
SizeName	VARCHAR(100)	NULL	Product size description
StyleID	INT	NOT NULL	Style identifier
StyleName	VARCHAR(100)	NULL	Style name
WorkOrderCode	VARCHAR(64)	NOT NULL	Order identifier
PurchaseOrderCode	VARCHAR(50)	NOT NULL	Purchase order number
OperationID	INT	NULL	Operation identifier
OperationCode	VARCHAR(64)	NULL	Operation code
OperationName	VARCHAR(128)	NULL	Operation description
MachineID	INT	NULL	Machine identifier
MachineCode	VARCHAR(50)	NULL	Machine code
MachineName	VARCHAR(128)	NULL	Machine description
WorkerID	INT	NULL	Worker identifier
WorkerCode	VARCHAR(50)	NULL	Worker code
WorkerName	VARCHAR(128)	NULL	Worker description
LineID	INT	NOT NULL	Production line identifier
LineCode	VARCHAR(50)	NULL	Line code
LineName	VARCHAR(50)	NULL	Line description
SectionID	INT	NOT NULL	Section identifier
SectionCode	VARCHAR(50)	NULL	Section code
SectionName	VARCHAR(50)	NULL	Section description
FactoryID	INT	NOT NULL	Company/factory identifier
FactoryCode	VARCHAR(50)	NULL	Company/factory code
FactoryName	VARCHAR(100)	NULL	Company/factory name
BranchID	INT	NOT NULL	Branch identifier
BranchCode	VARCHAR(50)	NULL	Branch code
BranchName	VARCHAR(100)	NULL	Branch name
InspectionDate	DATE	NULL	Date of inspection

Relationships

Foreign Keys:
- No Foreign Key
Referenced By:
- Quality reporting views
- Manufacturing dashboard queries
- Performance KPI calculations

Indexes

Primary Key: FaultSummaryID
Unique Constraint: UQ_EndLineDailyFaultSummary (InspectionDate, WorkOrderCode, PurchaseOrderCode, ColorID, SizeID, FaultID, FactoryID, BranchID, LineID, SectionID, WorkerID, OperationID, MachineID)
Performance Indexes:
- PK_EndLineDailyFaultSummary (Clustered)
- UQ_EndLineDailyFaultSummary (Unique, Non-Clustered)

3. Data Identification & Grouping

Unique Identifiers: FaultSummaryID (Primary Key)
Grouping Logic: Aggregated by order, color, size, line, section, branch, factory, fault, worker, operation, machine
Partitioning Strategy: Monthly partitioning by InspectionDate for performance

4. Data Lifecycle Management

Data Deletion Strategy

Retention Policy: 2 years for active reporting, 5 years for compliance
Deletion Triggers: Automated monthly archival process
Archival Process: Move to EndLineDailySummary_Archive after 2 years
Compliance Requirements: Manufacturing quality audit requirements (5 years retention)

Data Insertion Policy

Column-wise Insertion Rules:
- FaultSummaryID: Auto-generated (IDENTITY)
- All grouping keys required: WorkOrderCode, PurchaseOrderCode, InspectionDate, fault fields, color fields, size fields, line fields, section fields, factory fields, branch fields
- FaultCount: Defaults to 0, incremented via aggregation
- Worker/Operation/Machine fields: Optional, populated when available
- Descriptive fields: Populated from master data joins
Insertion Procedures: usp_InsertEndLineDailyFaultSummary
Modification Scripts: Only FaultCount updates allowed via processing procedures

5. Data Modification Jobs

Data Read Operations (Selection)

Job: EndLine Quality Reports
- Purpose: Generate daily/hourly quality reports
- Frequency: On-demand and scheduled
- Output: Quality dashboards, management reports

DML Operations (Insert/Update/Delete)

Job: Update EndLineDailyFaultSummary - Every 7 Minutes
- Purpose: Aggregate fault log data into daily fault summaries
- Data Sources: Quality.EndLineFaultLog
- Transformation: Fault aggregation with worker/operation/machine attribution
- Error Handling: Failed aggregations logged with data validation alerts

6. Business Rules & Calculations

Fault Categorization

Critical Faults: Customer-visible defects requiring immediate action
Major Faults: Quality issues affecting functionality
Minor Faults: Cosmetic issues with minimal impact
Rework Faults: Issues that can be corrected through rework

Aggregation Logic

FaultCount: SUM(FaultCount) from Quality.EndLineFaultLog grouped by all dimensions
Unique Constraint Handling: If same fault combination exists → increment FaultCount, else insert new record
Worker Attribution: Links faults to specific workers for performance tracking
Machine Attribution: Links faults to specific machines for performance tracking
Operation Tracking: Associates faults with specific operations for process improvement

7. Data Sources & Dependencies

Primary Source: Quality.EndLineFaultLog
Master Data Sources:
- Essentials.Fault (Fault details)
- Essentials.Line (Line details)
- Essentials.Section (Section details)
- Essentials.Color (Color details)
- Essentials.Size (Size details)
- Essentials.Branch (Branch details)
- Essentials.Factory (Factory details)
- Essentials.Operation (Operation details)
- Essentials.Machine (Machine details)
- Essentials.Worker (Worker details)
- Orders.PurchaseOrder (Purchase Order details)
- Orders.WorkOrder (Work Order details)

8. Reporting Usage

Views Using This Dataset:
- None
Reports:
- To be added later