Friday, January 02, 2009

Website Update

It's a new year and time for an update to this website.    

While the update is in progress, I'm going to turn off the blog, so I've put my next posting live a couple days early.  I hope to have the upgrade done next week so be sure and check back.  It will probably take a couple of weeks to get the kinks worked out.

Thanks for the support.  

A Positive Story from the Field

Yesterday I relayed a story about a consultant who made a process improvement of little value to the client and in doing so, created a lot of headache.  Today I'll recount a positive story of a value-added process improvement that made a noticeable difference.

One of the clients I was working with asked me to take a look at their database performance.  They felt that it was pretty poor and they were right.  One of the things I noticed immediately was the over-analysis of tables.  To explain, they were using Oracle and had an ETL job that analyzed the tables as soon as the load job finished.  The particular instance had a small amount of daily updates but the statistics were going stale on a daily basis.  When I started looking at the load job, I found that it did not differentiate between true and false updates.  A true update is when a data component changes, and a false update goes down the load option without being changed from its' prior state.  

Generally in good ETL architecture, the architect will put an escape clause to only load the true updates by doing a before and after comparison.  This keeps the load process free from only changing the update timestamp column as was happening in this instance.   

We had to change this job to only load the true updates in the target table, and in doing so we solved the stale statistics problem and also cut the load process by a factor of 10x.  By changing this job we were able to disable the analyze table routine thus saving more processing time.   The client was quite happy in this instance as the total time savings will be in the hours by the time this architecture change is propagated across all their subject areas.

Seconds don't sound like much until they begin to aggregate over a period of hours.  In my next post, I'll talk about separating DBA maintenance tasks from ETL processing.

Until then, happy data warehousing.  

Stories from the Field, Part 1

As I promised back in September, I would write about some experiences I've had with consulting.  I expect there to be many parts to this title, so I'm going to set some ground rules.
1. There will be no client names mentioned (or person names).
2. No part of my posts should be interpreted to insult a client or person, I am only reciting the event as it happened.
3. Names may be modified to protect the parties involved.

Now that the disclaimer is done (and I mean it, especially #2), let's start with our first story.

I was on a team working with a few other consultants on a large data warehouse project.  One of the guys (not brought in with us) was an independent consultant.   Let's call him Rob, and Rob is a good guy, very interesting and funny, and his stories caused great laughter at lunch.   Rob had a way of being very straightforward and profane, a quality that does not go well when working at client sites.  It was difficult for him to say a complete sentence that didn't include the F-bomb or using the G-D word, or any other curse word you can imagine.  This 'quality' frustrated a lot of the employees and gave me great pause into engaging him in conversation in front of other people. 

One day in a meeting Rob had a great idea.  In the client's ETL jobs, all jobs were designed to terminate at the first sign of an error, send a page to the duty pager, and have manual intervention to restart the process.  Without getting into whether this is good architecture or not, it is what it is, and Rob had an idea that we could create a routing using an exception handler in all ETL jobs instead of just terminating them.  He said it would be easier to code the jobs this way, and in a way he was correct.  Rob's problem was that he was solving an issue that wasn't a problem for the client - the client was happy with the way the jobs previously functioned, and Rob's hours and effort to retrofit working jobs with this new technique took valuable time away from solving problems that the client had identified.   However, the manager told Rob if he could get a sample done quickly, he would be open to changing the architecture.  

A couple of weeks later and Rob presented his working idea at a team meeting.  I had cautioned him in private about solving a problem that the client didn't seem to have, and my colleague suggested to him that this approach didn't take into consideration all the possible failure scenarios.  Rob pushed on and in the meeting, my colleague again suggested in front of everyone that he wasn't convinced this technique would work as described, and cautioned Rob to test it with every possible scenario before putting this into production.    The manager approved this code to be moved to production only after he had some tests for every scenario that had been described by my colleague.

By now you are probably starting to imagine the outcome of this scenario, and if your outcome includes the word disaster you are correct.  Rob moved this code into production and the first night there was a hiccup, but because he hadn't tested correctly for that failure scenario, incorrect data was loaded into the production tables and the job did not abort as it should have.  The next day people around the company were noticing data that was incorrect on reports and poor Rob had some explaining to do, which he did in a status overview laced with enough profanity to make a sailor blush.  

In the end, after much testing and some more issues, Rob was able to make this routine work correctly but the client largely considered it a failure and chose not to move forward with this approach, now leaving one subject area with this architecture while the remainder used the legacy code.  

For this story, let's analyze what happened here.  Using the flowchart methodology,
Rob identified a process improvement that the customer did not value and Rob did not adequately test this process improvement, causing the customer to suffer delays and bad publicity in their company, thus causing them to be shy on implementing further process improvements.

I'll make the next story an example of success, but the moral of the story here is that if you are going to go out on a limb to perform a process improvement, make sure it's something that the customer values and last but not least, make sure that it works.

Thursday, January 01, 2009

A Year in Review

First I would like to wish everyone a happy new year.  In many ways it's difficult to comprehend that another year has come and gone, a year that was challenging for many.   I am pleased to say and say it with some trepidation that 2008 was an excellent year for myself and Durable Impact.  DI is making some real progress in acquiring projects and clients and now I have a very good springboard for launching into 2009.  

One thing I didn't quite accomplish this year is giving a lot of presentations.  By my count, I only did five events this year.  I was very busy building DI up and traveled about 32 of the 52 weeks.  Traveling is not conductive to building a brand by giving speeches, but it is better to travel and make money than to give talks for free.  I have been and will always try to find a happy medium.  

SQL Saturday last February and the Day of Data event were great successes.  Pam Shaw and I are hosting another SQL Saturday on January 24 at the KFORCE facility in downtown Tampa.  Unfortunately, I was not able to put together a Day of Data to go with the Saturday, so maybe I'll host one in the spring. 

With 2008 we saw a launch of SQL Server 2008 and some great improvements to the SSIS tool suite.   With the coming launch of Windows 7 in 2009/early 2010, it's looking to be another good year for Microsoft. 

As for my personal goals, I'm just interested in working, building up DI, helping out the community where I can, and having a general all around good time.  If you're ever in Tampa and want to chat, drop me a line through the DI website (linked on the left) at the Contact Us form.   I will also try to put up more blog postings, as I have a fairly strong amount of readership and don't like to disappoint.  
Here's to a great 2008 and an even better 2009.