The WSU Pam Project

The WSU PAM Project

Klent E. Harkness

All large facilities maintenance organizations must have some means of tracking routine maintenance work, contractor projects, and other costs associated with maintaining the facility. This can be as simple as a paper system for tracking costs and generating work orders, or, as complex as a multi-million dollar computerized maintenance management system (CMMS). Wichita State University (WSU) is no exception and the following document outlines the steps taken by the Physical Plant at WSU to develop a new web based CMMS.

Wichita State University is a state funded institution of higher learning with a student population of about 14,500 students. The facilities for the campus are comprised of about 2.5 million square feet of office, classroom, dormitory, and general use space in 50 buildings. The Physical Plant maintains the campus and is responsible for all aspects of the facilities including utilities. The Plant has 245 workers which are organized into trade units such as electricians, plumbers, HVAC mechanics, carpenters, electronic and computer repair technicians, custodial, and other maintenance and service units.

The Physical Plant must continually track costs associated with maintaining the facilities in order to stay within its budget. Some of these costs are such things as contract projects, in house inventory usage, in house remodel projects, building utility usage, vehicle and equipment preventive maintenance (PM), vehicle fuel usage and custodial product usage. All work preformed by the Physical Plant is typically assigned to a work order as a way of tracking these costs to buildings, equipment, or projects.

The Physical Plant historically generates over 11,000 of these work orders annually, and all material used for maintenance flows though the central warehouse, which conducts over 45,000 parts transactions annually. The only efficient means to handle such large amounts of data, and to give management the ability to quickly generate reports and graphs of the costs, is through a CMMS. Figure 1 shows common CMMS inputs and outputs.

Some common tasks for a CMMS are work order control, or more specifically, generating work orders and tracking labor and material cost to a specific job or piece of equipment. Inventory control, which is insuring material is correctly charged to work orders and includes automatically generating orders for materials based of predetermined criteria. Employee information tracking such as hours spent on jobs to be billed to work orders for salary budgeting. Utility tracking such as electricity, water, and gas costs per building. A CMMS will also automatically schedule preventive maintenance for equipment and vehicles, as well as track all historical maintenance and associated costs preformed on the equipment. In other words, a good CMMS is the heart of any large maintenance organization.

Historically, the WSU Physical Plant has been a leader in the use of computers in facilities management. In 1986, I built a custom CMMS for the department using Dbase4 as the DBMS. The program utilized custom-built user interfaces and a network architecture that allowed all users on the network to access data for reports, generate parts orders, create job estimates, and view historical data on work orders. The system was composed of 70 program modules and 15,000 lines of code. It worked well and was on line for 11 years before its DOS based architecture began showing signs of age.

In 1996, the Physical Plant decided that the old Dbase4 system needed replaced with something windows based. After an extensive review of available systems, a new CMMS was purchased from a contractor we will call CMA . The cost of the new system was $35000.00 and studies demonstrated that the system would pay for itself in 4 years based on office process cost savings. The software package was developed around the Omnis database engine which has proven to be stable and reliable as far as the underlying architecture is concerned. However, from the very beginning, problems with the CMA software interface caused users to be very dissatisfied with its performance.

One of the main complaints about CMA was that access to the data was very slow over the 10 megabit Ethernet network. The speed of the network degraded significantly due to the size of the software and data files which had to be moved over each connection to the client machines.

At startup the program file was loaded from the server to the client machines and would take over 60 seconds to load, leaving the users with nothing more than a grey screen to look at as the program launched. Then, as data was requested, the 100-megabyte data file was very slow to process on the client’s machine. This caused a great deal of dissatisfaction for users who sometimes would simple reboot their machines rather than wait for the data, which caused the database to corrupt.

While planning for the CMA system, users other than admin workers were supposed to log in, get the data they needed, and then log out of the system. This would continually keep a pool of seats available for other users to use. Thus, the Physical Plant only purchased fifteen CMA seats at $1,500.00 a seat, which in theory, should have been enough for the system to work. However, due to the slow speed of the data access rather than logging in and getting the needed information, then logging back out, users would log in and then leave the connection live on their machine. This way, they did not have to go through the slow start up process again. After enough users tied up connections, all available seats would be in use and new users would be locked out of the system until a connection was made available.

