Data Sql Server

Bhupender

Email: ********@*****.*** Ph: 732-***-****

SUMMARY:

• Over 9 years of total IT experience and technical proficiency in building Data warehouses, Data Marts, Data Integration, Operational Data Stores and ETL Processes for clients in major industry sectors like Finance, Health, Insurance and Retail.

• Expertise in Data Warehouse/Data mart, OLAP and OLTP implementations teamed with project scope, Analysis, requirements gathering, data modeling, ETL Design, development, System testing, Implementation and production support.

• Strong experience in Dimensional Modeling using Star and Snow Flake Schema, Identifying Facts and Dimensions, Physical and logical data modeling using Erwin, MS Visio.

• Expertise in working with relational databases such as Oracle 10g/9i/8x, SQL Server 2000/2005/2008, DB2 8.0/7.0, MS Access, Netezza and Teradata.

• Extensive experience in developing Stored Procedures, Functions, Views and Triggers, Complex SQL queries using SQL Server, TSQL and Oracle PL/SQL.

• Developed mappings in Informatica to load the data from various sources into the Data Warehouse, using different transformations like Source Qualifier, JAVA, Expression, Lookup, Aggregate, Update Strategy and Joiner.

• Experience in Performance Tuning of sources, targets, mappings, transformations and sessions, by implementing various techniques like partitioning techniques and pushdown optimization, and also identifying performance bottlenecks.

• Experience in all phases of Data warehouse development using Data warehouse Application Console (DAC) from requirements gathering for the data warehouse to generating reports to business users.

• Extensive experience in writing UNIX shell scripts and automation of the ETL processes using UNIX shell scripting.

• Good knowledge about IDQ, Informatica MDM, OBIEE and oracle EBS.

• Acquired experience with full life cycle of Software Development (Planning, Analysis Design, Deployment, Testing, Integration and Support).

• Experience in using Automation Scheduling tools like Autosys, Control-M and Tidal.

• Worked extensively with slowly changing dimensions.

• Hands-on experience across all stages of Software Development Life Cycle (SDLC) including business requirement analysis, data mapping, unit testing, systems integration and user acceptance testing.

• Good communication skills with strong ability to interact with end-users, customers, and team members.

• Solid analytical and dynamic troubleshooting skills. Possessing excellent communication skills and is able to work individually and as a team member.

• Self-motivated and enjoy working in a technically challenging environment.

TECHNICAL EXPERTISE:

Operating Systems

Windows 7, WindowsNT/2000/XP, Sun Solaris, Unix/Linux, MS-DOS

Modeling

Dimensional Data Modeling, Star Schema Modeling, Snow - flake Schema Modeling, Erwin 3.5/4.0, Microsoft Visio.

RDBMS

Oracle 10g/9i/8i, Teradata, DB2, Netezza, SQL 2000/2005/2008, MySQL, Sybase 11.1

QA Tools

Win Runner, QuickTestPro, Test Director

ETL Tools

Informatica PowerCenter 6.X/7.X/8.X/9.x, PowerMart 5.X/6.X, PowerConnect 8.x, PowerExchange 8.X, SSIS

Reporting Tools

COGNOS, SSRS, Business Objects, OBIEE

Languages

Java, Java Script, HTML, XML, UNIX Shell Scripting, T-SQL, XSD, SQL, PL/SQL and C

EDUCATION

Masters in Information Technology from University of Northern Virginia, Virginia

Bachelor of Computer Science from Osmania university, Hyderabad, India

PROFESSIONAL EXPERIENCE:

Fidelity Investments, Westlake, TX (Sr. Informatica Developer) Jun 15-Till date

Description: Fidelity Investments provide financial planning and retirement options such as IRAs, annuities. It provides service to offer clearing, custody, investment products, brokerage and trading services. IRL (Integrated Registration Licensing) is an application which provides list of information of licensed registered clients.

Responsibilities:

• Involved in discussion of user and business requirements with business team

• Attended the meetings with business integrators to discuss in-depth analysis of design level issues

• Performed data migration in different sites on regular basis

• Developed complex SQL queries to develop the Interfaces to extract the data in regular intervals to meet the business requirements

• Extensively used Power Center to design multiple mappings with embedded business logic

