http://www.microsoft.com/TechNet/analpln/cs/nec.htm (PC Press Internet CD, 03/1996)
| Updated: March 14,1996 |  Go To TechNet Home Page |
NEC America, based in Melville, New York, manufactures a variety of communication products and automotive electronics. The company maintains a manufacturing facility in Hillsboro, Oregon, that employs 400 people.
In the automotive electronics industry, it's important to be able to track circuit-board information down to the component level Ñ that is, to know precisely which components are installed on each board. That way, if a particular lot of components is later determined to be defective, the automobile manufacturer has only to recall the vehicles that have components from that specific lot.
To gather this component data, the Hillsboro plant used a data collection and retrieval system written in Advanced Revelation that made use of bar-code technology. In addition to collecting component data, the system also identified quality problems and collected labor data off the floor (for example, the number of hours charged to different work orders).
While the system was automated to a degree, it required a significant amount of manual intervention. For example, when automated test equipment on the production line tested a board, an operator had to read the result on the monitor (Pass or Fail) and then refer to a menu sheet to scan the appropriate bar code into the system. Also, operators had to enter a part's lot-tracking number into the system at multiple points in the production process. Since these identification numbers might consist of as many as 15 digits, there was ample room for human error.
In general, the old system worked fine at the plant's initial production volume. But new product lines that were to be added in the near future were expected to more than double the production volume by the end of 1994. The system was simply too slow to be able to handle the higher volume.
A second problem was that the database was based on proprietary technology, so there was no way to get at the data except through the system's own report writer - which, like the system itself, was slow. In addition, the system had a fairly complicated user interface, so only the plant's IS people were able to develop custom queries and reports.
The plant's IS department decided that, to achieve the new production volumes, they needed a faster, more automated data-collection and retrieval system. In addition, to meet the needs of plant personnel, they wanted the system to be easier to use and based on open technology, so end users could access the data directly from spreadsheets and other PC applications.
The group began by looking at existing data collection and retrieval software. However, according to Stephen Deremer, the senior systems analyst at NEC America who headed the project team, We found that any existing software that did what we wanted was exorbitantly expensive. The NetWare.-based systems we looked at cost $50,000- 75,000; the UNIX.-based systems, more than $100,000. Accordingly, the group decided to build their own solution.
Initially, the project team looked at two possibilities: writing a UNIX-based system in ORACLE. that would run on an HP. minicomputer, and writing a system in Microsoft SQL Server. that would run under Microsoft Windows NT Server on a Compaq. Proliant. 2000 a 66-MHz system with dual Pentium processors.
After running performance tests on both types of systems, they chose the SQL Server-Windows NT-Proliant combination. "We were looking for the most bang for the buck,"; explained Deremer. "The HP/ORACLE solution was a little faster, but the operating system was about three times as expensive; the database, about twenty times as expensive; and the hardware, about twice as expensive. There was no way we could justify that kind of cost to management."
In addition, the project team found the Windows NT/SQL Server solution much easier to install and administer. "From an administrative point of view, there was no question that the Windows NT/ SQL Server solution was head and shoulders above the UNIX/ORACLE solution," said Deremer. "As an example, SQL Server took us only about half an hour to install without outside help. In contrast, ORACLE took us about two weeks to install, even though we had help from a consultant."
The new solution, called QCD (for Quality, Cost, and Delivery), has three parts: a front end, a back end, and a communication server, which transmits information between barcode scanners, PCs, and readers. (Readers are computerized devices that run proprietary software and are connected to scanners, either the fixed-head type or manually operated units.)
Front End. The front end of QCD consists of data-entry screens for generating barcode labels (for example, for the board serial number and label information); a data-entry screen for tracking quality information (such as defects and repairs); and screens for making packaging and shipping arrangements. The latter two screens provide, for each board, such information as the shipper and the box and pallet in which the board is packed. With all this information, NEC America can track what happens to a component from the time it enters the building to the time it leaves.
NEC America initially wrote the front end in Visual Basic. Later, they added Access for its reporting capabilities, saving them the time of developing a report writer from scratch. The developers were able to come up to speed on Visual Basic fairly quickly, despite the fact that they had not previously worked in an event-driven programming environment. The developers found Access so easy to use that end users can now develop their own custom reports. The elimination of the need to generate custom reports, together with the drop in the number of questions the group has to field, has freed up a significant amount of time, according to Deremer - time that developers can now spend in creating new applications.
Back End. The back end of QCD is written in SQL Server, which the developers chose both because of its cost-effectiveness and because of its ability to maximize performance through the use of stored procedures. For example, in the verification process, instead of having to issue a large number of SQL commands, the software just passes the relevant parameters (such as the board and component numbers) to stored procedures, which look up the information and return a Pass or Fail result.
Communication Server. The communication server, a multi-threaded program that also runs under Windows NT, transmits information from scanners or PCs to the back-end database and returns responses back to the PC or reader. This is a key part of the solution, because it allows the system to automatically verify, as each production step is performed, that all the necessary actions have been taken. For example, at certain points in the assembly process, the board may pass under a scanner to verify that all its components have been entered into the database - or that the board has gone through all the necessary assembly steps. In such cases, the board waits at the scanning station while the communication server transmits the relevant information to the database and then returns the response to the reader. Because the boardÕs progress along the production line is halted until a response is received, response time is critical for this portion of the solution. Accordingly, the developers wrote the communication server software in Visual C++ for Windows NT to maximize its performance.
Now, instead of the 30 seconds previously required for verification, the process takes only two seconds. As a result of this performance improvement, automated verification can now be performed at multiple points along the production line, instead of only at the end. This means that any problems that exist are caught much earlier, minimizing the need for rework.
Hardware Configuration. The client portion of QCD runs on 486-based PCs, equipped with barcode scanners. The server portion runs on Compaq Proliant 2000 systems, equipped with 128 MB RAM and 6 GB of disk space (divided among four drives in a RAID 5 configuration).
The communication server runs on a 66-MHz, 486-based system with 16 MB of RAM. An RS-232 port connects the communication server to a concentrator, which feeds it the combined input from multiple readers. Because the information from each reader contains a specific letter designator and address for that reader, the system always knows where the information came from and is able to send a reply back to the correct reader.
NEC America started developing QCD in late November 1993. At that time, they had Windows NT Advanced Server and a ten-user version of SQL Server, but had not yet received the Proliant systems. Consequently, they did their initial development on a 50-MHz 486-based machine.
At first, only two programmers worked on the software - one working on the front and back ends and the other working on the communication server. In January 1994, the team added a third programmer; and in February, a fourth. However, due to other obligations, all four programmers worked only part-time on the project until the last couple of weeks - at which point, due to the impending deadline, the project became a full-time effort for everyone.
After one week of alpha testing in March 1994, the solution went into production. "We had new products starting up in March, and they wouldn't work with the old system," explained Deremer. "It was a do or die situation; we basically went from alpha to production overnight."
As a consequence, the first couple of weeks were, in effect, a live beta. "In a case like this, you can only simulate so much without going into a production environment, anyway," observed Deremer. "We had the expected problems during the first week or two, but after that, it was pretty smooth sailing."
QCD has resulted in significant benefits for the production line. With the greater automation and improved performance of the new software, production line capacity increased by 67 percent.
One reason for the increased capacity was the extensive automation built into QCD. For example, instead of entering 15-digit lot-tracking numbers for components at several points in the production process, operators now enter a component's number just once. The system generates a bar code for it and is able to track the bar code along with the part from that point on.
Similarly, after each production-line test, the system now takes the Pass/Fail results directly from the automated test equipment instead of requiring the operator to manually enter the results. This approach not only saves time but also eliminates the possibility of operator error.
Another key element of the improved performance was the tremendous increase in verification speed. With the new software, the time required to perform verifications decreased from 30 seconds to just two. The faster speed meant that, instead of delaying verification until the end of the production process and then having to go back several steps to correct problems, the plant can now perform verification at every step. This approach is much more efficient and has resulted in a significant decrease in rework, as well as much less worker frustration.
The improved verification speed also allows the plant to make final shipping arrangements much more quickly. Under the old system, before the plant could ship even a small quantity of product, they had to take two to three hours to verify and process all the necessary data. Now, with verification being performed at each step, it takes only about one minute to process the final data. This improvement in processing speed allows the plant to be much more responsive to its customers' needs.
QCD is also a much more user-friendly system than the old software - one that lets users access data themselves whenever they need it instead of having to wait for the IS department to generate custom reports. In addition, usersÕ ability to generate their own reports means that they have a better understanding of the data and therefore fewer questions for the IS staff. Consequently, the time that the IS department once spent generating custom reports and answering questions is now available for developing new software. In fact, Deremer estimates that since the new system has been up and running, the department has had 20 percent more time available for new development.
Finally, the new solution has brought about an increase in user satisfaction. "Users are much happier with the new system," reports Snodgrass, one of the programmer/analysts who worked on QCD. "The old system was difficult to use, finicky, and cryptic at times. Having a Windows client makes it so much easier to use. And, since many of the users already are familiar with Microsoft Excel and Access, there's very little new learning required."
Future Plans
Later this year, NEC America plans to move a subset of QCD to another building that manufactures PBX, radio, and transmission equipment. That production line doesnÕt need as detailed information as the automotive components line, but they do need to collect labor data, report quality information, and track defects - and they are currently depending on manual reporting for this information. Deremer expects the automated data-collection capabilities of QCD to improve their efficiency significantly.
The IS department is also thinking of replacing mainframe financial, work-tracking, and MRP (manufacturing resource planning) applications with PC-based client-server applications."We're definitely sold on the advantages of client-server applications," said Deremer. "They make it so much easier for people to get at the data they need."
Because of their positive experiences with Windows NT and SQL Server in developing QCD, the IS department expects to use this development environment for new client-server applications, as well. "Windows NT has been very stable," says Snodgrass. "And, as for SQL Server, there's just no comparison between it and Revelation. Both in development and at runtime, it's so much quicker. Plus we aren't stuck with proprietary tools: we can choose any front end we want, and we can access any ODBC-compliant database."
Visual Basic and Access are also key parts of NEC America's future plans. One defect-tracking application currently in the planning stages, for example, will use a Visual Basic front end to take image information from an x-ray machine that reads each board. It will then compare the actual image to a known-good board image and send the results to a SQL Server database. NE America plans to use both Visual Basic and Access extensively in future front-end development
Note: The QCD development team was made up of Eric Lewis, who worked on the database design and the front end; Jose Vithayathil, who worked on various Visual Basic screens; Sean Mathews, who designed and wrote the communication server software as well as many of the stored procedures on SQL Server for Windows NT; and Don Snodgrass, who worked on the data transfer from Advanced Revelation, on the installation of the Compaq systems, Windows NT Server, and SQL Server, and on Visual Basic applications for collecting test data. Steve Deremer was the project manager.
NEC America has written a new data collection and retrieval system that automates much of the process of collecting quality-control data on their production line using Microsoft. Visual Basic. programming system, Microsoft Access., Microsoft Visual C++. for Windows. NT., and Microsoft SQL Server,. As a result, theyÕve increased their production line capacity by 67 percent and significantly reduced rework. The new system is also much easier to use than the previous system, allowing end users to generate their own queries and custom reports and freeing up the IS staff to work on other projects.
Industry
Manufacturing -Automotive Electronics
Business Solution
Automated data collection and retrieval system
Architecture
Back end in Microsoft SQL Server. that would run under Microsoft Windows NT Server on a Compaq. Proliant. 2000; Front end in Microsoft. Visual Basic. and Microsoft Access.; Communication server in Microsoft Visual C++. for Windows. NT.
Products Used
Microsoft. Visual Basic.
Microsoft Access.,
Microsoft Visual C++. for Windows. NT.
Microsoft SQL Server
Development Resources
In-house development team
Development Time
Four programmers part time for four months on front end and communication server
Benefits
Increased production line capacity 67 percent; decreased costly rework time; increased processing speed resulted in better customer response; provided MIS with 20% more development time.
This article is reprinted from the first edition of Microsoft Enterprise Solutions News. Published four times a year, its purpose is to give you more information about Microsoft's role in enterprise application development.
Microsoft's role in enterprise applications development is growing rapidly. Microsoft is on the desktops of every enterprise in big business today. But increasingly, its role in enterprise application development has expanded beyond the desktop. It encompasses everything from development architectures; to data access and storage: to desktop to mainframe connectivity and integration; to 24/7 service and support; to migration strategies and tactics. As Microsoft's role expands in those arenas, the need for better information about its supporting architectures, products, programs and services becomes necessary for MIS people to make better purchase decisions for its enterprise application development.
Microsoft Enterprise Solutions News is published quarterly by Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399. Additional editorial material supplied by Gartner Group, Inc. . 1994. Editorial supplied by Microsoft is independent of Gartner Group analysis and in no way should this information be construed as a Gartner Group endorsement.
1994 Gartner Group and Microsoft Corporation. All rights reserved. Microsoft, FoxPro and Visual Basic are registered trademarks and Visual C++, Windows, and Windows NT are trademarks of Microsoft Corporation. Compaq is a registered trademark and Proliant is a trademark of Compaq Computer Corporation. HP is a registered trademark of Hewlett-Packard Company. NetWare is a registered trademark of Novell, Inc. ORACLE is a registered trademark of Oracle Corporation. Pentium is a trademark of Intel Corporation. UNIX is a registered trademark of UNIX Systems Laboratories.
 |
Click Here to Search TechNet Web Contents | TechNet CD Overview | |
Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information. |
| Go To TechNet Home Page |
©1996 Microsoft Corporation |  Go To Microsoft Home Page |