Overall Satisfaction with Oracle Data Integrator
In my prior role, I primarily used ODI as the ETL tool to transform data from relational systems or flat files into Hyperion Essbase. It was also the extraction tool from Essbase to various data marts/warehouses. ODI was used to map multiple source files/tables together, transform the data appropriately to align with Essbase nomenclature/naming conventions. ODI was also used as a scheduling tool to kick off command-line utilities specific to the Hyperion toolset and to manage interdependent data processing jobs for a given organization.
- Transforms and loads large amounts of data (gigabyte and up).
- Stores complex transformation logic in an understandable way that is easily updated after the fact.
- Managed multiple ETL user groups/credentials to segment data availability and execution pathways.
- ODI has a very arduous process for migrating components from one environment to another. I've found it to be error prone unless migrating a full schema which is not always a viable option.
- ODI has no web-based administration panel, all admin tasks must be handled via hard install.
- The scheduler tool is difficult to initially set up and not intuitive to manage. Takes a while to stand up properly.
- ODI is relatively quick to stand up for run of the mill tasks that are common for Hyperion implementations. The speed to deployment is a big plus assuming complex transformations or scheduling is not required.
- ODI interfaces well with the Hyperion toolset and is able to manage data and metadata loads well.
- ODI has allowed us to implement solutions that effectively massage and load data into Hyperion with little customization or one-off solutioning
- HAL, Informatica, Data Relationship Management and Hyperion Financial Data Management
ODI is less user friendly than FDM and DRM but is much easier to deploy than core ETL tools such as HAL or Informatica. The tool is easier to master and is usually more than capable of handling the run of the mill tasks required for Hyperion deployments. It has been a good middle road for deployments where an IT shop can manage small updates to SQL logic but does not want the full overhead of managing something like Informatica.
Oracle Data Integrator (ODI) is good when large amounts of data are to be processed and the ETL logic is maintained by a team of IT professionals. Where it loses out against other tools, is it cannot be managed by the data owner (i.e. finance) as the tool relies on SQL logic which is not always understood by the data owner. Coupled with the fact that it is not managed via the web but through hard install, it makes it difficult to diffuse ETL tasks from IT to the core business.