Sayantan Pramanik

Lead ETL Informatica Developer

https://www.linkedin.com/in/sayantan-p-b1373a24/

PROFILE:

Informatica Certified Specialist with 11+ Years of Total IT Experience in ETL Development

Primary Skills:

Informatica Powercenter ETL, DW/BI, Developer IDQ, PowerExchange, Data Transformation Studio, Repository Manager, Oracle 12g, Data modelling, Data mining, Informatica Metadata querying, Informatica Cloud Services.

Secondary Skills:

Unix commands and scripting, Netezza ELT scripting, AWS technologies, BMC Control M, Tableau

SUMMARY:

11 + years of IT Industry experience.

Successful implementation of Projects using Informatica 7.X, Informatica 8.6, Informatica 9.1, Informatica 9.6.1& Informatica 10.1.1.

Detailed analysis of requirements and efficient management of Databases on Oracle 9i/10g/11g/12g, MS Sql Server and Netezza.

Integrated data between various data sources & targets like Oracle, MS SQL Server, Flat Files, XML, AWS MySQL, SFDC, HDFS, Netezza, Progress DB, AWS S3, Elasticsearch, AS-400 DB2, SOAP Web Services, Un-structed data files (pdfs).

Proficient on UNIX, including file handling & Shell scripting, database PL/SQL scripting

Strong in all phases of the Software Development Life Cycle and have worked in large scale projects with incremental deliverables.

Experienced in implementing complex business rules by creating Informatica transformations using Source Qualifier, Lookup (Connected, Un-connected, active, passive), Joiner, SQL, HTTP, Java, UDT, Union, Sorter, Aggregator, Rank, Normalizer, Filter, Expression, Router & Update Strategy.

Employed performance tuning in Power Center at the Target, Source, Mapping, Session and System Levels, by utilizing indexes, shared and persistent lookup caching, changing session DTM and block buffer memory, session partitioning & Pushdown optimization.

Performed administration activities such as migrating workflows/sessions/mappings from lower to higher environments, created and managed folder, connection and group access.

Successfully implemented ETL integrations with cloud technologies like AWS S3, Salesforce.

Designed mappings and data synchronization tasks using the Informatica Cloud Services.

Experienced in data quality tools like Informatica Developer (IDQ).

Skilled in Informatica PowerExchange for reading data from Mainframe AS 400 DB2, and for capturing change data.

Created re-usable PowerCenter components such as mapplets for re-usability in multiple processes.

Adept at the role of a data miner.

Expert in performance tuning ETLs and database queries, to ensure their compliance with SLAs.

Ability to independently perform complex troubleshooting, root-cause analysis and solutions development.

Strong in relational and NoSQL database concepts.

Familiar with handling tools such as TOAD, FileZilla FTP, Putty, Visual Studio 2005/2008, Secure CRT, WinSCP and MS-Office 365 suite of tools.

Experienced in working with scheduling tool (Control M jobs).

Utilized Tableau to slice and dice the data for end users.

Ability to meet deadlines while handling multiple tasks, with decisive and strong leadership skills, yet being flexible in work schedules.

Good Analytical and problem-solving skills with excellent communication skills.

Effective in working both independently and collaboratively in teams.

EDUCATION:

Bachelor of Technology in Computer Science & Engineering; from West Bengal University of Technology, Kolkata, West Bengal, India, May 2002 – Aug 2006

TECHNICAL SKILLS:

Core Technologies

Informatica 10.1.1, IDQ, PWX CDC, Informatica Cloud, DTS

RDBMS

Oracle 12g, Sql Server 2005 and 2008, Netezza, DB2

Packages

MS-Office 365, MS-Visio 2003/2007

Development Framework

Waterfall, Agile

Schedulers

Informatica scheduler, BMC Control M

Operating Systems

Windows 10, Windows 7, Windows Vista, XP, RHEL

Database Technologies

SQL Scripting and SQL performance tuning, PL/SQL scripting

Versioning Tools

Tortoise SVN

Reporting Tools

Business Objects XI R2, Tableau

Cloud Technologies

Salesforce, AWS S3, Amazon MySql

Third Party S/W

TOAD, Putty, Filezilla FTP, WinSCP, Remedy

PROFESSIONAL EXPERIENCE :

Waste Management Inc/ Quinnox Inc., Houston, TX October 2013 – May 2018

Onsite Informatica Developer &Technology Lead

Project: ETL Development & Support

Waste Management Inc. is America’s leading provider of integrated environmental solutions, managing and reducing waste from collection to disposal while recovering valuable resources and creating clean, renewable energy. Serving over 20 million residential, industrial, municipal and commercial customers, Waste Management posted $14 billion of revenues in 2015.

