BUSINESS IMPACT

Jul 08, 2016

Good Data Integration Design Patterns Are Good for Business

Meagan Longoria Posted by Meagan Longoria

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-data-integration-design.jpg

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.

New Call-to-action
Meagan Longoria

About The Author

Meagan Longoria

Meagan is a former BlueGranite employee. As a Microsoft MVP, she helps people understand their data and use it to learn and make good decisions. She works mostly with the Microsoft BI stack, doing everything from data modeling and data warehouse design to Analysis Services models to data visualization.

Latest Posts

Click here to register!