Friday, June 27, 2008

My Summer Job

I realize I just posted, but my script is still running, it is not time for lunch yet, and I have a lot to catch up on.

I just spent some time writing about a script I had to write for work, but I've not really elaborated on what it is I do. I mentioned a few couple posts back that I work as an intern in the Business Recovery Services department for First National Bank of Omaha. As it turns out, this is an exciting, dynamic, and challenging field and I'm glad I chose to come here this summer.

The primary goal of our group is to provide internal consulting services to the corporate business units and our affiliates regarding business continuity planning and disaster recovery. The idea lies in that if a business unit can list its core processes and functionality and then identify what resources (locations, people, resources, supplies, technology) it needs to do them, it can develop plans to work around a situation in which one or more of those resources becomes unavailable.

What we do is work with each group to assist them in identifying their processes and resources, and guiding them through their planning development. We use a toolkit called eBRP to store and organize all the information and manage our efforts.

My group only consists of three people, including myself. The interesting thing is that I can bring my IT and computer science experiences to the table. I've pretty much moved into the lead role for all efforts regarding technology. Once ETS (our technology division) settles down, I'll be responsible for rolling out our planning efforts to them.

Another big thing I do is data gathering and processing. That sounds pretty bleak I suppose, but the state of technology representation in our system was immature and not sufficient to recovery business in the event of an emergency. Therefore, I had to contact different groups within ETS to identify what kinds of information we had to describe our technology infrastructure, where it was, and how we could get it into eBRP. The main areas I focused on were databases, networking, and servers. I wanted to ensure an automated data flow that would pool into our system, so I worked with the respective managers to schedule reports that would end up on my machine. From there, I had to write scripts that would parse the data and massage it into a format that would work well with eBRP's import tools. It turns out I've run into some weak spots with eBRP, and pulling all that off tends to be fairly difficult so far. The good news is that I wanted to learn the Python scripting language this summer, and it lended itself well to creating solutions for these problems.

So far I've gotten most of the data import processes to a state where they can be fully automated, but I want to make sure they're bug free and maintainable before I leave. Either way, it's a lot of fun and it has variety. The best part is that I feel challenged and rarely bored here.

One final thought. While working with the tools and processes here, I've begun to see how our efforts are pretty valuable to any organization. I've also seen how the tools and processes available aren't as effective, efficient, or robust as they could be. Unfortunately, there don't seem to be many major players in that market so innovation seems to move slowly. It is entirely possible that if get an entrepreneurial spark before I graduate, I may try to create a more effective business recovery management solution and develop a consulting service around it. It's certainly an exciting field and I know quite a few talented people back at school that would be excited to help out. I suppose any readers that take an interest in this or have suggestions should feel free to contact me and let me know.

Ok, lunch time!

While I wait...

So currently I'm at work waiting for this heinous script to finish running. Sadly, certain constraints forced me to write some pretty inefficient code when I knew there was a better way to do it and it's just killing me to watch my CPU run at 75-85% because of something I wrote.

To get an idea of the problem and the need for the monstrosity, KPMG is currently assisting our internal audit group (I suppose I ought to be careful about how much I let on here) develop standards for their own auditing of our disaster recovery procedures. Part of that means we need robust and effective calling trees to ensure smooth communication in the event of an emergency. We use a system called eBRP which is supposed to manage our employee information and generate calling trees from the data. Unfortunately, that seems to have been wishful thinking, so we have to come up with another solution. Suddenly my status as the only person on my team with programming experience becomes painfully obvious.

I recently got read-only rights to the eBRP database (internally hosted SQL Server) and I subsequently set up an ODBC connection. Since I don't have a development machine, I can't really install the majority of software publicly available and I don't think my needs justified submitting a request for SQL Management Studio. What I did instead was use Excel's data import tool to consume the ODBC data. This is kind of a neat trick, and it helped me understand the database schema a little better. However, once the request came down to figure out how to generate calling trees for KPMG, the beauty of the workaround lost its luster.

So the basic problem goes like this: for each employee, identify any employees that may report to him or her. For each of those employees, look up their contact information and record it in another Excel workbook. Seems simple enough, except this problem scales up to over 5,000 employee records linked across multiple worksheets (it did come from a database).

Again, since I'm not on a development machine, I had to figure out what language I would do this in. The requirements were that it needed to be able to run on Windows with minimal supporting installations and it needed to be able to access Excel's object models to manipulate and move data from spreadsheet to spreadsheet. VBScript came to mind as the most likely solution. I've never used VBScript before, and I really had no desire to ever try it, however it was easy enough to learn as I went and there were many examples online. I used SciTE which is a great multi-purpose editor and software deemed acceptable by the powers that be in InfoSec.

Now on to the performance issues. This is basically a search and compare problem, so I need to be able to iterate through the data and make comparisons. VBScript doesn't have access to Excel's search functions, so I had to loop through the data. Now, if you have any algorithm analysis experience, searching records iteratively for this solution would require one loop to go through each employee, another loop to identify their direct reports, and another loop to find contact information for each identified employee. So that's two levels of nested loops from and an n3 algorithm. It's not very pretty.

So with all that being said, I ran this script over an hour ago and it's still cooking. Some ideas popped into my head in my last meeting for a way to make the search algorithm a little smarter, and it's nice to be able to sort the data before I execute. Either way, it would've been great to just write a script to fire SQL queries and make this go a lot faster.