Case Study

---

By Ryan Gaertner, Shekar Krishnaswamy, and Nipa Patel

AutoSched AP is increasingly being used in the semiconductor industry in the production planning process and to provide insight into critical planning decisions. Due to the complex nature of semiconductor processes, large amounts of data must be collected to build simulation models that are used to evaluate numerous planning and scheduling scenarios. Careful organization of collected model input and output data can accelerate the model creation, analysis, validation, and reporting processes.

Relational databases can be used to collect, store, analyze and report model input and output data. Relevant modeling data can be extracted from Manufacturing Execution Systems (MES), production and planning databases. This data can be organized using database methods and then integrated with the AutoSched AP simulation tool. By organizing data into normalized tables with established relationships, commercial database systems provide the ability to maintain data integrity. These systems also easily accommodate the large volume of data changes that are characteristic of a semiconductor environment. In addition, the database infrastructure incorporates the use of queries and applications to quickly summarize and extract specific data and generate timely and meaningful reports.

This paper proposes a potential database architecture to achieve better use of AutoSched AP as a decision support system. In addition to manufacturing operations and production planning, this integrated system can provide valuable information to other groups such as finance, facilities, equipment productivity teams and material procurement.

Contents
• Introduction
• Use of AutoSched AP
• The modeling database
• Integration with other systems
• Database applications
• Future enhancements
• Conclusion

Introduction
Advanced Micro Devices (AMD) is a high volume manufacturer of microprocessors, non-volatile memory, embedded processors and networking semiconductor devices. These devices are manufactured in wafer fabrication facilities (fabs) whose capital investment is in the order of billions of dollars. The optimal utilization of these facilities is one of the most important objectives for AMD to ensure cost competitive position in the marketplace. This however depends upon a number of factors such as market demand, desired levels of in-process as well as finished goods inventory, current and planned technological capabilities, current and planned resource capacity and, in some cases desired cycle times. The fab's performance is a result of the physical conditions such as work-in-process (WIP), equipment condition, operator skill level and efficiency as well as the operating policies such as scheduling and dispatch, equipment maintenance policies, equipment set up policies, material handling policies, etc.

In addition to the complexity of the factors mentioned above, the semiconductor manufacturing process itself is tremendously complex considering the large number of processing steps, technologies and equipment. The proposed database architecture could substantially improve AMD's use of AutoSched AP for factory planning and process improvement.
Return to Table of Contents

Use of AutoSched AP
Using AutoSimulations' AutoSched AP simulator, semiconductor manufacturers can experiment and test ideas for improvements in the fab, assembly or test facility such as new scheduling rules, product mixes, start rates, etc., in the virtual fab without the expense and impact of trying "what-if" ideas in the real fab.

The use of AutoSched AP can encompass both tactical assessment scenarios and strategic ‘what-if' scenarios. Tactical assessment scenarios focus on the short term, usually in days or weeks, and provide insight to areas requiring a reactive response. The horizon for the strategic scenarios can usually range in the order of months and sometimes years. This can provide information for a proactive response relating to equipment capacity planning, equipment procurement, space planning, planning of generic resources such as reticles, and operator resource balancing.

In both cases, simulation models become a ‘melting pot' for different types of data. These can include process flows, equipment processing rates, resource counts which could include equipment, personnel and generic equipment such as masks and fixtures, equipment reliability data, process quality data, and in some cases even cost information. Therefore, in addition to being an analysis tool, the AutoSched AP model can be viewed as a comprehensive ‘one stop shop' for data that influence factory behavior. While the manufacturing execution system (MES) also contains a great deal of this information, the behavioral aspects of the simulation model have created additional demands for analytical information. In addition to management and fab operations, a new customer base has evolved and comprises of financial planners, space planners, automation specialists and material procurement specialists. As a result, the organized set of data pertaining to AutoSched AP (both input and output) can be considered a ‘decision support system' (DSS). This paper focuses on the data organization aspect of AutoSched AP so that its full effectiveness as DSS is realized.

