Monday, May 30, 2011

Normalization and How to Know When You Are Done…

I enjoyed reading Louis Davidson article on normalization over at Nothing really new for me there but it got me thinking about my own learning process. I'm reposting my comments here:

An excellent introduction though nothing beats examples for teaching. It would be valuable to show a poorly designed database, show why it's poor and show how to correct it.

The light bulb moment is an amazing thing too. Everyone (who's successful) has one. It's fun to think about how the brain works and why these sudden leaps of comprehension take place.

Most of my work now is with data warehouses so I do the opposite -- denormalization. As I was learning and implementing the denormalization process, it was interesting how much my understanding of normalization practices increased. I had to do things counter to my instincts and I made some mistakes along the way. This forced me to think more specifically about the decisions I was making while putting data into a star and, in contrast, I better understood the normalization process.

A couple years back, I was asked to handle the database design and implementation for a small IT-internal project used to track something (can't actually remember what for). I worked with a co-worker to flesh out the requirements. He would be designing the website and his first ideas about the database was to have just a couple of Big Tables and handle all of the record management in code. (As an aside, I see this tendency among a lot of decent programmers - mostly because they never learned better ways.) I steered him away from that and started breaking things up. He agreed we'd handle CRUD operations through sprocs and views allowing him to focus on the function of the website instead of wrangling data.

I made the mistake of letting him talk me into leaving one table less normalized than my gut was telling me since it only served a small piece of the solution and normalization ended up adding a few xref tables that he felt made things too complex. As I started writing insert and update sprocs for the subsystem, I saw it turning into an awful, broken mess requiring far more effort to manage than if it had been normalized. I jumped on correcting it right away despite his protests (though he trusted me enough not to protest too much). That taught me to rely on my instincts but it also made me understand the mindset of people who haven't learned to normalize. I think they are afraid of the perceived complexity behind it when the fact is, proper normalization makes the pieces of a project work together more cleanly and removes a great deal of complexity in the use and function of the database. This is especially true when you encapsulate tasks in sprocs (like p_AddUser or p_RemoveItem).

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.