Manager Data

KHUSHBOO V. SHASTRI

Contact Number: 678-***-****

Email: *************@*****.***

SUMMARY

Over 7 + years of experience in System Analysis, Design and Development In

the fields of Data Warehousing, Data Integration, Data Migration and

Developing Client server application.

6 years of strong experience on Extraction, Transformation and Loading

(ETL) processes from various sources into Data Warehouse/Data Marts using

Data Stage 8.0.1/7.5.2/7.5.1 (Server & Parallel Extender)

Experience in Data profiling with the Information analyzer.

Worked on analyzing the process in the information analyzer.

Strong experience in designing of Server jobs, Parallel jobs, Job

Sequencers and Batch Jobs.

Experience in Data Modeling, Star Schema/Snowflake Schema modeling, FACT &

Dimensions tables, as well as reverse engineering, using tools Oracle

Designer, Erwin.

Worked on Mapping Specifications using Fast Track - Foundation Tools from

IBM Infosphere Datastage (ISD).

Experienced in Data Modeling as well as reverse engineering using tools

ERwin, Oracle Designer and MS Visio.

Resolving bottlenecks while developing the Source-Target Mapping for the

Dimensions and Fact Tables.

Extensively used Parallel Extender to load data into data warehouse with

different techniques like Pipeline and Partition in MPP environment.

Experience in UNIX Shell scripting as part of file manipulation, and have

strong knowledge in scheduling Data Stage jobs using Crontab as well as

familiarity with Autosys.

Hands on experience in writing, testing and implementation of the Triggers,

Procedures, functions at Database level using PL/SQL.

Experience in Performance Tuning and Optimization of Parallel Jobs and

Server Jobs.

Involved in Creating and verifying quality stage jobs for Match and un

duplication.

Experience in integration of various data sources like Oracle, SQL Server,

Sybase, Teradata, and DB2/UDB.

Having experience in using written Stored Procedures in Data Stage jobs.

Hands on experience in SQL *Loader for data migration from Legacy system.

Strong knowledge in OLTP/OLAP Systems and ODS models.

Experience working on multiple platforms such as Windows NT/2000, UNIX (Sun

Solaris, AIX, HP)

Possess strong ability to quickly adapt to new applications and having

Excellent interpersonal and communication skills.

Able to Work under pressure to meet job requirements and deadlines highly

driven and self-motivated with sound business judgment and strong work

ethics.

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.

TECHNICAL SKILLS

ETL Data Stage EE 8.0.1/7.5.2/7.5.1/7.0(IBM Infosphere

Datastage, Quality stage

Information Analyzer)

Database/RDBMS Oracle 10g/9i/8i, SQL Server 2000, DB2/UDB, Teradata

Languages SQL, PL/SQL, UNIX Shell Scripting, C/C++

Tools TOAD, Brio Query Explorer 6.5.2

OLAP/Reporting Cognos, Crystal Reports

Environment IBM UNIX AIX 4.2, Sun Solaris, Linux, Windows

2000/NT/XP, MS-DOS

PROFESSIONAL EXPERIENCE

Verizon - Irving, TX. Jan

2010 - Present

DataStage Developer/Analyst

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.

Responsibilities:

Performed source data analysis and identified business rules for data

migration using IBM InfoSphere 8.1.1.

Designed various jobs (Source-to-Target) using DataStage tool to link

between different source systems and data warehouse for loading data into

warehouse.

Used DataStage Designer for developing various jobs for Extracting,

Cleansing, Transforming, Integrating and Loading data into Data Warehouse.

Designed Parallel jobs using various stages like (Join, Remove Duplicates,

FTP stage, Filter, Dataset, and Lookup file set, Modify, Transformer and

Funnel stages).

Developed incremental and full load data jobs using CDC (Change Data

Capture stage), which were scheduled to run on daily, weekly or monthly

basis.

Used SCD (Slowing Changing Dimension stage) type2 & type 3 to handle online

transactional records from various data sources.

Received the master data and populated the Dimension Tables, Surrogate key

generation.

Analyzed and Implemented complex migration strategy and scripts.

Extracted data from text files, using FTP Stage and loaded into different

databases.

Used QualityStage stages such as Investigate, Standardize, Match and

Survive for data quality and data profiling issues.

Created UNIX Shell Scripts that invoked the Data Stage jobs passing all

variables for job to execute with parameterized databases connection

information.

Excellent working knowledge on multiple platforms like DB2 z/OS, IBM-AIX

and UNIX.

Along with designing mappings from scratch, re-wrote existing code to

enhance performance and trouble-shoot errors in both DB2-UDB and DataStage.

Extensive used TOAD for analyzing data, writing SQL, PL/SQL scripts

performing DDL operations.

Used AutoSys11.0 for work load automation in the DataStage ETL batch jobs