Factory modelers usually communicate with operations personnel to keep their models up to date. Using the relational database proposed, data residing in various systems can be extracted, formatted and downloaded into the models. This process can be followed for both strategic and tactical uses of the models. In tactical cases, a version of the model can be automatically updated with information such as current date, latest WIP, equipment status, plan wafer starts, plan yields, and products. Model runs can be made using the revised model to support planning and operations for the short-term horizon. The outputs of these runs can also be used for model validation and verification. Typically, finance groups utilize information related to throughputs by tool and operation and activities projections for costing and procurement analysis. The simulation model can also be used to evaluate numerous planning scenarios such as changes in product mix and starts. Changes to operational policies for improvements in manufacturing lines can also be simulated prior to implementation. One scenario evaluation may require several small progressive modifications to the model in order to gain a better understanding of the proposed solution or the problem at hand. Since each scenario can comprise of several versions of the same model, there is a need for version control. Due to the large amount of data in a single model, there is a crucial need for better management and storage of model input and output data to provide timely and accurate responses to the different customer groups.

This can be accomplished using a relational database at any semiconductor manufacturing facility that uses AutoSched AP for analyzing its production performance. AMD has recently developed a similar prototype database in Microsoft Access for this purpose and the following sections describe the architecture and the plan followed for this implementation. While this implementation is by no means complete, a major portion of the architecture has been developed. Areas for future enhancements have also been identified and described.
Return to Table of Contents

The modeling database
Objectives of the Modeling Database
The following were the defined objectives of the database:
Capability to import all AutoSched AP model input and output files into the database,
Capability to extract all input files from database for an AutoSched AP model,
Capability to perform consistency checks on input data
Capability to provide calculations based on input data
Capability to interface with and download information from external systems for data validation purposes
Capability to perform scenario analysis from several models' inputs and outputs
Capability to provide reports from input and output data
In addition to the above listed objectives, some subjective requirements included ease of use for the modelers, minimal amount of user inputs, and maximum flexibility for data imports and exports. The database development was undertaken in several phases as described below.

Database requirements, planning, and design
Definition of database tables
After the objectives were defined, the first step in the project was to design tables that would accurately represent the AutoSched AP model data. Initially, all of the tables matched the layout of the AutoSched AP files because the source data consisted of existing AutoSched AP ‘asd' directory files. The fields in these tables included certain key fields that were required by the database structure. For example, the PART field is required in the PRODUCT_FILES or the ROUTE and STEP fields in the ROUTE_FILES. Table names were derived from the file type options specified in the AutoSched AP options file, so that the existing flexibility in naming input files could still be maintained.

Definition of table keys and indexes
After the data was organized as fields in various tables, the primary keys and indexes were identified for each table. A key in a relational database is a single column or some group of columns that identifies one or more rows in the table. A primary key is the unique identifier for a record. Each of the defined tables has only one primary key. The following diagram (Figure 1) illustrates the initial table layout along with the defined primary keys.

The database structure encompassed all data elements because the database field names and the table layout closely matched the AutoSched AP file types. As a result, no data elements or data tables were missing from the database design. At the same time, the database architecture was consistent with the modelers' understanding of AutoSched AP software. Overall, the AutoSched AP file structure was found to be fairly normalized and provided a good foundation for our design.

Definition of table relationships
A primary advantage of developing a relational database is its inherent ability to establish and use relationships between data tables. A relationship is a logical, meaningful connection between tables. Relationships are established in order to provide efficient data access to data elements which exist in various locations. The table structure in the database (Figure 2) is derived from design rules related to data structure, data integrity and normalization. The organization of some of the fields in various tables was designed to conform to these design rules specified by relational database theory. Three major relationships were identified between all of the tables in the database: 1) one-to-one, 2) one-to-many, 3) many-to-many. According to the relational database design rules, the many-to-many relationships require a third table, called a junction table. The relationship between the original two tables must pass through the junction table. For example, a route name occurs many times in the PRODUCT_FILES and in the ROUTE_FILES, thus a junction table called Routes_Lookup, containing the unique route names from both tables, was created (Figure 3). The junction tables ensure that only one-to-one or one-to-many relationships exist.

