Inspection/Testing Lab Database and Web Application Story

OVERVIEW:

PDA upgraded a rapidly aging Microsoft Access database application for a heat treat lab. The application consisted of multiple access databases all used to collect specific criteria on testing and inspection from the clients’ personnel.  The Access application provided a means for manual data collection, file/photo attachments, import features, calculations, and export features. The challenges with the system were the day-to-day administration and troubleshooting, limited security, lack of historical data querying, and inflexible to future enhancements.

SCOPE:

The path that Process and Data Automation recommended was to upgrade the Microsoft Access application to a web-based application with a Microsoft SQL database backend. Moving to a system such as this would allow the client the ability to input data to the system from one common interface through multiple areas of the lab. This would allow the consolidation of extraneous databases into a Microsoft SQL database. Microsoft SQL is a far superior database tool for collection methods, archiving, and querying. When coupled with a web application, the client will have the ability to apply security levels restricting data and reporting access. The web application was developed to provide a powerful interface to the database for querying the data collected. The querying criteria included Part Number, Order Number, Project Type, and Lab Number that was filterable by this criteria and date. The client retained all the required functionality provided by the existing Access application with the new web application including importing, exporting, file/picture storage, etc. and gained functionalities that satisfied their wish list, along with the flexibility to add endless future enhancements.

HOW WE DID IT:  

Process and Data Automation achieved this upgrade using our software development lifecycle. The software lifecycle identifies all aspects of developing new applications for a client. In this case, we were replicating an existing application and adding a plethora of enhancements. The following steps were taken to ensure the clients’ needs were met:

1. Evaluation of the existing system and all functionality to be retained including
   1. Defining all system workspaces
   2. Database inputs/outputs
   3. Current users
   4. IT related components
2. Define where the client wants to go
   1. Security requirements and system user characteristics
   2. Major system capabilities
   3. Major System conditions
   4. System acceptance criteria
3. Development of the new system architecture and front end
   1. GUI development
   2. Database architecture
   3. Work with Client’s IT for server/hardware requirements
4. Testing/Deployment

CLIENT:

Aerospace company with more than 3,000 employees at approximately 12 production sites.

INDICATIONS CLIENT HAD NEED:

• System author has primary responsibilities within the lab that are not software development.
  • Day-to-day administration and troubleshooting is disruptive.
  • Upgrades/enhancements prove cumbersome to execute.
• Microsoft Access database has limitations in flexibility, security, archive-abilities.
• Would like simple connectivity to plant floor equipment such as Wonderware at vacuum furnace and/or Honeywell temper furnace controllers.
• Provide platform for more in-depth historical querying

PROVIDED BY PDA: 

PROJECT HARDWARE & SOFTWARE:

• Server
  • RAID 1 configuration
  • Hot-swappable hard drives
• Microsoft Server OS
• Microsoft SQL Standard
• Windows IIS
• Web-based application

DIGITALIZATION SERVICES:

• Frontend Web Application Design
• Working with client’s IT department
• Working with client and personnel to define system requirements
• Server design of hardware configuration and requirements
• Server Software Installations/Configurations
  • Microsoft SQL
  • Windows IIS
  • Microsoft Server
• System testing to validate software installations and process functionality
• Documentation of flow diagrams, requirements, and evaluations