Involved in the development of the OCS (Onboard computing system) & multiple other ETL projects for WM. It is the flagship order and vehicle tracking system for the WM trucks and is the major revenue generating application for WM. The project involved sourcing data from OCSD (Onboard Computing System Dispatcher), an OLTP DB, MAS (Mid Atlantic System) a CDC DB, Kronos (containing Driver punches), Compass (containing Truck fleet details) and getting all these data in a staging schema while doing an incremental pull from each of them. Then the application of certain business logic is performed and the data is pulled from the staging schema and pushed to a Datamart schema containing multiple facts surrounded by Dimensions in a Star schema type arrangement. Qlikview reports then extract data from this Datamart, and builds its daily and trending dashboards every day to give the progress and performance reports to the area vice-presidents.

Responsibilities:

Onsite technology lead, responsible for leading and driving a five-member support team who maintains, monitors and provides 24x7 support to the 80+ business critical ETL applications for Waste Management Inc.

Responsible for gathering new project requirements and sharing high level design details with the India development team.

Migrated Informatica jobs, connection strings between different environments during deployment activities.

Implemented ETL solutions for integrating with cloud technologies like Salesforce via API, AWS S3 via CLI, Amazon Aurora MySql DB via ODBC.

Involvement in Joint Application Development (JAD), incorporating ETL solutions during war room sessions with business.

Developed mappings using the Informatica Cloud Services, for synchronizing Customer data with the SFDC account object.

Initiated and spearheaded creation of data flow diagrams for documenting complex ETL and ELT process flows, with help of MS Visio and Lucid charts. The diagrams helped ease understanding of data flow processes.

Accountable for providing POC of new open-sourced data integration tools such as “Pentaho”; and to examine their viability as a low-cost alternative to Informatica.

Adept at querying the Informatica repository database, to obtain metadata about the ETLs, for various analytical and data mining purposes.

Experienced in creating PL/SQL stored procedures for computing the PM compliance metrics for the Fleet Dashboard Toolkit application.

Accountable for the detection and analysis of ETL and Netezza process bottlenecks, and implement ways to improve performance. Such exercises have resulted in the conversion of certain long-running database stored procedures into set based ETL processes, yielding improved data availability time for business.

Ownership of many ETL applications such as OCS (onboard computing system), Fastlane (Disposal operations), wm.com (Ecommerce application), Finance Cubes (Essbase), salesforce (Sales application) & providing detailed analysis and implementing plausible solutions for production issues.

Wrote UNIX shell Scripts & PMCMD commands for FTP of files from remote servers.

Environment: Informatica PowerCenter 9.1, 9.6.1& 10.1.1, Informatica Data Quality, TOAD, Oracle 10g/11g/12g, Linux server, Netezza, Pentaho, AWS

Waste Management Inc, Houston, TX Jan 2018 – Mar 2018

Senior Informatica ETL Developer/Lead

Project: WM Fastlane Disposal e-Manifest Project

The e-Manifest Project was conceived due to the recent requirements for automating the upload process of WM’s “Hazwaste” disposal information to the Environment Protection Agency (EPA).

Responsibilities:

Led the ETL team to devise ETL processes to consume the disposal files in binary blob format from the Fastlane Oracle DB, convert it to a pdf file, and then utilize Amazon CLI to upload the pdf files from the ETL server to an AWS S3 bucket.

Co-ordinated with the java team to capture the java code for converting the Oracle blob data to a pdf file, by using the Informatica Java transformation.

Environment: Informatica PowerCenter 10.1.1, AWS S3 CLI, BMC Control M

Waste Management Inc, Houston, TX Jun 2017 – Jan 2018

Role: Informatica ETL Developer/Lead

Project: WM Canada reporting

To integrate the Canadian payment data with WM Canada’s payment and ecommerce cloud databases, there was a requirement to design and develop new ETL batch processes.

Responsibilities:

Led the design and development of creating the ETL batch processes for transferring the payment transactions, methods and pay instructions from the Cyber-source vendor to WM’s Canada Ecommerce AWS MySql database.

Standardized the data using IDQ Standardizer transformation to standardize reference data, before pushing it to the AWS cloud database.

Designed ETL and ELT processes for ingesting the AWS Aurora MySQL payments data to the on-premise CCDB Netezza data warehouse, for usage in MicroStrategy reports.

Environment: Informatica PowerCenter 9.6.1, AWS Aurora MySQL, RHEL, BMC Control M

Waste Management Inc, Houston, TX Nov 2016 – Feb 2017

Lead Informatica ETL & Netezza Developer

Project: WM Service Delivery Optimization (SDO)

The Service Delivery Optimization project was Waste Management’s initiative to reduce the attrition rate of the WM drivers/helpers, to improve company’s employee retention.

Responsibilities:

Designed and developed ETL, ELT (Netezza) code and tables/views for consolidating Peoplesoft HR, PMT and Talent (Taleo) data for the SDO report.