Identification of data sources
After the required fields and tables were defined, the next step was the identification of data sources. In this case, the main source of data is the AutoSched AP model. However, our objectives included validating model inputs and outputs using the database and automating model updates with production data. The database also needs to have linkages to shop floor control systems, Real Time Dispatcher™ (RTD) system, production, planning and finance databases. These systems typically provide information relating to tools used at route, operation, down time data, starts plan, WIP, equipment status snapshots, etc. Issues such as means of information extraction and formats were discussed and explored. Wherever possible, automation was considered for data extraction so as to avoid data integrity issues.

Database Implementation
Software selection
The implementation phase started with the selection of the database software. At AMD, the modelers are the target users of the database and use the Windows NT version of AutoSched AP software. Since Microsoft Access was already installed on the modelers' desktop, and given their familiarity with Access, the choice was obvious. Access was thus chosen as the database platform for AutoSched AP.

Data import
Based on the location of the AutoSched AP model provided by the user, the files are imported into the database. First, the options file is imported. All other files as defined by the options file are then imported. The table locations for these files correspond to the file type options specified by AutoSched AP. At AMD, separate AutoSched AP models exist for each manufacturing facility. These models are developed and maintained by the modelers. Even though, uniformity exists in modeling practices, each model is based on the operating characteristics and modeling needs of the respective facility. Thus, each model will have some differences in field definitions and data files that the database must comprehend. A master table containing the AutoSched AP dictionary of all field names and data types is used to determine the field structure. As a result, AutoSched AP's inherent flexibility regarding the quantity of fields and their sequence is no longer a constraint. Shell scripting and Visual Basic (VB) scripts are used to facilitate the import of all AutoSched AP files.

Ensuring data integrity and consistency
The main purpose in establishing and using keys is to provide a means for creating and maintaining relationships in the database. The other fundamental purpose is to facilitate referential integrity, which assures that the data in one table is consistent with its related records in other tables.

Once the data is imported into tables, the integrity of the data must be checked to ensure data consistency. Once the data discrepancies are eliminated, the keys and relationships can be created between tables. Integrity is the set of logical rules placed in the data model. It applies to the fields within each table. Integrity checks provide data consistency by:

• disallowing entries that are not part of the defined set for that field
  
• ensuring correct data format
  
• requiring data entry for specified fields
  
• denying null entries for primary keys and
  
• ensuring uniqueness in primary key, etc.

For example, in the AutoSched AP data model, the PTIME field can contain only numeric data and the PTUNITS field can only contain certain keywords such as ‘sec', ‘min', ‘hr', or ‘day'.

As a result of establishing relationships between tables, any changes to the data in a table may affect other tables that are associated with it. Referential integrity is a prerequisite to establishing relationships between tables and ensures consistency between related tables throughout the database. For example, in the database, when a station having attached calendars is deleted without a corresponding deletion in the calendar attachment file, the records in the attachment files related to the station are referred to as ‘abandoned'. The cascade feature of referential integrity ensures that the database contains no abandoned records and changes the records throughout the tables based on the relationships. The cascade update guarantees that changes or updates made to the table at the highest level are also applied to the corresponding lower level tables. Similarly, the cascade delete feature ensures that deletions made to the table at the highest level will propagate to the corresponding lower level tables. For example, if the name of a station family is changed in the STATIONS_FILE table, then this change is propagated to the corresponding ROUTE_FILES table and the ATTACH_FILES table.

A feature was included in the user interface to check for data integrity and referential integrity errors. This feature will scan all records in the imported tables and identify data or referential integrity violations. These violations are then classified as fatal, warning and primary key violations. The following table illustrates each type of error (Table 1):

Table 1

