Development of Online Egg Grading Information Management System with Data Warehouse Technique

Authors: Yoon, Seung-Chul; Shin, Tae-Sung; Lawrence, Kurt; JONES, DEANA - US Department Of Agriculture (USDA)

Submitted to: Applied Engineering in Agriculture
Publication Type: Peer Reviewed Journal
Publication Acceptance Date: 5/8/2020
Publication Date: 8/18/2020
Citation: Yoon, S.C., Shin, T., Lawrence, K.C., Jones, D.R. 2020. Development of Online Egg Grading Information Management System with Data Warehouse Technique. Applied Engineering in Agriculture. 36(4), pp.589-604..

Interpretive Summary: The shell egg quality grading service administrated under federal supervision of USDA’s Agricultural Marketing Service (AMS) ensures the sanitation and quality of shell eggs during egg productions. Currently, the essential data and information about the sanitation and quality of eggs graded by USDA egg graders are recorded on paper. In the information age with ubiquitous internet, the paper-based egg grading recording system has fundamental limitations in efficient and timely collection and utilization of the data. In this study, a web-based egg grading record system (called USDA Egg Grading Information Management System, EGIMS) was developed to demonstrate the feasibility of an enterprise-level database system for data gathering, reporting and analysis. The study demonstrated its potential as an electronic egg grading record system with data analytics and data mining capabilities. Once fully implemented and tested in the field, the EGIMS is expected to provide a solution to modernize the egg grading practice of the AMS and produce the useful information for timely decisions and new knowledge discovery.

Technical Abstract: This paper is concerned with the development of a web-based online data entry and repository system for centralized storage and analytics of egg grading records produced by USDA egg graders. The egg grading records administrated under federal supervision of USDA’s Agricultural Marketing Service (AMS) are currently entered and compiled in paper form. Although the data and information contained in the egg grading records, such as graders, time, location, cleanliness, and specific quality attributes of graded eggs, can be used for data-driven knowledge discovery and decision making, the paper-based egg grading record system has fundamental limitations in effective and timely management of the data and information. While an electronic egg grading-record system can allow egg graders to enter the grading records from electronic devices at processing plants, there is also a demand to store the egg grading records in a database for data analytics and mining because online reporting and analysis of egg qualities can provide valuable information on egg grading trends and patterns on a large scale (e.g. nation or a state) to USDA administrators. In this study, the concept of electronic data entry by egg graders, asynchronous data submission into one data repository, online data monitoring, reporting and analysis was implemented in a web-based online data entry and storage system (called USDA Egg Grading Information Management System, EGIMS), based on a data warehouse framework. The designed and implemented data warehouse framework consisted of a data entry module, a data staging module for data extraction, transformation and loading to a data repository (warehouse) for data aggregation and query processing, and then a web-based dashboard for query and reporting. The key design criteria of the data warehouse system were to build a unified system platform that provides web-based applications for standardized data entry and rapid data update for reporting and analysis. The developed system was evaluated by a simulation study with statistically-modeled hypothetical egg grading records with a 12 year history in terms of response time. The study found that the EGIMS powered by a server-grade computer could handle up to 800 and 600 individual and simultaneous (zero delay) data entries and report queries, respectively. The study demonstrated the feasibility of the enterprise-level data warehouse system for the AMS and potential to provide the data analytics and data mining capabilities such that the basic queries about historical and current trends can be reported and more complex questions such as patterns and relationship in big data can also be handled. Once fully implemented and tested in the field, the EGIMS is expected to provide a solution to modernize the egg grading practice of the AMS and produce the useful information for timely decisions and new knowledge discovery.