What I expected…
The ask was typical: a 3-month engagement with possible extensions; make an existing SQL report more efficient; and they needed someone strong in Excel. How did this turn into a 3-week gig? I think the answer was – the devil is in the details. The project both turned out to be bigger than was originally thought and determined that it could be done in house for longer term success. Which was fine because I like to work myself out of a job – to provide insight and expertise and then teach the ropes the folks that will have to live with and maintain the system.
I’m used to projects rolling out a little differently than the initial request or specification After a brief orientation to the workgroup and the business requirements, I proceeded to read and translate the query that had morphed into an 80-page SQL. As I began to deconstruct the SQL that produced the main report, I soon realized this was not a 3-month project. I focused my efforts on showing the complexity and project scope through the documentation and beginnings of a plan for a rewrite. By the end of a week, I had outlined a pretty good framework and painstakingly plodded through the query to demystify it. The hunt was to look for opportunities and improvement in clarity and efficiency.
At the beginning of the second week, the client announced to me that they were going to bring the project back in house within 2 weeks. Chopped! Yes, it was a bit like taking these fragments, deconstructed, and putting together a recipe for transformation into another edible dish. I focused on getting the documentation to a state that someone could follow. And I found I had a lot of fun. I’d do another project like that in a heartbeat.
The rest of the story…
What I ended up doing isn’t probably that interesting to most of you, but I’ll tell the story just in case you found this blog and you want to know where to begin to dissect a SQL. Drop me a line firstname.lastname@example.org if you want a sample tracking spreadsheet.
The technical journey: Steps in deconstructing and documenting a SQL
- If your SQL editor allows it, print your SQL with line numbers to pdf. Otherwise, dump the SQL to Word and put a line# on the document, then print that to a pdf so you don’t change any line numbers. The pdf created will be the source of truth for line numbers and references in your documentation.
- Find the final output of the query and make yourself a little table or Excel spreadsheet, listing the columns and some sample data if you can get it. Be sure to look for any intermediate or iterative versions of that output file, because perhaps there is, as in this case, a (warning, SQL speak??) created table in the database that was used to create the final view.
- Spreadsheet maps
- On a separate tab of Excel or Google Sheet, create a map of all the tables used and any aliases if there were any. Documentation of all aliases is helpful in tracing the data back to the source.
- Perhaps you’ll have a tab that just lists the lines or groups of lines and explains what is happening.
- List the fields and output on any iterative queries. Use as many tabs as you need to accurately deconstruct the SQL so that you or whomever follows you will have the best clues possible.
- Document any oracle packages used, stored procedures or views that may be called but whose fields and logic aren’t overtly listed in the query
- Make notes right in the SQL code.
- As you proceed create the first tab of the spreadsheet which is a summary to explain the tabs and SQL process.
Just keep working through the queries in as systematic a fashion as possible. You have to look for queries that call views, stored procedures, functions, or packages and just keep on unraveling until the puzzle no longer looks chaotic and has turned into a reference document. There is no one size fits all when you have a SQL that’s been morphed by numerous programmers over several years.
Similarly, rarely is there a one size fits all contract when you hire someone to help you with this kind of project. While so many arrangements launch at 40 hours per week for 3 months, we prefer to scale the contract to your needs. Maybe you only need 10 hours a week, or even 10 hours a month if you just need some guidance or ongoing support.
Hope is not lost. Short term projects could be very beneficial for organizations. Got a disorganized library of reports that need cataloging? Have some reports that haven’t been tested and need to be? Need just a few weeks of help? Call us direct, we can probably help you out.
Of course, you can call me to chat – pick my brain. Maybe your situation is better than you think ??. Call today, you might just get the solution you’ve been searching for.
Call 615-373-1466 or contact email@example.com to explore your options.