This blog post is part of TSQL Tuesday. This month the host is Kenneth Fisher, and the topic is Your First Technical Job.
It's funny how things turn in life. I studied Industrial Engineering in the university between the ages 18 and 22. During my studies I took a course by the name "Introduction to Databases". I'm talking about the year 1994. The course was terrible, and I remember telling myself: "I don't want to deal with databases in my career. It's boring."
A couple of years later I started my army service in the technology unit of the Israeli Intelligence Forces. During the interview they asked me what I would like to do. I told them that I am and industrial engineer, and I can make things work better, but please don't let me work with databases. So they put me in charge of a newly established purchasing department, so that I can help planning working processes and making things work better.
While I had fun and enjoyed working with other people in the purchasing department, I quickly realized that it's even more boring than databases, and started looking around for more interesting things to do. There was a team in another department that was responsible for developing information systems, and they worked heavily with SQL Server. It was version 6.5. I hung out with those guys, and I found their work fascinating.
Then one day I received my first big opportunity, which changed my whole perception of databases and technology in general.
Once a year, a group of 5 people gathered together in a room for the duration of two weeks. Their job was to ensure that the unit had at least the minimum inventory level of all the parts we needed in case there is an emergency situation. They had a list of around 2,000 parts along with the minimum quantity required for emergency. They printed 5 copies of the list, one for each person. Then they divided the list between them, so each person was responsible for around 400 parts.
They worked with Mainframe, and each one of them used a terminal to interact with the logistics system. No mouse, only keyboard. And a lot of keystrokes.
Here are the steps they needed to perform for each part:
Go to the Inventory module, and into the search screen.
Type the part catalog number.
Get a list of all the inventory locations and quantities on stock.
Calculate the total quantity manually (some used calculators, some made calculation mistakes), and write it next to the part number on the paper.
Go to the purchasing module, and into the search screen.
Type the part catalog number.
Get a list of active in-process purchase orders for that part, including ordered quantities.
Calculate the total ordered quantity manually, and write it next to the current inventory level.
Manually subtract the minimum required quantity from the current inventory level plus the ordered quantity, and write the result on the paper.
If the current inventory level plus ordered quantity was lower than the minimum required, then continue to the next step. Otherwise, go to the next part.
Go to the purchasing module, and create a new purchase order.
Type all the required information, including the part catalog number and the required quantity (to get to the required minimum inventory level).
Submit the new purchase order.
Write the PO number next to the part number on the paper.
It took a group of 5 people two weeks to complete that work. They did it only once a year, because it was simply too much to do it once a month. The job was exhausting. Everybody hated it. But it was important, and someone had to do it.
They asked me to examine the process and see if I can make it better. I had already started to play around with databases (SQL Server 6.5) and code (Delphi 3). I sensed the opportunity, and decided to develop a system that would replace the entire two weeks process with a click of a button.
When I told other people about my idea, they laughed at me. It was considered nearly impossible at the time, and even I wasn't sure that I can do it. But I was certainly prepared to try.
First, I met with the team that was responsible for the Mainframe logistics system. I asked if they can export data from the inventory module and from the purchasing module in CSV files. It took some time and a lot of back-and-forth, but eventually I got what I wanted. Well, not exactly what I wanted, but it was good enough. I received two CSV files. One contained all inventory lines for all parts in all locations. The other contained all PO lines for all parts.
I created a new SQL Server database with a bunch of tables - Parts, InventoryQuantities, PurchaseOrders, etc. I used BCP to import the data from the CSV files into the relevant tables. I couldn't get a file containing the 2,000 parts, so I had to manually insert the part numbers and the minimum required quantities into the Parts table, but I had to do it only once.
Then I developed a process to calculate for each part the current inventory level, the in-process ordered quantity, and the required quantity to purchase. I don't remember if I used stored procedures. Not sure I even knew they existed back then.
I developed nice printable reports. I also exported a CSV file that contained all the information to create new purchase orders. I then negotiated with the logistics system team again, and after a while we managed to import the CSV file into the Mainframe and automate the process of creating all the required purchase orders.
I developed a simple user interface in Delphi, including a screen to edit the list of emergency parts - add new parts, delete parts, and update the minimum inventory level for each part.
After a lot of testing and a lot of bugs (we generated so many wrong purchase orders that we then had to cancel manually, one by one), it finally worked. I scheduled a presentation and invited all the relevant people, including, of course, the 5 people responsible for the process. I launched the application, and in the middle of the screen there was a big button with the text "Just Do It". I clicked the button, and after a few seconds, a window popped up with the message "Done".
I then clicked another button, and generated a report that included all the information needed for each part, including the required quantity to purchase. There was silence in the room. They couldn't believe it. Most of them were still sceptic and thought that it's too risky and we should continue with the manual process.
I continued with the demonstration. With the help of the Mainframe team, we took the exported CSV file (which was generated during the first button click), imported it into Mainframe and executed the job that created all the new purchase orders.
Then I asked the audience to pick one of their favorite parts. I then executed the manual process step-by-step (the 14 steps described above). It took me around 20 minutes. Then, I showed them the numbers and the created PO for that part as calculated and generated by the new system I developed. I proved everyone that the process was correct, and instead of two weeks of 5 people, it was done with a click of a button (well, a few clicks, actually).
The last thing I needed to do was to automate the process, including exporting and importing all the files. After some more development and a lot of testing, we managed to convert it into a daily process that ran automatically, so we didn't even need that big button anymore.
Not only we saved the time of 5 people for two weeks, we also improved accuracy by eliminating human error, and we also maintained inventory level at all times instead of just once a year.
I learned so much from that project, and it changed my career forever. In addition to all the technical stuff, I learned what technology can do, and more specifically, what we can do with data and with databases.
And this was my first technical job.
Comments