Important Considerations When Implementing an ETL Tool
So you’ve decided to take the jump. It’s been years of hand-coding ETL in your business intelligence department. Everything from one-time loads, 3rd party data delivery, type 2 dimensional loads, and decommissioning projects. The team has built an audit frame-work, alerting framework, and thousands of uncommented, non-standardized code. Now is time for a new paradigm in your team as you plan on rolling out an ETL tool with the help of an implementation partner. They promise to be up in running in just 2 weeks!
In two weeks, you’ll have the team attend training, and start working in the new platform to develop new projects and convert the existing data warehouse. In just a few short months you’ll have an adept team of ETL developers buzzing along in the new environment.
All done after that, right?
Not so fast. Don’t fall into one of the many pitfalls of drastically changing your platform.
Top 3 Considerations for a new ETL Platform
Leave No ETL Behind…Literally
Sustaining your ETL jobs across different platforms will cost you time, money, and unnecessary risk. When taking the leap to modernizing your ETL, plan to completely leave the old platform.
Here’s the thing, in order to truly deliver value to business, this paradigm shift has to be complete. No, not in the first 6 months, or maybe even a year, but for each productionalized piece of work, plan on converting it to the new platform. This is going to be effective for a number of reasons, but ensure that there is appropriate time built into the next several business cycles to convert everything you currently maintain in a separate platform.
Think of it this way, every project in production on an old platform will require the team to spend support time. Not only on maintenance, but also bug fixes, and possibly enhancements. There will not be the time savings you see from using the new platform applied, and all the legacy framework will also need to be maintained. That’s not the end of it, because your team is also going to get more expensive.
If these systems remain in place, you’ve not really shifted so much as grown, and think about what the ultimate sell to business is in implementing the new software platform, it is supposed to be a cost savings measure. Now with two platforms, you’ll need two sets of skills on the team, one becoming increasingly specialized overtime as the expertise moves quickly to the new tool. Two sets of skill developing, but also two sets of skill maintaining the system, servers, doing code reviews, and performing SDLC.
So How Do You Do It?
Begin by identifying all productionalized work and begin prioritizing. Not only are the large pieces of work important, but interview your team and find out where the pain points may be and what code is the most dreaded one to fail, or which only one team member may know how to fix if it breaks. After you have everything identified and categorized, give an estimate for time for each. Then start the plan in motion within a project management methodology. These are likely going to be operational in nature, but in terms of a capital expense like a new tool, these projects could be folded into the original time estimate so they may be able to budgeted in a similar way.
Standards and Best Practices
The terms ‘standards and best practices’ are thrown around a lot by vendors, but in the context of an ETL tool, they simply mean the reproducible steps to achieve excellency. The challenge with implementing a new tool is identifying what those steps should be, and how they fit into your organization. They are important because it allows your developers to adhere to a standard and speak the same language when creating new work and reviewing each other’s work. Again, it helps with maintenance and enhancement of the work after it goes live.
For most ETL tools, there are vendor released lists of best practices and standards. However, many of these will not include considerations for your change management system, defect-handling system, or specifics in integration and security. You’ll need to generate these with your team
Team Buy In
A great way to pull together the best practices is to lean on your team. They already have some of this expertise, so it will be a matter of determining the best way to include those great ideas in the new software. Hold some discovery sessions and dig deep to see what questions you may be able to pose to the vendor or implementation partner on how to integrate the system with your IT department.
There’s a lot that goes on under the hood in ETL. Not only are the database involved, but networking, storage arrays, and application servers all come in to play. Begin your paradigm shift knowing that there needs to be some time to work out the kinks in a configuration. There’s always a bottle-neck and they tend to go unidentified until deep analysis of the underlying systems is performed. You will need to work amongst the technicians in IT, various application owners, and network engineers to maximize the performance in your environment.
Get ready to go deep into analysis. Pull your best technical folks and start pulling logs, performance logs, review indexing, partitioning, CPU utilization, memory utilization, disk I/O, and more. You want to ensure that the balance of performance across the environment allows for maximum utilization of available resources on each system. Performance tuning requires a cross discipline of technical expertise, but the pay-off once correctly tuned, is immense. Revisit the system tuning at regular intervals as part of your normal maintenance.
If you take these considerations to heart, you’ll have no problem delivering incredible value to your organization using a common ETL tool and platform. The volume and velocity of work you will accomplish will increase capabilities and business value of your team to your business. Get the word out! Prepare for a flurry of interest and requests as the value of your capabilities becomes more and more visible!