The most serious problem however, was with the CMA front-end software. Warehouse transactions and other work order data was not reliably being posted to work orders as required. After observing the problems, plant workers developed work around office procedures to make the system function, but it was believed that CMA should solve the problems. The Physical Plant had an annual maintenance agreement with CMA, which granted us access to all new releases of the software. We worked closely with CMA staff and WSU was a Beta site for all new revisions of the workgroup software. Unfortunately, after numerous releases of the software many of the errors first seen at start up were never completely resolved after 3 years.

Other issues that made the CMA product unsatisfactory was that by being an off the shelf “one size fits all” software product a full 60% of the system was not utilized. CMA was originally developed for manufacturing facilities so most of functionality had little to do with tracking costs in a government institution. It of course could be customized for WSU use, but the costs associated with customization made that option prohibitive.

The final major problem with CMA was that reports were difficult to produce from the built in report system and there was virtually no way to write custom reports from within CMA itself. One work around for the problem was to build an ODBC connection from Microsoft Access to CMA. Once we had the data we needed imported into Access we could then write the reports we needed with the report system built into Access. This solution worked very well but each report was very labor intensive for the IS department to produce.

At the time the Physical Plant made the selection of the CMA system, CMA was the best and lowest cost CMMS on the market. However, in the late 1990’s CMMS and computer technology in general began rapidly changing. Web based applications began being introduced into the CMMS market which could solve many of the problems we were experiencing with CMA, as well as provide more enhanced capabilities that are normally associated with web based applications. CMA released a web-based version of their product a year after WSU purchased the workgroup version. As part of the web based service CMA would host our data on their server in Tulsa and users would connect to it over the Internet.

In 2001, it was decided to begin evaluating options for WSU’s third generation of CMMS. I conducted interviews with department heads and staff to get input on system functionality and features that would need to be requirements in the next system. Items identified were:

1. Faster Access Speeds than those seen with CMA

2. More seats available to users

3. Ability to customize data fields and screens

4. Ability to easily generate custom reports and graphs

5. Easy administration

With these general requirements as well as a well-defined set of base criteria that a CMMS must fulfill for our department, it was time to decide on how to proceed with the project.

Effectively, there where two paths to explore for the new system. Path 1 involved looking at the current state of technology and costs of vendor provided software, and determine if this was a valid option for WSU. Path 2 was to research the feasibility and costs of developing the CMMS in house. My task was to develop a plan to get the Physical Plant technology that would meet their needs, at the lowest possible cost.

I conducted a survey of CMMS developers and vendors to determine capabilities and costs for purchasing another system. Six different vendors were considered and their product costs and functionality were compared. Based on WSU criteria, CMA once again was the lowest cost system with the features WSU required.

CMA offered two options for us to consider. CMA option 1 consisted of upgrading our existing workgroup system to the CMA Oracle product. The cost would be $55,000.00 for the CMA software, and $30,000.00 for a single site Oracle license. CMA also required a $7,000.00 a year maintenance agreement for the product. This option would allow us to utilize built-in Oracle tools to access our data and develop reports as needed. It would also allow us to have unlimited seats for users and a reliable server based database engine that would be very fast under heavy loads. Total cost including the first year maintenance agreement and implementation assistance, $92,000.00.

CMA option 2 was to move to their web based application. They would host our data on their server in Tulsa and we would access it though the Internet. The advantage to this is that software updates would automatically be applied to the system as they become available and CMA was responsible for maintaining the servers and data backups. The cost for this option was a $10,000.00 setup fee and $850.00 a month for the duration of the contract.

Nether of these options would allow WSU the flexibility to customize screens or data fields and only option 1 would allows us much flexibility to develop custom reports and graphs from the data. Option 2 freed us from worrying about the servers and data backup systems but it would take our data out of our hands and effectively tie us to CMA. CMA option 2 was immediately ruled out because the Physical Plant wanted to maintain control over its own data and systems.

Having finished analyzing the vendor provided systems I moved on to explore my second main option, the feasibility of building the CMMS in house. I had already made up my mind that I wanted the application to be web based due to the many advantages this type of architecture provides. Such as being able to use thin clients to access data, fast data access speeds, and flexibility of programming access options.