Error Description	Error Classification
Part A exists in the START_FILES but is not defined in PRODUCT_FILES	Fatal Error (will cause incorrect AutoSched AP output)
Station family KLA1exists in the STATION_FILES but is not used in the ROUTE_FILES	Warning (will not cause any errors, but is an extraneous record and requires model cleanup)
Part A can be defined only once in PRODUCT_FILES	Primary key violation (relationship cannot be established because no unique record exists)

Calculations and reports creation
The input data in the AutoSched AP simulation model contains valuable information dispersed into an assortment of files for modeling purposes. This information is important to the modelers for the purpose of collecting and understanding input data. This information can also be used to generate static calculations relating to average step throughput, theoretical cycle time and ideal cycle time, etc. In addition, valuable information such as allowed tools for a route/operation can be generated based on defined information relating to station exceptions, station certifications and alternate station families. Considering the fact that this information is dispersed over many input files, the necessity for database methods becomes apparent to provide better understanding of the model. The following table (Table 2) presents sample output from the database relating to route, operation, qualified tools and throughput. Table 3 exhibits the route comparison matrix by operation.

Table 2

Route	Operation	Station	Throughput
Route X	160	Z41	25.457
Route Y	200	Z42	21.368

Table 3

Operation	Route X	Route Y
160	Tool 1, Tool 2	Tool 1, Tool 4
200	Tool 2	Tool 3, Tool 5

In addition to the ‘canned' reports, the users can develop and execute queries to view, aggregate, and filter data from many different tables.

Exporting data
Once the data discrepancies are eliminated and relationships are created, the user can easily make changes to the model. With the built-in cascade update and cascade delete functionality, a change in one place is propagated in all related places. These updated input files can then be exported as AutoSched AP compatible text files in an ‘asd' directory, thus creating an updated version of the model.

User Interaction
In order to import an AutoSched AP model into the database, the only input required from the user is the path for the ‘asd' directory and the name of the model. After the user inputs these and presses a button on the interface, the model files are imported into various tables, the junction tables are created, and the discrepancy errors are generated. If no data discrepancies exist, then the user can click another button to create all relationships between tables. Shell scripts, VB scripts, and SQL queries are used in the application to provide these features.
Return to Table of Contents

Integration with other systems
Integrating the database with other systems is essential since the amount of data required by our models is very large and is pulled in from multiple sources. At the same time, the model inputs and outputs stored in the database must be accessible by the systems of other customer groups to promote the timely and accurate transfer of data. The focus of our efforts included the implementation of key linkages between the modeling database and the MES. Future efforts will include linkages to planning, finance and procurement systems.

The MES data can be automatically extracted, formatted and downloaded into the database through the use of mechanisms such as RTD reports, shell scripts, VB scripts, etc. From the MES, WIP, equipment status snapshots, device names and yields can be provided to the database for simulation runs. Since model maintenance can be very time consuming, integration of high volume MES data is beneficial and will free up time for modelers to concentrate on running a greater number of model scenarios instead of formatting and transferring data files.

Integration between the modeling database and planning systems is accomplished by importing plan starts. After the simulation runs, the capacities, outs and cycle times can be pulled back in to the planning systems from the database in order to compare product mix and start scenarios. This reporting ability will give the planning groups a clear picture of how simulation results reflect against forecast generated by the planning system.
Return to Table of Contents

Database applications
The database can be used in many different ways including:

Model maintenance
The model updates can easily be made in the database because of the inherent capability of relational databases to propagate changes to all related tables. The discrepancy checking feature can be used to clean up the model input data and identify areas of missing data.

Data analysis
Implementation of the model data in a database allows for users to easily create queries to filter, aggregate and summarize the data in many different ways. In order to understand the model outputs, often the modeler needs to understand the input data. The database becomes an excellent analysis tool to provide a detailed understanding of the model.

