- 10+ years of experience as an application, ETL, database, Business Intelligence and data warehouse Developer/Data Modeler on various ETL/BI products and around 4+ as Data Architect.
- Co - Author of ODI blog ODIExperts.com and Developer of Open Source ODI driver ODI JDBC Excel, ODI JDBC Access, ODI Parallel and ODI HTML Email.
- Expert in Installation, Configuration, and Development of Oracle Data Integrator (ODI) objects and expert in ODI SDK, Java and other programming languages.
- Expert in developing out of box ODI Knowledge Modules and various versions of ODI.
- Extensive experience and expertise in Data Modeling and Architect. Designed and Architected various scalable OLAP & OLTP ranging from XML Data Model to 3 Billion Rows Dimensional Model.
- Intensive experience in Database and Non Database such as Oracle (including Exadata), SQL Server, DB2, Informix, MySQL, Json, XML, REST Web Service and so on…
- Extensive experience with Big Data tools and languages such Hadoop, Hive, Pig, Impala and HBase few including group projects.
- Experience with other BI tools like Informatica, Data Stage, SSIS, OBIEE & RPD, OWB and others.
- Expert in RDBMS concepts and experience in creating, maintaining and tuning database and database objects and experience in database architecture, process flow diagrams, data flow diagrams and ER diagrams.
- Multiple experience mainly in database development using SQL that includes DML, DDL and DCL, TCL, PL/SQL that includes cursors, ref-cursors, Bulking techniques, collections (nested tables, partitioned tables, anonymous, stored procedures, functions, triggers, packages, materialized views, dimensions, cubes, bitmap indexes, index organized tables (IOT), External table and dynamic SQL etc. performed query optimization, database performance tuning etc.
RDBMS: Oracle 12, 11g, 10g, 9i, 8i, SQL server 2000, 2005, 2008, Informix, DB2, MySQL, Teradata, PostgreSQL
Data Modeling Tools: Visio, ER-studio, Oracle Data Modeler
Oracle/SW Tools: Oracle Data Integrator 12C,11g,10, Informatica Power Center 4.8, 6.2 , Hyperion EPM 11 Suite, OBIEE, SAP BI, FI, MM,SSIS and more..
Languages: SQL, PL/SQL, TSQL, Shell Scripting, ODI API, Java, Python, Groovy, Hive, Pig and more…
Senior ETL Architect/Data Modeler/Developer
- The client maintains Tower Service Ticket in Web Service Tools which sends data in JSON format. This data is cyclic process once a token is passed. ODI directly failed to read the webservice because of the authentication issue. To resolve the authentication issue, custom Groovy code is passed along with token and authentication and stores the output JSON data in the oracle CLOB column. Using Oracle JSON parser the data is parsed and loaded into Oracle tables.
- The client maintains CRM data in their Salesforce and ODI does not support to read the data directly from Salesforce. To resolve, this Custom Technology was created to reverse Tables. Jython RKM was modified to read the metadata of the tables and extract data into Oracle. Custom IKM was also created to insert into Salesforce.
- Created LDAP Connection to extract data from the Active Directory environments to Extract Users and Group Data for US and Global market. This data is then modified to identify the SCD Type 2 functionality which is read by OBIEE to identify the Users report permission.
- Client maintains a custom application which is extracts data from multiple source system for the Auditing of Equipment’s and Towers and their information is later processed and stored as an Oracle CLOB column containing on average 700- 1500 lines of Simple to Multiple Grouped Hierarchical XML data, parsed and populated into Dimensional Model.
- The extension of the above application was created to re-audit the inspected data to maintain and filter data quality issues which led to extension of the above application. This complex extension handles and coordinate data from the various audits for weekly and Auditor comparison.
- Client purchased an important cloud application project management solution for Internal processes. This application stores data into Flat structure in oracle Tables. Designed the process to read the flat data into Dimension Modeling and process the data into oracle every 10-15 min cycle.
- I also upgrade the ODI Repository from ODI 11 to ODI 12 during the initial part of the project. I worked with Administration team to help them and guide them during the upgrade process.
- Client recently updated the oracle BIAPPS environment into R12 which also required to update the tool from Informatica to ODI 11. Some of the custom jobs was created to handle the Informatica to ODI 11 upgrade.
- Many small projects and complex ETL functionalities also implemented with few to mention like SSIS to ODI Conversion, SharePoint Data Extractions, KM creations for various above logics and functionalities. Data Modeling of Transactional and Star Schema design.
- In the above position and tasks, I have played the role of the Architect, Data modeler and Developer. All the development other than BIAPPS is developed using ODI 12c.
Senior ETL (ODI) Architect/Data Modeler/Developer
- I helped created the Data Warehouse Design capable of using the Exadata power and performance to extract and load data and support for International characters, from various AS400 systems across the globe, along with optimized partitioning strategy to support Global reporting.
- Global Orders is one of the most important ETL for client but the existing ETL had complex steps which took many hours and on Month end sometime even around a day. Redeveloped the complete Order Job leading to less Interface Steps and reducing Execution time from 2 hours per Million to 20 Minutes per Million.
- The client support hierarchical marketing Customer Support which included to handle bonus for upper part of the chain. Create a Multi Merge Loop Process, handling recursive looping of hierarchical data of 100 million, loading time between 30-50 min which would have taken few hours using connect by level or any other recursive process.
- Developed a Master KM which handles multiple requirement into One (IKM Merge/Incremental Update/Multi Insert/Exchange partition) and support for more logic. Developed a PLSQL Package which can handle Exchange Partitioning for any type of Partitioning / Partition Sub Partition Combination, which can be called independent and through Master KM.
- Created a PLSQL Package to handle multiple SCD Type 2 requirement for each unique Column present in the table thus providing detailed history.
- Create a Java Validation Program which can validate data between Source AS400 and Target (Oracle) Systems thus reducing Validation Hours and fetching unmatched or issues related to data with 99.9% accuracy.
- Automated some of the repetitive Data Extract Process through ODI SDK libraries thus reducing ETL Development time and cost drastically.
- Along with few above mentioned tasks I have developed 100's of Interface to handle various Daily ETL logic and client’s requirement. Data for ODS is sourced from AS400, Oracle, SQL Server through ODI 11. In this role I have played as Architect, Data Modeler and ETL Developer.
Senior ETL (ODI) Developer/Analyst
- Developed/Architected a complete data Warehouse to handle and identify Student across all university in State of Georgia.
- Created a Master Scenario and Error capturing mechanism involving Sunopsis API, Java bean shell .This scenario is called by all other Scenario so any kind of ODI error is captured.
- Data is fetched from various Oracle Transaction System and populated into Oracle data warehouse and data was processed through various ETL logic and populated in the Facts and Dimension.
Senior ETL (ODI) Developer/Analyst
- Sourcing the data from the JD Edwards and Oracle System and loading the data into a Hub for temporary data storage required transformation and validation.
- Setting up Topology and other User's Profile.
- Creating the Interface for required data loading and also modifying the KM for support of Partition by clause and No grouping support.
- Packaging the above interface and creating the support to handle or log the Failed ODI objects into File and later retrieving the same for handling error interface without going into operator.
- Jython Scripts to capture all the error into File by retrieving the information from SNP CHECK TAB table.
- Creating Wrapper scenarios which reads other interface scenarios and catalogs the errors and failure into respective File and can be called for all the interface scenarios.
- ODI scheduler scripts to be triggered by external scheduler UC4 and supporting the same in Development, UAT and Prod.
- Transferring the scenarios from Development to UAT and to Production.
- Knowledge transfer of the above to the client in detail by verbal communication and written documentation.
- Sourcing the data from AS400 system and loading the data into Oracle targets.
- Looping to reach through each of the source AS400 for individual country and looping through each country and finally loading the same into target and automatic the above into one single piece of scenario.
- Experience with Inventory Optimization Supply chain data and design processing.
Senior ODI - ETL Specialist
- Fetching Data from ODS (Operational Data Store) to Forecasting Application tables based on Simple and complex transformation and Logic.
- Design and Development of the Logic and related Interface, Package based on the required on it.
- Parallel Design and Development on Interface Development based on the Source of the Data for Multiple Work Repository (Development).
- Providing support on Test and Production environment.
- Migrating Informatica Mappings into ODI Interface and Packages that will update the Oracle target.
- Integration of ODI Services with Service Oriented Architecture (SOA) architecture
- Integrating ETL Process flow from ODI to Oracle Business Activity Monitoring tool (BAM)
- Created ODI Interface of various complexities, Lookup, Condition, Constraints.
- Created around 300 interfaces and packages of various complexities.
- Loading of Data from Multiple Flat File, People Soft, SAP sources and loading into Oracle Target.
- Optimized Existing process flow of Informatica and defining the same Data Transformation into fewer steps using ODI.
- Data matching between the Informatica and ODI data match for row by row testing.
- Integration of Unix Scripts for Pre-and Post Load process with BPEL process and retrieving the log in BAM.
Confidential - Kansas City, KS
ODI - ETL Developer
- Understanding and processing PL/SQL Procedures, Functions and other objects into ODI
- Strong Expertise and experience in shell scripting and converting the same into ODI.
- Configured connection in Topology for Sql Server 2000, Sql Server 2005, Hyperion Epm11, Oracle 10G, Oracle 11G, OpenLDAP, Informix, Excel, File and more.
- Migrated existing Informix database into Oracle 11g.
- Created optimized data flow Process and configured KMs to transfer few Million to 80 Million records in Historical Load from Informix (Legacy system) to Oracle 11g.
- Converted Existing Incremental process, which uses highly complex Shell and SQL scripts and Onpload process to ODI Incremental (Daily Load) process using ODI integration tool, Procedures, Functions and Variable.
- Migrated existing ETL Data stage jobs to retrieve data from the Hyperion 8.5
- Informix to Oracle database 11g using Oracle Data Integrator Interfaces and Packages.
- Created New Design and Architecture to connect to Hyperion EPM 11 and transferred data from Hyperion to Oracle Database using ODI Interfaces and Packages.
- Loading of Hyperion Essbase and planning data into Oracle.
- Loading of the Hyperion OpenLDAP using ODI and fetching data from it to update the Oracle data warehouse.
- Optimized Email notification system to automatically notify the User about the Step in ODI Jobs fails and the Records rejected in the process.
- Migrated existing Database stage jobs populating the SQL Server 2000 to Oracle Data integrator jobs.
- Migrated Data around 70 million rows from the SQL Server to Oracle Data Integrator using JDBC Unloader functionality defined using out of box KM and shell script.
- Created ODI jobs to replicate the existing process of Shell scripts and SQL scripts.
- Created ODI Interfaces with simple to complex transformation rules while migrating from one environment to another environment.
- Modified the Process and KM for using minus and Order by Functionality.
- Modified KM for automatized creation of File Model rather than manual creation.
- Created Oracle queries to create Faster Mapping from various technologies thus reducing Project Time Length in Initial Phase.
- Created simple to Complex query reports reading from ODI work repository tables.
- Handled various bugs of ODI related to oracle, SQL Server jdbc and more...
- Created ODI Package of various complexity and automated process data flow
- Created ODI Sequences and Data sequences and implemented them in the ODI process.
- Performed source to Staging and staging to target mapping specifications and developed ETL / ELT ODI code for the same
- Replicated PL/SQL and Complex SQL procedure into ODI Procedure and Interfaces.
- Created Multiple Temporary interfaces, Variable, Function.
- Implemented Sunopsis API in ODI procedures and KM enabling to modify the existing and creating new KM.
- Implemented Best Practices using ODI for Data Warehousing, ELT (Extract, Load and Transform) and Data Integration Purpose.