By Jason Jones, Key Performance Ideas
ODI was originally developed by a company called Sunopsis. After Oracle saw what it could do, how it was architected, and identified that it would fit very well into their own portfolio, they purchased Sunopsis in 2006. ODI is commonly referred to as an E-LT tool, rather than the more common industry term ETL. This simply means it is an “Extract, Load, Transform”, instead of “Extract, Transform, and Load”. In a nutshell, ODI is a tool for moving data from one place to another and transforming it according to business rules and needs.
Data is typically moved between relational databases such as Oracle, Microsoft SQL Server, and others; to and from Essbase cubes including Hyperion Planning, text files, web services, and more.
ODI is Oracle’s strategic product for data integration, and they are and have been putting significant resources behind its development. This is the tool to use moving forward. It’s also the evolutionary successor of tools such as Oracle Warehouse Builder.
ETL vs. E-LT
Let’s review for a moment this notion of ETL versus ELT. Let’s say we have transactional customer order data in one database system, and we want to load some or all of that data to a different server that analyzes those orders. How do we accomplish that?
In a traditional ETL architecture, we have a third server, the ETL server, acting as the middleman. It extracts the data we want from the source system, transforms it based on the established business rules, and then loads that data to the target system. This works well enough, but can have some downsides. The ETL server in the middle needs to be a robust and often expensive server since it’s working with a lot of data – not just for this one job but also for dozens, hundreds or even thousands of jobs.
Now let’s take look at the E-LT architecture. We are going to say that the source and target are already robust and very capable relational database servers that we manage. In fact, the source and the target might even be on the same server already. What if we could leverage this existing capacity by having the database servers do the heavy lifting for us? This is what ODI does: it conducts the ELT process through our existing infrastructure, effectively removing the need for a middleman server processing all of the data. Of course, if we need ODI to process the data as a middleman, we can easily configure that as well – ODI is very flexible when it comes to configuring data movement processes.
Now, let’s take a look at ODI in action. My client, a large health services company has a significant Oracle database footprint with many schemas, in many servers. They were in the process of rolling out Essbase, which will give them even more places data needs to be loaded. Additionally, they were transitioning to new database servers, so the physical server used in many of their data movement processes was about to change. Lastly, their data movement processes have grown organically over the years, so there is no real unified strategy for moving around data. In fact, it’s just “get it done, somehow”, through a combination of SQL scripts, batch files, custom programming, and other things. Although this approach can work, it typically starts to break down and become unwieldy as more jobs are added and complexity grows. Therefore, they turned to ODI to keep their situation under control.
View my webinar and hear the full story on my client’s ODI implementation. Plus, I’ll share the seven top lessons learned along the way.