Episode 59

There is a myth in software development that applications are built by lone programmers. There is this phenomenon of rock-stars performing heroic feats of self-sacrifice to single handedly deploy world changing applications all by themselves. However, any application that is developed today is the result of the cumulative contributions of thousands of smart people. Walking through my personal journey of learning SQL/SSIS/Power BI I hope to convince you that, even in the most isolating circumstances, software development is most productive when viewed as a social activity.

Modern organizations need to operate both at scale and with precision. These requirements are beyond the capacity of a single individual no matter how brilliant or capable they are. At the highest level, it is my opinion that the goal of software development is to produce quality working software quickly. There are numerous competing and overlapping ideas that attempt to describe the “best” way for software to be developed. Two ideas that help achieve this goal are Continuous Delivery and Continuous Integration.

DevOps VirtualTech

Continuous delivery is a software engineering approach in which teams produce software in short cycles, ensuring that the software can be reliably released at any time and, when releasing the software, without doing so manually. It aims at building, testing, and releasing software with greater speed and frequency. Continuous integration is the practice of merging all developers’ working copies to a shared mainline several times a day. In short, we want to deliver small features or changes regularly. If we consider technical debt, which as been covered in this blog series, the concept extends all the way down to the single line of code as the longer a feature remains isolated from the larger system (continuous integration) or the longer it remains isolated from users (continuous delivery) the greater the risk that the new code will break something, or that revisions will be more expensive after user feedback.

How does this all fit in with lone programmers? If the development team is one, there is no need to worry about feature branching. If the developer happens to be also the end user, development can be done directly in the production environment. This is exactly the case for my learning journey. The task was to replace a set of spreadsheets I have been maintaining for a video game, EVE Online, that I have been playing for over a decade. These spreadsheets were my pride and joy and I used them to showcase my Excel brilliance. It was not until I joined MERAK and engaged with professional developers that elements of my naivety became visible. The turning point was attending a PowerQuery talk at a Microsoft Ignite Conference that highlighted how my knowledge only covered a tiny fraction of what was possible. The major learning lesson was that no matter how confident one is in their skills there is always more to learn, so lose the ego, and be open to influence.

Two years ago, I started the process of replacing the functionality of the spreadsheets with a combination of SQL, SSIS, and Power BI. With Power BI being a natural extension of Excel, the first stage was to replace all Excel visualizations and logic tables with Power BI acting as the new front end. This took about a year to fully realize with numerous re-writes over that time as knowledge gained on each pass would highlight architecture or implementation weaknesses worthy of a re-write. Later, with Excel acting both as a database and the ETL engine for requesting and mashing data from numerous REST endpoints, refresh performance was horrible both for Excel and Power BI. Excel is magical, but it is not a great tool for this purpose. For anyone learning Power BI, and more specifically PowerQuery and DAX, I strongly recommend going through the exercise of building the same solution with the first iteration using as much PowerQuery as possible, and then on the second, using DAX as much as possible. While both systems are extremely flexible, if done right, they are highly complementary. A general rule of thumb is do everything you can at the row level in PowerQuery and leave aggregations for DAX.

Power BI

Once modestly comfortable with Power BI, the next task was to replace the data storage and ETL layer with SQL and SSIS. This step would have been impossible without guidance from MERAK developers. The replacement was done one table at a time focusing on datasets that ran over the row limits of Excel. After about 6 months Excel was retired. Refresh cycle times went from 45 minutes down to three. Super fast, easy macro level control of connector parameters, no row limits, automatic backup, deeper analytics capacity, and so much more. The new system operates at a completely different level compared to the old one.

Throwing away all that messy collaboration stuff might initially seem like a productivity boosting decision. Personally, I have moments of intense productivity: when my environment is free of distractions and I have had some time to settle my mind on a problem, with good music in the background. This highly productive state is isolating. It’s that grooving, head bopping, keyboard smashing, with the logic flowing as fast as I can input it. There is an intense feeling of pride after such a session as it feels like much progress has been made.

In the previous few months, I have been working on a significant re-write of SQL and Power BI code. Now that I have a good grasp of basic SQL script, this re-write has had numerous moments where I was in that groove state. While the nearly finished system works and feels like a great accomplishment, it was not until I began showcasing the result and speaking with others regarding its architecture that once invisible flaws began showing themselves. Now one way to rationalize the flawed process is to communicate the future work as “performance gains”, “refactor for readability”, “hardening”, “bug fixes”, “quality check”, etc. If I checked-in with others significantly earlier many of the flaws could have been dealt with before they grew to a state requiring large revisions. While this latest revision employed code structures, I have used for the first time, the isolated iterative process could have been significantly more efficient if I connected with others earlier. Instead I learned the hard way by making mistakes.

Bad Query

Ok, so just replace me with a more experienced and competent developer. The kicker is that the problem will persist, as future flaws will scale to match the competency of the next individual. While a better developer would not have made the newbie mistakes I did, there are potentially more costly revisions waiting for the organization due to the increased complexity capacity of a stronger developer. Now scale from a single developer to an entire team of solo rockstars and we have a perfect storm where significant talent and effort could be wasted. You do not have to look too hard to find stories of organizations throwing away years of development effort as individual features are unable to integrate with each other.

People are shaped by their environment. We are highly social and will quickly adapt to the norms of the group to fit in. This is why business strategists spend so much effort and time on corporate culture. Software development practices will be shaped by the culture of the organization as well as the tools (and governance structure) used to facilitate the development practice. From a culture perspective we want to acknowledge that errors will happen and that finding errors is not a source of blame but a source of excitement and learning. Paired programming, code review, lunch and learn, or a comfortable leisure area; use the tools that work for your organization to encourage developers to socialize and share ideas. Structure development governance practices that ensure work is committed regularly and branches are not left to fester. Acknowledging that software development is a highly social practice is critical to keeping your organization technology relevant.