Data Manager

Venu Gopal Koleti

*********.******@*****.***

Phone: 302-***-****

Summary

Over 6 years of experience in Data modeling, Datawarehouse Design, Development Testing,

•

implementation, documentation, maintenance and Data Migration life cycle using IBM

WebSphere DataStage 8.x/7.x

Extensive experience with Healthcare, Finance, Retail and Pharma domains.

•

Experience in analyzing the data generated by the business process, defining the granularity,

•

source to target mapping of the data elements, creating Indexes and Aggregate tables for the

data warehouse design and development.

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.

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, PL/SQL, Oracle,

•

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

Strong knowledge of Entity Relationship concept, Facts and dimensions tables, slowly changing

•

dimensions and Dimensional Modeling (Star Schema and Snow Flake Schema).

Have performed various testing activities like Unit Testing, Integration System Testing, Regression

•

Testing, User Acceptance Testing and ensured that the code is ideal.

Extensive experience in developing Stored Procedures, Functions, Packages, Views and Triggers,

•

Complex SQL queries using SQL and Oracle PL/SQL.

Involved in Performance/ Query tuning. Generation /interpretation of explain plans and tuning

•

SQL to improve performance.

Experienced in UNIX work environment, file transfers, job scheduling.

•

Experience in using Automation Scheduling tools like Autosys and DAC.

•

Maintained outstanding relationship with Business Analysts and Business Users to gather

•

information as per business requirements.

Experience in support and knowledge transfer to the production team.

•

Experience in working in an onsite offshore structure and effectively coordinated tasks between

•

onsite and offshore teams as an onsite lead.

Reliable, responsible, hard working and good team player.

•

Technical Skills:

Tools : IBM Information Server V8.x(DataStage, QualityStage, Information

Analyzer), Ascential DataStage V7.5 (Designer, Director, Manager, Parallel

Extender), OBIEE10g/11g, DAC

Database : Oracle 11g/ 10g / 9i/ 8i

OLAP Tools : OBIEE 10.x/11.x, Siebel Analytics 7.8

Scheduling Tools : Autosys and DAC

Operating System : Windows 9x/XP, Windows 7, UNIX

Programming Languages : C, UNIX shell Scripting, SQL and PL/SQL

Professional Experience

Teva Pharmaceuticals, North Wales, PA 10/2013 01/2014

ETL Designer/ DataStage Developer

Teva collects 2 years of Sales history and generates 3 years of Forecast based on sales data. Business

was looking to have a KPI Dashboard to measure the Forecast accuracy between Booking History and

Forecast Lag 1/Lag 3 mth Frozen data for past six months at different dimensions.

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.

Environment: IBM DataStage 8.7 (Designer, Director, Manager, Parallel Extender), Oracle 10g, DB2 UDB, Flat files,

Sequential files, Autosys, TOAD 9.6, UNIX, OBIEE 11g

Responsibilities:

Analyzed the business requirements and functional specifications.

•

Prepared the design document after the complete analysis of the requirement specification.

•