• Created transformations like Expression, Lookup, Joiner, Rank, Update Strategy and Source Qualifier Transformation using the mapping designer

• Created complex mappings using Unconnected Lookup, Sorter, Aggregator and Router transformations for populating target files in efficient manner

• Created mapplet and used them in different mappings

• Extensively involved in performance tuning of the Informatica ETL mappings by using the caches and overriding the SQL queries and also by using various components like Parameter files, Variable and Dynamic Cache

• Worked on XML and XSD as target data

• Written PL/SQL Procedures and functions

• Thorough understanding of existing java code which creates XML

• Implemented reverse engineering of existing java code and created informatica mapping

• Analyzed session log files in session failures to resolve errors in mapping or session configuration

• Written various LINUX shell Scripts for scheduling various data cleansing scripts, loading process and automating the execution of maps

• Scheduled the tasks using Control-M

• Written Unit test scripts to test the developed mappings

• Written Mapping documents for the informatica workflows

• Managed enhancements and coordinated with every release

• Worked under Agile methodology and used JIRA to track the tasks

• Written thorough design docs, unit test documentation, Installation and configuration guide documents

Environment: Informatica PowerCenter 9.5/9.6.1, Oracle 10g, Teradata, PL/SQL, JIRA, HP Quality Center 12.20, WINSCP, Control-M, LINUX2.6, XML, JAVA, Windows 7.

US Steel Corporation, Pittsburgh, PA (Sr. ETL Developer) Sep 14-May15

Description: United States Steel Corporation is an integrated steel producer with major production operations in North America and Central Europe and an annual raw steelmaking capability of 24.4 million net tons. The company manufactures a wide range of value-added steel sheet and tubular products for the automotive, appliance, container, industrial machinery, construction, and oil and gas industries.

Responsibilities:

• Involved in discussion of user and business requirements with business team

• Attended the meetings with business integrators to discuss in-depth analysis of design level issues

• Involved in data design and modeling by specifying the physical infrastructure, system study, design, and development by applying Ralph Kimball methodology of dimensional modeling and using Erwin.

• Developed internal and external Interfaces to send the data in regular intervals to Data warehouse systems

Accomplished data movement process that load data from databases, files into Teradata by the development of Korn Shell scripts, using Teradata SQL

• Loaded pre-system configured data by using flat files as source into OLTP

• Developed complex SQL queries to develop the Interfaces to extract the data in regular intervals to meet the business requirements

• Extensively used Power Center to design multiple mappings with embedded business logic

• Created transformations like Expression, Lookup, Joiner, Rank, Update Strategy and Source Qualifier Transformation using the Informatica designer

• Created complex mappings using Unconnected Lookup, Sorter, Aggregator and Router transformations for populating target tables in efficient manner

• Created mapplet and used them in different mappings

• Extensively involved in performance tuning of the Informatica ETL mappings by using the caches and overriding the SQL queries and also by using various components like Parameter files, Variable and Dynamic Cache

• Worked on XML and XSD as source and target data

• Written PL/SQL Procedures and functions

• Involved in change data capture (CDC) ETL process

• Involved in how to create relationships for the base objects which reflects from look up tables in MDM

• Implemented Slowly Changing Dimension Type II for different Dimensions

• Analyzed session log files in session failures to resolve errors in mapping or session configuration

• Written various UNIX shell Scripts for scheduling various data cleansing scripts, loading process and automating the execution of maps

• Provided reports using OLAP capabilities of Cognos Reports for the purpose of data analysis

• Maintained source control objects using Clear Case

• Written Unit test scripts to test the developed interfaces

• Managed enhancements and coordinated with every release with in Informatica objects

• Worked under Agile methodology and used version one to track the tasks

• Written thorough design docs, unit test documentation, Installation and configuration guide documents

• Supported production environment and also implemented the system in different sites

Environment: Informatica PowerCenter 9.1, IDQ 8, Oracle 10g, Teradata, Toad, PL/SQL, HP-UX, UNIX, Cognos 8.4.1, Clear Case, XML, Windows 7.

Lorillard Inc, Greensboro, NC (Sr. Informatica Developer) Jun 12 – Aug 14

