Sql Server Project

Prateek Agarwal

Apt #**, **** Glencoe Hill Dr., Ann Arbor, MI 48108

Ph. 401-***-**** Email: b7mvi7@r.postjobfree.com

Summary

• Almost 8 years of IT expertise in various BI initiatives and client-server applications, this involves requirement gathering, system analysis, development, testing activities, and complete project life cycle development. Area of expertise includes Data Warehousing, Data Modeling, ETL, Data architecture, Business Objects, and Ad-hoc reporting, and Healthcare domain.

• Distinguished proficiency in DataStage Basic Programming, SQL, PL/SQL, UNIX shell scripting, Teradata SQL, SAS, including performance tuning, query/code optimization and streamlining.

• Extensive experience in Managing and Implementing Data warehouses, ETL and Data Integration, Business Intelligence Reporting and Analytics.

• Thorough knowledge of Ralph Kimball's Data Modeling concepts including data modeling, data marts, dimensional modeling, star and snowflake schema, fact aggregation and dimension tables, slowly changing dimensions and surrogate keys.

• Architect and lead ETL team at offshore and onshore resources to integrate Healthcare data from several source systems into a warehouse for reporting and analysis.

• Expertise in building Operational Data Store (ODS), Data Marts, and Decision Support Systems (DSS) using Multidimensional Model (Kimball and Inmon), Star and Snowflake schema design.

• Worked on production support of ETL, BO, Teradata and Netezza applications.

• Good understanding of BO and OBIEE Architecture and Development Process.

• Reputation for strong organizational skills, excellent communication skills, dedicated teamwork, attention to details, ability to work under pressure to balance competing priorities and meet deadlines.

Technical Skills

Languages DataStage Basic programming language, SQL, PL/SQL, UNIX commands and Shell programming, SAS

Operating Systems UNIX, Windows 98, 2000, XP

Data Base Netezza, Teradata, Oracle 9i/10g/11i, SQL Server 2000/2005, Microsoft Access, Sybase

Tools DataStage (v7.1, 7.5.2), IBM Information Server V8.1/V8.5, Nexus, Teradata SQL, Teradata (TD) Utilities (BTEQ, FastLoad, FastExport, MultiLoad TD SQL Assistant 7.1), TOAD, VB.Net 2003, Visual Basic 6.0, MS Visio 2000/2003, Putty, ERwin

Business Applications Business Objects XI 3.1, Crystal Reports 2008, OBIEE 10g, Microsoft Office Suite, CCMS

Certifications

• Sun Java Certified Professional 1.4 (SCJP), Sun Microsystems

• Oracle PL/SQL Developer Certified Associate (OCA), Oracle

• Teradata Certified Professionals V2R5, Teradata

• AIX Basic Operations V5, IBM

• IBM Datastage 8.1 Certified

Professional Experience

Client: Thomson Reuters, MI, U.S.A (Nov 2011 – Till Date)

Team Size: 40

Languages: SQL, PL\SQL, Shell Scripting, SAS

Tools: ETL DataStage (IBM Infosphere v8.5), Putty

Database: Oracle 11g

Company: CSC, USA

Description: Thomson Reuters healthcare product called Advantage Suite® integrates information from diverse sources and systems, organizes it to support decision making, and enables easy access to information in a flexible, customized application. Its powerful applications are linked to an integrated warehouse of medical and prescription claims, encounters, eligibility, lab results, authorizations, health risks assessments, and performance measures.

ROLE: Team Lead/ Architect

• Working with Architectural group to create end-to-end detailed Data Integration layer/ETL architecture

• Defining ETL framework, Data Integration points, ETL naming standards, ETL Dev-QA-Prod migration process

• Implemented Production Control best practices: batch lifecycle standards, batch design and naming standards, schedule deployment processes, escalation processes, and reporting/diagnostic approaches

• Designed and developed ETL audit and control model and components

• Designed and developed plug-in modules for collecting ETL audit trail and stats for batch loads

• Involved in Relational and Dimensional data modeling for creating Logical and Physical Design of Database and ER Diagrams using Erwin modifying the staging and SQL scripts on Oracle.

