Data Sql Server

JAY CHAVDA

EMAIL: ***.**********@*****.***

CONTACT: (001)201-***-****

PROFESSIONAL SUMMARY:

• Over 6 years of Dynamic career reflecting pioneering experience and high performance in System

Analysis, design, development and implementation of Relational Database and Data Warehousing

Systems using IBM Data Stage 8.0.1/7.x/6.x/5.x (Info Sphere Information Server, Web Sphere,

Ascential Data Stage).

• Excellent Experience in Designing, Developing, Documenting, Testing of ETL jobs and

mappings in Server and Parallel jobs using Data Stage to populate tables in Data Warehouse and

Data marts.

• Proficient in developing strategies for Extraction, Transformation and Loading (ETL)

mechanism.

• Expert in designing Parallel jobs using various stages like Join, Merge, Lookup, Remove

duplicates, Filter, Dataset, Lookup file set, Complex flat file, Modify, Aggregator, XML.

• Expert in designing Server jobs using various types of stages like Sequential file, ODBC, Hashed

file, Aggregator, Transformer, Sort, Link Partitioner and Link Collector.

• Experienced in integration of various data sources (DB2-UDB, SQL Server, Sybase, Oracle,

Teradata, XML and MS-Access) into data staging area.

• Expert in working with Data Stage Manager, Designer, Administrator, and Director.

• Expert in using Information Analyzer for Column Analysis, Primary Key Analysis and Foreign

Key Analysis.

• Expert in working with Quality Stage for data profiling, standardization, matching and

survivorship.

• Excellent knowledge of studying the data dependencies using metadata stored in the repository

and prepared batches for the existing sessions to facilitate scheduling of multiple sessions.

• Proven track record in troubleshooting of Data Stage jobs and addressing production issues like

performance tuning and enhancement.

• Expert in working on various operating systems like UNIX AIX 5.2/5.1, Sun Solaris V8.0 and

Windows 2000/NT.

• Proficient in writing, implementation and testing of triggers, procedures and functions in

PL/SQL.

• Experienced in Database programming for Data Warehouses (Schemas), proficient in dimensional

modeling (Star Schema modeling, and Snowflake modeling).

• Expertise in UNIX shell scripts using K-shell for the automation of processes and scheduling the

Data Stage jobs using wrappers.

• Experience in using software configuration management tools like Rational Clear case/Clear

quest for version control.

• Experienced in creating various reports using Cognos.

• Experience in using scheduling tools Autosys and Control M.

• Experienced in Data Modeling as well as reverse engineering using tools ERwin, Oracle Designer

and MS Visio.

• Expert in unit testing, system integration testing, implementation and maintenance of databases

jobs.

• Effective in cross-functional and global environments to manage multiple tasks and assignments

concurrently with effective communication skills.

EDUCATIONAL QUALIFICATION:

Bachelors in Electronics and Communication, India.

GPA: 3.78

TECHNICAL SKILLS:

ETL Tools DATA STAGE- IBM WebSphere Data stage and Quality Stage 8.0,Ascential Data

Stage /7.5.2/5.1/6.0 Profile Stage 7.0, Informatics Power center 5.x/5.1/6.2, Power Mart

4.7/5.0, SSIS (SQL server 2005), DTS (SQL Server 2000), Data Integrator.

Business Intelligence tools Business Objects, Brio, SSRS(SQL Server 2005),IBM Cognos 8 BI,IBM Cognos series 7

Development Tools andSQL, PL/SQL, C, C++, Unix Shell Scripting, Perl

Languages

Testing Tools Auto Tester, Test Director, Lotus Notes

Data Modeling Tools Erwin 4.0, Sybase Power Developer

Operating Systems HP-UX, IBM-AIX 5.3, Windows 95/98/2000/ NT, Sun Solaris, Red-Hat Linux, MS

SQL SERVER 2000/2005/2008& MS Access, Teradata V2R6, DB2-UDB

WORK EXPERIENCE:

Dow jones Co. & Inc., NJ Jan 2010 - Sep 2010

Datastage Developer

Project was to design and develop enterprise data warehouse. Extract data from heterogeneous source

system, transform them using business logic and load in to data warehouse.

• Used the DataStage Designer to develop processes for extracting, cleansing, transforming,

integrating and loading data into staging tables.

• Extensively used ETL to load data from IBM DB2 database & Flat files Source to Informix

Database Server.

• Involved in analysis, planning, design, development, and implementation phages of projects using

IBM Web Sphere software (Quality Stage v8.0.1, Web Service, Information Analyzer, Profile

Stage, WISD of IIS 8.0.1).

