- Email: [email protected]
Design of a tooling database implementation for an existing facility
Computers in Industry 42 Ž2000. 221–229 www.elsevier.nlrlocatercompind
Design of a tooling database implementation for an existing facility Bharat Anumolu ) , John P. Shewchuk Industrial and Systems Engineering Department, Virginia Polytechnic Institute and State UniÕersity, Blacksburg, VA 24061, USA
Abstract One of the most critical resources for many manufacturing facilities is tooling. To properly control tooling and make tool-related data available to different manufacturing activities, tooling databases are often employed. Implementing a tooling database around computer hardwarersoftware in an existing facility, however, can be difficult. This paper describes how one U.S. aircraft component manufacturer tackled problems encountered in trying to implement a single tooling database in their facility. Three problems were found: some tooling data were already located in another database; one software application requiring tooling data already had its own internal database; and various computer hardware platforms were used in different departments, making a single interface difficult. The problems were solved by employing two databases, via various software agents, and creating a platform-independent interface to the primary database. The resulting implementation proved a viable approach for the facility, and provides a good example of how such database implementation problems can be handled. q 2000 Elsevier Science B.V. All rights reserved. Keywords: Tooling; Tool management; Database
1. Introduction Throughout the industrialized world, many manufacturing facilities are found which are primarily concerned with machining and related operations. One of the most critical resources for such facilities is tooling. Hundreds and even thousands of different tool types may be required in many facilities, and no matter how well jobs are scheduled and machines run, processing operations can only proceed if the right tools are available at the right machines at the
)
Corresponding author.
right times. The importance of tooling is best illustrated by the following statistics w1x: - 16% of scheduled production time is missed because tooling is not available; - 40–60% of a foreman’s time is spent expediting tools and related materials; - 7–10 times more money is spent on tools, jigs, etc. than on capital equipment; and - up to 30% of tool inventories consists of ‘‘lost’’ tooling – tools which have been distributed but are not accounted for. Additional tool-related problems include loss of control of tooling, stockpiling of tools at worksta-
0166-3615r00r$ - see front matter q 2000 Elsevier Science B.V. All rights reserved. PII: S 0 1 6 6 - 3 6 1 5 Ž 9 9 . 0 0 0 7 2 - X
222
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
tions, increased downtime, employee theft of tools, and an unexplained weakening in the firm’s competitive position w2x. In order to properly organize and control tooling, tool management systems are employed. These typically consist of a database for storing tool-related data, a system for identifying and tracking tools Že.g., barcode., and software for executing various tool management functions w3,4x. The heart of any tool management system, however, is the tooling database. Tooling databases contain data describing tool types Židentification code, description, length, diameter, what materials can be used for, what holders can be used with, tool life parameters, vendors, cost, etc.. and individual tools Žwhat type each tool is, how much life remaining, where tools are located, etc... They may also contain data on items such as tool holders and tool assemblies, and data on related items such as parts Žpart process plans — what tools are required. and workcenters Žwhat tools can be used.. As with most database applications these days, the relational data model is almost always utilized for tooling databases. The database implementation varies, depending upon the scope, nature, and amount of tool-related data being considered. The use of a single, centralized database implemented on a PC is common for small tool management systems of limited scope and size w5,6x. For larger, more ambitious designs, however, a distributed tooling database is often required w7x. More comprehensive efforts to capture and utilize tool-related data include multimedia databases and the use of the Internet. Whereas relational databases can handle numeric or textual data only, multimedia databases can also work with such data forms as images, graphics, video, sound clips, or even entire documents w8x. Such a capability offers great potential for CIM, as manufacturing firms typically deal with technical information which appears in a large diversity of forms including graphics, plans, pictures, speech, text, etc. w9x. With regards to tooling, the multimedia approach can be employed to develop a tooling database which can work directly with tool-related documents Žtool purchase orders, tool life plots, etc.. and images Žtool drawings, sketches, photographs, CAD wcomputeraided designx files, etc... The Internet is becoming the ideal mechanism for accessing and utilizing stored
data, due to the fact that the software Žbrowsers. and languages used Že.g., hypertext mark-up language, HTML. are platform-independent w10x. This feature is of great benefit in systems where the data must be routinely accessed by a variety of different computer platforms. This is exactly the case found in today’s manufacturing enterprises Žand especially with respect to tooling., where different functional areas and departments routinely use a variety of different computer hardwares. Tool-related data are utilized for execution of a large variety of manufacturing activities including product design, process planning, NC programming, detailed capacity planning, purchasing, procurement, shop-floor control, and inventory control w5x. Such data often play a central role in CIM in that it is the prime linkage between CAD, CAM, computer-aided process planning ŽCAPP., production planning and control ŽPP and C., and computer-aided quality assurance ŽCAQ. w11x. Additionally, in order to develop and employ valid simulation models of manufacturing facilities, tool-related data and tool management and control strategies must be considered w12x. Overall, the importance of tooling data and tool management in modern manufacturing firms cannot be overestimated. Various methods, such as that of Kehoe et al. w13x, have been developed for specifying tool management systems, and current research has focused on such items as using expert systems for selecting tools for machining operations w14x. The benefits of employing tool management systems are numerous, and include tool inventory savings, reduction of space needed for tools, reduced setup times, reduced expediting time, fuller shift utilization, tool kitting savings, reduction of stockouts, reduction of excess purchases, and reduction of obsolete tools w6x. In order to perform all tooling-related manufacturing planning and control activities effectively, the tooling database must be easily accessible by the departments performing these activities. Rahman and Heikkala w15x, e.g., describe how a tooling database for a flexible manufacturing system ŽFMS. can be effectively linked with the other modules of the FMS. Access to the database may need to be direct Žquery performed by an individual. or indirect Žquery performed by another software application.. When dealing with a new facility, the tooling database implementation can be designed in parallel with the
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
associated computer hardware and software applications to obtain a seamless, integrated system. With an existing facility, however, problems can arise in implementing the tooling database around existing computer hardwarersoftware in use in the various departments. This paper describes the database implementation problems encountered at an existing facility by one US aircraft component manufacturer Žwhich will remain anonymous., and how the problems were solved. Section 2 presents background on the initial facility, what tooling data were initially maintained and how, and the initial tooling database implementation plan. Details on the actual database design Žconceptual schema, relational schema, data dictionary, etc.. are not presented; many texts, such as Ref. w16x, describe the necessary steps to design a database for a given application. Section 3 describes the tooling database implementation problems that arose, while Section 4 describes how these problems were tackled. The resulting database implementation design is discussed in Section 5. Concluding remarks on the success of the database implementation, and lessons learned, are presented in Section 6.
2. Background 2.1. Description of initial facility The company of concern is a high-variety, lowto-medium volume producer of structural components for the aircraft industry. A large number of parts are machined from aluminum and other lightweight alloys, and have complex geometries. Together, the high variety of products made and complex product geometries results in thousands of different tool types being needed to meet production requirements. The company was experiencing difficulties controlling and organizing tooling. The only tooling data formally maintained was dimensional data: this was done via a CAD drawing for each tool type. The CAD drawings were generated and maintained by the NC Programming department, using a commercially available NC programming application. Each tool type Žand corresponding drawing. was identified
223
by a unique code that defined parameters such as tool length, diameter, etc. Whenever a new job was being prepared, the required tool types were established during NC program generation. The NC programmers would then manually search the directory of CAD drawings, based upon the codes, to determine which of the required tool types were already existing Ži.e., drawing on file. and which were new. For each new tool type, the code was specified and the corresponding CAD drawing generated. The tool drawings for the job were then forwarded to the tool crib, which was responsible for preparing the necessary tools for each job. The tool crib maintained all the CAD drawings on paper for their reference. 2.2. Initial tooling database implementation plan A study was performed to identify the various activities involved in design, procurement, organization, and control of tooling in the facility. Additionally, the study identified additional activities where tooling data could be used to advantage. The entire set of tooling-related activities was found to be spread over several departments, including Engineering Žproduct design., NC Programming, Manufacturing Engineering, the tool crib, and the shop floor. In order to make the necessary tooling data available to each of these departments, a tooling database was to be designed and implemented. The initial implementation plan was to employ a single relational database running on a single server and accessible to all departments as required. Based upon anticipated database size, the preferred operating systemrhardware platform, scalabilityrmigration considerations, and cost, it was desired to implement the tooling database using Microsoft Access on a PC.
3. Tooling database implementation problems The idea of a single tooling database for supporting all necessary activities in the facility suffered from three major problems. These are described as follows. The first problem was how to get part-related data into the tooling database. These data, which included
224
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
part data Židentification wIDx number, operations, and workcenters where operations performed for each part. and work order data ŽID number, part number, part quantity, and due date for each work order., were necessary to support the calculation of required tool type quantities for production and tooling requirements at workcenters, among other things. The data were maintained by the company’s ERP system; hence, a method was needed to somehow extract these data from the ERP system and provide it to the tooling database. An automated method for performing this task was considered a must. The second problem was that the NC programming application was designed for use with its own internal tooling database ŽNC database for short. only: it could not easily access an external database. Thus, for supporting NC programming, the NC database needed to be employed. ŽThis arrangement had its advantages, however: the NC database could easily be searched from the NC application interface, and NC programmers could import cutting tool parameters directly into an NC program by clicking on a specific tool number in the interface.. The fact, that the NC database needed to be used then naturally led to the idea of using this as the single tooling database in the facility. This would necessitate expanding the NC database as required to support the other manufacturing planning and control activities, and providing the necessary interfaces to the other departments. These objectives could not be met, however, as Ži. the NC database lacked the flexibility to be expanded as desired, and Žii. data in the NC database could not easily be accessed from outside the NC programming application. Thus, two databases were deemed necessary: one for supporting all activities except NC programming Žprimary database, using MS Access., and the NC database for supporing NC programming Žsecondary ŽNC. database.. This posed a difficult challenge, however, as some of the tooling data were common to both databases and thus, the problem of ensuring data integrity existed. The third problem was that the different departments were found to use different computer hardwares, necessitating a separate interface to the primary database for each platform. This was highly undesirable due to the effort that would be required to create and maintain multiple interfaces.
Thus, the company was faced with the following database implementation problems: Ži. How to automatically enter part-related data into the primary database; Žii. How to ensure that the primary and secondary databases remained synchronized Ži.e., data integrity maintained.; and Žiii. How to make the primary database accessible to the various departments via a single interface mechanism.
4. Solution to tooling database design and implementation problems The solutions developed for the preceding three database implementation problems are described as follows. 4.1. Generation of part-related data To obtain part-related data for the primary database, the following approach was employed. A program was developed to access the ERP system and then generate a file containing the necessary part-related data. This file then became the part-related data input file for the primary database. A Visual Basic program was then written which would perform the following steps: 1. Check Žin the appropriate directory. for the existence of a part-related data input file. If the input file does not exist, done. 2. Open the input file. 3. Delete the existing part-related data from the primary database. 4. Import the new part-related data Žfrom the input file. into the primary database. The part-related data were replaced in its entirety rather than updated because the data structure did not allow partial updates. The program employed a ‘‘timer’’ control, such that it executed at a set time every day. Once-a-day execution was considered sufficient, as part-related data were generatedrupdated daily or less frequently.
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
4.2. Synchronization of tooling databases
225
- Otherwise, add the input text file record to the table as a new record.
In order to ensure that the two databases were synchronized, it was necessary to ensure that data common to both databases were identical. The following data were common:
In similar manner as before ŽSection 4.1., this program employed a timer control and executed at a set time every day.
- cutter data Žcutter diameter, overall length, flute length, number of flutes, etc..; - cutter assembly data Žcutter identifier, holder identifier, tool preset length, etc..; and - holder data Žouter diameter, length, inner diameter..
4.2.2. Cutter, cutter assembly, and holder data from primary database into secondary database A single program was developed to extract cutter, cutter assembly, and holder data from the primary database and get it into the secondary ŽNC. database. The program performed the following steps:
Part-cutter data Žcutter data for a specific part. were generated via the NC programming application and automatically input into the secondary ŽNC. database, while cutter, cutter assembly, and holder data were manually input into the primary database. Thus, methods were needed to get part-cutter data into the primary database, and cutter, cutter assembly, and holder data into the secondary database. The methods developed are described as follows.
1. Check each record of the cutter, cutter assembly, and holder tables in the primary database. 2. For each record in each table, which was created or modified since the last time the program was executed, create a record for the corresponding ‘‘component’’ file in the NC database. 3. Trigger the NC database import utility to import the component fileŽs. into the NC database.
4.2.1. Part-cutter data from secondary database into primary database Two programs were used to extract part-cutter data from the secondary database and get it into the primary database. The first program extracted the part-cutter data from each cutter location ŽCL. datafile generated by the NC programming application. Whenever a CL datafile was generated, the program would extract the part-cutter data and write it to a text file Žappending the file, if already present.. This file then became the part-cutter data input file for the primary database. The second program would then perform the following steps: 1. Check Žin the appropriate directory. for the existence of a part-cutter data input file. If the input file does not exist, done. 2. Open the input file. 3. For each record in the input file: - Check the appropriate table of the primary database to see if a record having the same primary key exists; - If such a record is found, replace it with the input text file record;
As before, this program employed a timer control and executed at a set time every day. 4.3. Single interface mechanism to primary database To add data to or modify data in the primary database, or to query the primary database, a single interface mechanism was desired. The approach used
Fig. 1. Tooling database implementation design.
226
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
was to develop web pages for performing these tasks, as web pages are a ‘‘neutral’’ interface format in that they can run on any computer using a standard browser Že.g., Netscape.. Previous efforts to provide a web-browser-accessible system have employed static HTML pages linked together in a hierarchical manner w17x. However, HTML pages alone could not provide the ‘‘interactivity’’ that this application required. To provide dynamic content, Active Server Pages were used. These differ from regular HTML pages in that they contain a script which interfaces with the database to insert, delete, modify, andror query data as desired, and to generate the corresponding HTML pages dynamically.
Two types of Active Server pages were developed for interacting with the primary database: ‘‘Data Entry’’ web pages and ‘‘Query’’ web pages. Three sets of Data Entry web pages were developed: one set for adding, one set for modifying, and another set for deleting data pertaining to holders, extensions, workcenters, cutters, cutter assemblies, and workcenterrholder compatibility. The Data Entry web pages are used by personnel in the Manufacturing Engineering and NC Programming departments, using Windows-based PCs and IBM RSr6000 workstations. Query web pages provide for various queries. The main queries, and departments where performed,
Fig. 2. Example Data Entry web page.
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
include searching for cutter assemblies ŽNC Programming, Engineering., searching for specific cutters, holders, andror extensions ŽManufacturing Engineering., and viewing workcenter data Žshop floor.. Miscellaneous queries Žtools at a given workcenter, location of a given cutter, workcenters a given cutter can be used at, part numbers a given cutter is used on, etc.. are also available. Additionally, it is also possible to view CAD drawings of cutting tools via the query web pages. These files are maintained separately and used by the NC programming application. To view a tool drawing, the CAD model is first converted to IGES format by the NC programming
227
application. This is followed by the IGES file being exported into .dwf format via a CAD package. The .dwf file is then transferred over the Internet for viewing via a third-party tool such as CadViewer Ža Java applet.. 5. Resulting tooling database implementation design The resulting tooling database implementation design is shown in Fig. 1. The program ReadPart obtains part-related data from a file generated from the ERP system, and supplies this information to the
Fig. 3. Example Query web page.
228
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
primary database ŽSection 4.1.. The programs ReadCLData and ReadPartCut extract data from the secondary database and provide it to the primary database ŽSection 4.2.1., while the program WriteText extracts data from the primary database and provides it to the secondary database ŽSection 4.2.2.. The program ReadCLData is a UNIX shell script and interfaces directly with the NC programming application’s post-processor. The programs ReadPart, ReadPartCut, and WriteText were written in Visual Basic and run on a server with Windows NT Server 4.0 operating system. An example of a Data Entry web page for adding data is shown in Fig. 2. An example of a Query web page is shown in Fig. 3.
6. Discussion and concluding remarks The implementation of the tooling database in the facility was constrained by several factors. Cutting tool data was spread out over several information systems, which were different in terms of both software and hardware. The NC Žsecondary. database was not accessible via a web browser without proprietary server software. This made the creation of another Žprimary. database necessary, to collect the data and make it accessible. Given the constraints, the tooling database implementation achieves its objectives. The company is currently implementing an enterprise-wide information system with an open architecture. Therefore, it may not be necessary to transfer part-related data to the primary database. Instead, the original ERP database can be queried directly to view the data. At the present time, however, transferring the data to the primary tooling database is the only method by which all the data are made available via the web browser. The benefits of this implementation will be gradually visible. The benefits include increased productivity due to time saved searching for tools, cutter standardization, a reduction in the number of scrapped parts, and better estimation of order quantities of the cutting tools. This system is only the seed for a larger framework that would use the information in the database to dramatically increase the productivity on the shop floor. As an example, consider cutting tool preset-
ting. The preset length can now be quickly determined by referencing the drawings in the database, instead of using paper drawings of the cutting tool assembly. A further extension would be to tie-in the application with a producer–consumer data exchange system. To facilitate tooling data exchange with various tool producers, as well as with future in-house applications, a neutral data modeling format and communications interface are desirable. One approach for this is to consider tools as products and specify the data interface via Standard for the Exchange of Product ŽSTEP. model data w18x and the data models via the EXPRESS language w19x. Other efforts are underway to develop modeling methods and interfaces based upon STEP but specifically tailored for tooling data. For example, ESPRIT project 5136, Links and Interfaces for Tool Data Exchange ŽLITE., aims to create a producer model, a data exchange model and user interface, and a user model and application interface for facilitating tooling data modeling, specification, and communication between producers and companies and within companies w20x. The advantages of employing such items for tooling data modeling, specification, and communications are transparency, extensibility, and flexibility in system design and operation and communication transactions. In particular, they would require that tooling data be entered only once Žby the cutting tool producer.. By avoiding any manual input at a further stage and writing software applications that interface the data exchange files and the tooling database directly, the returns from harnessing the tooling information can be maximized.
References w1x F. Mason, Computerized cutting tool management, in: American Machinist and Automated Manufacturing,1986, p. 106, May. w2x Moving tool management toward the next century, Tooling and Production 60 Ž1994. 4–6, June. w3x G.S. Vasilash, Getting in control of cutting tools before you cut your profits, Production 99 Ž6. Ž1987. 40–45. w4x X. Wenli, L. Ya, Cutting tool management in FA, in: N.W.M. Ko, S.T. Tan ŽEds.., Proceedings of the International Conference on Manufacturing Automation, The University of Hong Kong, 1992, pp. 734–739. w5x R.V. Narang, A computer model for tool management information systems, in: F. Plonka, G. Olling ŽEds.., Computer
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
w6x w7x
w8x
w9x
w10x w11x
w12x
w13x
w14x
w15x
w16x
Applications in Production and Engineering, Chapman & Hall, London, 1997, pp. 208–217. Tool management software, Tooling and Production 60 Ž1994. 16–21, June. R.H. Hannam, in: Computer-Integrated Manufacturing: From Concepts To Realisation, Addison-Wesley, Longman, Essex, England, 1997, pp. 204–205. S.T. Campbell, S.M. Chung, Database approach for the management of multimedia information, in: K.C. Nwosu, B. Thuraisingham, P.B. Berra ŽEds.., Multimedia Database Systems: Design and Implementation Strategies, Kluwer Academic Publishing, Boston, 1996, pp. 17–44. J.B. Waldner, in: CIM: Principles of Computer-Integrated Manufacturing, Wiley, Chichester, England, 1992, pp. 102– 104. R. Brice, in: Multimedia and Virtual Reality Engineering, Newnes, Oxford, 1997, pp. 206–210. U. Rembold, B.O. Nnaji, A. Storr, in: Computer-Integrated Manufacturing and Engineering, Addison-Wesley, 1994, pp. 233–241. T. Perara, Modelling and simulation of tool management systems using DBMS, in: First Joint Conference on International Simulation Societies Proceedings, SCS, San Diego, CA, 1994, pp. 435–438. D.F. Kehoe, D. Little, I. Al-Maliki, T.M. Wyatt, Method for the specification of tool management information systems, in: Computer-Integrated Manufacturing Systems Ž4.1991, pp. 18–25, No. 1. M. Gulesin, R.M. Jones, Cutting tool selection for process planning using expert system techniques, in: G. Hencsey, G. Renner ŽEds.., Proceedings of the 3rd Annual International Conference and Exhibition on CADrCAMrCAErCIM: Applications for Manufacturing and Productivity, World Computer Graphics Association, 1993, pp. 132–140. M. Rahman, J. Heikkala, Developing and linking the tool database for tool management in FMS, in: P. Kopacek ŽEd.., Proceedings of the IFAC Workshop on Manufacturing Systems: Modelling, Management and Control ŽMIM ’97., Pergamon, Oxford, 1997, pp. 175–178. P. Rob, C. Coronel, Database Systems: Design, Implementation, and Management, 3rd edn., International Thomson Publishing, 1997.
229
w17x Z. Adamczyk, H. Malek, Internet tools supporting creation and management of technological environment in CADrCAM systems, Journal of Materials Processing Technology 76 Ž1–3. Ž1998. 102–108. w18x ISO, STEP Preliminary Design, ISO TC184rSC4rWG1N 208. w19x ISO, Exchange of Product Model Data: Part 11, The EXPRESS Language, ISO CD 10303- 11, TC184rSC4N64r WG1, ISO Internal Paper, 1990. w20x P. Gosset, G. Martel, Links and Interfaces for a tool data exchange ESPRIT project no. 5136, in: Proceedings of the Eighth CIM Europe Annual Conference,1992, pp. 127–138.
Bharat Anumolu is working towards his Master’s degree in Industrial Engineering at Virginia Polytechnic Institute and State University ŽUSA.. He has a Bachelor’s degree in Mechanical Engineering from the Indian Institute of Technology, Madras and has over 2 of years of experience in designing computer applications that utilize web technologies.
John P. Shewchuk is an Assistant Professor in Industrial and Systems Engineering at Virginia Polytechnic Institute and State University ŽUSA.. He received his PhD in Industrial Engineering from Purdue University in 1995. Dr. Shewchuk’s research interests include performance modeling and analysis of manufacturing and production systems, flexible and agile manufacturing, computer-integrated manufacturing, and applications of augmented and virtual reality in manufacturing. He is a member of IIE, INFORMS, SME, and IFIP WG 5.7 ŽComputer-Aided Production Management., and has been a registered Professional Engineer of the Province of Manitoba since 1986.
Design of a tooling database implementation for an existing facility Bharat Anumolu ) , John P. Shewchuk Industrial and Systems Engineering Department, Virginia Polytechnic Institute and State UniÕersity, Blacksburg, VA 24061, USA
Abstract One of the most critical resources for many manufacturing facilities is tooling. To properly control tooling and make tool-related data available to different manufacturing activities, tooling databases are often employed. Implementing a tooling database around computer hardwarersoftware in an existing facility, however, can be difficult. This paper describes how one U.S. aircraft component manufacturer tackled problems encountered in trying to implement a single tooling database in their facility. Three problems were found: some tooling data were already located in another database; one software application requiring tooling data already had its own internal database; and various computer hardware platforms were used in different departments, making a single interface difficult. The problems were solved by employing two databases, via various software agents, and creating a platform-independent interface to the primary database. The resulting implementation proved a viable approach for the facility, and provides a good example of how such database implementation problems can be handled. q 2000 Elsevier Science B.V. All rights reserved. Keywords: Tooling; Tool management; Database
1. Introduction Throughout the industrialized world, many manufacturing facilities are found which are primarily concerned with machining and related operations. One of the most critical resources for such facilities is tooling. Hundreds and even thousands of different tool types may be required in many facilities, and no matter how well jobs are scheduled and machines run, processing operations can only proceed if the right tools are available at the right machines at the
)
Corresponding author.
right times. The importance of tooling is best illustrated by the following statistics w1x: - 16% of scheduled production time is missed because tooling is not available; - 40–60% of a foreman’s time is spent expediting tools and related materials; - 7–10 times more money is spent on tools, jigs, etc. than on capital equipment; and - up to 30% of tool inventories consists of ‘‘lost’’ tooling – tools which have been distributed but are not accounted for. Additional tool-related problems include loss of control of tooling, stockpiling of tools at worksta-
0166-3615r00r$ - see front matter q 2000 Elsevier Science B.V. All rights reserved. PII: S 0 1 6 6 - 3 6 1 5 Ž 9 9 . 0 0 0 7 2 - X
222
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
tions, increased downtime, employee theft of tools, and an unexplained weakening in the firm’s competitive position w2x. In order to properly organize and control tooling, tool management systems are employed. These typically consist of a database for storing tool-related data, a system for identifying and tracking tools Že.g., barcode., and software for executing various tool management functions w3,4x. The heart of any tool management system, however, is the tooling database. Tooling databases contain data describing tool types Židentification code, description, length, diameter, what materials can be used for, what holders can be used with, tool life parameters, vendors, cost, etc.. and individual tools Žwhat type each tool is, how much life remaining, where tools are located, etc... They may also contain data on items such as tool holders and tool assemblies, and data on related items such as parts Žpart process plans — what tools are required. and workcenters Žwhat tools can be used.. As with most database applications these days, the relational data model is almost always utilized for tooling databases. The database implementation varies, depending upon the scope, nature, and amount of tool-related data being considered. The use of a single, centralized database implemented on a PC is common for small tool management systems of limited scope and size w5,6x. For larger, more ambitious designs, however, a distributed tooling database is often required w7x. More comprehensive efforts to capture and utilize tool-related data include multimedia databases and the use of the Internet. Whereas relational databases can handle numeric or textual data only, multimedia databases can also work with such data forms as images, graphics, video, sound clips, or even entire documents w8x. Such a capability offers great potential for CIM, as manufacturing firms typically deal with technical information which appears in a large diversity of forms including graphics, plans, pictures, speech, text, etc. w9x. With regards to tooling, the multimedia approach can be employed to develop a tooling database which can work directly with tool-related documents Žtool purchase orders, tool life plots, etc.. and images Žtool drawings, sketches, photographs, CAD wcomputeraided designx files, etc... The Internet is becoming the ideal mechanism for accessing and utilizing stored
data, due to the fact that the software Žbrowsers. and languages used Že.g., hypertext mark-up language, HTML. are platform-independent w10x. This feature is of great benefit in systems where the data must be routinely accessed by a variety of different computer platforms. This is exactly the case found in today’s manufacturing enterprises Žand especially with respect to tooling., where different functional areas and departments routinely use a variety of different computer hardwares. Tool-related data are utilized for execution of a large variety of manufacturing activities including product design, process planning, NC programming, detailed capacity planning, purchasing, procurement, shop-floor control, and inventory control w5x. Such data often play a central role in CIM in that it is the prime linkage between CAD, CAM, computer-aided process planning ŽCAPP., production planning and control ŽPP and C., and computer-aided quality assurance ŽCAQ. w11x. Additionally, in order to develop and employ valid simulation models of manufacturing facilities, tool-related data and tool management and control strategies must be considered w12x. Overall, the importance of tooling data and tool management in modern manufacturing firms cannot be overestimated. Various methods, such as that of Kehoe et al. w13x, have been developed for specifying tool management systems, and current research has focused on such items as using expert systems for selecting tools for machining operations w14x. The benefits of employing tool management systems are numerous, and include tool inventory savings, reduction of space needed for tools, reduced setup times, reduced expediting time, fuller shift utilization, tool kitting savings, reduction of stockouts, reduction of excess purchases, and reduction of obsolete tools w6x. In order to perform all tooling-related manufacturing planning and control activities effectively, the tooling database must be easily accessible by the departments performing these activities. Rahman and Heikkala w15x, e.g., describe how a tooling database for a flexible manufacturing system ŽFMS. can be effectively linked with the other modules of the FMS. Access to the database may need to be direct Žquery performed by an individual. or indirect Žquery performed by another software application.. When dealing with a new facility, the tooling database implementation can be designed in parallel with the
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
associated computer hardware and software applications to obtain a seamless, integrated system. With an existing facility, however, problems can arise in implementing the tooling database around existing computer hardwarersoftware in use in the various departments. This paper describes the database implementation problems encountered at an existing facility by one US aircraft component manufacturer Žwhich will remain anonymous., and how the problems were solved. Section 2 presents background on the initial facility, what tooling data were initially maintained and how, and the initial tooling database implementation plan. Details on the actual database design Žconceptual schema, relational schema, data dictionary, etc.. are not presented; many texts, such as Ref. w16x, describe the necessary steps to design a database for a given application. Section 3 describes the tooling database implementation problems that arose, while Section 4 describes how these problems were tackled. The resulting database implementation design is discussed in Section 5. Concluding remarks on the success of the database implementation, and lessons learned, are presented in Section 6.
2. Background 2.1. Description of initial facility The company of concern is a high-variety, lowto-medium volume producer of structural components for the aircraft industry. A large number of parts are machined from aluminum and other lightweight alloys, and have complex geometries. Together, the high variety of products made and complex product geometries results in thousands of different tool types being needed to meet production requirements. The company was experiencing difficulties controlling and organizing tooling. The only tooling data formally maintained was dimensional data: this was done via a CAD drawing for each tool type. The CAD drawings were generated and maintained by the NC Programming department, using a commercially available NC programming application. Each tool type Žand corresponding drawing. was identified
223
by a unique code that defined parameters such as tool length, diameter, etc. Whenever a new job was being prepared, the required tool types were established during NC program generation. The NC programmers would then manually search the directory of CAD drawings, based upon the codes, to determine which of the required tool types were already existing Ži.e., drawing on file. and which were new. For each new tool type, the code was specified and the corresponding CAD drawing generated. The tool drawings for the job were then forwarded to the tool crib, which was responsible for preparing the necessary tools for each job. The tool crib maintained all the CAD drawings on paper for their reference. 2.2. Initial tooling database implementation plan A study was performed to identify the various activities involved in design, procurement, organization, and control of tooling in the facility. Additionally, the study identified additional activities where tooling data could be used to advantage. The entire set of tooling-related activities was found to be spread over several departments, including Engineering Žproduct design., NC Programming, Manufacturing Engineering, the tool crib, and the shop floor. In order to make the necessary tooling data available to each of these departments, a tooling database was to be designed and implemented. The initial implementation plan was to employ a single relational database running on a single server and accessible to all departments as required. Based upon anticipated database size, the preferred operating systemrhardware platform, scalabilityrmigration considerations, and cost, it was desired to implement the tooling database using Microsoft Access on a PC.
3. Tooling database implementation problems The idea of a single tooling database for supporting all necessary activities in the facility suffered from three major problems. These are described as follows. The first problem was how to get part-related data into the tooling database. These data, which included
224
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
part data Židentification wIDx number, operations, and workcenters where operations performed for each part. and work order data ŽID number, part number, part quantity, and due date for each work order., were necessary to support the calculation of required tool type quantities for production and tooling requirements at workcenters, among other things. The data were maintained by the company’s ERP system; hence, a method was needed to somehow extract these data from the ERP system and provide it to the tooling database. An automated method for performing this task was considered a must. The second problem was that the NC programming application was designed for use with its own internal tooling database ŽNC database for short. only: it could not easily access an external database. Thus, for supporting NC programming, the NC database needed to be employed. ŽThis arrangement had its advantages, however: the NC database could easily be searched from the NC application interface, and NC programmers could import cutting tool parameters directly into an NC program by clicking on a specific tool number in the interface.. The fact, that the NC database needed to be used then naturally led to the idea of using this as the single tooling database in the facility. This would necessitate expanding the NC database as required to support the other manufacturing planning and control activities, and providing the necessary interfaces to the other departments. These objectives could not be met, however, as Ži. the NC database lacked the flexibility to be expanded as desired, and Žii. data in the NC database could not easily be accessed from outside the NC programming application. Thus, two databases were deemed necessary: one for supporting all activities except NC programming Žprimary database, using MS Access., and the NC database for supporing NC programming Žsecondary ŽNC. database.. This posed a difficult challenge, however, as some of the tooling data were common to both databases and thus, the problem of ensuring data integrity existed. The third problem was that the different departments were found to use different computer hardwares, necessitating a separate interface to the primary database for each platform. This was highly undesirable due to the effort that would be required to create and maintain multiple interfaces.
Thus, the company was faced with the following database implementation problems: Ži. How to automatically enter part-related data into the primary database; Žii. How to ensure that the primary and secondary databases remained synchronized Ži.e., data integrity maintained.; and Žiii. How to make the primary database accessible to the various departments via a single interface mechanism.
4. Solution to tooling database design and implementation problems The solutions developed for the preceding three database implementation problems are described as follows. 4.1. Generation of part-related data To obtain part-related data for the primary database, the following approach was employed. A program was developed to access the ERP system and then generate a file containing the necessary part-related data. This file then became the part-related data input file for the primary database. A Visual Basic program was then written which would perform the following steps: 1. Check Žin the appropriate directory. for the existence of a part-related data input file. If the input file does not exist, done. 2. Open the input file. 3. Delete the existing part-related data from the primary database. 4. Import the new part-related data Žfrom the input file. into the primary database. The part-related data were replaced in its entirety rather than updated because the data structure did not allow partial updates. The program employed a ‘‘timer’’ control, such that it executed at a set time every day. Once-a-day execution was considered sufficient, as part-related data were generatedrupdated daily or less frequently.
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
4.2. Synchronization of tooling databases
225
- Otherwise, add the input text file record to the table as a new record.
In order to ensure that the two databases were synchronized, it was necessary to ensure that data common to both databases were identical. The following data were common:
In similar manner as before ŽSection 4.1., this program employed a timer control and executed at a set time every day.
- cutter data Žcutter diameter, overall length, flute length, number of flutes, etc..; - cutter assembly data Žcutter identifier, holder identifier, tool preset length, etc..; and - holder data Žouter diameter, length, inner diameter..
4.2.2. Cutter, cutter assembly, and holder data from primary database into secondary database A single program was developed to extract cutter, cutter assembly, and holder data from the primary database and get it into the secondary ŽNC. database. The program performed the following steps:
Part-cutter data Žcutter data for a specific part. were generated via the NC programming application and automatically input into the secondary ŽNC. database, while cutter, cutter assembly, and holder data were manually input into the primary database. Thus, methods were needed to get part-cutter data into the primary database, and cutter, cutter assembly, and holder data into the secondary database. The methods developed are described as follows.
1. Check each record of the cutter, cutter assembly, and holder tables in the primary database. 2. For each record in each table, which was created or modified since the last time the program was executed, create a record for the corresponding ‘‘component’’ file in the NC database. 3. Trigger the NC database import utility to import the component fileŽs. into the NC database.
4.2.1. Part-cutter data from secondary database into primary database Two programs were used to extract part-cutter data from the secondary database and get it into the primary database. The first program extracted the part-cutter data from each cutter location ŽCL. datafile generated by the NC programming application. Whenever a CL datafile was generated, the program would extract the part-cutter data and write it to a text file Žappending the file, if already present.. This file then became the part-cutter data input file for the primary database. The second program would then perform the following steps: 1. Check Žin the appropriate directory. for the existence of a part-cutter data input file. If the input file does not exist, done. 2. Open the input file. 3. For each record in the input file: - Check the appropriate table of the primary database to see if a record having the same primary key exists; - If such a record is found, replace it with the input text file record;
As before, this program employed a timer control and executed at a set time every day. 4.3. Single interface mechanism to primary database To add data to or modify data in the primary database, or to query the primary database, a single interface mechanism was desired. The approach used
Fig. 1. Tooling database implementation design.
226
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
was to develop web pages for performing these tasks, as web pages are a ‘‘neutral’’ interface format in that they can run on any computer using a standard browser Že.g., Netscape.. Previous efforts to provide a web-browser-accessible system have employed static HTML pages linked together in a hierarchical manner w17x. However, HTML pages alone could not provide the ‘‘interactivity’’ that this application required. To provide dynamic content, Active Server Pages were used. These differ from regular HTML pages in that they contain a script which interfaces with the database to insert, delete, modify, andror query data as desired, and to generate the corresponding HTML pages dynamically.
Two types of Active Server pages were developed for interacting with the primary database: ‘‘Data Entry’’ web pages and ‘‘Query’’ web pages. Three sets of Data Entry web pages were developed: one set for adding, one set for modifying, and another set for deleting data pertaining to holders, extensions, workcenters, cutters, cutter assemblies, and workcenterrholder compatibility. The Data Entry web pages are used by personnel in the Manufacturing Engineering and NC Programming departments, using Windows-based PCs and IBM RSr6000 workstations. Query web pages provide for various queries. The main queries, and departments where performed,
Fig. 2. Example Data Entry web page.
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
include searching for cutter assemblies ŽNC Programming, Engineering., searching for specific cutters, holders, andror extensions ŽManufacturing Engineering., and viewing workcenter data Žshop floor.. Miscellaneous queries Žtools at a given workcenter, location of a given cutter, workcenters a given cutter can be used at, part numbers a given cutter is used on, etc.. are also available. Additionally, it is also possible to view CAD drawings of cutting tools via the query web pages. These files are maintained separately and used by the NC programming application. To view a tool drawing, the CAD model is first converted to IGES format by the NC programming
227
application. This is followed by the IGES file being exported into .dwf format via a CAD package. The .dwf file is then transferred over the Internet for viewing via a third-party tool such as CadViewer Ža Java applet.. 5. Resulting tooling database implementation design The resulting tooling database implementation design is shown in Fig. 1. The program ReadPart obtains part-related data from a file generated from the ERP system, and supplies this information to the
Fig. 3. Example Query web page.
228
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
primary database ŽSection 4.1.. The programs ReadCLData and ReadPartCut extract data from the secondary database and provide it to the primary database ŽSection 4.2.1., while the program WriteText extracts data from the primary database and provides it to the secondary database ŽSection 4.2.2.. The program ReadCLData is a UNIX shell script and interfaces directly with the NC programming application’s post-processor. The programs ReadPart, ReadPartCut, and WriteText were written in Visual Basic and run on a server with Windows NT Server 4.0 operating system. An example of a Data Entry web page for adding data is shown in Fig. 2. An example of a Query web page is shown in Fig. 3.
6. Discussion and concluding remarks The implementation of the tooling database in the facility was constrained by several factors. Cutting tool data was spread out over several information systems, which were different in terms of both software and hardware. The NC Žsecondary. database was not accessible via a web browser without proprietary server software. This made the creation of another Žprimary. database necessary, to collect the data and make it accessible. Given the constraints, the tooling database implementation achieves its objectives. The company is currently implementing an enterprise-wide information system with an open architecture. Therefore, it may not be necessary to transfer part-related data to the primary database. Instead, the original ERP database can be queried directly to view the data. At the present time, however, transferring the data to the primary tooling database is the only method by which all the data are made available via the web browser. The benefits of this implementation will be gradually visible. The benefits include increased productivity due to time saved searching for tools, cutter standardization, a reduction in the number of scrapped parts, and better estimation of order quantities of the cutting tools. This system is only the seed for a larger framework that would use the information in the database to dramatically increase the productivity on the shop floor. As an example, consider cutting tool preset-
ting. The preset length can now be quickly determined by referencing the drawings in the database, instead of using paper drawings of the cutting tool assembly. A further extension would be to tie-in the application with a producer–consumer data exchange system. To facilitate tooling data exchange with various tool producers, as well as with future in-house applications, a neutral data modeling format and communications interface are desirable. One approach for this is to consider tools as products and specify the data interface via Standard for the Exchange of Product ŽSTEP. model data w18x and the data models via the EXPRESS language w19x. Other efforts are underway to develop modeling methods and interfaces based upon STEP but specifically tailored for tooling data. For example, ESPRIT project 5136, Links and Interfaces for Tool Data Exchange ŽLITE., aims to create a producer model, a data exchange model and user interface, and a user model and application interface for facilitating tooling data modeling, specification, and communication between producers and companies and within companies w20x. The advantages of employing such items for tooling data modeling, specification, and communications are transparency, extensibility, and flexibility in system design and operation and communication transactions. In particular, they would require that tooling data be entered only once Žby the cutting tool producer.. By avoiding any manual input at a further stage and writing software applications that interface the data exchange files and the tooling database directly, the returns from harnessing the tooling information can be maximized.
References w1x F. Mason, Computerized cutting tool management, in: American Machinist and Automated Manufacturing,1986, p. 106, May. w2x Moving tool management toward the next century, Tooling and Production 60 Ž1994. 4–6, June. w3x G.S. Vasilash, Getting in control of cutting tools before you cut your profits, Production 99 Ž6. Ž1987. 40–45. w4x X. Wenli, L. Ya, Cutting tool management in FA, in: N.W.M. Ko, S.T. Tan ŽEds.., Proceedings of the International Conference on Manufacturing Automation, The University of Hong Kong, 1992, pp. 734–739. w5x R.V. Narang, A computer model for tool management information systems, in: F. Plonka, G. Olling ŽEds.., Computer
B. Anumolu, J.P. Shewchukr Computers in Industry 42 (2000) 221–229
w6x w7x
w8x
w9x
w10x w11x
w12x
w13x
w14x
w15x
w16x
Applications in Production and Engineering, Chapman & Hall, London, 1997, pp. 208–217. Tool management software, Tooling and Production 60 Ž1994. 16–21, June. R.H. Hannam, in: Computer-Integrated Manufacturing: From Concepts To Realisation, Addison-Wesley, Longman, Essex, England, 1997, pp. 204–205. S.T. Campbell, S.M. Chung, Database approach for the management of multimedia information, in: K.C. Nwosu, B. Thuraisingham, P.B. Berra ŽEds.., Multimedia Database Systems: Design and Implementation Strategies, Kluwer Academic Publishing, Boston, 1996, pp. 17–44. J.B. Waldner, in: CIM: Principles of Computer-Integrated Manufacturing, Wiley, Chichester, England, 1992, pp. 102– 104. R. Brice, in: Multimedia and Virtual Reality Engineering, Newnes, Oxford, 1997, pp. 206–210. U. Rembold, B.O. Nnaji, A. Storr, in: Computer-Integrated Manufacturing and Engineering, Addison-Wesley, 1994, pp. 233–241. T. Perara, Modelling and simulation of tool management systems using DBMS, in: First Joint Conference on International Simulation Societies Proceedings, SCS, San Diego, CA, 1994, pp. 435–438. D.F. Kehoe, D. Little, I. Al-Maliki, T.M. Wyatt, Method for the specification of tool management information systems, in: Computer-Integrated Manufacturing Systems Ž4.1991, pp. 18–25, No. 1. M. Gulesin, R.M. Jones, Cutting tool selection for process planning using expert system techniques, in: G. Hencsey, G. Renner ŽEds.., Proceedings of the 3rd Annual International Conference and Exhibition on CADrCAMrCAErCIM: Applications for Manufacturing and Productivity, World Computer Graphics Association, 1993, pp. 132–140. M. Rahman, J. Heikkala, Developing and linking the tool database for tool management in FMS, in: P. Kopacek ŽEd.., Proceedings of the IFAC Workshop on Manufacturing Systems: Modelling, Management and Control ŽMIM ’97., Pergamon, Oxford, 1997, pp. 175–178. P. Rob, C. Coronel, Database Systems: Design, Implementation, and Management, 3rd edn., International Thomson Publishing, 1997.
229
w17x Z. Adamczyk, H. Malek, Internet tools supporting creation and management of technological environment in CADrCAM systems, Journal of Materials Processing Technology 76 Ž1–3. Ž1998. 102–108. w18x ISO, STEP Preliminary Design, ISO TC184rSC4rWG1N 208. w19x ISO, Exchange of Product Model Data: Part 11, The EXPRESS Language, ISO CD 10303- 11, TC184rSC4N64r WG1, ISO Internal Paper, 1990. w20x P. Gosset, G. Martel, Links and Interfaces for a tool data exchange ESPRIT project no. 5136, in: Proceedings of the Eighth CIM Europe Annual Conference,1992, pp. 127–138.
Bharat Anumolu is working towards his Master’s degree in Industrial Engineering at Virginia Polytechnic Institute and State University ŽUSA.. He has a Bachelor’s degree in Mechanical Engineering from the Indian Institute of Technology, Madras and has over 2 of years of experience in designing computer applications that utilize web technologies.
John P. Shewchuk is an Assistant Professor in Industrial and Systems Engineering at Virginia Polytechnic Institute and State University ŽUSA.. He received his PhD in Industrial Engineering from Purdue University in 1995. Dr. Shewchuk’s research interests include performance modeling and analysis of manufacturing and production systems, flexible and agile manufacturing, computer-integrated manufacturing, and applications of augmented and virtual reality in manufacturing. He is a member of IIE, INFORMS, SME, and IFIP WG 5.7 ŽComputer-Aided Production Management., and has been a registered Professional Engineer of the Province of Manitoba since 1986.