Another major consideration was the database engine. It had to be fast and flexible enough to be accessed from many different software development platforms such as Java, Perl, PhP, or Python. The DBMS had to be capable of handling over 500,000 records without any degradation in performance. Most of all, the DBMS must be robust and rock solid from a reliability standpoint.

I began my survey of web-based database servers from both the open source software venue and the vendor supported software class. I quickly narrowed my focus to two database systems, Oracle, and MySQL. Both have excellent records for reliability and flexibility and are used in on-line applications in major banks and accounting houses as well as Yahoo.com and Amazon.com.

As part of the feasibility study, I down loaded copies of the latest versions of both systems and loaded them on test servers. The first package I worked with was Oracle. I developed a small web database application that would add, edit, and delete records from a test database. I also loaded a large data file of 35,000 records into a table to test report-writing capabilities. After the initial learning curve of using the JAVA CASE tools built into Oracle, I found that development in this system would be possible, and simple.

Next, I duplicated my efforts in MySQL. I used Code Warrior for Java as my integrated development platform (IDE) and as with the Oracle product the system worked extremely well. Development went quickly and the DBMS worked flawlessly every time. The MySQL command set used standard SQL where Oracle used some proprietary commands and interfaces that were a little hard to use. In addition, I thought MySQL was more user friendly. MySQL was also considerably faster in benchmark tests of accessing and modifying the 35,000 record dataset than Oracle was.

Next, I built some screens and data access components using Perl that would work with ether DBMS. Both database systems preformed well but once again, MySQL was considerable faster than Oracle. I also found that there were more development libraries on CPAN available for MySQL than Oracle. It quickly became apparent that the Perl DBI (database interface) for Oracle was very limited in its functionality as compared to the MySQL DBI. This particular issue could pose a problem during development.

These tests proved that it was technically feasible to develop a sophisticated, and robust web based CMMS in house. The costs associated with the Oracle software were $30,000.00 for a one-server license. The costs for the MySQL software was $0.00. The cost difference between the two software packages made my selection very easy. The only reservations I had about using MySQL was that it does not support transaction tracking, data rollbacks, and referential integrity is not natively enforced by the DBMS. Having written a lot of code for database systems I knew of ways to take care of these shortcomings in the program code so I was comfortable with selecting MySQL as our DBMS.

Since MySQL had satisfied all my criteria for a database system, and because there was such a significant difference in up front costs between the CMA system and one we build ourselves, I decided that we would build our own CMMS as opposed to buying one. I recommended that we take this course of action to the Director of the Physical Plant, Mr. Darrell Smith, who signed off on the project.

I had already interviewed the users to see what important issues and functionality they wanted in the new system, so I began developing a prototype for them to look at. The prototype consisted of static HTML pages that represented snapshots of the screens that would eventually make up the new system. After I had the pages built and mounted on our intranet server I invited the users to look at them and send comments on what they liked and did not like about the screens. Over all, there was a very high user satisfaction level with the user interface and I was encouraged to begin development.

To help generate interest we needed to have a name for the new system. This would give the users and I a common language to use when talking about the CMMS in meetings. After discussing names with different focus groups it was finally decided we would call the new system PAM for Parts and Maintenance system. Thus, the Physical Plant Parts and Maintenance system (PAM) was born in January of 2002.

After working on several design layouts, I decided to make PAM modular so that it could be easily upgraded without disturbing any of the other main functions. This would also reduce the compile time for the code when the web server first accesses the file. Figure 2 shows the module layout.

Next, the decision had to be made on what the hardware architecture for the new system was going to be. I decided that we would use a rack mounted 2U server with two 1.6 Gig Hz. processors, 1 Gigabyte of RAM, and running Red Hat Linux version 7.3. This server would have speed and the power to deliver the content my users wanted no matter what the load. Moreover, it would have all the web development tools such as Perl, PhP and others that I would need for the project already mounted as part of the default installation. It would also come standard with the Apache 1.3 web server which is the Industry standard.

Since I had users on both sides of our Plant firewall this sever would have two NIC cards so it could span the main firewall. For protection though, the server, named Atlas, would also have it’s own firewall and only allow access to port 80 from designated clients. Figure 3 shows the Plant server configuration for this segment of the network.