• Developed complex jobs using various stages like Lookup, Join, Transformer, Dataset, Row

Generator, Column Generator, Datasets, Sequential File, Aggregator and Modify Stages.

• Created queries using join and case statement to validate data in different databases.

• Created queries to compare data between two databases to make sure data is matched.

• Used the DataStage Director and its run-time engine to schedule running the solution, testing and

debugging its components, and monitoring the resulting executable versions on an ad hoc or

scheduled basis.

• Created shared container to incorporate complex business logic in job.

• Monitoring the Datastage job on daily basis by running the UNIX shell script and made a force

start whenever job fails.

• Created and modified batch scripts to ftp files from different server to data stage server.

• Extensively used slowly changing dimension Type 2 approach to maintain history in database.

• Created Job Sequencers to automate the job.

• Modified UNIX shell script to run Job sequencer from the mainframe job.

• Create parameter set to assign a value to job at run time.

• Standardized the Nomenclature used to define the same data by users from different business

units.

• Created multiple layer report providing a comprehensive and detail report with Drill through

facility.

• Used Parallel Extender for Parallel Processing for improving performance when extracting the

data from the sources.

• Worked with Metadata Definitions, Import and Export of Datastage jobs using Data stage

Manager.

• Providing the logical data model design, generating database, resolving technical issues, and

loading data into multiple instances.

• Implemented PL/SQL scripts in accordance with the necessary Business rules and procedures.

• Developed PL/SQL procedures & functions to support the reports by retrieving the data from the

data warehousing application.

• Used PL/SQL programming to develop Stored Procedures/Functions and Database triggers.

Environment: IBM Web Sphere DataStage 8.0.1 Parallel Extender, Web Services, Quality Stage 8.0,

(Designer, Director, Manager), Microsoft Visio, IBM AIX 4.2/4.1 IBM DB2 Database, SQL Server 2000,

IBM DB2, Informix, Query man, BMQ, Unix, Windows.

CIGNA Health Care, Richmond, VA Oct 2008 – Dec 2009

Datastage ETL Developer

Project was involved in design and development of a group insurance system, which processes claims for

group insurance. It covers benefits with subsystems covering Term Life Insurance, Medical Indemnity

and Managed Health Care.

Data Modeling:

• Gathered and analyzed the requirements of the in-house business users for the data warehousing

from JAD sessions.

• Collected the information about different Entities and attributes by studying the existing ODS and

reverse engineering into Erwin.

• Defined the Primary keys and foreign keys for the Entities.

• Defined the query view, index options and relationships.

• Created logical schema using ERWIN 4.0 and also created the Dimension Modeling for building

the Cubes.

• Designed staging and Error handling tables keeping in view the overall ETL strategy.

• Assisted in creating the physical database by forward engineering.

ETL Process:

• Extracted data from source systems transformed and loaded into Oracle database according to the

required provision.

• Primary on-site technical lead during the analysis, planning, design, development, and

implementation stages of data quality projects using Integrity (now known as Quality Stage).

• Involved in system analysis, design, development, support and documentation.

• Created objects like tables, views, Materialized views procedures, packages using Oracle tools

like PL/SQL, SQL*Plus, SQL*Loader and Handled Exceptions.

• Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers,

Packages, Records and Collections.

• Created views for hiding actual tables and to eliminate the complexity of the large queries.

• Created various indexes on tables to improve the performance by eliminating the full table scans .

• Used the DataStage Designer to develop processes for extracting, cleansing, transforming,

integrating and loading data into Data Marts.

• Created source table definitions in the DataStage Repository.

• Identified source systems, their connectivity, related tables and fields and ensure data suitability

for mapping.

• Generated Surrogate ID’s for the dimensions in the fact table for indexed and faster access of

data.

• Created hash tables with referential integrity for faster table look-up and for transforming the data

representing valid information.

• Used built-in as well as complex transformations.

• Used Data Stage Manager to manage the Metadata repository and for import/export of jobs.

• Implemented parallel extender jobs for better performance using stages like Join, Merge, Sort and

Lookup.

• Optimized job performance by carrying out Performance Tuning.

• Created Stored Procedures to confirm to the Business rules.

• Used Aggregator stages to sum the key performance indicators in decision support systems and

for granularity required in DW.

• Tuned DataStage transformations and jobs to enhance their performance.

• Used the DataStage Director and its run-time engine to schedule running the solution, testing and

debugging its components, and monitoring the resulting executable versions on an ad hoc or

scheduled basis.

• Scheduled Datastage job using Autosys scheduling tool.

