THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Context and Grain

Introduction: The World Series (1979) and Photography

Willie "Pops" Stargell started my interest in photography (...a funny way to start a post on a SQL Server blog site, but bear with me). Pops Stargell led the 1979 Pittsburgh Pirates to win the World Series. He was the MVP that year as well.

My Mom, in the only time I ever remember her gambling when I was a kid, bought a $10 spot in a World Series baseball pool that year. The pool was based on the 1's digit of the total runs scored by the Pirates and Orioles in the entire series with the person who had the 1's digits in the correct order winning $600, the person getting them in reverse order winning $300, and the organizer kept $100. The series lasted seven games that year. Mom was set to win the $600 if the final score of Game 7 was 4-1 in the Pirate's favor. Willie Stargell hit a home run that helped achieve that score, along with some good fielding (especially for a 39-year old player).

I had a budding interest in photography because some friends at high school were photographers. There was just no way we could swing $200 for a mid-range 35mm SLR back them. But Mom promised me if she won the big money she'd buy me the camera. She won and she bought me the camera.

I got to attend an interesting Yearbook Seminar the next summer held at Longwood University in Farmville, VA - located about 7 miles from where I currently live. I learned some fascinating things about photography and did a decent job getting shots for my senior year yearbook.

Photography Context and Grain 

One of the things I learned was this: If you take an out-of-focus shot, there's nothing you can do in chemical and light processing to bring that shot into focus. There may be (and probably are) digital things we can do nowadays, but this was the 70's. The very best you could do with an out-of-focus was produce a print no less in focus.

In the context of the photograph, the focus attribute was set when the picture was taken. The focus could not be improved after the picture was taken. It was the best it was ever going to be from that moment until forever.

Database Design Context and Grain 

Now here's a lesson we can take into database development.

Several for-instances leap to mind: granular resolution in a business intelligence data acquisition system, for one.

Imagine you are tasked with reporting real-time data for a business intelligence scorecard application. The client expects up-to-the-second updates from the data acquisition systems on the manufacturing floor and this is a project requirement.

Sure, you can do that.

Unless data is collected every five seconds on the floor. Then you have an issue. There's nothing you can do from your side of the project - since you merely read, store, transform, and display the acquired data - to "fix" this. You can give updates every second, but these counters (and metrics derived from them) will change only every five seconds.

In this imaginary scenario you cannot "re-focus" the picture. The best you can do is present the information at it's current grain - and no finer.

Software Development Context and Grain 

The same can be said for software development. I will state it in another way: Every decision to develop Feature A in a certain way is also a decision to not develop Feature A in any number of other ways.

There are consequences to choosing an approach, methodology, or context. These consequences are usually discovered well down the path towards a deliverable or release. Occassionally, talented developers can find a "silver bullet" - a fix that solves the current consequence to a past decision without breaking anything else. But this is unfortunately rare. Usually there are consequences to the consequence-initiated fix, and so on, ad infinitum.

There is a point early on in a development project where such consequences, if detected, can be addressed with relatively little effort. I use the analogy of deflecting an approaching asteroid: If you detect it early enough you can deflect it with a BB. Wait, and all the nukes on the planet won't stop it.

One rule of processes - I first saw this in The Goal - is "Losses accumulate, gains do not" (just call me Mr. Encouraging). Most good process measurement methodologies account for this. One way to think about it is to consider the example of a three-stage linear process where each stage is running at 90% efficiency.

<TrickQuestion> What is the overall efficiency of the system?
  <TrickAnswer> 90%</TrickAnswer>

90% is incorrect. It's a linear process. The output of stage 1 is 90%. The output of stage 2 is 90% of 90%, or 81%. The output of stage 3 is 90% of 81%, or 73%.

Consider a process-improvement initiative that results in 100% efficiency at stage 1. The output improves, but only to 81%. Consider the alternative - a reduction at stage 1 to 80%. Stage 2's output becomes 72%; stage 3's: 65%. The losses accumulate, the gains don't. To quote Foghorn Leghorn, "Figures don't lie."

Why Are You Sharing This, Mr. Encouraging?

There are a few reasons: First, this is a trap into which many a young developer falls. It looks simple but it is not. Life is filled with things that appear deceptively simple. Software development is but one of them.

Second, underestimating the effort required to accomplish any software related task is a trait shared by every good developer I know, plus me. We all think things will take less time than they do. I don't know why, but that doesn't make the fact any less true. Joel Spolsky has an excellent article on Evidence Based Scheduling that presents an interesting solution to this.

Third, it doesn't matter how good you are, this can happen to you. The odds of getting caught in a process-resolution trap increase exponentially with complexity.


Be aware during design / architecture phases, the decisions made today will limit future options. There's a side order of art included with the science here. Balance is the key.

:{> Andy

Published Saturday, November 3, 2007 9:41 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement