Monday, October 27, 2014

Go Go, Scurry Scurry

I work in an IT shop that has always been a bit chaotic in its approach to creating software.  This is slowly changing, but I deal in an environment of legacy applications, written over the last three decades. Applications written for mainframes.  For PCs.  For the Internet.  Written using a dozen different programming languages and even more philosophies, in dozens of semi-independent locations worldwide. Data is stored in several different kinds of databases, from a half-dozen vendors, and often, it's variations on the same data.  Which version is the master data, the version that the company can count on to always be right, to always be representative of its relationships to its suppliers and customers, linking manufacturing and logistics?  There are always gaps between what data one application needs and what another one can provide.  Every programmer has his or her own style of writing program code.

Did I say chaotic?  Schizophrenic might be a better, if colloquial, description.

In truth, this is not unusual.  Companies merge, form strategic alliances, break apart.  People come and go, change jobs, learn to work differently.  New technologies catch on, then fall out of fashion. And always, artifacts remain, encoded knowledge of the ways things used to work, resistant to change but evolving nonetheless, while cutthroat politics wax and wane.

Contractors are another kind of artifact.  They are often highly experienced, and their encoded knowledge tends to be arcane and specialized.  You would not be terribly off base to think of them as grizzled survivors of the IT wars, quirky veterans who have found their lucrative niches.

Arthur was such a contractor when my company brought him in to work with the maintenance group in 2005.  He looked distinguished and patriarchal with his dark horn-rim glasses, salt-and-pepper hair, and black eyebrows.  He had around 25-30 years of IT experience behind him.  He fit the quirky image:  he used Outlook to organize not only his time and email, but his notes about everything, the way people now use OneNote or EverNote.

And his niche?  The ETL tool in Sql Server 2000.

Don't let my use of a three letter acronym throw you.  ETL means Extract, Transform, and Load. Such tools are typically used in what used to be called Decision Support and is now usually called Business Intelligence.  The Sql Server ETL tool was called Data Transformation Services, DTS for short.

A typical use for DTS is to read data from a database, summarize it, and write it to a spreadsheet. And how do we do this?
  • Sign on to Sql Server Enterprise Manager.  
  • Find the database you're working with, select it, and right click to open a context menu, then click on Data Transformation Services.  
  • In the window that opens in response to this request, you have a blank work area to the right, a column of icons to the left, and a menu bar at the top.  The icons are divided into two sections: Connections and Tasks.  
  • Find the Database Connection icon and drag it to the work space; when you drop it, a properties window opens. 
  • Enter the database details, server, name, login id and password.  Click Ok.
  • Find the Excel -- this is, of course, the usual suspect -- Connection icon and drag it to the work space.
  • Enter the path to the spreadsheet, including file name, into the properties window that opens when you drop the icon in the work space, then click Ok.  Be aware that the blank spreadsheet must exist before you do this.
  • Select the Database connection (data source), then the Excel connection (data target), and click Workflow in the menu bar.  This opens a dropdown menu; click on Completion.  This adds an arrow between the connection icons, pointing from the database to the spreadsheet to show the direction of data flow.
  • Right click on the arrow and select Properties from the context menu.
  • The resulting pop-up window presents a radio button list of options.  Select Copy from database.
  • The next window has five tabs; we are concerned with the first three -- Source, Destination, and Transformations.
  • On the Source tab, select the database table to read and specify the desired data columns.
  • On the Destination tab, select the spreadsheet columns to write to.
  • On the Transformations tab, select each pair of corresponding source and destination columns, one at a time.  There is a dropdown that gives a list of choices in how to process the data, ranging from a simple copy to a custom program; this time, select Copy.
  • Once all the desired data has been specified, click Package on the menu bar, then Save As from the dropdown menu.  Give the package a meaningful name and save it in the database.
  • Run the package by once again clicking Package on the menu bar, then Execute.
  • Debug if necessary.  
  • Repeat the previous two steps until there are no runtime errors.
  • Confirm that the data in the spreadsheet is correct.  If it is, we're done!  If it isn't...
  • This is a simple usage of DTS.

Arcane and specialized knowledge, indeed.

During the first half of 2005, Arthur and I were part of a team upgrading a corporate product costing website.  We were adding a new company location, which was significant because data for our different brands was segregated by location.  Along with adding the new location, we were redefining our database keys; this required changes to almost every query in every one of the roughly thirty pages in the site, which was my role, and equivalent changes to the each of the seven DTS packages, which was Arthur's role.  

I was also charged with keeping Arthur on task, which was a challenge on more than one level.  I was not a supervisor, and I had no experience, at that time, with the technology Arthur was using.   

It wasn't long before Arthur started asking to add data to each spreadsheet and database extracted with DTS.  That was not his task, as I understood it, so I told him not to.  He went around me, to the software architect who designed the costing web site and got him to okay the changes.  Certainly not the smoothest example of teamwork, no?

We kept working like this until the project reached fruition:  Implementation Day!  I did my documentation, I moved my program changes from the testing environment to production, and I coordinated the database changes with the database administrators.  Just before 5:00, Arthur dropped by my cubicle and leaned against the wall with one of those grins on his face, a grin that makes you suspect that the person wearing it isn't quite all there mentally.  

I realized then that there was probably a gap between what was and what should be, so I asked, "Arthur, have you coordinated moving your changes to production with the DBAs?"  "No."

You expect certain things from the professionals you work with:  competence, punctuality, responsibility.  And if those expectations aren't met...

After telling me that he had not fulfilled his responsibility as a project team member on this day, Arthur kept standing there, leaning against my cubicle wall, grinning that stupid grin.  I looked at him and said, "Go go, scurry scurry." 

He did go, he did scurry, and better late than never, he completed his part of the project.  

This was not the first time that Arthur had been rather cavalier in his duties, and his contract was terminated a couple of days later.  He asked me if I would be a reference for him in his job search, and I told him I would.  It is fortunate for us both that no one ever inquired into my experience working with him, as I would have told the unvarnished truth.

It's amusing, what I said to Arthur that day, but I'm not proud of it.  I wouldn't have spoken to a child in such condescending tones.  I've learned to handle both myself and other people better, as I've gone gone, scurried scurried.