Description: Lorillard, Inc., through its Lorillard Tobacco Company subsidiary, is the third largest manufacturer of cigarettes in the United States. Founded in 1760, Lorillard is the oldest continuously operating tobacco company in the United States. Newport, premium cigarette brand, is the top selling menthol and second largest selling cigarette brand overall in the United States.

Responsibilities:

• Analyzed the requirements and framed the business logic for the ETL process

• Involved in JAD sessions for the requirements gathering and understanding

• Involved in the ETL design and its documentation

• Interpreted logical and physical data models for business users to determine common data definitions and establish referential integrity of the system using Visio

• Dimensional Modeling of Oracle data in SQL Server utilizing the Kimball Methodology to generate a logical and physical model

• Followed Star Schema to design dimension and fact tables

• Implemented Change Data Capture (CDC) processes for the large tables.

• Collect and link metadata from diverse sources, including relational databases and flat files

• Used Trillium as a Data Cleansing tool to correct the data before loading into the staging area

• Processed customer address elements by Cleansing, standardizing and converting using Trillium

• Used Informatica Designer to create mappings using different transformations to move data to a Data Warehouse from SQL server, Oracle and Teradata

• Developed mappings in Informatica to load the data from various sources into the Data Warehouse, using different transformations like Source Qualifier, Expression, Lookup, Aggregate, Update Strategy and Joiner

• Extracted DTS (Data Transformation Services) packages from the SQL server 2000/2008

• Identified the transformations used in each DTS packages

• Converted the DTS packages into Informatica mappings and loaded the target into the Oracle database

• Worked with IBM Mainframes server to extract centralized data of the organization

• Experienced in handling slowly changing dimensions

• Working closely with Connectivity with IBM MQ series

• Optimized the performance of the mappings by various tests on sources, targets and transformations

• Scheduling the sessions to extract, transform and load data in to warehouse database on Business requirements

• Scheduled the tasks using Autosys

• Loaded the flat files data using Informatica to the staging area

• Created SHELL SCRIPTS for generic use

• Developed Test cases for Unit Testing of the Mappings, and also was involved in the Integration Testing

• Generated reports using Cognos 8.4.1

Environment: Informatica Powercenter 8.6.1, IDQ 8, UNIX, SQL Server 2000/2008,Oracle 10g, SQL, T-SQL, PL/SQL, DB2, Teradata, SQL Developer, Trillium, Metadata Manager, Oracle Designer, MS VISIO, ERWIN, IBM MQ, IBM Mainframes, TOAD, Autosys, Korn Shell, Windows XP

Great-west HealthCare, Denver, CO (Data Warehouse Developer) Apr 09-May 12

Description: Great-west is one of the nation’s leading Insurance providers. The flow of data was from different sources and types, which encompassed premiums, claims, expenses and contracts. The primary objective of this project is to get data from different sources (SQL Server, Oracle, Flat file) and perform the necessary operations on the data as per the user requirement and load into the data warehouse for analysis.

Responsibilities:

Involved in Dimensional modeling to Design and develop STAR Schemas, used ERWIN, identifying Fact and Dimension Tables

Created ETL Packages and implement new data extracts, transformations, and load routines to populate the Data Warehouse utilizing the Kimball Methodology to populate dimension and fact tables

Worked with different Sources such as Oracle, Teradata, MS SQL Server and Flat file

Extensively used several transformations such as Source Qualifier, Router, Lookup (connected & unconnected), Update Strategy, Joiner, Expression, Aggregator and Sequence generator transformations

Identified and tracked the slowly changing dimensions, heterogeneous Sources and determined the hierarchies in dimensions

Used Trillium as a Data Cleansing tool to correct the data before loading into the staging area

Accomplished data movement process that load data from databases, files into Teradata by the development of Korn Shell scripts, using Teradata SQL

Tuned Teradata SQL Queries

Created reusable transformations and Mapplets and used them in mappings

Used Informatica Repository Manager to maintain all the repositories of various applications

Developed number of Complex Informatica Mappings, Mapplets and Reusable Transformations for the Claim Profitability Systems to facilitate Daily, Monthly and yearly Loading of Data

Involved in fixing invalid Mappings, testing of Stored Procedures and Functions, Testing of Informatica Sessions, and the Target Data

Worked on SQL tools like TOAD to run SQL queries to validate the data

