Koustav Bose

• I am responsible for transitioning from Informatica Power Center/Teradata to Redshift/IICS for EDW 2.0. My job involves decoding the existing teradata macros and bteqs and informatica pc jobs and providing a solution in IICS/redshift combination.
• Work on the enhancement of the existing architecture by decoding and debugging the codebase in Teradata/Informatica.

This is a conversion project mainly aimed towards converting legacy data and bringing it to the oracle database which acts as base for a front end tool called ACE. The architecture comprises of two layers landing and staging. Landing is where the legacy data lands after applying global filtration rules and staging is the converted data. We have provided a complete ETL end to end solution for conversion.
• Designing the basic ETL architecture for the conversion.
• Worked on complex conversion framework as per business requirement.
• Worked on complex RECON framework to produce reconciliation reports for the converted data. These reports are used to validate the data being converted.
• Worked on various types of performance enhancement activities to ensure swift completion of the conversion workflows.
• Performed detailed regression testing to ensure code and data quality.
• Conducted code walkthroughs and review peer code and documentation.
• Providing estimates (in man hours) for ETL and database objects involved. Played a major role in ETL planning and design.

I was working as the one of the primary etl resources for the building of a Data Warehouse to aid in Business Analytics. We are building the Integration layer which would unify the data from 18 different sources.
Responsibilities:
• Make mappings to read data from the flat files and load them in the Staging Layer.
• Populate the Integration Layer tables with the data from the different sources.
• Apply business logic to get data as per requirement.
• Extensively used Transformations like Router, Aggregator, Expression and Lookup, Update strategy and Sequence generator.
• Tested the mappings against various test cases and performed detailed analysis of the various functionalities.

I am working as the single ETL resource in a project called APeX which stands for Account Payables Excellence. It is a set of people, programs and processes built around standard Oracle AP to provide a highly customised, seamless solution for processing of AP transactions right from scanning of invoices to posting in general ledger. In this project Informatica is the middleware tool acting as an interface between different servers. I need to develop mappings in accordance to the business logic. The project has three different modules with their individual workflows. I have worked on mappings from all the three modules. As a part of this project I needed to understand the structuring of the data in the ERP staging tables.

Module1: The integration with a third party Service provider allows business to utilize the business program to calculate daily tires payment terms for invoices. Daily Tires provides the business with discount opportunity in that it allows for accelerated payment terms while maximizing discounts. There exists a process where approved invoices are sent to this Third Party Service provider on a daily basis for discount amount and discount date calculation. These invoices are then returned on a daily basis on a file with Due date, discount date and discount amount. Upon payment of the invoices a paid file is created and sent to this third party service provider for identifying payment eligible for funding. All these interfacing are achieved through informatica workflows.


Responsibilities:
• Design the mappings in accordance to the specification provided for the respective businesses.
• Cleanse and transform the received files and publish them in to the ERP staging tables.
• Used Informatica Power Centre 10.1.1 for extraction, transformation and load (ETL) of data in the ERP staging tables.
• Extensively used Transformations like Router, Aggregator, Normalizer, Joiner, Expression and Lookup, Update strategy and Sequence generator.
• Tested the mappings against various test cases and performed detailed analysis of the various functionalities.
• Parameterized mappings as and when required to make them more flexible to use.
• Extensively used Informatica debugger to figure out the problems in mapping. Also involved in troubleshooting existing ETL bugs.
• Created shell scripts to establish sftp connection between the third Party Server and the Informatica Server to send and receive files.
• Troubleshoot the connection issues.
• Followed Informatica recommendations, methodologies and best practices.

Module 2: APSS (Account Payables) is a custom built self-service portal for business user to process and enter immediate payments and various other requests which don’t have purchase order. The invoices entered are imported into AP through the custom interface that exists for importing AP transactions from outside source. This was a two way process which loaded data from ERP to APSS and then from APSS to ERP staging tables.
Responsibilities:
• Understand business need and make mappings to do simple insert/update in the APSS database.
• Extensively analyse the data.
• Used Informatica Power Centre 10.1.1 for extraction, transformation and load (ETL) of data in the ERP staging tables.
• Extensively used Transformations like Router, Aggregator, Expression and Lookup, Update strategy and Sequence generator.
• Tested the mappings against various test cases and performed detailed analysis of the various functionalities.
• Parameterized mappings as and when required to make them more flexible to use.
• Extensively used Informatica debugger to figure out the problems in mapping. Also involved in troubleshooting existing ETL bugs.



Module 3: There exists an internal billing system for the business to reconcile intercompany/internal transactions across various entities. Transactions reconciled in this system were sent back to oracle AP for accounting purposes in the form of a flat file.

Responsibilities:
• Make mappings to read data from the flat files based on line headers.
• Populate several target tables in accordance to the logic specified by the business team.
• Establish sftp connectivity between the mainframe server and informatica server.
• Automating the submission of .Jcl file in the mainframe psudo directory.
• Used Informatica Power Centre 10.1.1 for extraction, transformation and load (ETL) of data in the ERP staging tables.
• Extensively used Transformations like Router, Aggregator, Expression and Lookup, Update strategy and Sequence generator.
• Tested the mappings against various test cases and performed detailed analysis of the various functionalities.
• Parameterized mappings as and when required to make them more flexible to use.
• Extensively used Informatica debugger to figure out the problems in mapping. Also involved in troubleshooting existing ETL bugs.

