With the bulk of the comm series delayed, I thought I’d write the light hearted fun entry that’s been on my list for a long time.
In one of my incarnations, I work as a consultant for small businesses and home users. Usually I’ll be referred to someone by a friend or a friend of a friend. My many years of doing this have given me valuable insights of how a lot of people work. And time after time I’ve encountered this, and I can stay silent no longer. So with that….
DHP Presents: Excel is Not a Database!
MS Office is still a staple of many people’s software toolbox because it is deeply entrenched in businesses of all shapes and sizes. Because it is so common to see virtually everywhere, I see many people using it not just for the number crunching it was designed for, but to keep track of everything from contact lists to purchase records to inventory lists and more. The reason for this is obvious: it’s already on the computer, the person already knows how to use it, and for very small lists, it’s fine.
However, what tends to happen is a small manageable list that you can scan quickly without scrolling the window grow and grow with more entries, and sometimes the person realizes that he or she also wants to track some more information and adds another column. Pretty soon the small easily manageable list becomes cumbersome to use. You can no longer easily find information, you can’t easily add more columns and you find the need to add a “notes” field full of miscellaneous information, that isn’t related to other entries in the same column.
If this sounds familiar to you and if you read read this and think, “Yup I know the pain, it happens to me.” Read carefully, because this entry is for you. It might be painful to hear, but I’m telling this for your own good. Excel is not a database and if you’re using it like this then “you’re doing it wrong!” as George Carlin would say.
What you need is a real database. It doesn’t have to be fancy or expensive or difficult to learn. The advantages of a database are many over a simple list. The biggest is it saves time:
You can do a search for all the records that match a certain pattern, were created during a certain period of time or are related to a single person or piece of equipment and more.
By using a relational database, you can eliminate entering the same information over and over again and also link related records together by a single common element.
You can create reports that filter the information in the database in such a way that you can see patterns, trends and glean insights because you get both a complete overview of what you’re managing and can see fine detail at the same time if you want to know why something odd or unexpected is happening.
You can usually, easily add columns to track more informatoin and change layouts to show just the information you need.
You can usually create multiple views of your data by making a new layout with different fields.
Some more advanced databases allow you to add scripts that execute to alert you of changes, events, or update related records when the linking element changes.
In short you can become a lean, mean information organizing machine. And because I like to give real world examples of the differences doing it the inefficient way vs. doing it the smart way. I’ll give you my best example.
I was working as a systems administrator and a senior help desk technician for a very large, very profitable company at one point. During my normal duties I found that the company had problems when an employee changed machines getting him or her back up to speed. While the hardware was easy enough to actually swap out, the logistics of actually tracking and doing it was a huge mess. Not only did we have to check the machine’s serial numbers, but we also had to reinstall their software. This often resulted in trying to dig up all their software serial numbers, and find out if their new machine’s OS was compatible with all their older software, if they had any nonstandard software.
I was tasked with spending my spare time improving the process as a project to improve IT’s ability to handle the logistics of hardware and software tracking. I got the assignment because I was asked in a meeting if we could find out how many Ethernet ports we had available on the floor when a large influx of new hires was incoming and needed to be setup with network access. My manager expected me to get back to him the next day with the information. But I pulled out my PDA and told him that we had up to 8 on one switch and could task another 10 or 12 from another switch — maybe more if I checked if their were any ports that were connected but not being used.
He was taken aback a bit that I could answer him on the spot and asked how I knew that. I showed my manager my PDA with a copy of a database I made on my computer that tracked patchbay changes and other administrative records that I found helpful to have available that weren’t associated with the help desk ticketing database.
So, flash forward to me going to meet with the logistics manager. I needed some information on an employees equipment. He told me it would take up to a few hours to track down that info, but he’d have it for me after lunch. Curious I walked over to his desk to find out why it would take so long.
When I got there, he was looking for an invoice from the vendor that listed what equipment the employee was issued when he was hired. He was at his computer and looking through a bunch of excel spreadsheets for the employees name since he couldn’t recall when the employee was hired. He explained that he started a new spreadsheet every month since he needed to generated monthly reports for accounting purposes. After he found the employee’s name he went to his paper files to pull up the invoice with the serial number of the machine on it. I was floored. Here was a person in the IT department that had been a warehouse manager previously, that had never considered using a database to track the information and make his job easier.
The next 2 weeks I built a prototype in Filemaker Pro that had software serial numbers I culled from the other technicians’ excel spreadsheets, hardware asset numbers from the logistics managers many excel files, a list of all employees and their location, title and contact number I got off the company directory, I pulled a report out of our ticketing system with any reference to an employee along with a copy of what was entered in the ticketing DB. Basically, I assembled an entire system to make the process of gathering a users related information as simple as a few clicks.
I also requested a list of all employee’s managers so I could construct a streamlined equipment request process. But HR considered that information too sensitive to disclose in case of competing companies being able to determine our structure and potentially poach the more valuable employees if that DB was ever breached — that and they didn’t trust our IT dept. with that information. (That was the first sign of doom, but more on that later.)
Anyway, when I showed my completed prototype to my manager, along with a single click drill down, room for expansion to include other offices and plans for hooks into cubicle maps he was pretty blown away, and the next day got permission for me to develop a real logistics system for at least our office as a test site. I released my proto to the logistics manager immediately so he would no longer take hours finding which invoice he needed to pull and also gave access to my machine running that and other databases to the other IT department members.
The next week, when another migration came up, the logistics manager did the exact same thing he did weeks before in about 15 minutes.
This story does not end well though, and I am only including it because it’s an example of a combination of bad management decisions and office politics that I’ll refer to later in this series. Read on if you dare.
Unfortunately, the complete prototype system was opposed by the other office that had the resources (an SQL server cluster) I would have needed to create a large scale working copy. I wanted to use open source and host it locally, but since one of our partners had an SQL DB product, the decision was made to run it in their SQL server system. (That meant I had to remove a few features, but it would have still been much better than the current system of each office using different systems to track assets. This was the second sign.)
It took longer to actually get access to the server than it did to build the prototype, because the admin down the peninsula refused to give me the access I needed when requested. I had to resort to having my manager call his manager, and waiting another few weeks for him to “get around to it” to finally get the ability to login and create the SQL DB.
My supportive manager left less than a month later (a month or two after the CTO left), and was replaced by a guy that considered the current system good enough. Instead of receiving support from my new manager, he complained that I was always late for work because I was sick one week and called in twice to let them know I’d be coming in later when I felt better. He had the gaul to come and yell at me after I arrived the second day because I said (according to him) that I’d be there by 1PM. I told him I said I’d come in around 1PM, not that I would be there at 1PM because I didn’t control the bus schedule. (I got there at about 1:15 that day.) This was the guy that I often saw managing the department by playing first person shooters more times than not whenever I passed his glass walled office.
So, instead of completing the project, I talked to HR about my problems with my new manager. I ended up taking a leave of absence, and during that time decided I didn’t want to work with a hostile manager. This manager had come over from a company that my former company merged with a year earlier. Within 5 months after I left, the company started to fold. Considering how public the implosion was at the time, and knowing the management team was mostly from the acquired company it didn’t surprise me considering the attitudes of the people who came over from the other company. I wasn’t this first person to leave, our chief sysadmin jumped ship shortly after my manager left. I heard most of the IT team had jumped ship within few months of me leaving. Those that stayed til the end only got 2 weeks severance pay. I on the other hand had received 3 months severance.
Another kicker? My previous manager wanted to hire me out from the company and had the green light after a phone interview with the other company’s CTO, but he was blocked by clauses in his resignation. That company went on to be successful enough to survive to his day.