• Involved in design, source to target mappings between sources to operational staging targets, using DataStage Designer.

• Involved in analyzing scope of application, identifying the area in the project wherein using DataStage would improve the design, ease of development and improve the maintainability of the application.

• Tuned Data Stage transformations and jobs to enhance their performance

• Developed Shell scripts to automate file manipulation and data loading procedures.

• Leading a team of 4 DataStage Consultants to build data warehouse of North Carolina State.

• Developed SAS Programs based on wide usage of Base SAS, SAS Macro facility to generate tables, listings, reports for statistical analysis of medical claim data

• Worked on data profiling and data validation to ensure the accuracy of the data between the warehouse and source systems.

• Worked with Executive end-users and translate business requirements into ETL and Business Intelligence design documents

Client: Blue Cross Blue Shield, RI, U.S.A (Aug 2009 – Nov 2011)

Team Size: 100

Languages: SQL, PL\SQL, Shell Scripting, Netezza, SAS, DataStage BASIC programming

Tools: ETL DataStage (v7.5, 7.5.2 and Infosphere 8.1), Nexus, Putty, Business Objects XI 3.1, Crystal Reports 2008, OBIEE 10g

Database: Netezza, Oracle 10g, Sybase, MS Access

Company: Dell Services, USA

Description: BCBSRI is the Rhode Island’s largest, locally based not-for-profit health plan and a leading provider of healthcare coverage and wellness information to Rhode Island. The company’s strategic mission was to improve the health of its members, strengthen relations with providers, and simplify business processes. Dell Services has been working with BCBSRI for last 7 years, employing the full array of Dell services, including business process outsourcing, application development, maintenance, testing, and data warehousing services for various Mainframe and Non-Mainframe applications.

ROLE: Onsite Project Coordinator/Team Lead

• Designed and developed Enterprise Data Repository (EDR) to manage BCBSRI Healthcare information.

• Worked on migrating Datastage Projects from version 7.5 and 7.5.2 to 8.1.

• Lead & Mentored DataStage developers and Administrators, report writers, BO Developers.

• Interacted with Report Users and Business Analysts to understand and document the Requirements and translate these to Technical Specifications for Designing Universes and Business Object Reports.

• Documented DW/ETL/BI architecture on Ms Visio 2003 diagrams

• Created various mapping documents from staging area to Data Warehouse from Data Warehouse to various Data Marts.

• Provided support for the existing production jobs.

• Experience in different Scheduling tools like AutoSys for automating and scheduling jobs run.

• Created Universes with measures and dimensions objects, Resolving join path problems like Loops and Traps in the Universes in Business Objects Designer.

• Experience in performance optimization of universes and performance enhancement of reports.

• Performed Project Requirements Gathering, Requirements Analysis, Design, Development, and Optimization, Testing for the ETL, Data warehousing and reporting modules of the project.

• Developed Unix Shell Scripts to integrate ETL tool, Oracle and Netezza databases.

• Developed reports in OBIEE 10g

• Worked on production support of ETL, BO, and Netezza applications.

• Leading and Onsite Coordinating team of 8 datastage developer and 10 BO developers

• Responsible for integration of Data warehouse and reporting with other systems like Care Manager (CCMS)

Client: Harvard Pilgrim Health Care, U.S.A (Oct 2008 – Aug 2009)

Team Size: 40

Languages: SQL, PL\SQL, Unix Shell Scripting, SAS, DataStage BASIC programming language, Teradata SQL, Teradata Utilities (BTEQ, FastLoad, FastExport, MultiLoad, TD SQL Assistant 7.1)

Tools: Ascential Datastage7.5.2, Teradata SQL Assistant, Putty, Oracle 10g

Database: Teradata, Oracle 10g

Company: Dell Services, Noida, India

Description: Harvard Pilgrim provides health care coverage in Massachusetts and Maine and is the parent company of Harvard Pilgrim Health Care of New England, which serves Vermont and New Hampshire and the HPHC Insurance Company. This Project is a data warehouse project where we have to build and manage data warehouse using ETL processes, Teradata as backend database and provide customized reports to higher management for business decision making.

ROLE: Senior Software Developer