to process billions of records on a daily, weekly and monthly basis.

Used the Data Stage Director and its run-time engine to schedule running

the solution, testing and debugging its components, and monitoring the

resulting executable versions.

Analyzed data with discrepancies through Error files and Log files for

further data processing, cleansing, and also generated the OLAP reports.

Experienced working on Linux which provided a CLI through a shell, to

interact with a UNIX system.

Created Test plans, Test cases and performed Unit Testing, Integration

Testing & System Testing.

Responsible for designing Integration Architecture for various enterprise

application integration initiatives to enable Client's business objectives.

Collaborated with Reporting team in developing Schemas and Dashboards for

Analysis and Tracking of key business metrics and goals using Cognos 8.4.

Worked on Performance Tuning and Troubleshooting & debugging of various ETL

Jobs.

Excellent experience working with Oracle, PL/SQL and writing stored

procedures and triggers.

Extensive knowledge of the principles of Data Warehousing i.e. use of Fact

Tables, Dimension Tables, Star Schema modeling and Snow-flake Schema

modeling.

Environment: IBM InfoSphere DataStage8.1.1/8.0.1, QualityStage8.1.1/8.0.1,

IBM DB2 UDB9.5, Microsoft SQL Server 2008, Oracle11g, IBM AIX v6, TOAD,

ERwin 7.2, AutoSys 11.0 and Cognos 8.4.

DowJones - New York City, NY Jan 2009 - Dec 2009

ETL / Data Warehouse Analyst

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:

Involved with Business users and ETL Leads from different teams to

implement ETL Frame Work using DataStage Server/PX combination of jobs.

Involved in understanding the Business Process and Coordinating with

Business users, Data Modelers and DB A's to create Dimension and Fact

tables based on the existing Data Warehouse design.

Implemented various strategies for Slowly Changing Dimensions using

DataStage Server/PX jobs using the Frame Work approach.

Sourced data from various sources like DB2 UDB, Flat Files, and CSV files

and loaded to target DB2 UDB (Corporate Data Warehouse).

Designed jobs using different parallel job stages such as Join, Merge,

Lookup, Remove Duplicates, Filter, Dataset, Lookup File Set, Change Data

Capture, Switch, Modify, and Aggregator.

Worked on Real time stages (XML i/o stages)

Involved in creating XSD and extracted data in to XML

Involved in developing DataStage Designer- Server and PX jobs for

Extracting, Cleansing, Transforming, and Integrating /Loading Data into

Corporate Data Warehouse (CDW).

Developed Job Sequencers with restart capability for the designed jobs

using Job Activity, Exec Command, E-Mail Notification Activities and

Triggers.

Extensively designed, developed and implemented Parallel Extender jobs

using Parallel Processing (Pipeline and partition parallelism), Restart

ability techniques to improve job performance while working with bulk data

sources.

Extensively used DataStage PX Plug-ins like Data Set, File Set, Lookup File

Set, Aggregator, Filter, Funnel, FTP, Join, Merge, Lookup, Modify, Sort,

Transformer, DB2 Enterprise, DB2 API and Sybase OC stages.

Extensively used DataStage Director to Monitor and check the run statistics

of the Jobs.

Extensively used DataStage- Manager to Export/import DataStage components.

Performed SQL tuning techniques to improve the database read performance

through DataStage Jobs and used Frame Work approach to improve

transformation and loading steps.

Worked closely with Data Quality Analysts and Business Users for data

accuracy and consistency after table loads.

Developed reports based on user requirements for fixed and Ad-Hoc requests

using Cognos.

Involved in documenting the Frame Work Templates and the process of

developing jobs using Templates.

Environment: Ascential DataStage7.5.2, DB2 UDB 8.1.6(EEE), IBM DB2

Mainframe, XML, XSD, Sybase Server, DB2 SQL, UNIX, Windows NT 4.0/2000, Sun

Solaris 2.8, IBM AIX 5.2, Cognos 8.4

CVS Pharmacy - Minneapolis, MN

Jan 2008 - Dec 2008

DATASTAGE DEVELOPER

The combined history of CVS Caremark and its business units is one of

innovation and leadership. It began with the opening of first retail store

in 1963 and has continued ever since, with pioneering efforts in both the

Pharmacy Benefit Manager and health clinic industries for customer service

and a commitment to create a better future in health care.

Responsibilities:

Involved in developing different interfaces Fuel Quest, Telephone Exchange

Management, Procurement Card (P-Card) Vehicle Management System to JD

Edwards Interface.

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 8.0 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).

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 8.0, Ascential

Datastage7.5/EE (Parallel Extender), SQL Server 2005/2008, Linux, Teradata

12, Oracle10g, Sybase, PL/SQL Toad, UNIX (HP-UX),Control-M.

