I work for a large trucking company in Chicago. These days I’m head of information services. But many years ago, right after I was hired as one of the low-end IT guys, I was asked to take on a month-long, “quick-hit” project using Microsoft Access and Oracle to capture shipment data and analyze it. I named it CTA/PTA, for Cycle-Time Analysis and Post-Trip Analysis.
I realized there was no standard method that I could use, so I created a standardized, event-based model of how trucks moved on our routes. Trailers start out empty, then they get loaded, then they get moved, then they get unloaded. Trailers move from point A to point B, loaded; and then they go from point B to point C, unloaded; when they reach point C, they get loaded again and go back to point A. That’s a triangular move.
In other cases you simply move from A to B, and then from B back to A. On top of all of that, I had to shoehorn in an intermodal model for trailers that go from train to truck to ship, which is an entirely different deal and never worked properly.
It soon became clear that there was no way I was going to be able to bang this out in a month. In fact, it took three months of daily meetings just to get to the point where I was ready to start creating a clear definition of how data should be captured and structured.
In the end I produced an 80-page document that defined how the data might be crunched. My bosses loved it -- and decided that I was not senior enough to run a project of this complexity. Soon I had a project manager, a business rep, and too many consultants. As the data model grew more complex, we realized that Access alone wasn’t going to do the job, so we brought in a tool from Business Objects for reporting.
Within a few months there was a 10-person team running my quick-hit project. While I continued to manage contacts with various business entities, I also began designing logical metadata models called universes. Most of them were made up of fact tables with 11 dimensions. I didn’t realize until much later that this was unusual. Ralph Kimball -- noted data warehousing design and development guru -- says that seven dimensions is the upper limit; beyond that data becomes too complex to understand. He may be right.
At this point, we renamed the CTA/PTA project SPHERE, for Shipment Performance Historical Events Reporting Environment. Several weeks later, the Dustin Hoffman-Sharon Stone-Samuel L. Jackson movie, Sphere, came out and bombed. I should have guessed we were in trouble.
Three years passed, and SPHERE was still “in development.” Costs were nearing $10 million. I still think it was a good idea, and it might have worked if the project manager hadn’t sabotaged the project. There were several critical moments when he made decisions designed to produce political gains for himself rather than improving the design of our developing system.
Fortunately, by this time I had several other projects working at the trucking company. Something told me it might be a politic time to slip discreetly away from SPHERE, which I did.
Knowing when to jump off a runaway truck, especially when it’s heading for a crash, is mostly a matter of timing.