Wednesday, May 18, 2011

Back to Programming -- Sort of

This is my first blog post but I'm going to skip the formalities and get to the point.

I did some non-database programming today and it was wonderful. OK, it was VBA in Excel -- WAIT COME BACK! It gets better.

I'm currently working to hack together some automated Credit Policy reporting. These reports basically consume one person's time for an entire month. She can't take vacations without a computer by her side. And these reports are the foundation of much of our regulatory reporting. They go to the "C"s, they go to the Board and they go to the Feds. So much of what she is doing is manual: Spreadsheet from here, query from there, cobble it together, add a bunch of manual exception entries.

I was brought in to leverage the data warehouse I've developed (SQL Server backroom, Business Objects WebIntelligence in the front, Kimball methodology with a bit of Ingram thrown in where needed.)

I brought data into our core warehouse from 4 additional sources (more on that later, I suppose). There's still some tweaks being made to the way business rules are applied to some of these but the heavy lifting is done.

Today, however, was the day I used to create a way for the users to write manual exceptions to the warehouse. There are all sorts of exceptions that needed to be addressed -- payment checks that didn't post on time, reclassifications of risk following month end, Market assignments changing, etc etc. It basically boils down to a process failure somewhere along the line which those who report on this data are expected to disguise, despite what a very costly and complex source system insists on reporting.

My original recommendation for exception entry was a .NET app deployed on our intranet. Security could be managed through AD and there would be no need for anything beyond a browser on the desktop. I was prepared to provide the use case, do the entire database design (simple, single table with some lookups) and all the CRUD stored procs. I believe I could have even coded the front end -- but, since I'm not on the "developer list" -- not even in IT, actually -- those tools are reserved for the very competent developers.

So our acting PM went to the projects team with what should have been a simple 2-3 day task and was told that they didn't have the cycles to do it and would need to bring in a consultant. A consultant in this role would take at least week to get them network credentials and many more hours of haggling with IT bureaucracy to implement this solution that any "very competent programmer" should take to do. And so . . . the project sponsor said "Can't we just do something in Excel?"

And here's where my faulty honesty kicks in. "Of course! I've done this plenty of times!" I didn't elaborate on the long history of every company I've worked for wanting to get by on the cheap with a sub par Excel solution. The maintenance issues and code rot involved with it. Worst of all, the terrible error handling. I did manage to let them know that, if we went down this path, we'd be developing it "New Jersey Style". A term I may or may not have coined but which is certainly apt and I let that imagery sink in while wondering if I had just insulted anyone who might be from New Jersey (seriously, your beaches are lovely and I barely notice the smell). However, and don't tell anybody this, I have a soft spot for Excel -- the golden hammer of the luckless. I've been using Excel macros to create monsterpieces since the mid 90s. It was my first real IT job to manage an awful rats nest of reporting automations -- in Excel 3.0 mind you! I've learned a tremendous amount since then about what good code looks like. The current incarnations of VBA provide an ample assortment of solid tools to work with. And you can actually produce a solid piece of work with some elegant touches if you stay away from recording macros and have the confidence to hand code your work.

And so, I became inspired by the horrible dearth of BI blogs that aren't trying to sell you something to start blogging about my experience in the industry. And here I am kicking things off telling you how much I love programming -- even when it's with a sub par tool like Excel VBA.

There is something absolutely magical and intangibly satisfying about making something work and doing it in the simplest and most straight-forward way you can contrive. In poetry, they speak of the "economy of language" making every word add something to the piece. Using just the right words and no more than are needed to express what you intend.

A good bit of code does just that.

Moreover, it is the building of the thing starting with the decomposition of the problem -- forming a mental picture of the problem space and slowly stripping away the complexity until you understand the component parts that encapsulate the discrete functions of the thing. This is then followed by creating the facade that couples the functions together to create procedures. Then the writing of the functions that do their one thing well and briefly. And the bricks are snapped together and they just work. When it's done well, there is nothing that can be added or subtracted to improve upon what you've created.

Of course, Excel VBA fails to provide a framework that allows for such sublime master pieces but I feel like wrenched the most out of Excel that I could today and I feel good about it. I think if I had to program every day, I would get bored of it but it's moments like today that make me love my job.

1 comment:

  1. Hello,
    We facilitate the provision of independent analysis to support expert testimony, regulatory or legislative engagements. Frequently, this work includes economic, financial and statistical studies of varying data analysis, technical and