This last step isn’t so much a part of the SAFR method as it seems to be a SAFR team obsession. Sometime in February 1997—I don’t know exactly when since the whole month is a blur—I had a particularly memorable meeting with Jay. It wasn’t so much what was discussed as when it was discussed. Let me first set the stage.
We’ve noted in ERP Reporting that the high-tech chip manufacturer waited until things got to crisis level before they called Rick. What ensued was the toughest project I think any of the members of that SAFR team on it ever experienced.
It began with the challenge that unless a new processing approach was found, the company wouldn’t be able to close their books daily in three month’s time. The processes they had constructed functionally worked, they just took too long. And they were taking longer and longer the more the data volumes increased.
The team was charged with translating the programs written in custom ERP language into SAFR processes. Every available SAFR knowledgeable person was flown to the client site. I was pulled off the Insurance Company to go—but I didn’t have to fly. The company was a 40 minute drive from my home. Everyone was assigned a multitude of tasks. We didn’t start with analysis or specs or even any time at a white board. We were given ERP program listing to start translating.
Oh the pain. Although it was the only project assignment I ever had in that town, and I was no longer required to commute half way across the the country twice a week at eight hours each way, my wife told me she saw me less than at any other time. I didn’t make it home a number of nights but stayed in a hotel because I couldn’t afford the 40 minute drive. I worked something like 120 of 168 possible hours one week, and something over 200 hours in two weeks.
I worked multiple all-nighters. The whole team ran at a similar pace. Randall called one Monday morning to ask if he should go to the airport to fly down and I said I needed his help right then. So he didn’t leave and worked from home in Seattle all week. I put him on speakerphone and we just left the line open hour after hour. We didn’t talk constantly, but we didn’t want to take the time to dial when we did. Others would walk over to my cube to talk to Randall as if he were working in my cube as well.
One night after midnight I had a very large test file to upload from some server to the mainframe. I started the script to do the file transfer, and from the script messages calculated it would take over an hour to complete. Randall was still on the phone working. The lights in the building were off except for a desk light at my cubicle. I told Randall I was going to lie down on the floor and sleep a bit, and asked if he would wake me up when my transfer job had finished. He said he would.
When I woke up my pager was buzzing at my hip, and I could hear indiscriminate keys being pushed on the phone. Still lying on the hard floor groggy I asked what was going on. Randall said he had yelled into the speakerphone to try to wake me up but he couldn’t raise me, so he had to switch over to his other phone line and page me to get me to wake up. He said my job had blown up; I hadn’t allocated enough space for the file on the mainframe. Uggggh!
This went on for weeks. Dave Padmos was the associate partner and project manager. He knew the client very well having been there for more than a year. He didn’t have any SAFR experience, so he took on the job of protecting the team. I have never seen such an effective job of giving air cover to the troops on the ground. Although the client seemed to be in a panic, he told them that except for one person that could accompany Dave they had no access to any of the team members. They wanted to have daily status meetings, and he said he would be the only one attending. They said they needed more information about what was happening and he said they could ask him any question and he would get the answer. He ordered food, and kept everything away from interfering so we could meet the deadlines. It was very effective leadership and I learned a great deal from it.
I recall a number of years later I was at lunch with him and we talked about the project. He remembered coming into my cubicle and asking about something. Randall and I would talk about potential ways of solving the problem. Randall was developing a way of generating JCL scripts rather than handwriting them and I was helping. While others were writing views, we knew that the last thing that must be done before the system can be tested was creation of JCL, and the system would come to require hundreds of thousands of lines of scripts to run the Scan Engine. Dave said he would listen to us discuss possible ways of solving a problem, telling him “Well, this way would be more elegant and have these benefits but would take longer…” just like technology people are wont to do. He said, “At times I just wanted to scream, ‘Will you guys just make a decision!’”
Jay was the architect of the solution. He was there all the time. He decided how the programs should be translated into views; what the event files would be; how each run of the Scan Engine would interact with the next. Our sleep schedules became so confused we never knew when to sleep. The meeting happened one morning when I woke up at home about 2:00 AM as I remember it. I got out of bed and decided to check and see what needed to be done. I called Monica Logan’s cubicle, and Monica picked up. She gave me a short update, and then got interrupted by someone else coming to her cube. I think it was Padmos. She said she would put me on speakerphone. We talked for a moment and then she got another call. It was Jay who had similarly just woken up and was checking in from the hotel. She conferenced him in. We made plans about what needed to be done next. Here it was 2:00 AM and we were having an unscheduled meeting with four professionals all in the same time zone when the deadline was still weeks away!
Over the years when projects have neared deadlines and things were tough, anyone who was on this project would invariably say, “Well, it isn’t as bad as the high tech chip manufacturer.” I heard that Monica said this on the Investment Bank project and then paused and said, “Well, perhaps it is as bad.” But I don’t think any of them had quite the same intensity. Work at this pace went on for a number of months.
The team barely completed development in time. The last executions of system testing weren’t completely defect-free; there were a couple of small errors, I believe in the JCL scripts. They decided to go forward with the implementation. The first execution without any errors was in production. The team had sprinted to make the first implementation, but it wasn’t the last. Additional processes had to be converted as well. So the next phase of the project started, but at a little less intense pace.
A few years after the project, I wrote the following description of the results of the project:
High Tech Chip Manufacturer Project Results
After this Fortune 100 firm implemented multiple ERP modules, they tackled the problem of analyzing all the integrated data captured by the new system. But running the ERP processes 24 hours a day, 7 days a week left very little time for anything but transaction processing. They needed their data warehouses updated three times each day to reflect the close of business in each of its worldwide operations. An innovative solution would be required, and it took a few attempts to find the right one.
One approach might have been to simply analyze the data in the production database. But the potential disruption of transaction processing ruled this out. There was simply no room for analysis in the transaction processing environment.
Another approach involved replicating tables from the transaction database to a separate ERP instance. This was supplemented with third party software to capture changes to specific data. After extracting the data, custom programs were used on other processors to complete the data transformation. The data was then loaded into a variety of EIS and data warehouse applications. While this approach freed up the production server, data volumes were so high that the extract and transform processes were taking 48 hours to complete for each of the three daily processes. They were getting farther behind the harder they worked!
The company turned to SAFR for a solution. To shorten the data extract process, database triggers were used to capture just updates, deletes, and inserts to the database rather than replicating the entire database. To reduce the transform process time, the transformation logic was translated into SAFR “Views” supplemented with custom programs. This approach cut the elapsed time from 48 hours to 45 minutes without impacting the production system.
The company also found some unanticipated benefits as well. The SAFR solution provides a detailed repository of historical data for analysis. This data can be stored in the SAFR environment and archived from the ERP tables, thus improving the transaction system performance. New types of detailed analysis were possible using SAFR and the process has proven itself scaleable as the company continues to grow.
Since the first implementation, the company employed SAFR in several different systems, and used it to replace many data analysis processes originally coded in statistical analysis software. After re-configuring one statistical analysis application with SAFR, the end users were able to slash eight hours off of the delivery time for their reports.
A typical execution of SAFR at this client reads 100 million records, performs 1.2 billion foreign key joins across 2.2 gigabytes of table space. It writes out 100 million records in 70 user extract files. This process takes just under an hour of elapsed time and 73 minutes of CPU time.
The following is the architecture diagram used to describe the project.
Although the team continued after the initial implementation, my involvement didn’t even make it that far. My wife was pregnant with our third child and at her birth, I knew I had to stop calling in to work at 2:00 AM or my wife would kill me. After a week or two, she told me I had better head back to the insurance company. Through experience I had learned to listen when she gave me advice like that. Doing so started a deeper association lasting for five years with my next mentor, Doug.
Next: Part 5. The Programmer
Previous: Chapter 36. Optimize For Performance
Parent Topic: Part 4. The Projects