• Prepared the documentation of Data Acquisition and Interface System Design.

• Assigned the tasks and provided technical support to the development team.

• Monitored the development activities of the team and updated to the Management.

• Created complicated reports using reporting tool Cognos.

Environment: IBM / Ascential Data Stage E.E./7.5(Manager, Designer, Director, Parallel Extender),

Quality Stage 7.5 Data Stage BASIC language Expressions,Autosys, Erwin 4.0, Windows NT, UNIX,

Oracle 9i, PL/SQL, Cognos, Sequential files, .csv files.

Siemens, Malvern, PA July 2007 -- Sep 2008

Sr. Data Stage Developer

As a DW developer designed, developed, and deployed DataStage Jobs and associated functionality. The

warehouse employed highly complex data transformations including Slowly Changing Dimensions and a

series of Stored Procedures, which made performance tuning and efficient mapping highly critical. Along

with designing jobs from scratch re-wrote existing code to enhance performance and trouble-shoot errors

in both DataStage & Oracle9i.

Responsibilities:

• Used IBM Datastage Designer to develop jobs for extracting, cleaning, transforming and loading

data into data marts/data warehouse.

• Developed several jobs to improve performance by reducing runtime using different partitioning

techniques.

• Used different stages of Datastage Designer like Lookup, Join, Merge, Funnel, Filter, Copy,

Aggregator, and Sort etc.

• Used to read complex flat files from mainframe machine buy using Complex Flat File Stage.

• Sequential File, Aggregator, ODBC, Transformer, Hashed-File, Oracle OCI, XML, Folder, FTP

Plug-in Stages were extensively used to develop the server jobs.

• Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database.

• Worked on Complex data coming from Mainframes ( EBCIDIC files) and knowledge of Job

Control Language (JCL).

• Used Cobol Copy books to import the Metadata information from mainframes.

• Designed Datastage jobs using Quality Stage stages in 7.5 for data cleansing & data

standardization Process. Implemented Survive stage & Match Stage for data patterns & data

definitions.

• Staged the data coming from various environments in staging area before into DataMarts.

• Involved in writing Test Plans, Test Scenarios, Test Cases and Test Scripts and performed the

Unit, Integration, system testing and User Acceptance Testing.

• Used stage variables for source validations, to capture rejects and used Job Parameters for

Automation of jobs.

• Strong knowledge in creating procedures, functions, sequences, triggers.

• Expertise in PLSQL/SQL.

• Performed debugging and unit testing and System Integrated testing of the jobs.

• Wrote UNIX shell script according to the business requirements.

• Wrote customized server/parallel routines according to complexity of the business requirements.

• Designed strategies for archiving of legacy data.

• Created shell scripts to perform validations and run jobs on different instances (DEV, TEST and

PROD).

• Created & Deployed SSIS (SQL Server Integration Services) Projects, Schemas and Configured

Report Server to generate reports through SSRS SQL Server 2005.

• Used to create ad-hoc reports by MS SQL Server Reporting Services for the business users.

• Used SQL Profiler to monitor the server performance, debug T-SQL and slow running queries.

• Expertise in developing and debugging indexes, stored procedures, functions, triggers, cursors

using T-SQL.

• Wrote mapping documents for all the ETL Jobs (interfaces, Data Warehouse and Data Conversion

activities).

Environment: IBM WebSphere Data stage and Quality Stage 7.5, Ascential Datastage7.5/EE (Parallel

Extender), SQL Server 2005/2008, Linux, Teradata 12, Oracle10g, Sybase, PL/SQL Toad, UNIX (HP-

UX), Cognos 8 BI

Cingular Wireless, Atlanta, GA Sep 2006 - June 2007

Datastage ETL Developer

Implemented a data warehouse that collects, organizes and stores data from different operational systems

to provide a single source of integrated and historical data. The purpose was for end user reporting,

analysis and decision support and to improve the client services by preventing errors, providing real-time

data and updating records as the transactions are completed.

• Responsible for understanding Business Process and managing with Business users to meet

specific user requirements.

• Identified the Facts and Dimensions using Erwin Data modeling tool to represent the Star Schema

Data Marts.

• Responsible for documenting user requirements and translated requirements into system

solutions.

• Implementation plans and schedules are also developed.

• Develop processes for cleansing, transforming, extracting, integrating and loading data with use

of Datastage.

• Written complex queries to facilitate the supply of data to other teams.

• Responsible for using different types of Stages such as FTP, Hashed File, Sequential File, Sort

Aggregator, Transformer and ODBC to develop different jobs.

• Done various other duties as assigned.

• Used the DataStage Designer to design and develop jobs for extracting, cleansing, transforming,