A model can be considered a data warehouse for various types of process flows, equipment processing rates and resource counts. It is essential that the validity of this data is maintained since a large customer base relies on this information. The database can also import data from systems such as MES, RTD, and finance systems and help validate the AutoSched AP data.

Static calculations
Numerous scenarios can be evaluated with horizons in the order of years. In order to narrow the number of what-if scenarios, static capacity modeling can be used. The credible static capacity model scenarios can be simulated to evaluate other fab metrics such as outs and cycle time. Database reports related to the number of tools at a process step and the cumulative step throughput become a starting point for many of these static capacity analyses.

Scenario analysis
Modelers can import input and output data from different versions of an AutoSched AP model. These could represent multiple scenarios. The database can be used to analyze and compare these scenarios.

Communication and data collection tool
The discrepancy checking feature can be very useful to the modeler to identify missing data from the model. The modeler can print out many consolidated reports filtered for specific tool types or process modules. This will assist in the communication and data collection process with the operations personnel. The reports can also be easily tailored to meet requirements from other customers such as finance, space planners, automation, and material procurement.
Return to Table of Contents

Future enhancements
In a capital-intensive industry such as the semiconductor industry, costs need to be continually examined. The information from the modeling database can be used by costing and cost of ownership systems. For example, costing systems utilize rate information such as units per hour by process step and equipment utilization to project tool costs and consumables usage. Having the modeling data automatically integrated with finance systems enables the finance groups to generate faster and more accurate forecasts.

Procurement systems can also benefit from information housed in the modeling database. For example, integrating data such as activity details by step will assist procurement groups in forecasting tool purchases, chemical usage, etc. With the high lead times required for tool acquisitions in the semiconductor industry, this data can be very helpful in planning for equipment delivery and installation. Figure 4 illustrates the integration between the modeling database and various supply-chain systems.
Figure 4
Return to Table of Contents

Conclusion
AutoSched AP can be considered a cohesive ‘decision support' system with the incorporation of efficient data access, data management and data reporting techniques. AMD has taken initial steps towards achieving this goal. AutoSimulations, Inc. and the users of AutoSched AP need to work together to fully realize the benefits of such a system.

Acknowledgments
The authors wish to acknowledge the contributions made by the modeling customers, Ed Cervantes, Vic Palmeri, Keith Pare, Kishore Potti, Partha Ramakrishna, Joerg Weigang and Jeriad Zoghby, for for this prototype system and providing us their valuable feedback.
References
Relational Database Design Class Notes, Productivity Point International.
AutoSched AP 6.2 Manual, AutoSimulations, Inc.

Author biographies
Ryan P. Gaertner is a Systems Analyst with AMD's Operational Modeling Group. He joined the group in 1998 and has been involved with site-wide systems integration and database development relating to planning and modeling. Prior to joining the OM group, he worked as a Business Analyst in the Wafer Fab Strategic Planning Group specializing in long-range fab planning and inventory analysis. Ryan has been working in the semiconductor industry since graduating in 1995 with a B.B.A. in Finance from the University of Texas at Austin.

Shekar Krishnaswamy is a Member of Technical Staff and currently manages the Operational Modeling Group at AMD. He has had extensive experience with modeling and scheduling tools as applied to the Semiconductor industry as well as their integration with Manufacturing Execution Systems. He was also the Project Manager for the implementation of the Real-Time Dispatch (RTD) system at AMD. Prior to AMD, Shekar worked for IBM Microelectronics and also at SEMATECH as an IBM assignee. Shekar has a B.S. in Mechanical Engineering and an M.S. in Industrial Engineering and Operations Research from The University of Massachusetts at Amherst.

Nipa R. Patel is an Operational Modeling Engineer, specializing in factory performance modeling and database development. Prior to working at AMD, she was an assignee at SEMATECH in the Operational Modeling Department. Nipa also works on cost modeling, model integration, and database applications. She holds a B.S. degree in Industrial Engineering from Mississippi State University and a M.E. degree in Systems Engineering from the University of Virginia.

Free Logistics Online Newsletter