Project Data
Resume:
Jains Francis
BI, DATA WAREHOUSE, DATA MANAGEMENT *****.*******@*****.***
Summary
> 7 plus Years of experience in IT industry; worked with phases of
analysis, design, development, and documentation for Business
Intelligence Solutions in Data Warehousing/Decision Support System
using ETL Tools.
> Expert in business process engineering and software development life
cycle, including analysis, design, development, testing and
implementation of software applications.
> Familiar with current industry standards, such as HIPAA, SOX, ISO, Six
Sigma, and Capability Maturity Model (CMM).
> Strong Experience in interacting with Business Users to analyze the
business rules on requirements to design the database.
> Worked with Data Modeling both Physical & Logical using Erwin/
Designer 2000.
> Proven ability to plan, coordinate and implement Full Life Cycle
software development and maintenance projects.
> Strong experience in Ab Initio GDE, Co-Operating system, EME and
developing Source and Target Databases in a complex, high-volume
environment.
> Developed Data Warehouses/Data Marts/ODS and Business Intelligence
applications.
> Extensive Knowledge in conversion of Corporate Data Model into a
Warehouse Star Schema or Snowflake Model
> Strong skills in Oracle are PL/SQL, SQL*PLUS, Pro*C, back-end
programming like Packages, Functions, Stored Procedures, Database
Triggers and wrote Shell scripts.
> Strong in Database/Application Design, performance tuning.
> Worked extensively in data warehousing using ETL (Extraction,
Transformation and Loading) tool Ab Initio and Informatica well versed
with RDBMS's like Oracle & SQL Server and Operating System concepts
> Strong skills in developing data management solutions using Ab Initio
along with Enterprise Meta Environment (EME)
> Strong experience with Informatica Power Mart 7.1/6.2.1/5.x, Power
Center RT 7.1/6.2/5.x.
> Strong in Database/Application Design, performance tuning and Database
Administration, Metadata Management, Dimensional Modeling
> Expertise in Working UNIX Shell Script, scheduling cron jobs and
mainframe environment.
> Good understanding and exposure to Data Warehousing techniques like
OLAP, ROLAP, MOLAP, and Star/Snowflakes Schema.
> Working Knowledge on different data sources as Flat Files, Oracle, SQL
Server, DB2, Teradata and Excel Files.
> Strong analytical and conceptual skills in database design and
development using hyperion, Crystal reports, Oracle 9i/8i, PL/SQL,
DB2, Teradata and MS SQL Server 2000.
> Good problem solving ability in diverse software environment. Very
good communication skills and Team player.
Skills:
Data Warehousing Ab Initio GDE 1.14,1.13/1.12/
>Operating System 2.14,2.13/2.12/2.11/2.10.
Informatica Power Center 8.6, 7.1/6.2/5.1
Hyperion, Crystal Reports, BO
Database Oracle 9i/8, Oracle 10G
DB2/UDB 8.1,
Teradata v2r8,
Pl/SQL,
MS SQL Server
Data Modeling Dimensional Data Modeling,
ER Modeling
Operating System Windows XP/2000/NT
Unix, Mainframe
Industry Knowledge Health care
Finance
Web related VB Script, HTML (Theoretical knowledge)
Language PL/SQL, SQL, Pro*C, Java, UNIX (K/B/C) Script
Tools Designer 2000,
Erwin 4.0,
Developer 2000/6i/9i,
Form Builder,
Query Builder,
Reports Builder,
Forms 9i/6i/4.5,
Reports 2.5/6i,
TOAD,
SQL*Plus,
SQL*Loader,
Explain Plan
Leadership/Team Skills
. Directing and supervising teams on design, build, and support
. Project planning and managing onsite/offshore delivery
. Engaging with client leadership and developing trust and rapport.
. Defining standards and processes for BI design and development.
. Mentoring and coaching internal resources as well as client teams.
Experience Highlights:
Kaiser Permanente, CA Jan '09 - Present
ETL Team Lead/ Sr Developer
Environment: Ab Initio GDE 1.14. Ab Initio Co>OS 2.14.1, Informatica Power
Center 8.6, 7.1/6.2/5.1, Oracle9i, DB2, UDB, Teradata, Windows NT 4.0,
UNIX, Mainframe, PL/SQL, TOAD Quest,
Project Description:
Kaiser Permanente is an integrated managed care organization, which
operates in nine states and Washington, D.C., and is the largest not-for-
profit managed care organization in the US.
As an ETL Designer and Developer, I have worked on the following projects
in KPIT from end-to-end.
Project Description Medicare Letter Optimisation:
The Medicare Letters Optimization Project intends to replace or
substantially replace the current Medicare correspondence functionality as
it exists today in the three core membership systems: Foundation Systems
(FS), Common Membership (CM), and The Membership System (TMS) in order to
streamline the processes and achieve cost savings for all KP regions.
To align the Medicare Letters Optimization project with the Print Strategy
Pathways initiative, an interim solution has been developed which produces
extracts using the EDW environments. Also to enhance MLO to support MBE
Project Medicare Secondary Payer Mandatory Insurance Reporting:
Center for Medicare and Medicaid services (CMS) has added a new mandatory
reporting requirement for group health plan (GHP) arrangements. As per the
CMS requirements, the group health plan shall:
I. Secure from the plan sponsor and plan participants such information as
the Secretary shall specify for the purpose of identifying situations
where the group health plan is or has been a primary plan to the
program under this title; and
II. Submit such information to the Secretary in a form and manner
(including frequency) specified by the Secretary.
CMS seeks to collect various data elements from the applicable reporting
entities for purposes of implementing the mandatory MSP reporting
requirements. This information will be used to ensure that Medicare makes
payment in the proper order and/or takes necessary recovery actions.
Responsibilities:
Roles and Responsibilities:
As an ETL Designer/Developer and Analyst, I was responsible for
. Liaised with business and functional owner during risk engineering and
high-level review sessions to derive and execute action plans, meeting
deadlines and standards.
. Interfaced with business users to prepare and update Business Process
Requirements (BPR) and Software System Requirements (SSR). Created
test cases and test scripts
. Analyzed data for report design and implementing new procedures.
. Created innovative ways to utilize time saving techniques when
creating complex and large reports.
. Designed the ETL processes to load data from Mainframe, Teradata,
Oracle, SQL Server and Flat Files target Oracle, DB2, Teradata
databases.
. Graph/Mapping development using Informatica and Ab Initio and Unix
Shell scripts
. Provided guidance to the juniors as team lead for successful
delivery.
. Extensively worked in the performance tuning of the programs, ETL
Procedures and processes.
. Testing - Unit testing & Integration testing
. Driving Run activity and UAT co-ordination.
. Involved in developing Tiwoli and Jobtrac jobs for scheduling
. Involved in Performance Engineering for the existing ETL
. Involved in building and maintenance of data mart from which
reports where extracted for CMS
Hyundai Motor Finance Company, CA Nov 2007 to Dec 2008
Data Management Analyst
Environment: Oracle 9i/10g, PL/SQL, Forms 9i,Unix, Reports, UNIX Sun Os
5.9, Tracker, Designer 6i/2000, Erwin, Stored Procedures, SQL*Loader, SQL
Navigator 4.0, TOAD 7.4
Project Description:
Hyundai Motor Finance Company is a subsidiary of Hyundai Motor America
providing financial services to their customers. HMFC offers both retail
and lease products designed for specific finance needs
As a Data Management Analyst and Developer, I have worked on the following
projects end-to-end.
Project Pricing: The purpose of pricing project is to create a pricing bulk
upload tool which gives a solution for one of the major pain points in the
Daybreak system .i.e. the setting up of new pricing. Setting up new
pricing was a laborious task of manually entering up to 17000 records. It
required many man-hours and was prone to user errors. The upload tool
essentially does a mass 'copy and paste'. It takes existing (old) product
pricing definitions and copies it into a new product pricing records with a
new start date. All related price parameters and promotion features are
copied into the new pricing records.
Project Nixie/ACS: The primary objective of Nixie/ACS (Address Change
Service) is to process the NIXIE/NCOA file and update the daybreak accounts
based on the deliverability code. Accounts which are being received in the
ACS file based on deliverability code are updated with action and result
codes, comments for those non delivered mails.
Project Correspondence Letter: The objective of the Letter Project is to
automate the correspondence process, especially CA NOI. New forms are added
to daybreak to capture mandatory fields for NOI which are not in daybreak.
CA NOI is developed using oracle report based on the data from the
daybreak.
Roles and Responsibilities:
. Involved in building Enterprise wide Logical and Physical database design
using the Designer 6i/2000. As Data Management specialist was responsible
for designing and providing solution for the projects based on business
requirements
. Designed, developed and deployed Forms and Reports to the Forms 6i and
Reports Server.
. Installation and set-up of Oracle 9i/8i database.
. Created and modified Oracle Schema objects like Tables, Indexes.
. Involved in process of normalization. Developed Entity relational
Diagrams (ERD).
. Involved in SQL & PL/SQL programming for stored procedures.
. Worked on performance tuning of SQL queries involved in the reports,
indexes partitioned tables to improve overall performance of RIM system.
. Generated Triggers for data integrity, Created views to resolve complex
queries.
. Designed/ created Oracle database objects, Roles/ Privileges and schemas.
. Wrote PL/SQL procedure, functions, Triggers to maintain data integrity.
. Worked on creating batch programs using C-Shell also developed Oracle
jobs.
. Fine tune SQL queries and resolve performance issues.
. Web enabled Oracle reports using Reports 6i and put it on the Intranet
for the managers
Kaiser Permanente, CA Jan '07 - Oct '07
Sr.ETL developer
Environment: Ab Initio GDE 1.14. Ab Initio Co>OS 2.14.1, Oracle9i, DB2,
UDB, Teradata, Windows NT 4.0, UNIX, Mainframe, PL/SQL, TOAD Quest,
Project Description:
Kaiser Permanente is an integrated managed care organization, which
operates in nine states and Washington, D.C., and is the largest not-for-
profit managed care organization in the US.
As an ETL Designer and Developer, I have worked on the following projects
in KPIT from end-to-end.
Project NPSR: The purpose of the "National Pricing System (NPSR) Teradata
Summarizations" ETL project is to create eight summarizations in Teradata
for all regions. ETL extracts HEP Pricing Production data of Insured and
Claim from DB2 incrementally to create Claim & Insured summarization in
Teradata for different regions. The source data is extracted from the HEP
DB2 database using the BMC unload utility. These summarizations are used by
marketing team for analysis of claims.
Project TKAS (TIMS To KASIS): The TIMS/KASIS interface updates the
"Northern California Kaiser Surgery Scheduling System" with price changes
to operating room supplies .The source data is an extract from the
"Total inventory Management System" (TIMS) application in a Mainframe
system that contains 90% of all products in use for Kaiser Permanente's
Northern California Region's surgery scheduling. The interface will update
23 KASIS instances in Oracle 9i
Project GLMR: The Global Internet Metrics Reporting (GLIMR) project
represents an architectural rebuild of the systems and processes used by
the Internet Services Group (ISG) Performance Measurement and Research
(PMR) group's reporting server infrastructure, including systems, tools,
and updated processes. Historically, the processes used by ISG PMR were
manually intensive, requiring a substantial level of effort to create,
maintain and continue to produce reports of general and feature-specific
use of the KP.org site, organized both by the various audiences that the
site serves, as well as the various regions providing site-specific
services.
Project Travel & Expense: Kaiser Permanente is in the process of
implementing a hosted Travel and Expense solution for Travel, Out of pocket
and Procurement Expense activities. The Concur third party application was
selected and Concur is currently providing the On Line Transaction
Processing (OLTP) services for this application as an Application Service
Provider (ASP). The reporting environment is refreshed with daily data
dumps from Concur. It is to replace the original reporting system with a
more robust, maintainable, extensible, flexible and powerful system
Roles and Responsibilities:
As an ETL Designer and Developer, I was responsible for
. Four High Priority Projects ( GLIMR,NPSR,TKAS,TNE)
. Responsible for requirement gathering from business users.
. Designed the ETL processes using Ab Initio to load data from Mainfame,
DB2, Teradata, Oracle, SQL Server and Flat Files target Oracle, DB2,
Teradata databases.
. Graph development using Ab Initio and Unix Shell scripts
. Extensively worked in the performance tuning of the programs, ETL
Procedures and processes.
. Testing - Unit testing & Integration testing
. Driving Run activity and UAT co-ordination.
. Involved in developing Tiwoli and Jobtrac jobs for scheduling
. Involved in Performance Engineering for the existing ETL
GE Money Americas, CT Mar 2006 to Dec 2006
ETL ANALYST/DEVELOPER
Environment: AB Initio Coop 2.12, 2.14, GDE 1.12,1.13,1.14,Oracle 9i,
Unix Sun Os 5.9, EME, Autosys, Data Profiler 1.17,1.16,Hyperion,(Brio)
Project Description:
GE Money Americas is one of the leading financial institutions in credit
card industry. Consumer Data Customer Insite (CDCI) is one of the vertical
organizations of GE Money Americas. CDCI warehouse contains Cardholder data
of its various clients, which is used for analyzing and reporting
Project CDCI Reengineering: This project dealt with the re-architecture of
the CDCI DW which loads and maintains various consumer details in the
warehouse having accounts with the various clients like WALMART, JCP,
LOWES, EXXON, etc using Ab Initio.
The ware house generally loads around 60 GB of data on a daily basis.
Around 5 Million records will get loaded in the dimension tables. The
warehouse was set to grow up to 12-16 Million records for the dimension
tables.
This enormous growth is always expected in a warehouse, but prior to this
project implementation the ETL architecture was not ready to meet the
service level agreement by considering the growth factor.
The dimension table updates (350 column updates per row) were the extremely
time consuming process causing regular misses in the Service Level
agreements which related to timely load of data.
The partitioning strategy for many of the major tables - dimensions and
fact were changed to improve business user query performance as well as to
increase ease of maintenance (hash portioning to client based
partitioning).
The design was to accommodate the concept of lazy-updates which replaced
the heavy update strategy with simple flag column updates, direct sql
loader inserts into the dimensions, weekly purge mechanism and view
creations for business users.
The overall gain in load speed after implementation has been 70%.
Roles & Responsibilities:
. Analyzing the current data model and proposing the data model which
can improvise the performance of the loads
. Identify the strategy for lazy update and building the same.
. Utilizing parallelism concepts provided by Ab-Initio to improve data
processing performance.
. Creating new projects for the ETL team.
. Managing space constraints in the ETL box.
. Creating purge ETL for weekly purging of unwanted data created in
dimensions based on lazy update
. Performed System, Integration, regression and performance testing
which helped in getting 100% match between the UAT and Production
data.
GE Money Americas, CT Aug 2005- Feb 2006
ETL DEVELOPER
Project: CDCI Track2 Migration
Environment:Informatcia 6.2, AB Initio Coop 2.12, 2.14, GDE 1.12,1.13,1.14,Oracle 9i, Unix Sun Os 5.9, EME, Autosys, Data Profiler
1.17,1.16
GE Money Americas is one of the leading financial institutions in credit
card industry. Consumer Data Customer Insite (CDCI) is one of the vertical
organizations of GE Money Americas. CDCI warehouse contains Cardholder data
of its various clients, which is used for analyzing and reporting.
The goal of CDCI is to consolidate data from CDW (Consumer Data warehouse),
FDR (First Data resource), Fraud, and ADS warehouses into a single source
of consumer card data which supports business requirements for reporting
and analysis. It provides a single view of consumer data and allows new
applications (e.g. RDC) to be added in a timely fashion with reduced cost.
CDCI is a 40 TB warehouse, supported by Oracle 9i Database and containing
the cardholder data of 40 plus clients which include retail bigwigs like
WALMART, JCPENNEY etc.
CDCI Track2 Migration is migrating Wal-Mart, JCPenny, Gap, Banana Republic
and Old Navy from independent data mart to consolidated cdci data
warehouse
Roles & Responsibilities:
. Designing and developing the ETL for extracting data from the
independent datamart of each client, necessary transformation required
for loading into the cdci warehouse tables.
. Key member in Developing abinitio graphs for migrating the history data
from one warehouse to another warehouse
. Graph development using Ab Initio and Unix Shell scripts
. Testing - Unit testing & Integration testing
. Driving Run activity and UAT co-ordination
. Involved in developing Autosys jobs for scheduling
. Managing the EME as EME Admin to maintain a smooth environment
. Creating new projects for the ETL developer to work on it.
. Created teradata summarization for analyzing the data
. Driving Run activity and UAT co-ordination.
. Writing UNIX wrapper scripts to run Ab-Initio graphs.
GE Money Americas, CT JUL 2003 - Jul 2005
ETL DEVELOPER
Environment: Hyperion, AB Initio Coop 2.12, 2.14, GDE 1.12,1.13,1.14,Oracle 9i, Unix Sun Os 5.9, EME, Autosys, Data Profiler 1.17,1.16
Project Description:
GE Money Americas is a leading provider of financial services to retailers,
manufacturers, and consumers in over 45 countries around the world. It has
different business verticals like Collections, Consumer, CDCI and
Commercial. This project deals with support & enhancement for the data
warehouses of all the 4 business verticals. We need to provide end-to-end
data management solution for CDCI GECF
Project CDCI Build: The goal of CDCI is to consolidate data from CDW
(Consumer Data warehouse), FDR (First Data resource), Fraud, and ADS
warehouses into a single source of consumer card data which supports
business requirements for reporting and analysis. It provides a single
view of consumer data and allows new applications (e.g. RDC) to be added in
a timely fashion with reduced cost Contribution
The project deals with developing modules using Abinitio to extract,
transform and load Credit cards related information of the customers who
have account with the various clients like wall mart, JCP, EXXON etc. Their
data are to be transferred from different-source systems (flat file, COBOL
file, Oracle Table etc.) to Relational Systems.
Roles and Responsibilities:
. Responsible for requirement gathering from business users.
. Releases involving addition of more than 20 major clients to the CDCI
Data warehouse.
. Designed the ETL processes using Ab Initio to load data from Oracle,
SQL Server and Flat Files target Oracle Data Warehouse database.
. Graph development using Ab Initio and Unix Shell scripts
. Extensively worked in the performance tuning of the programs, ETL
Procedures and processes.
. Developed PL/SQL procedures for processing business logic in the
database.
. Responsible for EME maintenance as ETL Admin
. Testing - Unit testing & Integration testing
. Driving Run activity and UAT co-ordination.
. Involved in developing Autosys jobs
. Managing the EME to contain, different versions of the graphs.
Education:
Bachelor Degree of Engineering in IT: Cochin University of Science and
Technology
References: Available Upon Request
Contact this candidate