After making the hardware selection and ordering the server it was time to begin developing the software. I used my “Master” server, which is also a Red Hat Linux 7.3 server, as my test and development platform. I would develop the software here and set up a test site where the users could test the module sections. Once tested, the modules would be moved to the production server, Atlas.

My first step in development was to build a program for faculty and staff to be able to use the Internet to look up parts in our warehouse inventory and send work orders to the Physical Plant. I decided that this web interface to PAM would be mounted on Intrepid, our plant web server. It would access data directly from Atlas, the database server, over the Plant intranet but there would be a layer of hardware and software between the web server and the database. This would keep the database safe from hackers if they managed to penetrate Intrepid, which is a publicly accessible server.

This web interface module was built with PHP, which is the open source equivalent of .ASP, and use Java Script for form validation. Once a user has logged into the system using a password they can fill out a form with work order information. When complete, the information is then emailed to the service desk at the Plant where it is turned into a work order. Users can also look up parts from our large warehouse inventory and place orders directly from the web page using a web based shopping cart. Once an order is received, it is pulled off the shelves and place in a holding area for pick up.

Atlas arrived and was immediately loaded with software, configured, and put into production. MySQL was mounted and Apache:DBI, the persistent database connection system, was set up to assist the Apache web server to quickly access the database server. A parts table was built which was populated with the 3600 separate part items from the Plant inventory. Then the web interface was tested and put into production. Shortly after the announcement that this service was available to the campus, positive feedback began arriving from users and its usage has continually increased.

With the immediate needs taken care of, development of the rest of the system could begin. All modules would be written in Perl and programmed to use the mod_perl script-caching engine. Mod_perl caches all scripts in memory so that the Apache server can quickly serve them from there, rather than read them from disk each time a client makes a request as is the case in standard CGI based programs. All of the HTML pages would use Java Script to do client side form validation and cascading style sheets would set the theme for each page.

Module development and their associated database tables would proceed on the following schedule:

1) Login Module

2) Work Order Module

3) Inventory Module

4) Supervisor Module

5) Managers Module

6) Automotive Module

7) Preventive Maintenance Module

The login module is the gatekeeper for the rest of the system. It has access to the user table that contains such things as the users name, login name, password, salary level, supervisor, and email address. It is also responsible for encryption of passwords and other information using the 256-bit Blowfish encryption algorithm. As part of the security schema for the system the server encrypts a specially hidden HTML field. This field must accompany any data exchange between the server and the client. If the field is not present, or has been modified in anyway, access to the server is denied.

The login module also limits access by users to different sections of the system. There are ten different access levels with the lowest level, level one, only having access to the parts catalogue. Level ten users are the administrators of the system who can access all areas of the program. Each level also can have sub groups such as 5a users that can access the automotive system, but with read only access.

The work order module is the core process of the system. It generates the work orders, tracks costs associated with the work orders, and generates the data used by the managers for budget development. Its processes are:

1) Create and edit work orders

2) Post labor costs to work orders

3) Post warehouse charges

4) Post other charges

5) Post Gas and Oil charges to Automobiles

6) Update auto mileages in the Automotive system

7) Input information about utility costs per building into managers system

Work orders track costs to buildings and can narrow the focus to a specific room or piece of equipment. It also tracks the work based on whether it is a routine maintenance issue, or work that is chargeable to a department such as a remodel.

The inventory module is the second core process and provides such functions as inventory level tracking in real time, transaction tracking, maintaining a list of recommended vendors, part receiving tracking, and order and quote generation. The system watches minimum stocking levels, and if a parts on-hand level reaches or goes below minimum, the system will flag the part for ordering.

The Plant purchasing agent maintains a list of recommended vendors inside the system and can easily generate request for quotes from the list of flagged parts that are below minimum. The system will also groups items by vendor and will list items that have been purchased from this vendor before, but that have not reached minimum. The agent can then add these parts to the order to take advantage of volume discounts. Once a quote has been returned from the vendor, an order is easily generated with one button click. All quotes and orders are created and printed by PAM as Adobe Acrobat documents.

