Oracle PL/SQL versus a commercial ETL tool
When implementing a data warehousing environment in a company, a question needs to be asked: whether to use already owned tools and technologies or buy a new, commercial ETL product. Having in mind the fact, that most big companies use an Oracle database, the question becomes: use PL/SQL to do the processing or buy an ETL tool?.
Before making a decision on that, the managers need to compare pro’s and con’s of buying a commercial ETL solution.
Strengths and weaknesses of using Oracle PL/SQL as an ETL tool
- Costs – PL/SQL comes along with the standard Oracle licence and if an oracle database is installed, PL/SQL can be used straight away with no additional costs. No additional hardware is needed. However, implementing ETL process in PL/SQL is a lot more time and human resources consuming process. It applies both to the implementation phase and later – production support and enhancements.
The designers need an in-depth knowledge about oracle and it may take months to become an expert in PL/SQL. The ETL tool like Datastage or Informatica can be learned during a few days training and in fact the designers don’t need to know much about programming, scripting and can do their job without a low-level IT knowledge.
From the other hand, a Datastage or Informatica expert may be far more expensive and less accessible than a PL/SQL consultant.
- Workload and time – it’s a factor directly related to costs. An ETL tool comes with the whole framework to simplify the design of the process which is usually an administration panel, GUI frontend, global options, documentation module, day-to-day operation management, failover capabilities, logging and reporting module, user management, connectors to different data sources, plugins, etc.
In PL/SQL most of that modules must be programmed manually and it may significantly increase the time of implementation.
- Flexibility – an ETL tools comes along with the set of mostly used components and it is rather difficult to expand its capabilities. For example, when we need to extract data to a non-typical format (for example EDI files, EPIC files) or process them in a non-standard way, then PL/SQL should be a lot more helpful.
- Efficiency – if the company uses Oracle databases, then the ETL processing using the native database language which is PL/SQL will be a lot more efficient. There is no better way to process data than well optimized queries issued on the database internal engine and apart from that very often an ETL tools operates on different server than the database which causes the need to transfer the data across network.
- Integration - The commercial ETL tools provide functionality to integrate with different systems, including connections to multiple data sources, operating systems integration, FTP support, plugins to ERP systems, etc. PL/SQL doesn't have that features and it has to be implemented externally.
The conclusion is - there is no easy answer to the question which approach is better. The most important thing is to review the company’s needs, calculate costs, estimate results and then make a choice between buying an ETL tool or using a PL/SQL processing.