Senior Data Warehouse Developer

PRADEEP KUMAR

E-mail: *******.****@*****.***

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

Voice: 781- 399- 4943

-------------------------------------------------------------------------------------------------------------------------------------------

EXPERIENCES SUMMARY

 Over 9 years of total IT experiences in the Business Requirements Analysis, Application Design, Data Modeling, Development and Implementations of Data warehousing. Client/Server Applications and Web based Applications.

 5 Years of strong Working experiences on Data Migration Applications, directly responsible for the Extraction, Transformation and Loading into Data Warehouse with the different data base like Oracle, SQL Server, Sybase, DB2, Teradata

 Well versed with ETL tools like IBM InfoSphere V8, Ascential DataStage 7.5 and Informatica 7.1

 Well Versed with Informatica Power Center 4.7.1/4.7.2/5.1.1/6.2.1/7i, and Power Mart 4.x/5.x, & Data stage ETL Tools to extract Transform and Load Data Warehouse.

 5 Years of Data Modeling experience of using Dimensional Data Modeling Star Schema/Snow flake schema, Fact and Dimensions tables, Physical and logical data modeling, ERWIN 4.x, 5.x

 Extensively Knowledge with OLAP Tools such as BUSINESS Objects 5.1.1 (Supervisor, Designer, Web Intelligence 2.6) Cognos (Impromptu administrator 5.0, Hyperion Reports 6.5, Scheduler 5.0, Impromptu Web Reports, Script writer)

 6 years of experiences with SQL and PL/SQL to write Stored Procedures, Functions, Packages and Triggers

 Used Ascential DataStage 7.5 for integrating data from Legacy sources to Operational Data source (ODS) to Data Mart. Experience in Meta data creation and Maintenance

 Well versed with Installation and Upgrade of DataStage version 7.5 to 8.0

 Working knowledge of Performances tuning techniques, Folder and Session migration and Repository Administration

 Extensively worked in creating Data marts using RDA, ETL Functions and related OLAP tools

 Excellent experiences with Performances Tuning ,Normalization, Partitioning of data and Indexing

 Experience in implementing the complex business rules by creating re-usable transformations, mapplets, work lets and PL/SQL stored procedures and sessions

 Over 7 years of database experiences using Oracle 9i/8i/7i, Sybase, SQL Server 12.0/11.x, DB2 OS/390, MS SQL Server 2000/7.0/6.5, MS Access 7.0/2000, SQR 3.0, Erwin, SQL, PL/SQL, SQL*Plus, SQL*Loader and Developer 2000

 Worked with different Data sources ranging from flat files, VSAM files, Oracle, Teradata, SQL server, DB2 databases

 Hands-On experience on ETL, Oracle and Unix shell scripting experience in Data Mapping, transformation and Loading on a complex, high-volume environment

 Experience in complete life cycle of an Enterprise Data warehouse

 Experience in various Industry Sectors Such as Financial, Telecommunications, Investment, Insurance and Manufacturing

 Exceptional analytical and problem solving skills. Team Player with the ability to communicate effectively at all levels of the development process

Education: Master’s Degree in Computer Sciences (MSc)

OSMANIA UNIVERSITY, Hyderabad, INDIA

Bachelor’s Degree in Computer Sciences (BSc)

OSMANIA UNIVERSITY, Hyderabad, INDIA

Master’s Of Business Administration (MBA)

Preston University, UK

Microsoft Certified System Engineer (MCSE)

TECHNICAL SKILLS

OS: UNIX (Solaris, Linux), Windows XP/NT/2000/98/95

Databases: Oracle7.x/8.1/8i, 9i, MS SQL Server, DB2, MS Access, and Sybase.

ETL: DataStage 7.5/8.0 Informatica Power Centre 1.7/5.x/6.x

Reporting Tools: Business Objects, Cognos and Power Analyzer (IAS)

Database Skills: Stored Procedures, Database Triggers, and Packages, views