The supervisor’s module is the interface the department supervisors use to access work order information, assign workers to work orders, look up and order parts, and set up preventive maintenance schedules for equipment. After the service desk operator creates a work order, PAM automatically emails the supervisor in charge of the department. This notification insures that the supervisor is aware that a work order is in their PAM queue awaiting action. Figure 4 shows the life cycle of a work order inside PAM.

All processing of data is real time so when a client views a part in the web interface or other system, the on-hand level is always correct. This allows supervisors to plan projects by knowing how many parts are on-hand as well as how many are on order. This cuts down delays on projects and reduces costs by having the proper numbers of parts available.

The manager’s module is an information module. It is designed to give managers information about what is going on inside PAM, how the various departments are doing with their assignments, and to provide information for budget development. Specific information provided is the number of work orders generated by department, work orders completed by department, number of open work orders by department, and the number of completed work orders by department.

Currently all utility billing information for water, electricity, and gas is contained inside Pam for the years 1993 through 2002. A manager can quickly call up utility data on any building on campus and get a print out of the costs for any given year, or years. They can also have the information plotted on a chart, which can be included in a word document. (Fig 5) Other decision support systems will be included in this module later to track maintenance costs per building and revenue generated by department for any given year, which will also be chartable.

(Fig 5)

The automotive module, which is the most complex module in the system, has to perform the following tasks:

1) Generate Work Orders for automotive fleet (over 400 vehicles)

2) Generate and maintain maintenance records for all vehicles

3) Track parts requirements for all vehicles

4) Track all costs associated with vehicles such as oil and gas usage

5) Generate preventive maintenance work orders automatically

Automotive work orders differ significantly in the types of data captured as compared to the work orders used for buildings, so it uses a separate work order table programmed into the main work order module. However, users needed a seamless user interface to work with both systems. This presented some interesting challenges and required PAM to use 10 screens more to handle all the different input needs of the users.

I decided that to make it easy for the users to distinguish between building maintenance and automotive work orders that I would use a different work order designator for each. I would use a PP prefix on building work orders such as PP-1000, and I would use a AM prefix on automotive. This way a user could tell quickly one from the other. It also made it easer for the program to tell which system to work with when it saw the prefix.

The preventive maintenance system is still under development at this time. However, once complete, a user will be able to set up regular maintenance schedules for equipment, or rooms, and PAM will automatically generate a work order on the given maintenance frequency. This is a very important module; it will insure campus buildings and equipment are adequately maintained on a routine schedule. Good PM is the way to avoid expensive and disruptive equipment failures.

PAM is 100% complete with all modules on-line and more being added daily. So far, there are 258 modules and web pages and 65,000 lines of code driving the system. Users have noted a significant speed increase by using a thin client and a high-speed database server as opposed to the old CMA system. A benchmark parts query was developed that requests 660 records from the database. In PAM, the query takes less than 1 second to complete, in CMA, the same query takes 80 seconds to complete.

Users have also commented how easy it is to retrieve data and generate reports compared to the old system. They like the clean simple interfaces and the fact that they are using a browser, which is the software they are most familiar with operating instead of a complex and hard to use interface like CMA. Managers have also used the charting capability of PAM to generate reports for budget hearings at the capital, and are impressed with the systems speed and flexibility.

With PAM on line, and the costs to develop the system now known, a comparison between the 5-year costs associated with up grading our CMA system compared to developing PAM can be made.

CMA Up Grade

Oracle License $30,000.00

CMA Software $55,000.00

Annual CMA Maintenance X 5 $35,000.00

New Server to Host System $ 3,500.00

Total $123,500.00

PAM

MySQL Software $ 0.00

Development Cost $15,000.00

New Server to Host System $ 3,500.00

Total $18,500.00

Total 5 year cost savings $105,000.00

PAM Return on investment (RIO) = 2 years

As seen from the above figures there has been a significant cost savings by developing an in house system as opposed to purchasing a commercial software package. PAM also has a return on investment that is well inside the 5 year ROI the State of Kansas likes to see on projects. These two factors make PAM a very successful project from a financial standpoint.

From a software stand point, the University has a robust web based application that can be accessed with a standard web browser on any platform, anywhere on campus. In addition, since it is designed to meet the users needs, and they are stakeholders in its design, there is a high level of user satisfaction. Over all, the PAM project has been quit successful and should give the University many years of reliable use.