etl dESIGNER
Resume:
Krantikumar Reddy Kadiyam
*******.***@*****.***
phone: 718-***-****
Professional Summary
9+ years of total IT experience in the business requirements analysis, application design,
•
dimensional data modeling, data modeling, designing, coding, development, testing and
implementation of business applications with RDBMS, Data Warehouse/Data marts, ETL, and
client/server environment applications for insurance, pharmaceutical, financial and hospitability.
6 years of experience working on End To End implementation of Data warehouse
•
6 six years of strong experience in Data Warehousing and ETL using Informatica Power Center
•
9.5/9.1/8.6/8.1 .
Strong experience in designing the Design Documents for loading data into Data Warehouse and
•
Data Mart tables.
• Strong understanding of Data warehouse concepts, ETL, Star Schema, Snowflake, data
modeling experience using Normalization, Business Process Analysis, Reengineering,
Dimensional Data modeling, FACT & dimensions tables, physical & logical data modeling.
Good programming experience in Transact SQL, SQL, PL/SQL, Complex Stored Procedures,
•
Triggers.
Three years of hands on experience working with scheduling tools like Auto sys .
•
Experience in working with multiple RDBMS including Oracle 11g, SQL Server, Teradata and MS
•
Access.
Strong expertise on the various data design models like Star Schema and Snowflake models.
•
Exceptional analytical and problem solving skills. team player with the ability to communicate
•
effectively at all levels of the development process
Technical Skills:
ETL: Informatica Power center 9.5/9.1/8.6/7.1
Databases: Oracle 11.i/9.i/8.x, SQL Server, MS Access.
Packages: SQL* PLUS, Toad 7.x, SQL Navigator 4.
Languages: C, Visual Basic, SQL, UNIX Shell (Ksh/Csh),
Operating system: Windows (XP, vista), Win NT, UNIX
Data Modeling Tools: ERWIN 4.5,ER Studio.
Web technologies: HTML, DHTML, XML.
Education:
Bachelor’s in Computer Engineering JNTU, Hyderabad.
Professional Experience:
Client: U.S. Cellular® Corporation, Bensenville, IL Dec 2011 – Present
Role: Sr. ETL Designer/ETL Developer
Project Description: The OPS EDW project is the creation and migration of existing data from the legacy
Amdocs CES billing application known as CARES to an instance of Amdocs CES v8.1, which includes
CES v.8.1 and related interfaces, called Billing/Operational Support System (B/OSS). Although the
existing CARES system is a legacy Amdocs application, extensive customization has limited the facility for
a standard upgrade path.
Markets would migrate from CARES to B/OSS in a series of waves. During this transition, some markets
would be converted to B/OSS while other yet to be migrated markets would remain on CARES.
Applications that interface with the billing system, like the operational data store ( ODS), enterprise data
hub (EDH), and the enterprise data warehouse (EDW) must remain operational during this migration.
During the bridging period (when both CARES and B/OSS would be operational), components and
technology developed would ensure simultaneous and seamless support for both converted and
unconverted markets.
The OPS EDW project’s purpose is to ensure that the EDW not only interfaces with the new B/OSS
platform, but also meets this bridging requirement. The OPS EDW effort provides parity of functionality to
existing EDW 1.0. It supports the new pre paid functionality coming from B/OSS and also a new
Enterprise Product Catalog. It also takes care of changes of code values from CARES to B/OSS.
Responsibilities:
Gathering the requirements from the business to design the mapping interface for the project.
•
Documenting the business requirements and converting the requirements into ETL technical
•
specifications.
Reviewing the requirements and working with data architect/modeler for design changes.
•
Designing the various components and interfaces required for the Informatica
•
mappings/workflows.
Implementing error handling strategy and audit information into the design.
•
Reviewing the code of the design developed by other team members and making appropriate
•
suggestions to match the business requirements.
Finalizing the design plan and preparing the ETL design document.
•
Generated sample data based on the data model design and loaded the data into the database
•
using UNIX shell scripts, which was used both for data analysis and also unit testing purposes.
Analyzing the data for defects and fine tuning the approach for design.
•
Creating unit test cases and plan for unit testing approach.
•
Creating mappings to load data from various sources, using different transformations like Source
•
Qualifier, Expression, Lookup, Aggregator, Update Strategy, Joiner, Normalizer, Filter and Router
transformations, Union transformations, etc.
Creating the job schedule component using Tivoli Workload Scheduler.
•
Coordinating with the QA team to ensure that the test case scenarios are met by the
•
design/development standards.
Working as SIT (System Integration testing) Lead for few builds.
•
Code Migrations to Main Development folders and SIT repository.
•
Helping Associate developers on analysis and Development.
•
Performance Tuning by adding hints and session level partitions.
•
Working on Billion record tables ( performance tuning for historical loads )
•
Developed ABAC (Audit bound and Control) process through JAVA programming.
•
Working on Agile process.
•
ABAC process will handle all Control tables to capture aggregated data from historical loads and
•
use that audit data for Incremental processes.
Working on CDC (Change data capture) for Type 1 and Type 2 loads )
•
Using IBM Clear Quest 10.0 to Log SIT and UAT Defects.
•
Working on Microsoft Share Point to load S2T mappings ( ETL Technical specs )
•
Prepare Design Documents for Development based on S2T documents.
•
Prepare Component and Detail Design documents based on S2T documents.
•
Environment: Informatica Power Center 9.5, Oracle 10g, UNIX Solaris 10, JAVA,Tivoli Workload
Scheduler, HP Quality Center and Windows Server 2000.
Client: Kaiser Permanente, Pleasanton, CA Jan 2011 – Dec 2011
Role: MDW Informatica Developer
Project Description: MEMBERSHIP DATAWAREHOUSE (MDW 2.0): The Membership Data
warehouse(MDW) will deliver a single point data source to analyze member needs, behaviors and market
trends in order to improve quality, optimize sales, drive membership and retention, launch new products,
and spend media dollars
Responsibilities:
Contact the Business Analysts/Users to develop, document and to evolve process and data
•
models based upon their input and feedback
Worked as lead for the particular schema development, team consists of 4 developers.
•
Experience in Agile methodology
•
Experience translating business requirements into Conceptual and Logical Process and Data
•
Models.
Experience in preparing Logical data models as well as Physical data models and document the
•
same
Created reusable transformations for converting varchar into upper case and string to date.
•
Worked on Netezza to build ETL table along with some external tables.
•
Created Mapplet for history management dates,ETL Process status
•
Created mapping for error handling sessions
•
Participated in Data modeling meetings, helped to identify the relationship between tables
•
according to the requirements
change data capture, Error handling, restart and refresh strategies
•
Created mapping for creating a parameter file
•
Worked with DBA’s to design and build the Staging/Target databases
•
Assisted business users for data cleansing and user acceptance testing
•
Worked in Informatica for the Extraction, Transformation and Loading from various sources to the
•
enterprise data warehouse
Developed and tested extraction, transformation, and load ( ETL) processes
•
Involved in change data capture (CDC) ETL process
•
Created sessions and workflows for the source to staging data flow and staging to Target
•
loading
Implemented Type II changes for different Dimensions
•
Developed functions and stored procedures to aid complex mappings
•
Environment: Informatica Power Center 8.6, Oracle 10g, SQL developer, TOAD, Windows XP,HP Quality
center 10.0 .
Client: Life Technologies, Carlsbad, CA May 2010 Jan 2011
Role: EDW Informatica Developer
Life Technologies provides products and services that support Pharmaceutical and Biotech companies
worldwide in their efforts to improve the human condition. The company provides essential life science
technologies for disease research, drug discovery and commercial bio production. Lifetech/Invitrogen is
using Informatica, Cognos Data Manager and Cognos Decision Stream as main ETL tools and Cognos8
BI Suite as the main reporting toolset.
Project name: GOS PROCUREMENT SPEND
Responsibilities:
Involved in gathering requirements from Business and converted them it into Technical
•
documents.
Extensively worked on Informatica Designer, Workflow Manager, Workflow Monitor
•
Worked on Source Analyzer, Warehouse designer, Mapping Designer & Mapplet
•
Designer
Involved in extracting data from ERPs like SAP, ENTERPRISE 1 (Oracle) into EDW (Oracle
•
10g) and developed Transformation logic using Informatica.
Developed ETL technical specs, Visio for ETL process flow and ETL load plan, ETL
•
execution plan, Test cases, Test scripts etc.
Created ETL mappings and Mapplets to extract data from ( EAI) ERPs like SAP, JD
•
ENTERPRISE 1 (Oracle) and load into EDW (Oracle 10g)
Knowledge of various transformations like Source Qualifier, Lookups, Expression Editor,
•
Update Strategy, Filter, Router, Joiner and Sequence generator.
Used the Aggregator transformation to load the summarized data for Sales and Finance
•
departments.
Hands on EDM (Enterprise data management), Extract data from Teradata and load into
•
Oracle 10g.
Knowledge of connected and unconnected lookups
•
Developed Error handling process incase Fact record gets submitted without corresponding
•
dimension record
Created multiple sessions for all the existing mappings.
•
Used the Informatica Workflow Manager to create and run the Workflows, Worklets and
•
Sessions for loading the data using the logic from mappings.
Scheduled Informatica Workflows/Sessions to execute on a timely basis and create
•
backup for source files.
Used CDC logic to load incremental data from source to Data warehouse (EDW).
•
Developed UNIX shell scripts to control the parameter files and copy dates from one
•
parameter file to other
Environment: Informatica Power Center 8.1/8.6, EAI like SAP( R3 & ABAP ) and E1 ( JD Edwards 1)
ERP systems, Power Exchange, ETL, Flat files, Oracle 10g,Netezza, PL/SQL, Cognos8 Data Manager,
SQL * Loader, Toad, Excel, Windows XP,HP Quality center 10.0
Client: Sankyo Pharma Inc., Parsippany NJ Apr 2009 May 2010
Role: ETL Developer
Sankyo Pharmacy Inc., is subsidiary of Sankyo Co. Ltd. Of Tokyo, established its U.S. headquarters in
Parsippany, New Jersey. It mainly develops drugs like Benicar, HCT, and Welchol. The name of the
project is SSR (Stock Status Report) and the project is to build the SSRDM which is used for report
generation to analyze and to take decisions about the stock of their products.
Responsibilities:
Attend meetings and gathered business requirements.
•
Design, developed and implemented of the SSRDM.
•
Use debugger to test the mapping and fixed the bugs.
•
Working with UNIX shell scripting.
•
Develop Mapplets using corresponding Source, Targets and Transformations.
•
Tune Informatica Mappings and Sessions for optimum performance.
•
Solely responsible for IMS Data and converting those to require structure using Informatica.
•
Develop and tune all the Affiliations received from IMS and other data sources using Informatica
•
and tested with high volume of data.
Name containing duplicate rows which are deleted using aggregator.
•
In a map I had to load department table and employee table .Under constraint based loading first
•
it loads employee table and then department table as it contains the foreign key.
I have validated the address of pharmacists and hospitals using trillium.
•
In sales table if we find more employees selling the same product and I used rank transformation
•
to find the first 10 employee who is selling more products.
Use power exchange CDC which captures changes in the sources and loads those changes into
•
the customer dimension.
Create database triggers for Data Security.
•
Assisted in writing project documentation used Microsoft Visio for different diagrams.
•
Environment: Informatica Power Center 8.6, Flat files, Oracle 11g, SQL * Loader, Toad, Excel and Unix
scripting .
Client: Bank of New York, NYC, NY Nov 2008 Apr 2009
Role: DW Informatica Developer
Bank of NY has a long and unrivaled history as a provider of financial services throughout the world. Bank
of NY offers their customers personal service and the advantages of a local bank, while their global
platform allows them to introduce a continuous stream of new and innovative products quickly and more
cost effectively than their competitors. This project is developed for Sales & Marketing and Finance
Departments to assist them in their business Analysis.
Responsibilities:
Attend meetings and get the business requirements.
•
Update maps, sessions and workflows as a part of ETL change.
•
Develop and documented Data Mappings/Transformations, and Informatica sessions as per the
•
business requirement.
Wrote UNIX shell scripts for various purposes.
•
Create maps, sessions, tasks and workflows as a part of starcom and aero plan project.
•
Schedule the workflows in the workflow manager.
•
Call stored procedures in oracle as pre sql session.
•
Create reusable failure and success email tasks.
•
Lead the team in my projects.
•
Cached up all informatics jobs along with my team.
•
Use trillium software to remove duplicates from the data.
•
I had to used change data capture to get only new and updated records from ODS and load the
•
shadow table.
Working with slowly changing dimensions type 1,2,3.
•
Create job status report in excel sheet during weekends.
•
Worked on SQL tools like TOAD to run SQL Queries to validate the data.
•
Design and develop Oracle PL/SQL Procedures and wrote SQL, PL/SQL scripts code for
•
extracting data to system.
ETL Job daily verification.
•
Modifications to existing ETL Code and document the changes.
•
Download Informatica power center 8.6 client.
•
Download oracle 10g client and toad 9.0.
•
Upgrade from Informatica power center 8.1 to Informatica power center 8.6.
•
Environment: Informatica Power Center 8.6, Oracle 10g, Excel and shell scripting, MS Windows XP
professional 2002,UNIX.
Client: Starwood Hotels, White Plains NY Oct 2005 Nov 2008
Role: DW Informatica Developer
Starwood is one of the world's largest hotel and leisure companies. It conducts hotel and leisure business
both directly and through its subsidiaries. Its brand names include the following: St.Regis, The luxury
Collection, W, Westin, Le Meridian, Sheraton, Four Points, Aloft, Element. Through its brands, we are well
representing in most major markets in the world. Our operations are grouped into two business segments,
hotels and vacation ownership and residential operations.
Responsibilities:
Analyze the functional specs provided by the data architect and created technical specs
•
documents for all the mappings.
Develop logical and Physical data models that capture current state/future state data
•
elements and data flows using Erwin.
Tune Informatica mappings for optimum performance.
•
Provide Knowledge Transfer to the end users and created extensive documentation on the
•
design, development, implementation, daily loads and process flow of the mapping.
Extensively used Power Center/Mart to design multiple mappings with embedded business
•
logic. Created Mapplet and used them in different Mappings.
Create events and tasks in the work flows using workflow manager.
•
Design and developed Oracle PL/SQL Scripts for Data Import/Export, Data Conversions and
•
Data cleansing.
Design and deployed in Perl.
•
Environment: Informatica Power Center 7.1, Erwin 3.5, Control M, PL/SQL, Oracle 9i, SQL Server 2000,
Windows XP .
Contact this candidate