• Involved in low-level design and developed various jobs and performed data loads and transformations using different stages of Datastage and pre-built routines, functions and macros.

• Build various Routines in Datastage Basic Programming to standardize and automate the ETL process for load to Enterprise Data Repository (EDR)

• Created SQL procedures and the SAS macros and tables needed for statistical analysis

• Completed data analysis and provided business solutions

• Worked with client to build EDR standards and Methodologies and ETL templates

• Designed and coded ETL logic using Datastage to enable initial load and incremental processing as well as exception handling, restart ability, and recovery, data cleanup, validation and monitoring.

• Lead a team of 8 Datastage developers in designing Enterprise Data Warehouse.

• Implemented data warehouse on the Teradata platform

Client: Domin-8, U.S.A (DEC 2007 – Oct 2008)

Team Size: 10

Languages: VB.NET, SQL

Tools: Microsoft SQL Server 2005, Microsoft Visual Studio .NET

Database: Microsoft SQL Server, MS Access

Company: Dell Services, Noida, India

Description: Domin-8 is a Property Management product that is used for managing the information of Properties of the Owners, Tenants, etc. This Project is a migration project from MS Access to Microsoft SQL Server involving various reports.

ROLE: Software Developer

• Responsible for developing migration code from Access 2000 to Microsoft SQL Server 2005 database.

• Worked on optimization of reports including table redesign, indexing, query rewrites, and stored procedure at VB.NET and Microsoft SQL Server.

• Used SQL Server 2005 tools like SQL Server Management Studio, SQL Server Profiler, SQL Server Agent, and Database Engine Tuning Advisor to identify bottlenecks.

Client: Budget Truck Rental, U.S.A (AUG 2006 - DEC 2007)

Team Size: 24

Languages: UNIX (Shell Scripts), SQL, PL/SQL

Tools: Borland C++, Toad, Oracle 9i, Putty, Toad

Database: Oracle 9i

Company: Dell Services, Noida, India

Description: Budget Truck Rental is the second largest consumer truck rental company in the United States, operating through a network of more than 2,800 locations across the nation, and serving consumers as well as light-duty commercial customers. This project is creation of Budget Application in .NET with strong backend oracle database.

ROLE: Software Developer

• Developed processes using TOAD, SQL*Loader and SQL*PLUS to meet key project initiative requirements.

• Developed various UNIX shell scripts to automate the system.

• Developed back end interfaces using PL/SQL packages, stored procedures Collections, Object Types, Ref cursor, triggers, etc

• Performed tuning and optimization on SQL queries using Analyze, Explain Plan, TKPROF utility and optimizer hints.

Client: College of Technology, Pantnagar, India (Sep 2004 - May 2006)

Team Size: 2

Languages: VB Script, C++

Tools: Borland C++, VB 6, Matlab, Microsoft Speech compiler

Description: Gesture Recognition Project was a research project under College of Technology, Pantnagar. Under this project we have to replace Computer Mouse with human hand gestures using photo sensors and computer programming by detecting motion. Windows operations like opening file or folder were controlled by speech recognition system and text-to-speech converter using Microsoft Speech-to-text and Text-to-Speech compiler.

ROLE: Research Assistant

• Developed array of photo sensors using Microprocessor chip programing.

• Developed C++ Program to integrate photo sensors with system

• Developed speech recognition system using Microsoft Speech-to-text and Text-to-Speech compiler to open a windows files/folder

AWARDS

• Received SPOT AWARD for being recognized by the client for effective problem resolution at Dell Services, India

• Presented a paper at Kshitij 2005 at IIT-Kharagpur on Dataflow Architecture for a NeuroComputer for Spike-Processing Neural Networks after getting selected in 16 top students all over India.

• Won first prize in Deep Blue C (C Competition) in Colosseum'05

• Member of Executive Body of Department Of Computer Engg

• Captain of School and University Basketball Team

• College Coordinator of Srijan- Sanidhya (Departmental Cultural / Gaming Event )

Education

Bachelor of Technology - Computer Engineering (Aug 2002 - June 2006)

Govind Ballabh Pant University of Agriculture and Technology, Pantnagar, India

Contact this candidate