Design patterns may sound like a technical concept that only developers care about, but the presence (or absence) of good design patterns for moving and integrating your data can have a significant business impact.
In software engineering, a design pattern describes a recurring and useful solution to a common problem in a particular context. A good data integration developer can look at existing code and abstract it into patterns. As we go through our careers we build up (mental or documented) libraries of design patterns for solving common problems. We build hundreds of processes to copy data from a table in a database and insert it into a table on another database. After a while we determine what we consider to be the best way to complete this task, and we reuse our optimized logic the next time we need to complete a similar task.
Good SSIS Design Patterns Decrease Total Cost of Ownership
We use SQL Server Integration Services (SSIS) to exchange data between mission critical transactional systems and to populate data in data warehouses that our organizations depend upon to make informed decisions. When our ETL isn’t running smoothly, we are slowing down the business.
Different organizations, and even different people within organizations, may solve the same problem in different ways. If a data warehouse team allows development to be a free-for-all they end up with a mess of a solution that is difficult to understand, difficult to support, and difficult to enhance. Employee morale decreases when everyone is too bogged down to make progress and achieve goals.
If SSIS developers communicate and discuss design patterns, they are likely to create better solutions because they can incorporate the best ideas from each contributor. If the team commits to employing consistent design patterns across people and projects, they can create a well-oiled machine.
Developers don’t have to spend the time and effort to re-think how to solve a common problem if they already have a design pattern for a solution. This results in decreased time to delivery. And over time, it facilitates better estimates of time and effort because the team has solved similar problems before in a similar manner, and they know how long it took them.
When SSIS project and package designs are consistent, it’s easier to more thoroughly test the packages before putting them into production. Required testing time is decreased and data quality is improved. If the same design pattern is used over and over again, the last SSIS package to use it benefits from all the other packages that came before it because they helped to improve the pattern over time.
Support, maintenance, and integration costs are usually underestimated when calculating total cost of ownership. When the support team is working with consistently designed SSIS projects, they are more effective at troubleshooting because they know where to look when someone encounters an issue. They don’t have to dig through obscure properties or wait on the developer who wrote it to become available to answer questions. Decreased response time and issue closure statistics show the team working efficiently.
DBAs and teams whose applications touch the data created by the SSIS packages get used to the consistency of the work and can better plan for their interactions with the resulting data and database. This makes security and performance tuning easier.
Consistent design patterns can help business analysts and data scientists gain an understanding of how their data is populated and refreshed over time. They understand what questions to ask when analyzing their data or requesting new data integration efforts. And they can feel more confident in data quality because of the improved testing performed before release to production.
The decrease in support and maintenance cost creates more capacity to add value through new data or new capabilities to analytics solutions that improve analysis and decision making in the organization.
Please contact us to discuss how BlueGranite can help you create and implement good SSIS design patterns that decrease your total cost of ownership and enable you to build new BI capabilities.
