- Over 7 years of professional experience with a proven background of technical problem solving and development.
- Extensively worked on mappings in Informatica PowerCenter to load the data from various sources into the Data Warehouse using different transformations like Update Strategy, Router, Lookup, Sequence Generator, Filter, Sorter, Source Qualifier, Joiner, Aggregator, etc.
- Knowledge in Full Life Cycle development of Data Warehousing.
- Worked on complex mappings which involved Slowly Changing Dimensions, implementation of Business Logic and capturing the deleted records in the source systems.
- Involved in extracting the data from the Flat Files and Relational databases into staging area.
- Developed Informatica Mappings and Reusable Transformations to facilitate timely Loading of Data of a star schema.
- Experienced with Informatica PowerExchange (9.x/10.x) for Loading/Retrieving data from mainframe systems.
- Good Knowledge in Installing, Upgrading, Migrating and, Configuring of Microsoft SQL Server 2005/2008
- Hands on experience in writing functions, procedures, packages and complex SQL queries and working with triggers, views, indexes, materialized views, collections, cursors, sql loader, Unix shell scripting, Oracle workflow builder and TWS runbooks.
- Good knowledge on AutoSys for Scheduling, Monitoring and reporting of Jobs.
- Experience in Extracting, Transforming and Loading (ETL) data from Excel, Flat file, Oracle to MS SQL Server by using BCP utility, DTS and SSIS services.
- Experience in DTS packages migration and creating SSIS packages using Active X scripts and with Error Handling.
- Expertise in enhancing and deploying the SSIS Packages from development server to production server.
- Expertise in SQL, PLSQL, SQL Server and T - SQL (DDL and DML) in constructing Tables, Normalization techniques on database Tables
- Strong RDBMS concepts and experience in creating, maintaining and tuning Views, Stored procedures User Defined Functions and System Functions using SQL Server, T-SQL.
- Having good knowledge on creation and scheduling of T-SQL jobs to run daily.
- Good knowledge in developing and designing reports through SSRS and Excel.
Databases: MS SQL Server, Oracle, DB2, MS Access
MICROSOFT Technologies: SSIS, SQL Server Business Intelligence Tools,SSRS
Programming Language: SQL, T-SQL, PL-SQL, Oracle9i, 10g,11g
Operating Systems: Unix, Linux, Windows, AIX, Solaris
Scripting Language: Unix Shell Script, HTML, XML
Tools: Toad, SQL Developer, Informatica, TWS(Tivoli workload scheduler),AutoSys, TWS Process List, Oracle Workflow Builder, Callidus PortalActuate Reports, VSS, SVN, SSIS,JIIRA, Bitbucket, ERWIN
MS Office: Word, Power point, Excel-Pivot table, Vlookup, formulas
PLSQL Developer/ETL Developer
- Developed several new PL/SQL packages, procedures and triggers.
- Developed complex SQL queries to retrieve the data from BETA files to HODS warehouse.
- Created partitioned tables, indexes for manageability and scalability.
- Extraction, Transformation and Loading of the data Using PL/SQL.
- Creation of database objects like Tables, Views, materialized views, Stored Procedures, Functions, Packages, Triggers, and Synonyms.
- Involved in fixing bugs and responsible for production support.
- Involved in data analysis and reducing data discrepancy for the source and target schemas.
- Documenting procedures and scripting, and eventually be drafting up them into Unit test plans.
- Used cursors, exception handling, throwing exceptions, and nested loops, and also used the pragma exception init in order to associate our own created error messages with the error codes.
- Experience with Oracle Supplied Packages such as DMS SQL and UTL FILE.
- Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF. Developing several dimension and fact tables according to user requirements.
- Used FTP, SCP and WinSCP to transfer the files between different servers.
- Worked on SQL*Loader to load data from flat files obtained from various facilities every day.
- Review, enhance and optimize several PL/SQL procedures to meet user requirements.
- Reviewed all foreign key constraints to check the cascade constraint issue and updated the constraints.
- Used DbLinks efficiently to connect to different databases.
- Used Dynamic SQL programming technique to build and process SQL statements.
- Wrote Unix Shell Scripts to process the files on daily basis like renaming the file, extracting date from the file, unzipping the file and remove the junk characters from the file before loading them into the base tables.
- Used TOAD, PL/SQL developer tools for faster application development.
- Partitioned the fact tables and materialized views to enhance the performance.
- Used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
- Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching.
- Used Pragma Autonomous Transaction to avoid mutating problem in database trigger.
- Responsible for developing the ETL(Extract, Transform and Load) processes using Oracle Data Integrator (ODI) 11g(11.1.1) and 12c.
- Loaded the BETA data and demographical data into the warehouse and extracted the data from the warehouse into the FILES using ODI.
- Highly involved in ODI upgrade process from 11g to 12c.
- Created connection pools, physical tables, defined joins in the physical layer of the repository.
- Developed many Dashboards/Reports for providing analytical information using Oracle BI Answers and Dashboards.
- Created Ad-Hoc Facility and trained business users in getting familiar with OBIEE in developing the reports.
Environment: Oracle11g/12c, SQL, PL/SQL, ODI 11g/12c, OBIEE 18.104.22.168/22.214.171.124 , OBIA 126.96.36.199, SQL*Loader, Unix Shell Scripting.
Technology Lead/BI Developer
- Working closely with the business users to understand the requirements and converting them into project level technical capabilities.
- Worked with business analysts to identify the appropriate data elements for required capabilities.
- Update the status and plan the releases through the scrum meetings.
- The project involved developing mappings for moving data from Beta Flat files to Staging Area (STG) and then to Data Warehouse (DWH) and then to Data Mart.
- Developed Data Mapping, Data Governance, Transformation and Cleansing rules for the Master Data Management Architecture involving OLTP, ODS and OLAP.
- Configuring, tuning, and optimizing SQL Server 2005.
- Transformed data from various data sources using OLE DB connection by creating various DTS packages.
- Developed reports using complex formulas and to query the database to generate different types of ad-hoc reports using SSRS.
- Developing the ETL detail design documents for each target tables (Fact and dimension tables).
- Creating primary objects (tables, views, indexes) required for the application
- Designed and developed complex mapping for varied transformation logic like Expression, Filter, Aggregator, Router, Joiner Update Strategy, Unconnected and Connected lookups
- Designed and developed common modules for error checking (e.g. to check if the reject records output file is empty and to check if there are duplicate natural keys in a given table.)
- Performed the tuning at source, Target and informatica mappings using Indexes, Hints and Partitioning in Oracle, SQL Server and Informatica.
- Co-ordinate with the QA team in various testing phases by resolving the defects and ensuring smooth execution of the test plans.
- Creating the deployment documents and migrating the code to the production environment.
- Investigating and fixing the bugs occurred in the production environment and providing the on-call support
Environment: SQL Server 2008, Integration Services (SSIS), Reporting Services (SSRS), Informatica 10.2, Informatica Power Exchange, HP-UX, Oracle 11g, JIRA, BitBucket, Bamboo, Autosys
- Worked on Informatica Power Center tool - Source Analyzer, Data warehousing designer, Mapping & Mapplet Designer and Transformation Designer.
- Extracted data from SAP system to Staging Area and loaded the data to the target database by ETL process using Informatica Power Center.
- Performed the performance and tuning at source, Target levels using Indexes, Hints and Partitioning in DB2, ORACLE and Informatica.
- Designed and developed various PL/SQL stored procedures to perform various calculations related to fact measures.
- Converted the PL/SQL Procedures to Informatica mappings and at the same time created procedures in the database level for optimum performance of the mappings.
- Investigating and fixing the bugs occurred in the production environment and providing the on-call support.
- Extensively used SSIS Import/Export Wizard, for performing the ETL operations.
- Created traces using SQL server profiler to find long running queries and modify those queries as a part of Performance Tuning operations.
- Successfully migrated data between different heterogeneous sources such as flat file, Excel and SQL Server 2008 using SSIS, BCP and Bulk Insert.
- Loading data from various sources like OLEDB, flat files to SQL Server database Using SSIS Packages and created data mappings to load the data from source to destination.
- Created the automated processes for the activities such as database backup processes and SSIS Packages run sequentially using SQL Server Agent job.
Environment: Informatica 9.2, MS SQL Server 2005/2008, SSIS, OLAP, SSRS, MS Visual Studio 2005HP-UX, Oracle 11g, MS Excel, MS Access.