- Email: [email protected]
“Paraprogramming” manufacturing information systems
Computers ind. Engng Vol. 21, Nos 1-4, pp. 229-233, 1991 Printed in Great Britain. All rights reserved
0360-8352/91 $3.00+ 0.00 Copyright © 1991 Pergamon Press pie
"PARAPROGRAMMING" MANUFACTURING INFORMATION SYSTEMS
John H. Manley Industrial Engineering and Manufacturing Systems Engineering University of Pittsburgh Pittsburgh, Pennsylvania
AnS~CT Computer integrated manufacturing (CIM) is creating unexpected problems for a growing number of manufacturing companies. Manufacturers are finding it especially difficultto attract programmers who are both willing and able to develop the highly complex software that integrates existing accounting, sales, production, engineering, and quality control information subsystems. Consequently, m a n y companies abdicate their responsibility for manufacturing information systems and seek third party support ranging from consulting assistance to a total takeover of the company's information resources and operations. Companies that "give away" their internal information system capabilities to third parties will ultimately lose control of their enterprise information, a danger to be avoided. Off-the-shelf software for desktop computers has become sufficientlypowerful to help solve a major portion of this serious problem. W e hypothesize that manufacturing engineers (and others) can be trained to use packaged software to leverage their company's systems programming capabilities.In effect they would become "paraprogrammers" who would help design, develop, and maintain manufacturing information systems. This new type of professional would not require a computer science or similar educational background, but could be trained to satisfy m a n y specialized programming needs in a manner similar to how paramedics and paralegals are trained and used in the medical and legal professions, respectively. This paper reports on the early stages of research to determine whether or not product design engineers can use a desktop relational database management system and its various c o m m a n d languages to develop a master bill of material information system (BOMIS). The purpose of the research is to evaluate the amount of programming complexity reduction and increased operational effectiveness that can be achieved through paraprogramming by manufacturing engineers.
II~gDD.ILK~D2i A manufacturing company must have the flexibilityto respond to rapidly changing technology and market conditions in today's fiercely competitive global environment . A company must offer diverse products which can be manufactured quickly to satisfy customer demand. At the same time, both product lifecycle and inventory must be minimized. Factory automation and robust, integrated information systems are required to help satisfy these difficultrequirements. Until recently, the generally-accepted model of a new, manufactured-product development life cycle has been sequential, starting with design engineering and ending with production. The flow of information between stages of this process have been characterized as a "waterfall,"or "throwing it over the wall." Now, a more effective, simultaneous or concurrent engineering, parallel, life cycle model is being used wherein players from marketing, sales, design, manufacturing, quality control, etc. work together in teams to "carry products over the walls together." The Department of Defense describes concurrent engineering as the engineering of the product and process such t h a t a better understood product requirement and concomitant process variability reduction as a t h r u s t from the start can lead to the elimination of inspections when such variability is reduced to low enough levels. The goal is a drastic reduction of time, money, people and other resources. The integrated team approach is a critical element of concurrent engineering, in contrast to the traditional linear and departmental approach to design and manufacturing engineering. Thus, the concurrent engineering process insists t h a t all barriers to the effective exchange of information and people between functional groups in the organization be minimized. Concurrent W . n ~ i n e e r l n f Tmnlenm.ntstinn Problem A serious problem can occur that defeats the purpose of concurrent engineering after product development has been completed and routine production begins. This problem is most likely to occur in assembly manufacturing where modifications to a product requested by marketing or sales can be accommodated by manufacturing without requiring major changes to the assembly line. This situation is most prevalent in industries t h a t supply a wide variety of custom-designed parts and assemblies for use by end-product manufactures, or construction companies. In fenestration product manufacturing, for example, new window designs are engineered, tooling set up, procedures for assembly determined, a manufacturing process put in place, and production begun to satisfy repeat custemer orders. Since windows are made in many different sizes, colors, shapes, etc., customer needs vary from dayto-day, month-to-month, and year-to-year. For each new design, the concurrent engineering process is employed to include adding new models to the sales catalog. However, when customers tell a salesman t h a t they only want a "small" change to an existing catalog product, e.g.,reduce the width of a window by 1/2 inch, a serious information system problem can occur. This can happen if the sales department short circuits the concurrent engineering pro229
230
Proceedings of the 13th Annual Conference on Computers and Industrial Engineering
cess and asks the manufacturing manager directly whether or not he can produce this modified product. Since the answer to the question is invariably positive, a "special order" is entered into the business information system, the new product is manufactured, delivered to the customer, and all seems well. But is it? What has really happened is t h a t design engineering has been left out of a modified product development project. This causes a negative ripple effect on the enterprise manufacturing information systems t h a t encourages waste and, most importantly increases operating costs. When this common practice occurs, and tens of thousands of "specials" are entered into the corporate database, excessive costs and related problems occur for the following reasons: • Optimized parts cutting from standard stock is invalidated due to "minor" changes made on the shop floor without the knowledge of design and industrial engineering, thus invalidating important scrap loss information. • Product cost information based on approved bills of materials can only be approximated, thus making cost estimation and materials ordering imprecise and more difficultto accomplish. • In extreme cases, the coding schemes for products are not able to handle the large number of "special" products, essentially invalidating m a n y business software modules, especially if they were "hard coded." Confunnmtlon Control as a Possible Solution Assembled products from the aerospace, defense, and nuclear industries require rigid control due to their potentially hazardous nature if manufactured incorrectly. In these industries, product configuration control is, in effect, a specialized information system which can trace the evolution of the minutest detail of every component part of every product throughout its life cycle, from conceptual design, through product withdrawal from service and disposal. An assembled product configuration control information system (APCCIS) contains unambiguous part numbers, design drawings, and bills of m a t e r i a l (BOM) as its key information elements. Configuration control systems include people who make engineering design change decisions, often facilitated by teams possessing change approval authority called configuration control boards. For critical products configuration control boards are often necessary, but they are also costly to operate. In essence, assembled product configuration control entails strict control of any proposed change to an existing product from its initial design through disposal. The military approach to such control is not recommended for small- to medium-sized commercial manufacturers of non-life-critical products. On the other hand, all companies need an inexpensive but also effective configuration control system t h a t will help prevent the problems described above. A PC-based bill of material information system (BOMIS) can serve this purpose when coupled with the business information system t h a t exists in even the smallest manufacturing company. By supporting an effective engineering design process with a well-designed BOMIS, an effective APCCIS can be constructed. However, the challenge in accomplishing this involves improving the existing mini- or mainframe-based BOMIS, which is generally ineffective for several important reasons. To understand why this is so, let us first examine the BOM. BILL OF M A ' r I ~ J A L CONSIDERATIQ~N,q The BOM is the vehicle by which the structure of a product is communicated throughout the manufacturing organization. From an engineering control perspective, this document is critical to the enterprise. Since it is originated by design engineers, it is considered to be a key input into all engineered product manufacturing control systems. In addition to product, manufacturing, and systems engineering, the BOM is also used as key input data to a wide variety of enterprise functions such as: (1) production planning, (2) material planning, (3) research and development, (4) accounting, (5) marketing and sales, and (6) business management. Tvnes of Bills of Material Although there should logicallybe only be one unique B O M for each product, at least five variations exist [5]: Master Bill of Material: A structured, level-by-levellistingof all of the components of a product, including part number, part name, quantity used, and a linkage from each level of the assembly to subassemblies and components. Original master B O M s are produced by design engineers, but subsets (views or schemas) are used throughout the manufacturing enterprise. Material List: This is a simple listing of the components of a product, without any intermediate levels of assembly, e.g., a cake recipe. This represents one "view" of the master B O M and is used by: (1) production to assemble a portion of a product, e.g.,subassemblies, (2) by accounting to calculate the material cost of a product, and (3) by sales to identify options which can be ordered with the basic product. Quick Deck: This is a summary of all quantities of each component part used in an assembled product. It is a material list of the total product without any reference to intermediate levels of assembly, and is used for quick calculation of material requirements. Materials planning uses the quick deck to calculate the quantity of components required to build a scheduled quantity of product. Where Used: This is an upside-down B O M . It lists all of the next level assemblies and subassemblies in which a component or subassembly is used, including quantities of the component in each next level. Design engineering uses the where-used B O M to determine all usages of that portion of a product which is affefted by a reported failure of a specificcomponent in order to improve product quality through redesign. Top-Level Where Used: This is a list of all of the products (top level assemblies) in which a component or subassembly is used. Design engineering uses the top-levelwhere used B O M to determine all usages of a part which is being considered for change. In short, different users of a single master BOM need different versions (views or schemas). Design engineering finds it most convenient to describe a product in terms of bills which are organized by function. Assembly finds it more convenient to use material lists which represent the sequence in which the components are physically assembled. Materials planning BOMs generally only need to include those components which are purchased or in-
Manley: "Paraprogramming" Manufacturing Information Systems
231
ternally manufactured. Accounting needs the same bill of material as materials planning. However, if materials planning used a quick deck, it would not serve the needs of accounting to calculate the cost of product subassemblies. Sales needs a material listwhich includes the options which can be ordered with the product. TnvAntm.v ~ n ~
~R;l~nm~
Traditional manufacturing information systems usually provide one complete engineering BOM, but often not one than can be easily manipulated to produce the subsets or views described above. This inflexibility creates many irritating information system problems, such as not being able to provide manufacturing with an easily-understood assembly sequence, or sales with clear definitions of what are product options, and which are not. The information system challenge is to provide a single master BOM and a mechanism whereby each functional area t h a t uses a BOM can "view" the common product structure to suit their own needs. In addition, controls must be in place to ensure t h a t these departmental views are consistent with the original design engineering intent. It is clear t h a t developing world-class engineering control systems is not a simple matter, even at a level so basic as the BOM. For example, products with more than ten components, and manufacturing facilities t h a t produce over 100 different products are very common. Therefore, with hundreds of components being used in any of several different products, it is easy to understand why inventory management is very complex. Mathematical assistance is provided from the fields of operations research and management science (OR/MS) in the form of traditional inventory minimization algorithms and simulation models. But these are not complete answers. The point is simply this, product proliferation exacerbates the inventory management problem. Tight control of the bill of material is absolutely necessary to prevent complexity growth in inventory optimization systems. With simpler systems, analytical solutions are possible, with overly complex systems they are oRen impossible. Currently, the software and hardware complexities have become such that many small- and medium-size companies have abdicated their responsibility for solving these manufacturing information technology problems by handing them over to third parties, so-calledC I M developers, in spite of the drawbacks pointed out above. Although this works well for some companies, it is not a desirable solution for all companies. ~ h l ~ m m With ~ x i ~ i n f Product ('~ntrol Sv~ffin~ One of the most important reasons for difficulty in real world assembled produc t configuration control is that traditional management information systems (MIS) to support manufacturing enterprises were developed primarily by data processing professionals and not business managers or engineers. Compounding this problem is the fact that MIS is usually separated technologically both in hardware and software from engineering information systems (EIS). With the development of improved communications technologies, the linkage between these two worlds is now feasible, and in many cases a reality, but the logical designs of MIS and EIS are not often architecturally compatible. This exacerbates the current practice of throwing engineering BOMs over the wall to the MIS for subsequent business control purposes. This inhibits consistent and accurate updating of engineering drawings and BOMs as they could be with a rigorous (but expensive) APCCIS. To make matters worse, there is a common problem that widens this gap between engineering and data processing which involves phantom and pseudo bills of material. One argument for having data processing retain control of the master BOM database rather than engineering is due to the very confusing presence of "phantom" and "pseudo" BOMs. A phantom BOM describes a grouping of parts that may or may not be assembled into a product, and if they are, they are accounted for by exception in a manufacturing resource planning system (e.g., MRP II). A pseudo BOM is a group of parts that cannot be manufactured and exists either to simplify BOM maintenance or to assist in master production scheduling. According to Garwood [1], "A phantom is an item t h a t is not normally (or maybe never) produced and put into the stockroom (i.e., inventoried)." Tersine states [4], "Pseudo bills of material are imaginary components which are never actually produced. They sometimes are called 'phantom bills,' 'super bills,' or 'S bills.'" The term "phantom" was first introduced as part numbers and BOMs t h a t were put into MRP II systems. Exploded requirements had to be netted against residual inventory of the phantoms. Also, the engineering bills included some subassemblies manufacturing never made. In order to combine the engineering and manufacturing bills into a common file, those subassemblies were also added as phantoms. The t e r m "pseudo" was introduced when forecasting and master scheduling options created the need to identify groups of parts common to the product or an option, although the parts group could not be physically constructed. Whether or not an item could be assembled, it is treated the same by the data processing system. There is no clear distinction between phantoms and pseudo BOMs with regard to how they are handled internally by data processing. Both of these artifacts are just as confusing to engineers as they are to data processing specialists but they must be dealt with. The important question is, "Does the existence of phantom and pseudo BOMs justify the retention of master BOM control by data processing specialists?" I think not. The perceived need for data processing workarounds does not justify modifications to engineering design documentation. It seems reasonable to me t h a t if our research eventually proves that the need for phantoms and pseudo BOMs is in fact justified to improve data processing, then they should also be able to be created as views or schema from an en~neerin~-controlled BOMIS, just as quick decks and other BOM subsets can be created for use by purchasing, sales, etc. pOR~TR!.R Ri"ILI[J'~ON'R AND RIlPJ~.]RCH DlrREC'I"ION
Even so-called computer integrated manufacturing CIM has not come to grips with the logical separation of design engineering from MIS. In fact, there are three adverse impacts on manufacturing systems engineering (MSE) that stem from early efforts to integrate engineering and management Rmctions through projects loosely labeled as CIM: (1) the central information administration and control systems were developed by MIS professionals who had CAIE 21:1-4-P
232
Proceedings of the 13th Annual Conference on Computers ~ihd Industrial Engineering
little knowledge of MSE needs, (2) communication between MIS and EIS was designed to require engineers to time-
share central MIS computing facilities, and (3) centrally controlled MIS provided very little flexibility for engineering innovation and change. Attempts to increase MSE flexibility through the use of engineering work stations and increasingly powerful personal computers created a different set of problems: (1) software for EIS was written in different programming langnages than software written for MIS, thus increasing incompatibilities between MIS and EIS, (2) communications between the EIS and MIS became more complex and difficult to handle, (3) the new EIS were tied to automated factory equipment through new communication protocols such as MAP, which increased engineering information complexity even further, and (4) a new body of supporting information and systems was developed to help MIS and EIS personnel deal with the growing technical complexity of total enterprise information systems. The same technology that is causing the inventory control problems described above, can also provide a solution. W e hypothesize that certain package soRware currently becoming available for desktop personal computers is sufficiently powerful to be able to help manufacturers regain control of some of their criticalengineering information systems by using engineering paraprofessionals rather than M I S systems programmers, "gurus," or 'Rackers." One reason that this problem must be solved is that highly-skilledM I S software and systems personnel who also understand increasingly complex manufacturing technologies are a critically scarce resource, and medium- to small-sized manufacturer cannot hire them, almost at any price. Therefore, we have focused our research on trying to simplify at least one criticaldimension of this information technology problem, while simultaneously increasing its usefulness to a variety of users. Specifically,we are striving to simplify for engineers the development and maintenance of the complex B O M information system using "paraprogramming" techniques. W e are at the same time trying to increase B O M flexibility,availability,and usefulness to all other enterprise functions that require views of them to accomplish their work, e.g., management, marketing, sales, manufacturing, purchasing, etc. All of this work is being accomplished in keeping with manufacturing systems engineering global competitive standards. In this regard, we are undertaking a pilot project to evaluate the amount of complexity reduction and increase of management control of complex B O M creation, modification, analysis, and distribution functions. Project steps include: (1) development of master B O M s using a desktop database management system (DBMS), (2) production of indented, where is, sales, quick deck, and other forms of the B O M s for various functional areas, (3) transfer of accounting and costing version(s) of bill of material to appropriate business system software modules, (4) analysis of BOMs for cost reduction and part optimization purposes using compatible desktop spreadsheet software, and (5) through all of the above, maximize the use of fourth generation programming languages and techniques, such as macros, procedures, etc. to evaluate where hard coding can be saved in comparable conventional DBMSs. It is hypothesized t h a t this prevalent concurrent engineering problem can be solved through a bill of material information system (BOMIS) that is designed by database specialists, but kept up-to-date and controlled by product design engineers who have been trained as paraprogrammers. The database control mechanisms should be PC-based and easily modifiable by engineers using application programming languages. Various schema or "views" from the BOMIS database should be available on-line for use by the enterprise manufacturing and business information systems, to include all material resource and manufacturing production planning operations. Tranmmrent ][~)M R n r l ~ d a ~ t o - l ~ t A t m ~
Tntm.~. f~ l~ncln~m
A t r a n s p a r e n t spreadsheet-to-database interface can facilitate engineering analyses of BOM status and historical data. If a direct spreadsheet-to-database communication link is made available to engineers, data will be more readily accessible, information stored in the database will be more useful, and resulting analytical solutions more timely and valuable. Therefore, what is urgently needed by all engineers is easy interactive access to a wide variety of manufacturing enterprise databases, in addition to BOM files, while retaining the familiar interface of a general-purpose spreadsheet of their choice. For example, if such an easy-to-use system were available, a design engineer could use group technology to quickly identify commonly used fasteners from stored master BOM data for 20,000 different assembled products and thereby be able to simplify not only product designs, but manufacturing and material management processes as well. Today's technology forces the engineer to attack this problem in a stepwise fashion. He or she must formulate specific queries of a MIS-controlled BOM database using their procedural languages. The outputs are normally rekeyed into a spreadsheet in order to solve the analytical problem using t h a t spreadsheet's command language. Fortunately, new personal computer software technologies have recently made it possible for easy-to-use interfaces to be developed with some extra work employing paraprogramming. One of the leaders in promoting easier interfaces such as those needed by engineers is Borland International Inc. They have developed near-transparent interfaces between their PC-based Quattro Pro spreadsheet and their Paradox database, plus providing additional linkages to PC, mini, and mainframe databases through commanly-ueed communication software products. Borland's progress represents a giant step toward providing engineers with the t r a n s p a r e n t interfaces they need. However, the benefits of these new physical software and communication capabilities also have a cost t h a t can exceed their purchase price. This added cost involves the education and training expense to help engineers learn how to use the new systems effectively. This is often a price t h a t few engineers are willing to pay. The central problem is caused by the necessary complexity of the seRware. ~ m m i , ~ .
The Key to ~ h l ~
In order to take the next logical (and final) step to develop the desired transparent interface between spreadsheet
Manley: "Paraprogramming" Manufacturing Information Systems and a variety of databases, we need to reduce their level of operational complexity through
233 '
. The
specific objective is to eliminate the necessity for engineers to learn how manipulate the integrated spreadsheetdatabase system through increasingly complex command-language programming. Unaided, an engineer must learn how to master Quattro Pro's three distinct ways of accessing database information: (1) direct file access, (2) access through structured queries from within the spreadsheet, and (3) access through hot links with the Paradox database. From another perspective, we find that Lotus Development Corp. offers a product called DataLens which allows direct querying of Structured Query Language (SQL) databases from within a Lotus 1-2-3 spreadsheet. Thus, some engineers prefer Lotus they must learn its procedures, whereas if other engineers prefer Quattro they will find that spreadsheet requires an additional product, SQL Link, to help Paradox work with SQL queries. Companies could standardize across the enterprise on one vendor's product and make all engineers learn to use it, or they can provide a uniform, transparent interface for g,~ engineers which make the linking of their favorite spreadsheets to any and all databases totally transparent. This can be accomplished by experts who develop macros and procedures that simplify spreadsheet-to-database interfaces. Engineers will then be able to operate these systems through relatively simple paraprogramming commands, rather than through complex system languages. Our four-phase research project will hopefully be completed in the coming year. The plan of attack involves the analysis of a pilot company's existing bill of material information system documentation to determine its direct impact on accounting, engineering, manufacturing, materials planning, and sales functions. This will be followed by developing a paraprogrammed prototype master BOMIS from a comprehensive product sample using the Paradox 3.5 relational database management system and the QuattroPro 2.0 spreadsheet installed on an IBM 386 PC. From the master BOM we plan to derive a family of generic BOMs that satisfy accounting, materials, manufacturing, and sales BOM requirements at the company headquarters and one or more of its manufacturin~ vlants. The pilot system will be validated and if successful, continue on with another project to implement the system, and possibly work with industry in the future to commercialize the paraprogramming solution. SIrYnVLdLRY
The objective of our research is to determine the feasibilityof using a PC-based, full-featured relational database management system to assist small- to medium-size company engineering departments in planning, designing, and implementing an improved engineering master bill of material information system (BOMIS). The objective of the master BOMIS is to provide tight central engineering control of all company product specifications,while simultaneously providing improved product technical information to an existing management information system (MIS) in order to solve generic problems with: (1) order generation, (2) product costing, (3) materials planning, (4) manufacturing planning, (5) sales catalog preparation and maintenance, and (6) product pricing. The suggested approach will take advantage of state-of-art, PC-based, relational, database management sol,ware that links easily to state-of-art spreadsheet programs, and to minicomputer-based corporate database management systems, and also mini- or mainframe-based corporate accounting systems. The approach involves programming primarily in powerful application and command languages, rather than third generation languages, while simultaneously providing the capability for calling subroutines written in the commonly used state-of-practice engineering programming languages C and Pascal. Expert assistance will be provided to manufacturing engineers to help them easily use and maintain integrated spreadsheet and database management systems. The research team will be performing a detailed analysis of a pilot company's current product coding system for developing bills of material with respect to its advantages and disadvantages to the company. A new master bill of material system will be designed for use on an IBM 386 PC that will capture all advantages of the existing company system, but eliminate the disadvantages, and wherever possible add improvements not currently possible. The new system implementation plan will ensure that the current MIS will not be disrupted while adding new and improved capabilities for accounting, engineering, manufacturing, and sales. This research approach will make every attempt to provide companies with more accurate, powerful, and timely bill of material information systems. The primary result is aimed at lowering direct product information system processing costs, with further indirect cost reductions in accounting, engineering, manufacturing, and sales. ]~gFERENCE$ 1. Garwood, Dave, Bill of Material: Structured for Excellence, Dogwood Publishing Company, Inc., Marietta, Georgia, 1988. 2. Hunt, Rick, Petros Mantzouridis, Keith Sheard, "Simulation and Modeling," MSEP Working Paper, University of Pittsburgh, Dec 8, 1990, pp. 19-20. 3. Mather, H., Bills of Materials, Recipes & Formulation, Wright Publishing Company, Inc., Atlanta, Georgia, 1982. 4. Tersine, Richard J., Principles of Inventory and Materials Management, 3rd Edition, Elsevier Science Publishing Co., Inc., New York, 1988. 5. Wittry, Eugene J. Managing Information Systems: An Integrated Approach, Society of Manufacturing Engineers, Dearborn, Michigan, 1987.
0360-8352/91 $3.00+ 0.00 Copyright © 1991 Pergamon Press pie
"PARAPROGRAMMING" MANUFACTURING INFORMATION SYSTEMS
John H. Manley Industrial Engineering and Manufacturing Systems Engineering University of Pittsburgh Pittsburgh, Pennsylvania
AnS~CT Computer integrated manufacturing (CIM) is creating unexpected problems for a growing number of manufacturing companies. Manufacturers are finding it especially difficultto attract programmers who are both willing and able to develop the highly complex software that integrates existing accounting, sales, production, engineering, and quality control information subsystems. Consequently, m a n y companies abdicate their responsibility for manufacturing information systems and seek third party support ranging from consulting assistance to a total takeover of the company's information resources and operations. Companies that "give away" their internal information system capabilities to third parties will ultimately lose control of their enterprise information, a danger to be avoided. Off-the-shelf software for desktop computers has become sufficientlypowerful to help solve a major portion of this serious problem. W e hypothesize that manufacturing engineers (and others) can be trained to use packaged software to leverage their company's systems programming capabilities.In effect they would become "paraprogrammers" who would help design, develop, and maintain manufacturing information systems. This new type of professional would not require a computer science or similar educational background, but could be trained to satisfy m a n y specialized programming needs in a manner similar to how paramedics and paralegals are trained and used in the medical and legal professions, respectively. This paper reports on the early stages of research to determine whether or not product design engineers can use a desktop relational database management system and its various c o m m a n d languages to develop a master bill of material information system (BOMIS). The purpose of the research is to evaluate the amount of programming complexity reduction and increased operational effectiveness that can be achieved through paraprogramming by manufacturing engineers.
II~gDD.ILK~D2i A manufacturing company must have the flexibilityto respond to rapidly changing technology and market conditions in today's fiercely competitive global environment . A company must offer diverse products which can be manufactured quickly to satisfy customer demand. At the same time, both product lifecycle and inventory must be minimized. Factory automation and robust, integrated information systems are required to help satisfy these difficultrequirements. Until recently, the generally-accepted model of a new, manufactured-product development life cycle has been sequential, starting with design engineering and ending with production. The flow of information between stages of this process have been characterized as a "waterfall,"or "throwing it over the wall." Now, a more effective, simultaneous or concurrent engineering, parallel, life cycle model is being used wherein players from marketing, sales, design, manufacturing, quality control, etc. work together in teams to "carry products over the walls together." The Department of Defense describes concurrent engineering as the engineering of the product and process such t h a t a better understood product requirement and concomitant process variability reduction as a t h r u s t from the start can lead to the elimination of inspections when such variability is reduced to low enough levels. The goal is a drastic reduction of time, money, people and other resources. The integrated team approach is a critical element of concurrent engineering, in contrast to the traditional linear and departmental approach to design and manufacturing engineering. Thus, the concurrent engineering process insists t h a t all barriers to the effective exchange of information and people between functional groups in the organization be minimized. Concurrent W . n ~ i n e e r l n f Tmnlenm.ntstinn Problem A serious problem can occur that defeats the purpose of concurrent engineering after product development has been completed and routine production begins. This problem is most likely to occur in assembly manufacturing where modifications to a product requested by marketing or sales can be accommodated by manufacturing without requiring major changes to the assembly line. This situation is most prevalent in industries t h a t supply a wide variety of custom-designed parts and assemblies for use by end-product manufactures, or construction companies. In fenestration product manufacturing, for example, new window designs are engineered, tooling set up, procedures for assembly determined, a manufacturing process put in place, and production begun to satisfy repeat custemer orders. Since windows are made in many different sizes, colors, shapes, etc., customer needs vary from dayto-day, month-to-month, and year-to-year. For each new design, the concurrent engineering process is employed to include adding new models to the sales catalog. However, when customers tell a salesman t h a t they only want a "small" change to an existing catalog product, e.g.,reduce the width of a window by 1/2 inch, a serious information system problem can occur. This can happen if the sales department short circuits the concurrent engineering pro229
230
Proceedings of the 13th Annual Conference on Computers and Industrial Engineering
cess and asks the manufacturing manager directly whether or not he can produce this modified product. Since the answer to the question is invariably positive, a "special order" is entered into the business information system, the new product is manufactured, delivered to the customer, and all seems well. But is it? What has really happened is t h a t design engineering has been left out of a modified product development project. This causes a negative ripple effect on the enterprise manufacturing information systems t h a t encourages waste and, most importantly increases operating costs. When this common practice occurs, and tens of thousands of "specials" are entered into the corporate database, excessive costs and related problems occur for the following reasons: • Optimized parts cutting from standard stock is invalidated due to "minor" changes made on the shop floor without the knowledge of design and industrial engineering, thus invalidating important scrap loss information. • Product cost information based on approved bills of materials can only be approximated, thus making cost estimation and materials ordering imprecise and more difficultto accomplish. • In extreme cases, the coding schemes for products are not able to handle the large number of "special" products, essentially invalidating m a n y business software modules, especially if they were "hard coded." Confunnmtlon Control as a Possible Solution Assembled products from the aerospace, defense, and nuclear industries require rigid control due to their potentially hazardous nature if manufactured incorrectly. In these industries, product configuration control is, in effect, a specialized information system which can trace the evolution of the minutest detail of every component part of every product throughout its life cycle, from conceptual design, through product withdrawal from service and disposal. An assembled product configuration control information system (APCCIS) contains unambiguous part numbers, design drawings, and bills of m a t e r i a l (BOM) as its key information elements. Configuration control systems include people who make engineering design change decisions, often facilitated by teams possessing change approval authority called configuration control boards. For critical products configuration control boards are often necessary, but they are also costly to operate. In essence, assembled product configuration control entails strict control of any proposed change to an existing product from its initial design through disposal. The military approach to such control is not recommended for small- to medium-sized commercial manufacturers of non-life-critical products. On the other hand, all companies need an inexpensive but also effective configuration control system t h a t will help prevent the problems described above. A PC-based bill of material information system (BOMIS) can serve this purpose when coupled with the business information system t h a t exists in even the smallest manufacturing company. By supporting an effective engineering design process with a well-designed BOMIS, an effective APCCIS can be constructed. However, the challenge in accomplishing this involves improving the existing mini- or mainframe-based BOMIS, which is generally ineffective for several important reasons. To understand why this is so, let us first examine the BOM. BILL OF M A ' r I ~ J A L CONSIDERATIQ~N,q The BOM is the vehicle by which the structure of a product is communicated throughout the manufacturing organization. From an engineering control perspective, this document is critical to the enterprise. Since it is originated by design engineers, it is considered to be a key input into all engineered product manufacturing control systems. In addition to product, manufacturing, and systems engineering, the BOM is also used as key input data to a wide variety of enterprise functions such as: (1) production planning, (2) material planning, (3) research and development, (4) accounting, (5) marketing and sales, and (6) business management. Tvnes of Bills of Material Although there should logicallybe only be one unique B O M for each product, at least five variations exist [5]: Master Bill of Material: A structured, level-by-levellistingof all of the components of a product, including part number, part name, quantity used, and a linkage from each level of the assembly to subassemblies and components. Original master B O M s are produced by design engineers, but subsets (views or schemas) are used throughout the manufacturing enterprise. Material List: This is a simple listing of the components of a product, without any intermediate levels of assembly, e.g., a cake recipe. This represents one "view" of the master B O M and is used by: (1) production to assemble a portion of a product, e.g.,subassemblies, (2) by accounting to calculate the material cost of a product, and (3) by sales to identify options which can be ordered with the basic product. Quick Deck: This is a summary of all quantities of each component part used in an assembled product. It is a material list of the total product without any reference to intermediate levels of assembly, and is used for quick calculation of material requirements. Materials planning uses the quick deck to calculate the quantity of components required to build a scheduled quantity of product. Where Used: This is an upside-down B O M . It lists all of the next level assemblies and subassemblies in which a component or subassembly is used, including quantities of the component in each next level. Design engineering uses the where-used B O M to determine all usages of that portion of a product which is affefted by a reported failure of a specificcomponent in order to improve product quality through redesign. Top-Level Where Used: This is a list of all of the products (top level assemblies) in which a component or subassembly is used. Design engineering uses the top-levelwhere used B O M to determine all usages of a part which is being considered for change. In short, different users of a single master BOM need different versions (views or schemas). Design engineering finds it most convenient to describe a product in terms of bills which are organized by function. Assembly finds it more convenient to use material lists which represent the sequence in which the components are physically assembled. Materials planning BOMs generally only need to include those components which are purchased or in-
Manley: "Paraprogramming" Manufacturing Information Systems
231
ternally manufactured. Accounting needs the same bill of material as materials planning. However, if materials planning used a quick deck, it would not serve the needs of accounting to calculate the cost of product subassemblies. Sales needs a material listwhich includes the options which can be ordered with the product. TnvAntm.v ~ n ~
~R;l~nm~
Traditional manufacturing information systems usually provide one complete engineering BOM, but often not one than can be easily manipulated to produce the subsets or views described above. This inflexibility creates many irritating information system problems, such as not being able to provide manufacturing with an easily-understood assembly sequence, or sales with clear definitions of what are product options, and which are not. The information system challenge is to provide a single master BOM and a mechanism whereby each functional area t h a t uses a BOM can "view" the common product structure to suit their own needs. In addition, controls must be in place to ensure t h a t these departmental views are consistent with the original design engineering intent. It is clear t h a t developing world-class engineering control systems is not a simple matter, even at a level so basic as the BOM. For example, products with more than ten components, and manufacturing facilities t h a t produce over 100 different products are very common. Therefore, with hundreds of components being used in any of several different products, it is easy to understand why inventory management is very complex. Mathematical assistance is provided from the fields of operations research and management science (OR/MS) in the form of traditional inventory minimization algorithms and simulation models. But these are not complete answers. The point is simply this, product proliferation exacerbates the inventory management problem. Tight control of the bill of material is absolutely necessary to prevent complexity growth in inventory optimization systems. With simpler systems, analytical solutions are possible, with overly complex systems they are oRen impossible. Currently, the software and hardware complexities have become such that many small- and medium-size companies have abdicated their responsibility for solving these manufacturing information technology problems by handing them over to third parties, so-calledC I M developers, in spite of the drawbacks pointed out above. Although this works well for some companies, it is not a desirable solution for all companies. ~ h l ~ m m With ~ x i ~ i n f Product ('~ntrol Sv~ffin~ One of the most important reasons for difficulty in real world assembled produc t configuration control is that traditional management information systems (MIS) to support manufacturing enterprises were developed primarily by data processing professionals and not business managers or engineers. Compounding this problem is the fact that MIS is usually separated technologically both in hardware and software from engineering information systems (EIS). With the development of improved communications technologies, the linkage between these two worlds is now feasible, and in many cases a reality, but the logical designs of MIS and EIS are not often architecturally compatible. This exacerbates the current practice of throwing engineering BOMs over the wall to the MIS for subsequent business control purposes. This inhibits consistent and accurate updating of engineering drawings and BOMs as they could be with a rigorous (but expensive) APCCIS. To make matters worse, there is a common problem that widens this gap between engineering and data processing which involves phantom and pseudo bills of material. One argument for having data processing retain control of the master BOM database rather than engineering is due to the very confusing presence of "phantom" and "pseudo" BOMs. A phantom BOM describes a grouping of parts that may or may not be assembled into a product, and if they are, they are accounted for by exception in a manufacturing resource planning system (e.g., MRP II). A pseudo BOM is a group of parts that cannot be manufactured and exists either to simplify BOM maintenance or to assist in master production scheduling. According to Garwood [1], "A phantom is an item t h a t is not normally (or maybe never) produced and put into the stockroom (i.e., inventoried)." Tersine states [4], "Pseudo bills of material are imaginary components which are never actually produced. They sometimes are called 'phantom bills,' 'super bills,' or 'S bills.'" The term "phantom" was first introduced as part numbers and BOMs t h a t were put into MRP II systems. Exploded requirements had to be netted against residual inventory of the phantoms. Also, the engineering bills included some subassemblies manufacturing never made. In order to combine the engineering and manufacturing bills into a common file, those subassemblies were also added as phantoms. The t e r m "pseudo" was introduced when forecasting and master scheduling options created the need to identify groups of parts common to the product or an option, although the parts group could not be physically constructed. Whether or not an item could be assembled, it is treated the same by the data processing system. There is no clear distinction between phantoms and pseudo BOMs with regard to how they are handled internally by data processing. Both of these artifacts are just as confusing to engineers as they are to data processing specialists but they must be dealt with. The important question is, "Does the existence of phantom and pseudo BOMs justify the retention of master BOM control by data processing specialists?" I think not. The perceived need for data processing workarounds does not justify modifications to engineering design documentation. It seems reasonable to me t h a t if our research eventually proves that the need for phantoms and pseudo BOMs is in fact justified to improve data processing, then they should also be able to be created as views or schema from an en~neerin~-controlled BOMIS, just as quick decks and other BOM subsets can be created for use by purchasing, sales, etc. pOR~TR!.R Ri"ILI[J'~ON'R AND RIlPJ~.]RCH DlrREC'I"ION
Even so-called computer integrated manufacturing CIM has not come to grips with the logical separation of design engineering from MIS. In fact, there are three adverse impacts on manufacturing systems engineering (MSE) that stem from early efforts to integrate engineering and management Rmctions through projects loosely labeled as CIM: (1) the central information administration and control systems were developed by MIS professionals who had CAIE 21:1-4-P
232
Proceedings of the 13th Annual Conference on Computers ~ihd Industrial Engineering
little knowledge of MSE needs, (2) communication between MIS and EIS was designed to require engineers to time-
share central MIS computing facilities, and (3) centrally controlled MIS provided very little flexibility for engineering innovation and change. Attempts to increase MSE flexibility through the use of engineering work stations and increasingly powerful personal computers created a different set of problems: (1) software for EIS was written in different programming langnages than software written for MIS, thus increasing incompatibilities between MIS and EIS, (2) communications between the EIS and MIS became more complex and difficult to handle, (3) the new EIS were tied to automated factory equipment through new communication protocols such as MAP, which increased engineering information complexity even further, and (4) a new body of supporting information and systems was developed to help MIS and EIS personnel deal with the growing technical complexity of total enterprise information systems. The same technology that is causing the inventory control problems described above, can also provide a solution. W e hypothesize that certain package soRware currently becoming available for desktop personal computers is sufficiently powerful to be able to help manufacturers regain control of some of their criticalengineering information systems by using engineering paraprofessionals rather than M I S systems programmers, "gurus," or 'Rackers." One reason that this problem must be solved is that highly-skilledM I S software and systems personnel who also understand increasingly complex manufacturing technologies are a critically scarce resource, and medium- to small-sized manufacturer cannot hire them, almost at any price. Therefore, we have focused our research on trying to simplify at least one criticaldimension of this information technology problem, while simultaneously increasing its usefulness to a variety of users. Specifically,we are striving to simplify for engineers the development and maintenance of the complex B O M information system using "paraprogramming" techniques. W e are at the same time trying to increase B O M flexibility,availability,and usefulness to all other enterprise functions that require views of them to accomplish their work, e.g., management, marketing, sales, manufacturing, purchasing, etc. All of this work is being accomplished in keeping with manufacturing systems engineering global competitive standards. In this regard, we are undertaking a pilot project to evaluate the amount of complexity reduction and increase of management control of complex B O M creation, modification, analysis, and distribution functions. Project steps include: (1) development of master B O M s using a desktop database management system (DBMS), (2) production of indented, where is, sales, quick deck, and other forms of the B O M s for various functional areas, (3) transfer of accounting and costing version(s) of bill of material to appropriate business system software modules, (4) analysis of BOMs for cost reduction and part optimization purposes using compatible desktop spreadsheet software, and (5) through all of the above, maximize the use of fourth generation programming languages and techniques, such as macros, procedures, etc. to evaluate where hard coding can be saved in comparable conventional DBMSs. It is hypothesized t h a t this prevalent concurrent engineering problem can be solved through a bill of material information system (BOMIS) that is designed by database specialists, but kept up-to-date and controlled by product design engineers who have been trained as paraprogrammers. The database control mechanisms should be PC-based and easily modifiable by engineers using application programming languages. Various schema or "views" from the BOMIS database should be available on-line for use by the enterprise manufacturing and business information systems, to include all material resource and manufacturing production planning operations. Tranmmrent ][~)M R n r l ~ d a ~ t o - l ~ t A t m ~
Tntm.~. f~ l~ncln~m
A t r a n s p a r e n t spreadsheet-to-database interface can facilitate engineering analyses of BOM status and historical data. If a direct spreadsheet-to-database communication link is made available to engineers, data will be more readily accessible, information stored in the database will be more useful, and resulting analytical solutions more timely and valuable. Therefore, what is urgently needed by all engineers is easy interactive access to a wide variety of manufacturing enterprise databases, in addition to BOM files, while retaining the familiar interface of a general-purpose spreadsheet of their choice. For example, if such an easy-to-use system were available, a design engineer could use group technology to quickly identify commonly used fasteners from stored master BOM data for 20,000 different assembled products and thereby be able to simplify not only product designs, but manufacturing and material management processes as well. Today's technology forces the engineer to attack this problem in a stepwise fashion. He or she must formulate specific queries of a MIS-controlled BOM database using their procedural languages. The outputs are normally rekeyed into a spreadsheet in order to solve the analytical problem using t h a t spreadsheet's command language. Fortunately, new personal computer software technologies have recently made it possible for easy-to-use interfaces to be developed with some extra work employing paraprogramming. One of the leaders in promoting easier interfaces such as those needed by engineers is Borland International Inc. They have developed near-transparent interfaces between their PC-based Quattro Pro spreadsheet and their Paradox database, plus providing additional linkages to PC, mini, and mainframe databases through commanly-ueed communication software products. Borland's progress represents a giant step toward providing engineers with the t r a n s p a r e n t interfaces they need. However, the benefits of these new physical software and communication capabilities also have a cost t h a t can exceed their purchase price. This added cost involves the education and training expense to help engineers learn how to use the new systems effectively. This is often a price t h a t few engineers are willing to pay. The central problem is caused by the necessary complexity of the seRware. ~ m m i , ~ .
The Key to ~ h l ~
In order to take the next logical (and final) step to develop the desired transparent interface between spreadsheet
Manley: "Paraprogramming" Manufacturing Information Systems and a variety of databases, we need to reduce their level of operational complexity through
233 '
. The
specific objective is to eliminate the necessity for engineers to learn how manipulate the integrated spreadsheetdatabase system through increasingly complex command-language programming. Unaided, an engineer must learn how to master Quattro Pro's three distinct ways of accessing database information: (1) direct file access, (2) access through structured queries from within the spreadsheet, and (3) access through hot links with the Paradox database. From another perspective, we find that Lotus Development Corp. offers a product called DataLens which allows direct querying of Structured Query Language (SQL) databases from within a Lotus 1-2-3 spreadsheet. Thus, some engineers prefer Lotus they must learn its procedures, whereas if other engineers prefer Quattro they will find that spreadsheet requires an additional product, SQL Link, to help Paradox work with SQL queries. Companies could standardize across the enterprise on one vendor's product and make all engineers learn to use it, or they can provide a uniform, transparent interface for g,~ engineers which make the linking of their favorite spreadsheets to any and all databases totally transparent. This can be accomplished by experts who develop macros and procedures that simplify spreadsheet-to-database interfaces. Engineers will then be able to operate these systems through relatively simple paraprogramming commands, rather than through complex system languages. Our four-phase research project will hopefully be completed in the coming year. The plan of attack involves the analysis of a pilot company's existing bill of material information system documentation to determine its direct impact on accounting, engineering, manufacturing, materials planning, and sales functions. This will be followed by developing a paraprogrammed prototype master BOMIS from a comprehensive product sample using the Paradox 3.5 relational database management system and the QuattroPro 2.0 spreadsheet installed on an IBM 386 PC. From the master BOM we plan to derive a family of generic BOMs that satisfy accounting, materials, manufacturing, and sales BOM requirements at the company headquarters and one or more of its manufacturin~ vlants. The pilot system will be validated and if successful, continue on with another project to implement the system, and possibly work with industry in the future to commercialize the paraprogramming solution. SIrYnVLdLRY
The objective of our research is to determine the feasibilityof using a PC-based, full-featured relational database management system to assist small- to medium-size company engineering departments in planning, designing, and implementing an improved engineering master bill of material information system (BOMIS). The objective of the master BOMIS is to provide tight central engineering control of all company product specifications,while simultaneously providing improved product technical information to an existing management information system (MIS) in order to solve generic problems with: (1) order generation, (2) product costing, (3) materials planning, (4) manufacturing planning, (5) sales catalog preparation and maintenance, and (6) product pricing. The suggested approach will take advantage of state-of-art, PC-based, relational, database management sol,ware that links easily to state-of-art spreadsheet programs, and to minicomputer-based corporate database management systems, and also mini- or mainframe-based corporate accounting systems. The approach involves programming primarily in powerful application and command languages, rather than third generation languages, while simultaneously providing the capability for calling subroutines written in the commonly used state-of-practice engineering programming languages C and Pascal. Expert assistance will be provided to manufacturing engineers to help them easily use and maintain integrated spreadsheet and database management systems. The research team will be performing a detailed analysis of a pilot company's current product coding system for developing bills of material with respect to its advantages and disadvantages to the company. A new master bill of material system will be designed for use on an IBM 386 PC that will capture all advantages of the existing company system, but eliminate the disadvantages, and wherever possible add improvements not currently possible. The new system implementation plan will ensure that the current MIS will not be disrupted while adding new and improved capabilities for accounting, engineering, manufacturing, and sales. This research approach will make every attempt to provide companies with more accurate, powerful, and timely bill of material information systems. The primary result is aimed at lowering direct product information system processing costs, with further indirect cost reductions in accounting, engineering, manufacturing, and sales. ]~gFERENCE$ 1. Garwood, Dave, Bill of Material: Structured for Excellence, Dogwood Publishing Company, Inc., Marietta, Georgia, 1988. 2. Hunt, Rick, Petros Mantzouridis, Keith Sheard, "Simulation and Modeling," MSEP Working Paper, University of Pittsburgh, Dec 8, 1990, pp. 19-20. 3. Mather, H., Bills of Materials, Recipes & Formulation, Wright Publishing Company, Inc., Atlanta, Georgia, 1982. 4. Tersine, Richard J., Principles of Inventory and Materials Management, 3rd Edition, Elsevier Science Publishing Co., Inc., New York, 1988. 5. Wittry, Eugene J. Managing Information Systems: An Integrated Approach, Society of Manufacturing Engineers, Dearborn, Michigan, 1987.