Language: C, C++, SQL, PL / SQL, UNIX Shell Scripting, Java, COBOL, PERL

Design Tools: RDA,Erwin 4.0, Oracle Designer / 2000.

GUI: Developer/2000, Visual Basic.

Internet Tools Applets, Servlets, HTML, JavaScript, VBScript, JSP, XML, ASP.

Packages: SQL* PLUS, Toad 7.x, SQL Navigator

PROFESSIONAL EXPERIENCES:

CLIENT: - IBM, LEXINGTON, MA APRIL 2006 TO PRESENT

SENIOR DATA WAREHOUSE DEVELOPER

This is an Integration Project the aim of this project is to support Business Executives to take quick decisions. IBM Receives the Data from COGNOS, Telelogic, and Micromuse Source’s Systems in the form of Flat files, and also from Data base SQL Server and ORACLE is transferred into HIDW staging areas of IBM. The Data from COGNOS and Acquired Companies source systems should be brought into DB2 by applying business logic through ETL tool

DataStage 7.5/8.0 and PL SQL .The loaded data is used to generate reports using OLAP tool with Hyperion Reprot and Cognos.

RESPONSIBILITIES:

 Interacted with end-users and business analysts to identify and develop business requirements and transform it into technical requirements and ultimately responsible for delivering the solution.

 Involved in creating Technical Specification Document (TSD) for the project

 Involved in Creating the FACT tables and Dimensional Table’s

 Responsible for the developing ETL processes/programs with DataStage Designer to extract data from the client's operational databases, Transform and filter the data, and Load it into Target database

 Created jobs in DataStage Designer 7.5 using Transformations (Aggregator Transformation Custom Transformation, functions, routine ,Router Transformation,

 Created the Server Jobs and Parallel Jobs and Sequences Job’s in Data Stage 7.5/8.0

 Used Parallel Extender in an orchestrate Environment for splitting bulk data into subsets

And to dynamically distribute to all available processors to achieve job performances

 Migrated the existing job’s from Ascential DataStage 7.5 to IBM Information Server 8.0

 Created the Validation Script’s in Designer to validate the Sources Data and also the Target DB

 Extensively written PL/SQL code for Stored Procedure, Function, Trigger and Views

 Worked on User-defined and System Defined Exceptions to handle different types of Errors

 Involved in the preparation of Documentation for ETL using DataStage Standard, Procedures and naming conventions

 Extensively used the DataStage Director for Testing and Debugging its components and monitoring and resulting executable versions

 Created Stored Procedures to transform the Data and worked extensively in PL/SQL for various needs of the transformations while loading the data.

 Used Transformations like Lookup, Filter, Router, Expression, Aggregator, Joiner, and Stored Procedures etc.

 Created Various Jobs in DataStage Designer to move Data from Development DB to UAT DB and to Production Support data base and to Production Data base

 Created Sessions and managed the Workflows using various tasks like Command,

Email using Server Manager

 Involved in Partitioning of huge Target tables and identifying the fields in source tables to add indexes in order to manage very large volume of data to achieve best application performance

 Strongly experienced in using Hash File’s for lookup, including the use and configuration of different types of cache (Static and Dynamic).

 Used DS Director to create sessions with the task developer and workflows with workflow manager to run with the logic embedded in the mappings.

 Prepared Documentation and analysis for each project workflow based on the information gathered from each project manager.

 Assist Customer development group with extracting and analyzing large complex data

 Modified the existing batch process in Unix shell script, and PL/SQL Procedures for Effective logging of Error messages into the Log table

 Provided requirements to generate reports from DB2 with COGNOS and HYPERION Reports

ENVIRONMENT: Data Stage 7.5/8.0 (Designer, Director, Manager, Administrator ), Hyperion Reports (Designer, Objects, Supervisor), COGNOS,RDA, Rapid SQL, SQL, PL/SQL,DB2, Oracle 9i, Excel, Flat files(delimited and fixed width), SQL Server, MS Access, Windows NT 4.0/Xp,Unix AIX..

.

CLIENT: FAIRFAX INSURANCES, PARSIPPANY, NJ FEB’05 TO MAR’06

LEAD DATAWAREHOUSE DEVELOPER

The Objective is to build an Enterprise Data warehouse for various patterns to produce the accurate metrics information falling in the areas of Auto Processing Systems, Claims Processing Systems and Agents Office of Automation System. Designed the new Business objects universes in order to support the Daily/Monthly claims activities. Given agents team a better functionality and in depth understanding of the policies/claims/coverage’s pattern using the drill own/Graphs/charts Used Web Intelligence to publish the report on the web in order to have a better security and interface for the users. Also each agent in the team would be provided with a customized web page on Web Intelligence in order to access some particular set of reports every day.

RESPONSIBILITIES:

 Followed the interface agreements and interacted with the business users for designing ETL applications.

 Interacted with Data Modeler to design and prepare conceptual schema and identified specifications.

 Installation and Configuration of DataStage 6 client and server components, connections with the Oracle database in Data warehouse using ODBC

 Designed database, logical & physical (relationship, referential integrities, object creation) as per business requirements

 Responsible for the developing ETL processes/programs with DataStage Designer to extract data from the client's operational databases, Transform and filter the data, and Load it into Target database

 Created DataStage Jobs for data Transformation ,conditioning, and validation and loading

 Used the DataStage Administrator to assign privileges to users or user groups to control

 Which DataStage client applications or jobs they see to run.

 Used the DataStage Manager for importing Metadata into Repository and also used for importing and exporting the jobs into different projects

 Created mappings with DataStage using Transformations (Expression Transformation, Router Transformation, and Stored Procedure Transformation etc) and Look-ups.

 Worked on Source Analyzer, Data warehousing designer, Mapping Designer and Transformations

 Created Stored Procedures to transform the Data and worked extensively in PL/SQL for various needs of the transformations while loading the data.

 Imported tables definitions from Oracle and other database with Designer.

 Involved in Partitioning of huge Target tables and identifying the fields in source tables to add indexes in order to manage very large volume of data to achieve best application performance

 Assist Customer development group with extracting and analyzing large complex data

 Modified the existing batch process, shell script, and PL/SQL Procedures for Effective logging of Error messages into the Log table

ENVIRONMENT: DataStage 6.5 Oracle 9i, UNIX, Windows XP, WinNT/2000, SQL*Loader, Korn Shell, SQL Navigator, PL/SQL, Power Analyzer, Toad tools for Oracle (Quest Software), Erwin, MS Visio, MS Office 2003

CLIENT: - EXCEL INSURANCES, WEST TRENTON, NJ OCT’03 TO JAN’05

ETL DEVELOPER

Excel Insurances having data in different databases like DB2, Sybase, Informix and People Soft.. Excel had different data Staging areas, OLTP (Online Transaction Processing), legacy system mainframe (Total Systems) and People Soft. We have created a Warehouse from different systems and then we have created different Data Marts to answer different query reports.

RESPONSIBILITIES:

 Installed and configured Informatica Server and Power Center/Power Mart

 Created Repository and User groups Using Repository Manager

 Assigned different permissions to users and user groups as admin

 Analyzed the Specifications and identifying the source data needs to be moved to data warehouse

 Involved in various discussions with end user to analyze Meta data

 Extracted data from different systems into Repository using Informatica Power Connect

 Used IPC to connect People soft and legacy systems

 Used different Data Warehouse techniques like Star-Schema, snowflake schema

 Created Source and Target Databases on Oracle 9I

 Analyzed and Created Facts and Dimension Tables

 Extracted data from different systems into Source

 Created Mappings between Source and Target using Power Center Designer

 Developed different Transformations to load the data into target

 Loaded data into target database from source database using work Flow Manager

 Created and Scheduled Sessions based on demand, run on time, run only once using Informatica work flow Manager

 Developed Shell scripts for the data warehouse

 Created various data marts from data warehouse and generated reports using Business Objects.

 Use of Designer Module to implement Universes, linkages between tables, creating prompts and queries, parsing the Universes, applying contexts and alias tables to remove cyclic dependencies

 Creation of reports like Reports by Product, Reports by Customer, Reports by Period, Demographic reports and Comparative reports. Working with database connections, SQL joins, cardinalities, loops, aliases, views, aggregate conditions, parsing of objects and hierarchies.

 Generated reports using business Objects Report Designer.

 Writing PL/SQL procedures for processing business logic in the database. Tuning of SQL queries for better performance

 Creation of different types of reports, such as Master/Detail, Cross Tab and Chart (for trend analysis). Use of filters, conditions, calculations, etc.

 Use of Analyzer and Explorer to view reports from different prospect (Slice and Dice and Drill down)

ENVIRONMENT: Oracle 9i, DB2, Informix, Sybase, Informatica Power Mart /Power Center 5.x, PL/SQL, Business Objects 5.0, Oracle DBA Studio, Erwin 3.5.2,Operating Systems, Solaris 9, Windows XP

CLIENT: - MASS MUTUAL FINANCIAL, HARTFORD,CT APR’02 TO JUL’03

DB2 UPGRADE PROJECT

The DB2 project is an enterprise wide project that will migrate from VANTAGE from VSAM to DB2 Database. As part of this Upgrade Subprojects which are dependent on vantage been implemented. These projects include recapturing fund numbers and updating Mass Mutual versions of VANTAGE to include new Columns for each release. These projects will impact all of Mass Mutual applications and process. In total 36 applications will be directly effected as they use same fund numbers.

Previously the database was built in Oracle with Stored procedure and Packages. This data warehouse was migrated from Oracle to SQL Server with an ETL tool Informatica. The main Sources for these applications are VSAM files, DB2, Siebel and Oracle. Reports developed from Oracle are also redeveloped from SQL SERVER with BUSINESS OBJECTS.

RESPONSIBILITIES

 Developed various mappings for initial Loads from Oracle to SQL Server, these mapping will load the data before running the daily load from the original sources such as VSAM and DB2 databases.

 Worked with Power Connect to work with Siebel data, to import into Designer in Informatica

 Created a master workflow, which includes validation of sources and indicator files before loading the daily cycle and also to archive the indicator files from their respective directories after the load.

 Implemented performance tuning with the Transformations, Sessions and on the Source Databases to load the data efficiently in to the target

 Involved in the analysis of coding which was written in VB Script to implement this coding in informatica with some business changes in the Up gradation project.

 Created parameter files for the workflows and mappings for various applications to use different environments like Development, Model Office (Testing) and Production. Created complex mappings to compare two databases Oracle and SQL Server after daily loads for some rows initially.

 Worked with Erwin tool to Create data model for data marts in SQL Server.

 Prepared Documentation for each mapping and workflow in higher level to understand the Business users.

 With good understanding of UML worked on activity diagrams and state transition diagrams and class diagrams.

 Worked with Copy Books of COBOL files to import the definition of COBOL files into the designer

 Worked with transformations like Lookup, Expression, Router, Transaction control and stored procedure.

 Created Mapplets, which can be reusable in other mappings for various purposes. To follow the standards of mappings

 Modified the existing batch process, shell script, and PL/SQL Procedures for Effective logging of Error messages into the Log table

 Provided requirements to generate reports from SQL Server with Business Objects

ENVIRONMENT: Informatica PowerCenter5.x/powermart. VSAM Files, Oracle, SQL Server, DB2, UML, VB Script, Siebel, Power Connect, Erwin, Windows2000/NT

CLIENT: WELLS FARGO SERVICES COMPANY, TEMPE, AZ MAR’01 TO FEB’02

ETL DEVELOPER

Activity Based Management Toolset (ABMT) is a financial data warehouse application, which helps the Management team (internal and external users) at Wells Fargo to pull their monthly, Year to Date, Forecast, Invoice, Plan Transfer reports and making Decisions for the future

RESPONSIBILITIES:

 Responsible for Preparing the Data warehouse Requirements Specification Documents

 Used Erwin to model a Star schema with Dimensions and Facts with the help of user feedback’s

 Worked on Informatica - Source Analyzer, Data warehousing designer, Mapping Designer & Mapplet and Transformations.

 Developed ETL procedures to ensure conformity, compliance with standards and lack of redundancy translate business rules and functionality requirements into ETL procedures using Informatica-PowerMart/PowerCenter.

 Responsible for the definition, development and testing of the processes/programs necessary to extract data from the client's operational databases, Transform and cleanse the data, and Load it into the data marts.

 Involved in the analyzing and comparing complex data from multiple sources (Oracle, SQL Server and flat files).

 Responsible for the overall design of the processes to Extract, Transform and Load the data into the Data

 Wrote shell scripts, Sql*loader control scripts, PL/SQL scripts, Stored Procedures for regular Maintenance and Production Support to load the warehouse in regular intervals and to performed Pre/Post Actions.

ENVIRONMENT: Informatica Power Center/Power Mart 5.1, Oracle 8i (8.1.7), Oracle 9i, SQL*Loader, SQL*Plus, PL/SQL, Toad tools for Oracle (Quest Software), MS Visio, MS Office 2000, SUN OS and WinNT/2000.

CLIENT: EURO GROUP OF COMPANIES, LONDON, UK MAY’00 -DEC’00

Project: Sales/Purchase/Payroll/Financial Accounting/Production

Worked as Team lead cum Oracle DBA. Responsible for Installation, administration, Performance Tuning, Backup & Recovery of Oracle Databases on Windows NT.

RESPONSIBILITIES:

 Installation and Configuration of Oracle Designer 2000

 Oracle 8.0.5 Database installations on Sun Solaris 2.x, Windows NT 4.0

 Logical and physical database design and implementation

 Capacity planning in terms of database size

 Creation of Tabelspace, Rollback Segments, Users and other schema objects

 Managing Users, Client Sessions, Connectivity Issues.

 Installation & management of Oracle Clients and Net*8 connectivity

 Design and Implementation of backup schedules and recovery methodologies

 Memory & I/O tuning of Oracle Database

 Assisted developers in writing Correlated and Nested Sub queries

ENVIRONMENT: Windows NT 4.0,Oracle 7.3, Developer-2000

CLIENT: ELECTORAL ROLL MANAGEMENT SYSTEM JAN’97 TO AUG’99

PROGRAMMER

The project aimed at maintaining the comprehensive database of electors and geographical information for the state. Database is designed to handle huge amount of data (app. 49.6 million records) efficiently. Involved in the development of computerization of electoral rolls. The proposed system provides data entry records of all electors in the respective parliamentary constituency and Assembly constituency for the corresponding state. The proposed system provides printing of draft rolls of all electors in the corresponding state.

 Analyzed the aim and end user requirements of the project. Studied the existing system and infrastructure availability. Resource planning for the project

 Designed the Database with the help of the end client

 Generated the time frame for completing the various activities of the project

 Designed and developed backend database using Oracle8I

 Involved in coding stored procedures, database triggers, packages and functions using Oracle PL/SQL for efficient data retrieval.

 Involved in database importing, design and coding of screens in Visual Basic

 Used package/deployment tool to deploy the application on the client’s machine

 Developed the general-purpose Control table data (CTD) software. It was designed to assist other government departments like Home, Census, etc. in solving their complex problems. The CTD software was distributed to other states as a model for development.

 Provided technical support for counting software to General Elections

ENVIRONMENT: Oracle8, Visual Basic 6.0, ODBC, Crystal Reports, Windows NT

Contact this candidate