integrating, and loading data into different Data Marts.

• Defined the data definitions, and created the target tables in the database.

• Wrote routines to schedule batch jobs to obtain data overnight from various locations.

• Troubleshot jobs using the debugging tool.

• Analyzed the performance of the jobs and project and enhance the performance using the

standard techniques.

• Standardized the Nomenclature, used to define the same data by users from different business

units.

• Created job sequences and schedules for automation.

• Used the DataStage Director and its run-time engine to schedule running the solution, testing and

debugging its components, and monitoring the resulting executable versions (on an ad hoc or

scheduled basis).

• Used DataStage to transform the data to multiple stages, and prepared documentation.

• Provided data modeling support for numerous strategic application development projects.

• Analyzed business functionalities and building the fundamental blocks.

• Created ETL execution scripts in Autosys for automating jobs.

• Worked on writing routines to read parameters from hash file at runtime.

• Documented the Purpose of mapping so as to facilitate the personnel to understand the process

and incorporate the changes as and when necessary.

Environment: Data stage 7.5, Oracle 9i, DB2, UNIX, and Oracle.

Unit Trust of India, Chennai, India Jan 2006 – Aug 2006

ETL Developer

Fixed income trading system is a financial trading entry system. System accepts new issues, purchase and

sales transactions; validates all transactions according to company rules and accepts transactions for long

term, short term, structured inflation, bonds etc. And system generates various reports.

Responsibilities:

• Involved in writing ER diagrams and DFD diagrams.

• Performed database migrations and tuning.

• Mass loaded the legacy data to Oracle database by using load utility tool SQL*Loader.

• Generated schema level and table level post scripts (DDL generation).

• Generated DDL changes during the process of different builds.

• Developed PL/SQL based stored procedures, triggers and packages.

• Involved in Forms Screen designing and Reports layout designing.

• Database support for testing team.

• Planned, designed, created, maintained, documented relational database management system

(RDBMS), logical and physical modeling.

• Developed procedures required for migrating changes to database objects through development

cycle.

• Conduct customer interviews and other data gathering tasks.

• Maintained database dictionaries, ensured database performance, resolved problems.

• Coordinated with other departments to ensure implementation, monitoring of databases.

• Reviewed completion, implementation of technical products.

• Evaluated vendor capabilities to provide required product or services.

• Worked with Oracle support services to resolve customer open issues.

• Performed configuration management for application enhancements.

• Assisted client, prepared production procedures, troubleshooting of queries/report.

• Demonstrated, provided hands-on testing of upgrade documentation on both Windows NT/ UNIX

environments.

• Installed, configured, upgraded Oracle software, related products.

• Wrote user requirement and functional specification documents for product.

• Coding of load module in PL/SQL, database package and writing Shell script to run the Interface

as a part of nightly batch process generate log files.

Environment: Windows NT, Sun Solaris 2.6, Oracle (7.3.4/8.0.x/8i), Unix Shell Scripting, PL/SQL

Scripting, Unix CRON jobs, Sun Solaris 2.6/8, AIX, Windows NT, Erwin

ICICI-GUJARAT, INDIA July 2004 – Dec 2005

Jr. Datastage developer

Description: ICICI Prudential Insurance provides a wide range of insurance policies such as Life

Insurance, Health Insurance, Motor Vehicle Insurance and General Insurance etc. This project is

developed as a process of automation for insurance policy management by using centralized data

warehouse and Data Mart. This application provides the provision to take in various related information

regarding the region, generates premiums and desired data in the form of reports.

Responsibilities:

• Designed and developed mappings between sources and operational staging targets, using Star

and Snow Flake Schemas.

• Provided data models and data maps ( extract, transform and load analysis) of the data marts

for systems in the aggregation effort.

• Involved in Extracting, cleansing, transforming, integrating and loading data into data warehouse

using Datastage Designer.

• Developed various transformations based on customer last name, zip code for internal business

analytical purposes, loaded warehouse based on customer credit card number with dynamic data

re-partitioning.

• Developed user defined Routines and Transformations by using Universe Basic.

• Used Datastage Manager for importing metadata from repository, new job categories and creating

new data elements.

• Used the Datastage Director and the runtime engine to schedule running the solution, testing and

debugging its components and monitoring the resulting executable versions (on adhoc or

scheduled basis).

• Developed, maintained programs for scheduling data loading and transformations using

Datastage and Oracle 8i.

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

Environment: Datastage 5.2/6.0, Oracle 8i, SQL, TOAD, UNIX, Windows NT 4.0.

.

Contact this candidate