Design and Development of pre-session, post-session and batch execution routines to run Informatica sessions using Informatica Server

Created and Scheduled sessions & Worklets using workflow Manager to load the data into the Target Database

Wrote PL/SQL stored Procedures and Functions for Stored Procedure Transformations

Used Control–M scheduler to automate the process

Environment: Informatica PowerCenter 8.6.1, PowerExchange, Oracle9i, PL/SQL, DB2, MS SQL Server 2000, Teradata, MS Enterprise Manager, MS Query Analyzer, Flat file, XML, Control-M (scheduler), UNIX, TOAD, ERWIN, Java, Windows XP.

First Horizon National Bank, Memphis, TN (Mid-Level Informatica Developer) Feb 08 – Mar 09

Description: FHNC is a financial services company: Know your customer (KYC) is bank regulation that financial institutions and other regulated companies must perform to identify and ascertain their clients under the Bank Secrecy Act and USA PATRIOT Act.

Responsibilities:

Involved in Requirement analysis in support of Data Warehousing efforts

Maintain Data Flow Diagrams (DFD’s) and ETL Technical Specs or lower level design documents for all the source applications

Worked with source databases like Oracle, SQL Server and Flat Files

Extensively worked with various Active transformations like Filter, Sorter, Aggregator, Router and Joiner transformations

Extensively worked with various Passive transformations like Expression, Lookup, Sequence Generator, Mapplet Input and Mapplet Output transformations

Created complex mappings using Unconnected and Connected lookup Transformations

Implemented Slowly changing dimension Type 1 and Type 2 for change data capture

Worked with various look up cache like Dynamic Cache, Static Cache, Persistent Cache, Re Cache from database and Shared Cache

Responsible for definition, development and testing of processes/programs necessary to extract data from client's operational databases, transform, cleanse data and load it into data marts

Worked with various Informatica PowerCenter objects like Mappings, transformations, Mapplet, Workflows and Session Tasks

Responsible for the performance tuning of the ETL process at source level, target level, mapping level and session level

Worked extensively with update strategy transformation for implementing inserts and updates

Extensively used the tasks like email task to deliver the generated reports to the mailboxes and command tasks to write post session and pre session commands

Extensively used debugger to test the logic implemented in the mappings

Performed error handing using session logs

Tuned performance of Informatica session for large data files by increasing block size, data cache size and target based commit interval

As per business we implemented Auditing and Balancing on the transactional sources so that every record read is either captured in the maintenance tables or wrote to Target tables

Auditing is captured in the audit table and EOD snapshot of daily entry is sent to the distributed list to analyze if anything is abnormal

Monitored workflows and session using PowerCenter workflows monitor

Used Informatica Scheduler for scheduling the workflows

Environment: Informatica (PowerCenter 7.3), Oracle 9i, SQL Server, PL/SQL, Cognos, AIX, windows XP.

Focus Software Solutions LTD, India (Jr. Informatica Developer) Mar 07 - Jan 08

Description: Focus is a leading Software Solutions and Services Provider in the Global Market, providing Business Solutions and High-End Technology based services to its customer base in USA, Europe, Nordic and Asia with on-site, off-site and off-shore development models. Focus delivered many large-scale enterprise class solutions in the areas of E-Business, Knowledge Management, Business Intelligence, etc., using cutting edge technologies and re-usable frameworks.

Responsibilities:

Involved in designing and developing the database for the business division of Creative Eye at Focus for effectively managing manufacturing processes

Designed and developed database views and stored procedures

Maintained user roles and privileges at the database level It involved enrolling users, maintaining the system security, controlling and monitoring user access

Used SQL to extract the data from the database

Worked with Development Life Cycle teams

Wrote Stored procedures, Stored functions, Packages and used in many Forms and Reports

Wrote database triggers for automatic updating the tables and views

Designed and developed forms and reports

Involved in the creation of jobs using Informatica workflow manager to validate schedule run and monitor jobs using Workflow Monitor

Involved in the preparation of documentation for ETL using Informatica standards, procedures and naming conventions

Environment: Informatica PowerCenter 6.1, Oracle, Oracle Forms 50/45, Reports 25, SQL, PL/SQL, Windows NT/98/95, Erwin, UNIX

Contact this candidate