Utilized Tableau to show the prototype data to the end users from the SDO table.

Co-ordinated with the end users to gather the requirements and used PowerCenter and Netezza DB to integrate the data between the various source systems, and to create techno-functional documents.

Scheduled the developed Informatica workflows and Netezza jobs via the Ctrl M Scheduler.

Environment: Informatica PowerCenter 10.1.1, IBM Netezza, BMC Control M

Waste Management Inc, Houston, TX Jan 2014 – Jan 2018

Role: Informatica ETL Developer/Lead

Project: WM Salesforce Data Integration

Waste Management harnessed the out of the box capabilities of SFDC to streamline and automate sales processes, giving everyone in WM a complete view of the customer, thus providing deeper analysis and insight into critical sales and customer metrics.

Responsibilities:

Created the nightly integration ETL processes to batch process the customer data from WM’s iSeries AS 400 DB2 system and into the Salesforce objects.

Utilized Power Exchange CDC real-time capabilities to replicate the AS 400 DB2 customer and their associated services data into an oracle staging DB. Then used Informatica Cloud data synchronization utility to process this data into the SFDC objects.

Utilized PowerCenter batch ETLs to perform one-time historical data loads after major SFDC releases.

Environment: Informatica PowerCenter 9.6.1, Informatica Cloud Services, PowerExchange, SFDC, Control M

Waste Management Inc, Houston, TX Jan 2016 – Mar 2016

Informatica ETL Developer/Lead

Project: WM EBIZ BAMs Project

Due to change in the payment vendor of WM from Blue Pay to Cyber-source, there was a requirement to develop new ETL processes to integrate the data from the new vendor with WM’s in-house e-commerce database. The project saved WM approx. 14 million annually.

Responsibilities:

Led the ETL team to create feeds to transfer the transactional payment data from Cyber-source (vendor) to the MAS and eBiz billing/ecom systems, by utilizing XML/XSD’s to read the source data.

Troubleshot and resolved the QA bugs raised during the QA, SIT and data validation testing with user.

Environment: Informatica PowerCenter 9.6.1, Linux, Control M.

Waste Management Inc, Houston, TX Nov 2015 – Apr 2018

Senior Informatica ETL Developer

Project: WM Accident Injury Management System (AIMS)v2.0

As WM planned to migrate the existing AIMS system to a third-party claims processing system (Application International Corporation, AIC), the ETL team was required to build ETL processes for the historical & real-time feeds to this new vendor system. The AIMS data caters to the healthcare (caregiver, health insurance, prognosis, EOB) and claims related information for the WM employees.

Responsibilities:

Designed and developed ETL processes to feed the various vehicle, property and accident processing loaders of the AIC system.

Created re-usable PowerCenter sessions and utilized parameterized datestamps to load the historical incidents data on a YOY basis to the AIC server.

Made use of the Informatica Developer to enforce Data quality, standardization and consolidation rules before passing the data over to AIC.

Environment: Informatica PowerCenter 10.1.1, Oracle 12g, IDQ, RHEL, Control M

Waste Management Inc, Houston, TX Jan 2015 – Oct 2015

Informatica ETL Developer/Lead

Project: WM MAS to OCS Route Automation (OCSD)

To achieve greater processing throughput, WM’s MAS billing system changed their route transfer process to the Onboard dispatch system from sequential to bulk. New ETLs were needed to be developed to replace the existing ESB mappings for this file transfer process.

Responsibilities:

Led the design and development of new ETL processes to forklift the existing ESB mappings and convert them into ETL code.

Involved in the troubleshooting and performance tuning of the ETL mappings, to achieve the desired throughput.

Environment: Informatica PowerCenter, Linux, Control M

Waste Management Inc, Houston, TX Nov 2014 – Mar 2015

Informatica ETL Developer

Project: WM Customer at a Glance (CAAG)

Due to a requirement to display the WM customer master data on the Customer At a Glance (CAAG) application, the ETL team was needed to create new ETL processes to move this data from the MAS customer billing application to the Elasticsearch cloud system.

Responsibilities:

Developed ETL processes to move the WM customer data from the MAS customer billing system to the Elasticsearch server.

Converted the relational customer master data into a Json string format by utilizing various Informatica transformations.

Created re-usable Powercenter designer mapplet to convert relational customer master data into NoSQL json string.

Co-ordinated/troubleshot with the Quality, User acceptance teams for Move to Quality & Production activities.

Environment: Informatica PowerCenter, Linux, Control M

Waste Management Inc, Houston, TX Oct 2013 – Mar 2014

Informatica ETL Developer/Lead

Project: Finance Cubes Automation (ETL/Essbase)

