Data migration is the first step when moving your mission critical data to an Oracle database. The initial data loading is traditionally done using Oracle SQL Loader. As data volumes have increased and data quality has become an issue, Oracle Data Warehouse and Oracle Data Integrator have become more important, because of their capabilities to connect directly to source data stores, provide data cleansing and profiling support, and graphical drag and drop development.
Here i touching SQL Loader, External Table concept and ODI ( Oracle Data Integrator ) in this context. Not covering OWB -Oracle Warehouse builder as my knowledge is limited in that.
SQL LOADER – SQL Loader is the primary method for quickly populating Oracle tables with data from external files. SQL Loader is typically used in ‘fat file’ mode. This means the data is exported into a command-delimited fat file from the source database or arrives in an ASCII fat file. With the growth of data volumes, using SQL Loader with named pipes has become common practice.
External Table : The external tables feature is a complement to the existing SQL Loader functionality. It enables you to access data in external sources as if it were in a table in the database. Therefore, standard SQL or Oracle PL/SQL can be used to load the external file (defined as an external table) into an Oracle database table.
if you know SQL well, then it is easier to code the external table load SQL than SQL Loader control files and load scripts.
Oracle Data Integrator (ODI) –Oracle Data Integrator (ODI) is a product that Oracle acquired from Sunopsis SA in 2006.
ODI is a data migration and integration software product, providing a declarative design approach to defining data transformation and integration processes, resulting in faster and simpler development and maintenance. Based on an Extract-Load- Transform (E-L-T) architecture, Oracle Data Integrator, unlike traditional Extract- Transform-Load (E-T-L) products, loads the data into the target database and then does the transformation processing.
By combining data, event-based, and service-based integration, ODI is able to address varying needs from legacy data migration, data warehousing, and business intelligence to Master Data Management, Service Oriented Architecture, and others (such as cloud computing).
The most likely instances or use cases when ODI would be the Oracle product or tool selected are:
- Bulk loading data on a continuous, daily, monthly, or yearly basis.
- Direct connection to ODBC and JDBC compliant databases for data modernization, migration, consolidation, and physical federation (data mart, data hub, and data warehouses).
- Web services-based data migration.
- Data migration or Change Data Capture (CDC) that offers a graphical interface, scheduled data movement, data quality and cleansing, and data profiling.
Now a day, more and more projects are using ODI and moving away from SQLLOADER, do reach me out for any questions around ODI.