Look inside a Hershey database effort

P. Michael Rosenshine, staff engineer, Information Services area of Hershey Chocolate North America (Hershey, Pa., www.hersheys.com), is working on a pilot project on three lines to test the feasibility of a plant-wide database for process parameters and results (excluding labor, which is handled in another system). The project uses Oracle 8i, but may be ported to Microsoft SQL Server 2000 for some plants.

08/14/2002


This article is provided as enhanced online coverage to the June 2002 issue of Control Engineering. How to Get the Most from a Database '' (Control Engineering, June 2002).

In considering a central data model, sometimes it's easier to gain knowledge and experience working on a smaller piece.

P. Michael Rosenshine, staff engineer, Information Services area of Hershey Chocolate North America (Hershey, Pa., www.hersheys.com ), is working on a pilot project on three lines to test the feasibility of a plant-wide database for process parameters and results (excluding labor, which is handled in another system). The project uses Oracle 8i, but may be ported to Microsoft SQL Server 2000 for some plants. Computations for reporting process results are performed on the database server side for future migration to browser-based information access, rather than incorporated with SCADA system functions or client-side software.

Mr. Rosenshine says having a single general database design fit any line would require too many fields and tables, many of which would be for metadata (information about the structure of tables and their mappings to the configuration of production lines). In the database, he's found that 60-75%-perhaps 100 tables and views and 100 procedures-can be central, then others added to meet the specific process architecture. Core information includes ingredients and materials, equipment, sequencing, recipes, quality, and product tracking, among other parameters.

Part of Mr. Rosenshine's effort is abstracting common process elements such as quality assurance testing or waste/rework, and then creating the metadata elements required to present information to the user with the minimum amount of programming on the SCADA or client-side systems. That way, when the database needs to be modified for a new line or process, adaptation takes three or four hours, instead of days creating something new in SCADA software. The additional benefit is that it has the same "look and feel," even though a plant's lines and processes are different.

Users log-in via a Microsoft VisualBasic interface. However, in the future, the effort will incorporate Microsoft .Net tools to move to a web client, Mr. Rosenshine says, to protect the value created going forward.

Mr. Rosenshine talked with Control Engineering Feb. 12, 2002, at the ARC Advisory Group Manufacturing Strategies Forum meeting, in Orlando, Fla.

For a bit of fun, the www.hersheys.com website includes a plant tour of "world's largest chocolate factory."

- Mark T. Hoske, editor in chief
MHoske@cfemedia.com





No comments
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by...
The System Integrator Giants program lists the top 100 system integrators among companies listed in CFE Media's Global System Integrator Database.
The Engineering Leaders Under 40 program identifies and gives recognition to young engineers who...
This eGuide illustrates solutions, applications and benefits of machine vision systems.
Learn how to increase device reliability in harsh environments and decrease unplanned system downtime.
This eGuide contains a series of articles and videos that considers theoretical and practical; immediate needs and a look into the future.
Sensor-to-cloud interoperability; PID and digital control efficiency; Alarm management system design; Automotive industry advances
Make Big Data and Industrial Internet of Things work for you, 2017 Engineers' Choice Finalists, Avoid control design pitfalls, Managing IIoT processes
Engineering Leaders Under 40; System integration improving packaging operation; Process sensing; PID velocity; Cybersecurity and functional safety
This article collection contains several articles on the Industrial Internet of Things (IIoT) and how it is transforming manufacturing.

Find and connect with the most suitable service provider for your unique application. Start searching the Global System Integrator Database Now!

SCADA at the junction, Managing risk through maintenance, Moving at the speed of data
Flexible offshore fire protection; Big Data's impact on operations; Bridging the skills gap; Identifying security risks
The digital oilfield: Utilizing Big Data can yield big savings; Virtualization a real solution; Tracking SIS performance
click me