FIN MART Essbase cubes provide a common vehicle for collection and accounting close and corporate financial consolidation information, facilitates corporate management reporting, including operating reports/metrics/budgeting/forecasting, and provides a common vehicle for communication between corporate and field-level users.

IRG cube is used by WM to identify key components affecting annual revenue changes across all its business units. The system is designed to distinguish revenue changes attributed to internal efforts (e.g. price changes, new/lost business). IRG analyses to explain the impacts of both unit volume and rate variances upon company revenues.

Responsibilities:

Worked with the financial analysts to capture the requirements and convert them into ETL specs.

Designed and developed ETL’s integrating data from the PeopleSoft Finance and PMT databases to generate Essbase files containing financial consolidation, aggregated volume and rate change and budgeting information.

Environment: Informatica PowerCenter, Linux, Control M, Oracle.

Quinnox Consultancy Services, Waste Management Inc, Bangalore India Apr 2011 – Oct 2013

Offshore ETL Developer/Technology Lead

Project: Waste Management Development and Support

Waste Management Inc. is America’s leading provider of integrated environmental solutions, managing and reducing waste from collection to disposal while recovering valuable resources and creating clean, renewable energy. Serving over 20 million residential, industrial, municipal and commercial customers, Waste Management posted $14 billion of revenues in 2012.

Responsibilities:

Technical lead at offshore, responsible for leading and driving the ETL support team that maintains and provides 24x7 support to the 80+ business critical ETL applications for Waste Management Inc.

Received business knowledge transition from client, and worked on the requirement analysis and development of business-critical applications such as Onboard computing tool, and pricing applications.

Ownership and accountability of critical ETL applications such as OCS (onboard computing system), Fastlane (Disposal operations), wm.com (Ecommerce application), salesforce (Sales application); providing detailed analysis and implementing plausible solutions for production issues.

Responsible for performance tuning at a functional level and at a mapping level. Banked on heavy usage of relational SQLs wherever possible to minimize data transfers over the network, thereby improving the ETL’s ability to meet SLAs.

Developed mappings that landed Netezza tables from other source systems (Oracle, SQL-server and flat files) using simple, medium and complex mapping logics for the WM EBI Business unit.

Provided technical expertise to the support team which helped the team collaboratively resolve many productions issues.

Provided techno functional application presentations for certain applications that were developed for client, during the onsite visit around May, 2013.

Environment: Informatica PowerCenter 9.1, TOAD, Oracle 10g/11g, Linux server.

DOW Chemical Company /Accenture Services Private Ltd Jan 2011 – April 2011

Business Objects Developer

Project: DOW Support Project

Responsibilities:

Handled the Business Objects module for the DOW support project.

Environment: Business Objects XI R2.

Hewlett-Packard, USA Dec 2006 – Dec 2010

Informatica Developer

Project: Hewlett-Packard Home& Home Office (HHO)

Responsibilities:

Analysis of Business Requirements & laid down to a Design Document and Investigation documents.

Worked on different Sources such as Oracle 10g, Sap R/3, SQL Server, IVR, ICM and Flat Files.

Role as an ETL developer includes developing simple and complex mappings. Implementing complex business logics by using various transformations at mapping level.

Understanding business process flow to achieve Application performance.

Performed Unit Testing, performance tuning of mappings and SQL statements.

Coordinating/troubleshooting with Support team for the MTP (Move to Production) activities.

Environment: Informatica Powercenter 7x(Later 8.6), Oracle 10g, UNIX.

Hewlett Packard, Bangalore India Apr 2008 – Oct 2009

Informatica Developer

Project: APOLLO BI ETL Project

Apollo BI project comprised of a series of nine software releases (from Mercury to Pluto) wherein data from the FMCONSOLE OLTP database was transformed into OLAP type data for the FMBI database using ETLs. The FMBI Datamart was utilized by Business Objects to create universes and display reports and dashboards to users.

Responsibilities:

Role as an ETL developer includes developing simple and complex mappings.

Used concepts such as control tables to control the range of data loads for Incremental Load logic, history table loads, update overrides, post sqls and manipulating with target session properties to achieve various target load patterns.

Environment: Informatica Powercenter 8.6, Oracle 10g.

Extra& Co-Curricular Activities:

Coached Informatica and the SDO project’s data integration process trainings at WM.

Competed in nature, wildlife as well as portrait photography events.

Member of social media and other photography media societies.

Avid documentary watcher concerning latest battlefield technologies and war history.

Won the 2004 intra and inter-college chess tourney.

Honors and Awards:

Received Informatica Specialist Certification for PowerCenter Data Integration 10: Developer, Specialist.

Attended 19 days long National Astrophysics Olympiad at Mumbai. Received participation awards.

Received multiple appreciations from the Waste Management IT and business owners for commendable work on performance tuning of SLA critical data load processes, and for creating integrations with new and upcoming cloud technologies.

Contact this candidate