Metlife - Tampa, NJ

Jan 2007 - Dec 2007

Data Warehouse Developer

As a developer, was involved in building data marts for the customer

service and claims departments and to integrate all the information into

the data warehouse for generating reports. The data marts in the integrated

data warehouse provided feed for extensive reporting, which enabled an

insight into the current and future customer service and the claims

business decisions based on the reports generated from this data warehouse.

Responsibilities:

Interacted with the Business Analyst in collecting the technical and

business requirements for the project.

Extensively worked with DataStage Manager, Designer, Director and

Administrator to load data from flat files, legacy data, to target Oracle

database.

Used DataStage for subjecting the data to multiple stages, thereby

transforming it and prepared documentation.

Created DataStage jobs, batches, job sequences and tuned them for better

performance.

Performed data loading with multiple and parallel ETL processes.

Developed Shell Scripts for event automation and scheduling.

Created the Entity Relationship diagrams & maintained corresponding

documentation for corporate data dictionary with all attributes, table

names and constraints.

Maintaining warehouse metadata, naming standards and warehouse standards

for future application development.

Responsible for the Dimensional Data Modeling and populating the business

rules using mappings into the Repository for Meta Data management.

Defined the ETL strategy for data population from Oracle databases.

Designed database PL/SQL triggers and procedures, Functions, Packages.

Extensively involved in writing SQL queries (Sub queries and Join

conditions), PL/SQL programming and Query Level Performance tuning.

Extensively used almost all of the transformations of DataStage including

lookups, Stored Procedures, aggregators and other stages.

Responsible for performance tuning of DataStage code to increase the

performance and throughput.

Responsible for UNIT, System Integration testing. Developed Test scripts,

Test plan and Test Data. Participated in UAT (User Acceptance Testing).

Environment: Ascential DataStage 7.5.1 (DataStage Manager, DataStage

Administrator, DataStage Designer, DataStage Director), SQL, PL/SQL, Oracle

8i, Windows NT 4.0 and Solaris 2.6.

Bank of America - Hyderabad, India

Jan 2006 - Dec 2006

Sr DATASTAGE DEVELOPER

Company was a global financial services provides capital markets services,

investment banking and advisory services, wealth management, asset

management, insurance, banking and related financial services worldwide.

Responsibilities:

Worked on the logical and physical design of the Data warehouse. Identified

sources/targets and analyzed source data for dimensional modeling.

Good knowledge on Voluntary Insurance plans to employers to offer total

Insurance packages.

Worked in design of Voluntary Disability, Voluntary Dental and Voluntary

Life of data marts.

Good knowledge on policy and claims processing

Worked on integration of Health Claims ODS from legacy systems.

Designed and developed jobs for extracting, transforming, integrating, and

loading data into data mart using DataStage Designer, used Data Stage

manager for importing metadata from repository, new job categories and

creating new data elements

Worked with EBCIDIC files to extract data in required format.

DataStage jobs were scheduled, monitored, performance of individual stages

was analyzed and multiple instances of a job were run using DataStage

Director.

Used Parallel Extender for splitting the data into subsets, utilized

Lookup, Sort, Merge and other stages to achieve job performance

Used DS Erwin MetaBroker to import Erwin 4.x Metadata into DataStage

Repository.

Developed user defined Routines and Transformations for implementing

Complex business logic.

Extensively used Shared Containers and Job Sequencer to make complex jobs

simple and to run the jobs in sequence

Involved in the preparation of ETL documentation by following the business

rule, procedures and naming conventions.

Created reports for various Portfolios using the Universes as the main Data

Providers.

Created the reports using Business Objects functionality's like Queries,

Slice and Dice, Drill Down, Cross Tab, Master Detail etc.

As a part of report development, created the reports using universes as a

main data provider and using the Powerful business objects functionalities,

and formulae. Involved in trouble shooting of various reporting errors.

Created Business Objects reports, Queries with constant interaction with

the end users. Trained end users in understanding the reports.

Functionalities such as Slice and Dice, Drill mode and Ranking were used

for Multidimensional Formatting.

Web Intelligence was used to generate reports on the internet/intranet.

Exporting the Reports to the Broadcast Agent and Used the Broadcast Agent

to Schedule, Monitor and Refresh the Reports.

Developed Test plans, Test Scenarios and Test cases for Code testing.

Trained team members

Provided 24/7 production support

Environment: IBM Web Sphere DataStage 7.5, Metastage 7.0, Business Objects

6.5, Oracle 9i, PL/SQL, SQL * Plus, UNIX Shell Scripts, Windows 2000/NT

4.0, ERWIN 4.1.

Data Transit - Hyderabad, India Jan

2005 - Dec 2005

Informatica / Cognos Developer