Implemented dimensional model (logical and physical

•

Studied the PL/SQL code developed to relate the source and target mappings.

•

Worked with Datastage Manager for importing metadata from repository, new job Categories and

•

creating new data elements.

Designed and developed ETL processes using DataStage designer to load data from Oracle, Flat

•

Files (Fixed Width) and XML files to staging database and from staging to the target Data

Warehouse database.

Used DataStage stages namely Hash file, Sequential file, Transformer, Aggregate, Sort,

•

Datasets, Join, Lookup, Change Capture, Funnel, Peek, Row Generator stages in accomplishing

the ETL Coding.

Developed job sequencer with proper job dependencies, job control stages, triggers.

•

Used QualityStage to ensure consistency, removing data anomalies and spelling errors of the

•

source information before being delivered for further processing.

Excessively used DS Director for monitoring Job logs to resolve issues.

•

Involved in performance tuning and optimization of DataStage mappings using features like

•

Pipeline and Partition Parallelism and data/index cache to manage very large volume of data.

Documented ETL test plans, test cases, test scripts, and validations based on design

•

specifications for unit testing, system testing, functional testing, prepared test data for testing,

error handling and analysis.

Used Autosys job scheduler for automating the monthly regular run of DW cycle in both

•

production and UAT environments.

Verified the OBIEE Report by extracting data from the Staging Database using PL/SQL queries.

•

Wrote Configuration files for Performance in production environment.

•

Participated in weekly status meetings.

•

AXA UK, Tunbridge Wells, KENT, UK 06/2011 09/2013

ETL Designer/ DataStage Developer

Project Description:AXA UK is one of the biggest insurance company in the world and is located in UK.

Healthcare is a subsystem of AXA PPP system which deals with Policy loading, from another Mainframe

system, through the daily and weekly batch process. All the policies information will be maintained in the

Siebel system. Call center Customers use the Siebel health application to operate on the policies and

answer the queries of the policy holders.

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 & Oracle10G.

Environment: IBM DataStage 8.0 (Designer, Director, Manager, Parallel Extender), Oracle 10g, DB2 UDB, Flat files,

Sequential files, Autosys, TOAD 9.6, UNIX, OBIEE 10g

Responsibilities:

Involved as primary on site ETL Developer during the analysis, planning, design, development,

•

and implementation stages of projects using IBM Web Sphere software (Quality Stage v8.1, Web

Service, Information Analyzer, Profile Stage, WISD of IIS 8.0.1).

Prepared Data Mapping Documents and Design the ETL jobs based on the DMD with required

•

Tables in the Dev Environment.

Active participation in decision making and QA meetings and regularly interacted with the

•

Business Analysts &development team to gain a better understanding of the Business Process,

Requirements & Design.

Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the

•

ORACLE database.

Designed and Developed Data stage Jobs to Extract data from heterogeneous sources, Applied

•

transform logics to extracted data and Loaded into Data Warehouse Databases.

Created Datastage jobs using different stages like Transformer, Aggregator, Sort, Join, Merge,

•

Lookup, Data Set, Funnel, Remove Duplicates, Copy, Modify, Filter, Change Data Capture,

Change Apply, Sample, Surrogate Key, Column Generator, Row Generator, Etc.

Extensively worked with Join, Look up (Normal and Sparse) and Merge stages.

•

Extensively worked with sequential file, dataset, file set and look up file set stages.

•

Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for

•

development and de bugging purposes.

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 on ad hoc or

scheduled basis.

Developed complex stored procedures using input/output parameters, cursors, views, triggers

•

and complex queries using temp tables and joins.

Converted complex job designs to different job segments and executed through job sequencer for

•

better performance and easy maintenance.

Creation of jobs sequences.

•

Maintained Data Warehouse by loading dimensions and facts as part of project. Also worked for

•

different enhancements in FACT tables.

Created shell script to run data stage jobs from UNIX and then schedule this script to run data

•

stage jobs through scheduling tool.

Coordinate with team members and administer all onsite and offshore work packages.

•

Analyze performance and monitor work with capacity planning.

•

Performed performance tuning of the jobs by interpreting performance statistics of the jobs

•

developed.

Documented ETL test plans, test cases, test scripts, and validations based on design

•

specifications for unit testing, system testing, functional testing, prepared test data for testing,

error handling and analysis.

Participated in weekly status meetings.

•

Developed Test Plan that included the scope of the release, entrance and exit criteria and overall

•

test strategy. Created detailed Test Cases and Test sets and executed them manually.

AXA USA, Chennai, India 09/2009 05/2011

ETL Designer/ DataStage Developer

AXA Insurance provides a wide range of insurance policies such as Life Insurance, Health Insurance,

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.

Environment: IBM DataStage 8.0 (Designer, Director, Manager, Parallel Extender), Oracle 10g, DB2 UDB, Flat files,

Sequential files, Autosys, TOAD 9.6, UNIX, OBIEE 10g

Responsibilities:

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.

•

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.

•

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

•

Nokia Corporation, Chennai, India 08/2007 09/2009

ETL Designer/ DataStage Developer

Project Description : Retail net application will give the complete information about Nokia sales data and

it compares the Nokia sales with other competitor sales.

Analytics will pull the required information from Siebel OLTP. Business users in turn use this data

to generate reports using dash boards.

Environment : IBM / Ascential Data Stage 7.5, Quality Stage 7.5, Autosys, Windows NT, UNIX,

Oracle 9i, SQL SERVER, Siebel Analytics 7.6, Sequential files, .csv files

Responsibilities

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

•

data into data marts/data warehouse.

Worked closely with data warehouse architect and business intelligence analyst in developing solutions.

•

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

•

Aggregator, and Sort etc.

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

•

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

Converted complex job designs to different job segments and executed through job sequencer for

•

better performance and easy maintenance.

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

•

techniques.

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

•

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

•

standardization Process

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.

•

Wrote complex SQL queries using joins, sub queries and correlated sub queries. Expertise in PLSQL/SQL.

•

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

•

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

Education:

Bachelor of Technology in Computer Science & Engineering, JNT University, India

Contact this candidate