Sql Server Data
Resume:
Umesh Garg
ETL Developer
***********@*****.***
SUMMARY:
Over 9 years of extensive experience in Information Technology with special emphasis on Data Warehousing and ETL process using Talend Data Integrator 6.1 (DI), Informatica Power Center9.x and SQL Server Integration Services(SSIS) in various domain Banking and Financial Services, Energy and Utilities and Telecom
Excellent knowledge of OLTP and OLAP Systems, developing Database schemas – Star and Snow flake schemas (Dimensions and Facts).
Excellent experience in Extraction, Transformation and Loading of data into Data Warehouse and downstream applications from heterogeneous sources using ETL tool - Informatica Power Center 9.x/8.x,Talend Open Studio and Talend Integration Suite and SSIS.
Expertise with Talend frequently used components (tMap, tDie, tConvertType, tFlowMeter, tLogCatcher, tRowGenerator, tSetGlobalVar, tHashInput & tHashOutput and more).
Expertise in developing complex mappings using various transformations like Unconnected and Connected lookups, Router, Filter, Expression, Normalize, SQL, Stored Procedure, Aggregator, Joiner, Union, Update Strategy, SQL and Stored procedure using Informatica.
Expertise in MSBI – Stack: SQL Server Integration Services (SSIS), SSAS, SSRS and SQL Server 2005/2008/2012
Expertise in designing and developing complex ETL packages and has deep insights of SSIS architecture such as Dataflow Task Components, Control Flow Elements, Containers, Connections Managers, Runtime Events, and Log Providers etc.
Hands on experience on several key areas of Enterprise Data Warehousing such as Change Data Capture (CDC), Slowly Changing Dimensions (SCD Type I and Type II).
Excellent experience working on Informatica Data validation option (DVO).
Strong experience in performance tuning, debugging and Error handling of mappings, sources and targets for better performance, also identified and fixed the bottlenecks.
Extensive experience in creating Reusable Mapplets, Worklets and used assignment, Event wait, email and command tasks in Workflows.
Excellent knowledge in RDBMS concepts and constructs along with Database Objects creation such as Tables, User Defined Data Types, Indexes, Stored Procedures, User Defined Functions
Extensive experience in writing Transact-SQL (DDL/DML) queries.
Hands on experience in SQL Query performance tuning and trouble shooting performance issue.
Experience in Agile – Scrum model.
TECHNICAL SKILLS:
ETL Tools
Informatica Power Center 9.x, Talend Open Studio, Talend Integration Suite 6.1 and SSIS 2012
Databases
SQL Server 2005, 2008, 2012, Sybase, MS-Excel.
Programming Skills
T-SQL, PL/SQL, C#, Unix Shell Scripting, Power Shell
Data Modeling Tools
Erwin
Source Control
Tortoise SVN, VSS, TFS 2012
Education and Certifications
Master of Computer Applications (2006) - Maharshi Dayanand University, Rothak (74%)
Bachelor of Computer Applications (2003) - Punjab Technical University, Jalandhar (72%)
Microsoft .NET Framework 2.0-Web Based Client Development certified (96.5%)
Brain bench Certification in .SQL Server, NET Framework,C#,ASP.NET and in Business Communication
Cognizant Certified Professional L1 Security Services
PROFESSIONAL EXPERIENCE:
Ecova Inc (Nagarro), Spokane, WA Mar 2015 – Till Date
Project Abstract:
Treasury is a system involving multiple applications which is designed to efficiently manage and pay the client bills for utilities e.g. Electricity, Water, Telecom, Waste. The project handles all processes related to client bills including digitization, maintenance and financial management of the bills. The funds from clients are efficiently managed by handling all banking transaction related utilities bills and benefits clients by paying bills in advance and later on reimburse same from client. It help client to avoid Late Fee Charges.
Role: ETL/Talend Developer
Responsibilities:
Patriciate in project Sprint Planning, Refinement and Retro
Estimate Story points based on business requirement
Coordinated with Product owner on a regular basis to discuss the business requirement.
Create data mapping, work flow and deployment documents
Created Talend jobs to copy the files from one server to another and utilized Talend FTP components
Created and managed Source to Target mapping documents for all Facts and Dimension tables
Migrate existing SSIS package into Talend and design new jobs to pull data from heterogeneous sources (XML, SQL Server, Sybase, MS Excel) using Talend most used components (tMap, tDie, tConvertType, tJoin, tBufferOutput, tFlowMeter, tSetGlobalVar, tHashInput & tHashOutput and many more)
Created Joblets and Parent child jobs in Talend
Designed & Developed the ETL Jobs using Talend Integration Suite by using various transformations as per the business requirements and based on ETL Mapping Specifications.
Created Implicit, local and global Context variables in the job.
Worked on Talend Administration Console (TAC) for scheduling jobs and adding users.
Created Database objects and wrote complex T-SQL queries to extract data from SQL Server 2012.
Wrote T-SQL queries to validate data
Design and Implemented ETL for data load from heterogeneous Sources to SQL Server target databases and for Fact and Slowly Changing Dimensions SCD-Type1 and SCD-Type2.
Environment: SQL Server, Talend, SSIS, TFS 2012, SVN, PL\SQL, T-SQL, WinSCP, Putty, XML, MS Excel
TTX (Nagarro), India May 2014 – Feb 2015
Project Abstract:
CBM is Component condition Based Maintenance. CBM aims to enable TTX to identify car’s components that require attention due to that component’s specific condition. This will enable TTX to maintain current reliability levels and focus maintenance resources where they are needed when they are needed - providing the highest value proposition to TTX.
Role: ETL Lead
Responsibilities:
As BI Team Lead, I have performed the following tasks:
Interaction with the Business analyst and Business Users to understand existing application and new Requirements
Validating technical feasibility of business requirements
Interfaces with Business users and Technical SMEs to clarify requirements, review design, test cases, UAT and deployment
Project milestone discussion and Customer approvals on the delivery dates
Design logical and physical data model to support CBM requirements
Dimensional Data model creation for the reporting system using Erwin 9.6
Discussion of Solutions/Approaches with client and architects. Preparation of Design Documents.
Review and sign off from customer/ stockholders on design specifications and UAT
Document project requirements and translate them into functional and non-functional specifications for BI reports
Created Database objects and wrote complex T-SQL queries to extract data from SQL Server 2012.
Designed & developed ETL procedures, Data dictionaries, data mappings involving data analysis, data validations and data migrations.
Designed the Data Warehouse/ ETL processes using Informatica Power Center 9.6 to extract, transform and load data from SQL Server.
Created complex mappings using various transformations such as Rank, Joiner, Expression, Lookup (Connected/Unconnected), Aggregate, Filter, Update Strategy, Sequence Generator etc to implement the user requirements.
Implemented Type II Slowly Changing Dimensions Methodology to keep track of historical data.
Used Session parameters, Mapping variable/parameters and created Parameter files for imparting flexible runs of workflows based on changing variable values.
Created and scheduled workflows using Workflow Manager to load the data into the Target Database.
Performed Unit Testing and Integration Testing of Mappings and Workflows.
Environment: Informatica 9.x, Erwin, SQL Server 2012, T-SQL, PL\SQL
Amex, India Oct 2011 – Apr 2014
Project Abstract :
ARC-MIS is migration project. It involves migration of CORN jobs (Shell script/SAS code) to Informatica workflows. These workflows load data from heterogeneous Databases (Sybase, Teradata, csv) to Data Warehouse on SQL Server. ARC-MIS application provides the functionality to the end business user to view reports which are generated from Relational database and Cubes.
Role: Sr. ETL Developer
Responsibilities:
Interaction with the client to understand existing application and new Requirements
Analysis and Estimation of the requirements
Discussion of Solutions/Approaches with client and architects. Preparation of Design Documents
Used Informatica workflow manager to execute the workflows and informatica workflow monitor to monitor the status of the workflows.
Developed standard and re-usable mappings and mapplets using various transformations like expression, aggregator, joiner, source qualifier, router, lookup, and filter.
Configured the mappings to handle the updates to preserve the existing records using Update Strategy Transformation.
Validated Informatica extraction, transformation and loading process by writing SQL against Business rules.
Development of SSIS Packages and Database objects
Migrated SSAS 2005 Cubes to SSAS 2012 and did enhancements as required
Interaction with testing team for SIT and Business for UAT
Preparation of deployment documents and support during code releases
Worked on BI Roadmap to suggest value additions/improvements to client
Provide consultancy/training to other projects
Environment: Informatica Power Center 8.6.1, SSIS 2012, SQL Server 2012, Sybase, Teradata, T-SQL, PL\SQL
JP Morgan Chase, India Jan 2011 – Sep 2011
JP Morgan Chase, Dallas, USA May 2010 – Dec 2010
JP Morgan Chase, India Oct 2008 – Apr 2010
Project Abstract :
Fee Biiling which has two intranet portals - Feebilling and Feedhandler. The Fee billing website contacts the Advantage Web database and internally contacts the Advantage database for some of the operations. The FeedHandler is a job scheduling and management software application designed for use in distributed environments.
Role: ETL Developer
Responsibilities:
Team member of the Application Development team in this Project.
Involved in – creating functional specifications, writing requirement understanding document, test case writing and effort estimations.
Communicated with the Development and Business team to understand the business processes and collect the required information.
Developed SSIS package which accepted data from MS Excel, SQL Server databases and load into SQL Server.
Shaped SSIS package to load data from XML to SQL Server. Used different type of transformation e.g. Lookup, Joiner, Update Strategy, Router to load data into SQL Server 2012 database. Wrote Business code in T-SQL queries and stored procedure.
Created matrix reports, tabular, drill-down, drill-through, sub reports, chart reports and multi parameterized reports.
Developed sub reports parameter driven, matrix, sub-reports and integrated report hyper-link functionality to access external applications.
Created Tabular and Matrix Reports with all aggregate functions and expressions
Environment: SSIS, SSRS, SQL Server 2008, T-SQL, PL\SQL, C#
Contact this candidate