This Project is to Design and Construct Enterprise Database for Data

Transit. The objective is to achieve single point of reference to get the

customer data from the various databases. Distributed data residing in

heterogeneous data sources is consolidated onto target enterprise Data

Warehouse database.

Responsibilities:

Involved in Dimensional Modeling and Creation of fact and Dimension tables.

System Analysis: Setting the Objective, interacting with the users to find

the requirement, analyzing the current system and projection of various

alternatives and tools like Toad.

Report generation in Cognos 8.2

Involved in Performance Tuning of the workflows, mappings and PL/SQL

scripts.

Created SCD type 1 & type 2 mappings using Joiner, Router, Union,

Expression and Update Transformations.

Worked with Business Analysts to gather the business requirements to

develop the transformation logic used in ETL maps.

Extensively used workflow manager to create connections, sessions, tasks,

workflows, etc.

Designing of reports According to Client requirements.

Involved in design of the data warehouse.

Designed the mappings between sources (external files & databases) to

operational staging targets.

Extract the data from multiple Flat Files and relational sources, loaded

the data into data warehouse and associated data marts.

Extensively created mapping/mapplets, reusable transformations using

transformations like Lookup, Filter, Expression, Stored Procedure,

Aggregator, Update Strategy etc.

Involved in Performance Tuning of the workflows and mappings.

Data Modeling using Erwin.

Responsible for providing support to the team with Business Intelligence

data related issue.

Responsible for providing Tool demonstration to internal teams and their

clients.

Responsible for mapping data objects and designing/making changes to the

package in the Frame Work Manager of Cognos and in analyzing and

implementing the various rules and logic in the package.

Involved in creating indexes and facilitating setting up of the Explorer

functionality in Business Objects.

Involved in troubleshooting existing reports.

Involved in creating dashboard

Involved in creating complex list, cross-tab, chart based and drill through

reports using Cognos Report Studio and Query Studio.

Involved in creating and maintaining packages using Cognos FrameWork

Manager.

Environment: Informatica Power Center 8.6, Cognos Frame Work Manager,

Cognos Report Studio, Oracle 11g, Erwin 4.0, Power Analyzer 4, SQL, PL/SQL,

TOAD 7.1.

Cisco - Hyderabad, India Jan 2004

- Dec 2004

Informatica Developer / Cognos Developer.

Cisco is one of the worlds leading firm in dealing with Network Services

and Network routers etc. The project was to develop a data warehouse for

reporting purposes for Cisco Vendors and Cisco Agents. The main purpose was

the data extraction from the Cisco's EDW and Legacy systems into Data Marts

monthly for Cisco Vendors and Cisco Clients. The Data Warehouse was built

in Oracle 9i making use of the various utilities it offered for the purpose

of building Data Ware House. Production support was also necessary for the

existing Warehouse. The Reporting Database was responsible for handling

almost all of the business related information like the services bought by

the clients etc.

Responsibilities:

Created SCD type 1 & type 2 mappings using Joiner, Router, Union,

Expression and Update Transformations.

Worked with Business Analysts to gather the business requirements to

develop the transformation logic used in ETL maps.

Extensively used workflow manager to create connections, sessions, tasks,

workflows, etc.

Development of scripts for loading the data into the base tables in EDW

using MultiLoad, TPump, BTEQ utilities of Teradata.

Writing MultiLoad scripts, TPump scripts, FastLoad and Bteq scripts for

loading the data into stage tables and then process into BID.

Tuning the Teradata SQL queries to improve the performance of the queries

System Analysis: Setting the Objective, interacting with the users to find

the requirement, analyzing the current system and projection of various

alternatives and tools like Toad.

Report generation in Cognos 8.2

Designed the mappings between sources (external files & databases) to

operational staging targets.

Extract the data from multiple Flat Files and relational sources, loaded

the data into data warehouse and associated data marts.

Data Modeling using Erwin.

Responsible for providing Tool demonstration to internal teams and their

clients.

Responsible for mapping data objects and designing/making changes to the

package in the Frame Work Manager of Cognos and in analyzing and

implementing the various rules and logic in the package.

Involved in creating complex list, cross-tab, chart based and drill through

Cognos Report Studio and Query Studio.

Involved in creating and maintaining packages using Cognos FrameWork

Manager.

Environment: Informatica Power Center 8.x, Cognos Frame Work Manager,

Cognos Report Studio. Oracle 10g, Erwin 4.0, Power Analyzer 4, SQL, PL/SQL,

TOAD 7.1.

EDUCATIONAL QUALIFICATION

Master of Computer Application.

PROFESSIONAL CERTIFICATION AND TRAINING

Training in Datastage, Informatica, Cognos from Hyderabad, India.

Training in Core Java from NIIT Hyderabad, India.

Contact this candidate