After successfully moving to Production with the first source system, we took up the next source system. I was working in a team of 5. This was a bigger challenge as this source system had complex business logic. The entire architecture was re-structured to accommodate the business logic. The final EDW table contained a combination of fact and dimension tables. The change in data was identified while loading in to the Type 1 tables. And only the update and insert marked records were pulled in the EDW mapping. We had to implement error logic in both Type 1 and EDW tables. After loading the data successfully to the EDW tables, we had to design the mapping to change the flags in the Type 1 table for the successfully processed records. There were customized error tables in both the Type 1 and EDW layers. We had to inform the target teams with a system generated mail about the error flagged records.
Responsibilities:
• Analysed the business requirements and functional specifications.
• Extracted data from Oracle database, loaded them in to the staging tables and then to the different layers of the architecture.
• Used Informatica Power Centre 9.6.1 for extraction, transformation and load (ETL) of data in the data warehouse.
• Extensively used Transformations like Router, Aggregator, Normalizer, Joiner, Expression and Lookup, Update strategy and Sequence generator.
• Compared the data by validating against the repots sent by the QA team. Implemented the changes as and when required.
• Tested the mappings against various test cases and performed detailed analysis of the various functionalities.
• Defined schedulers for the workflows.
• Automate the entire flow by triggering one workflow after the other to minimize the wait time as much as possible.
• Defined recovery strategy to recover sessions in case of failures.
• Implemented performance tuning logic on sources, mappings, sessions to provide maximum efficiency and performance.
• Parameterized mappings as and when required to make them more flexible to use.
• Extensively used Informatica debugger to figure out the problems in mapping. Also involved in troubleshooting existing ETL bugs.
• Created the ETL exception reports and validation reports after the data is loaded into the warehouse database.
• Written documentation to describe program development, logic, coding, testing, changes and corrections.
• Created Test cases for the mappings developed and then created integration Testing Document.

I was working in a team of 3 to develop completely new mappings for a new architecture called the ETL 2.0. The old architecture for which we were providing production support was not efficient enough as it was taking a lot of time to populate the data into the final EDW tables.
Since the different source systems had different timings to send their source data, the first source system had to wait for the last source system’s data to be loaded in to the Type 1 table for their data to be loaded in to the EDW tables.
To overcome this, a new architecture was proposed in which every Source system will have individual flows all the way to EDW.

Responsibilities:

• Analysed the business requirements and functional specifications.
• Extracted data from oracle database and spreadsheets and staged into a single place (staging tables) and applied business logic to load them in the central EDW tables.
• Used Informatica Power Centre 9.6.1 for extraction, transformation and load (ETL) of data in the data warehouse.
• Extensively used Transformations like Router, Aggregator, Normalizer, Joiner, Expression and Lookup, Update strategy and Sequence generator.
• Developed complex mappings in Informatica to load the data from various sources.
• Implemented performance tuning logic on sources, mappings, sessions to provide maximum efficiency and performance.
• Defined schedulers for the workflows.
• Automate the entire flow by triggering one workflow after the other to minimize the wait time as much as possible.
• Defined recovery strategy to recover sessions in case of failures.
• Parameterized the mappings and increased the re-usability.
• Used Informatica Power Centre Workflow manager to create sessions, workflows and batches to run with the logic embedded in the mappings.
• Extensively used Informatica debugger to figure out the problems in mapping. Also involved in troubleshooting existing ETL bugs.
• Created a list of the inconsistencies in the data load on the client side so as to review and correct the issues on their side.
• Created the ETL exception reports and validation reports after the data is loaded into the warehouse database.
• Written documentation to describe program development, logic, coding, testing, changes and corrections.
• Created Test cases for the mappings developed and then created integration Testing Document.

I worked in a team of 12 to monitor and maintain Informatica Jobs in Production for 4 different source systems. The workflows populated the data in to Data Warehouse for these 4 source systems. Every source system had different timings to send their source data. Out of 4, 3 source systems had oracle tables as source and one sent flat file as a source.
Our day to day activities involved performing basic validations like checking if the workflows are properly scheduled or not, the source file has arrived or not, whether the data is getting populated properly into the various prestaging, staging, type 1 and type 2 tables. And after the completion of all the workflows, send out status mails to the dependent teams pointing out the status of Informatica Jobs.
We also performed various trouble shooting steps in case the workflows ran into some error.
Responsibilities:

• Monitor the jobs all throughout the day.
• Check for rejections and work on them immediately to either get them processed or to prepare them for next day’s load.
• Check for the source files for the source system with flat file as source.
• Analyse the various db issues that occurred on a daily basis and to get them corrected at the earliest.
• Send out daily report mails to the end users about the entire etl status.