Agile Design Documentation with SSIS

So I've got this project - a very small Data Mart that we plan to build in less than a month.

I've talked through my design with the junior BI Developer and the Business Analyst who will be assisting me, but only briefly. How do I communicate the design to the team in a way that is efficient and effective? Something that has a bit of staying power, but not too much?

For efficiency and effectiveness I always try to "write the pictures first" - which, for an ETL package, means a flowchart with annotations.

I learned the concept of "writing the pictures first" from a college professor in Communications who taught me that when your medium includes a visual component you should assemble the visuals first. Tell as much of the story as possible with visuals and only after that avenue has been exhausted do you fill in words where the pictures have fallen short.

Thanks to Dr. Shook, when I document anything I always start with flow charts, tables, diagrams, charts and then move on to fill in the gaps with words. We all know it intuitively; specs with flowcharts and models always get us closer to communication than five paragraphs describing sequence and dependencies.

"Writing the pictures first" is especially sage advice in our globalized industry where our teams are assembled with people from diverse backgrounds.

And what about that "staying power but not too much"? What do I mean by that? It's a concept Scott Ambler wrote about in his book "Agile Modeling" almost a decade ago. Ambler notes that if you create an artifact and present it to your client, someone, somewhere it going to think that means you're going to always keep it up to date.

The effect of this unfortunate truth is that I, at least, have learned to avoid documenting things unless my arm is twisted up behind my back until I utter "Avuncular" or in this case "Okay I'll document it." The crazy thing is that I love documentation, I love writing it. I just don't
like maintaining it once it's outlived it's purpose. If the documentation isn't driving the development forward, I find it really frustrating to use valuable project time documenting history.

I find myself heading for the white board more often than not these days. I've figured out that I can't be asked why I didn't keep a document up-to-date if no one can prove it ever existed.

Funny thing is, the white board drawing often becomes the center of the project's universe. Everyone refers to it. Over and over. For weeks on end. Conversations shift from one cube to another because someone reached out mid-sentence to point to an arrow running from here to there and found "our drawing" wasn't within reach.

I want to have that drawing - that visual worth a thousand words- but I don't want to have to maintain it unless there's a really great reason to do so. I want the best of both worlds. And I think I've figured out how I can have it all.

I do my design in SSIS.

For one thing, I already have Visual Studio on my machine. The client made sure of that. I don't want to have to wait to get Visio approved and installed. My ETL design is going to be implemented in SSIS anyway, so why not draw it there in the first place.

Why not stub the whole thing out? I can write descriptive annotations out to the side to "fill in any gaps." The annotations will describe what I think I am going to do and the order I think I am going to do it. I can also pull in tasks - whatever type I think I might use for each annotated step.



When I have a rough design laid out I can print it to PDF or throw it up on projection screen and we can do a working design session - my whole team - by editing the skeleton of a package flow. As my team and I discuss the pros and cons and make better decisions about the best design I can edit the annotations right then and there.

When the session is over I can output the PDF again, print it and post it somewhere.

SSIS is very visual - and in that respect meets my criterion of "writing the pictures first" very well.

But the real beauty of an SSIS design flowchart is that it doesn't have to be maintained. Well, it sort of can't be maintained and can't keep from being maintained as the same time.

The design can't be maintained because it becomes the code. So as a design it ceases to exist. Then again, it becomes the code, the developed work, and in that sense it is always up-to-date as the documentation of the finaly implementation. Could it get any better than that?

When the design session is over I could potentially even hand off the SSIS package to the junior developer and they would have a sort of design scaffolding system within which to work. But even if I don't transfer the work, even if I fill in the details myself, the project is better because we have the common reference point, the visual that we communicate around without creating a documentation burden that will drag our project under.

In the end, all you have left is the real working code, and if you kept the annotations precise you have self-documenting code.