SUMMARY:

• Eight plus years of working experience in all the phases of data warehousing involving analysis, design, development and testing.
• Strong experience in ETL development using Informatica Power Center 10.x/9.x/8.x (Repository Manager, Designer, Workflow Manager, Workflow Monitor).
• Designed complex ETL mappings to load facts, bridges and slowly changing dimensions.
• Experience in building data marts with dimensional data modeling methodology Star schema. Good understanding of Snowflake dimensional modeling.
• Experience in building operational data store (ODS) near real time integrating with MDM to give 360 - degree view of the customer.
• Experience in profiling and cleansing using Informatica IDE and data cleansing functions in power center.
• Implemented incremental load strategy for batch loads and dynamic cache lookup for change data captures.
• Expertise in performance tuning on sources, targets, mappings and sessions. Used hints, indexes, temp tables, database and informatica partitions, bulk load option, merge statements as part of tuning techniques.
• Hands on experience in writing and tuning SQL queries. Exposure to PL/SQL concepts such as stored procedures, functions, cursors and oracle analytical functions.
• Involved in ETL code migration using repository manager from lower to higher environments.
• Used SVN and GitHub tools for code check-in and version controlling.
• Experience in writing UNIX shell scripts and Python scripts on demand basis.
• Exposure in Hadoop distributed file system, map reduce architecture, amazon web services and redshift.
• Experience in creating, managing and scheduling ETL workflows through Autosys tool.
• Experience in building data visualization worksheets and reports using Tableau 10.
• Worked in production support project involving deployments of database objects and ETL code, batch load monitoring, defect fixes and enhancements.
• Interacted with business analysts, data modelers and subject matter experts in gathering business requirements and understanding data models.

TECHNICAL SKILLS:

Data Warehousing tools (ETL): Informatica (Power Center 10.1.1/9.5.1 /9.1/8.6.0/7.1.0 ), IDE 9.0.1

Databases (RDBMS): Oracle 12c/11g/10g, SQL Server 2008/2012

Platform: Windows 10, Windows 7, Linux, IBM AIX 5.3

Programming Languages: SQL, PL/SQL, Shell Scripting, Python

Versioning tools: SVN 1.7.6, GitHub, MKS Integrity 9.5

Business Intelligence tools: OBIEE 11g, Tableau 10

Scheduling Tools: Autosys 11.3

PROFESSIONAL EXPERIENCE:

Confidential, Woodland Hills, CA

Responsibilities:

• Interacted with subject matter experts to understand MDM application functionality and types of notifications being generated.
• Interacted with data modeler to understand the ODS stage and base layer data models and granularity.
• Performed analysis and profiling on XML notifications after staging the data.
• Prepared design and mapping documents to build staging and base tables.
• Involved in designing ETL strategy, code standards, archival mechanism and restartability functionality for each layer.
• Implemented incremental load strategy to run the jobs on daily basis for staging and base layers.
• Involved in developing mappings, mapplets, reusable transformations, sessions and workflows.
• Used transformations such as Joiner, Expression, Lookup (connected/unconnected), XML parser, Sorter, Aggregator, Filter, Router, Unconnected Stored Procedure, Update Strategy and Sequence Generator in the mappings.
• Created slowly changing dimension type2 template with dynamic cache logic for the dimensions that need to maintain history.
• Created temp tables to capture snapshot of common business logic used across multiple mappings. Temp tables are used in source and lookup queries which simplified the sql complexity, reduced the cache on informatica server and improved the performance.
• Used debugger and session logs to identify any data issues and performance bottlenecks in the mappings.
• Performed unit testing and documented test results. Used analytic functions to ensure versioning is happening correctly in type2 dimensions.
• Created jobs and dependencies were set between them through autosys and tested in lower region.
• Used GitHub for checking in the ETL code and DB scripts. Coordinated with various teams as part of deployment in higher versions.
• Helped offshore in understanding the project requirements, reviewed the code developed by them and provided inputs on best practices and tuning techniques.

Environment: Informatica Power Center 10.1.1, Oracle 12c, Tableau 10, Toad, SQL Developer, Linux, GitHub, Autosys 11.3

Confidential, Glendale, AZ

Responsibilities:

• Interacted with subject matter experts to understand legacy systems and IIW data models and performed data analysis and profiling on required objects.
• Prepared design and mapping documents to build required dimensions, bridge tables and facts in PDM.
• Implemented incremental load strategy to run the batch jobs on daily basis which has the flexibility to increment on data availability in source or increment forcefully.
• Implemented slowly changing dimension type2 with dynamic cache logic for the dimensions that need to maintain history.
• Created mapplet and reusable transformations if required and used across multiple mappings to reduce metadata and simplify the flow. Also, created common set of mappings to populate dimensions and facts for all source systems.
• Implemented pass through and round robin partitioning using informatica workflow manager for parallel processing.
• Applied tuning techniques at database level such as building indexes on source tables, using parallel hints on SQL queries, using partitions to minimize row scanning on tables, inserting data in no logging mode with constraints disabled and making indexes unusable on the objects.
• Created temp tables to capture snapshot of business logic used across multiple mappings and used them in source and lookup queries.
• Performed unit testing and documented test results.
• Created stored procedures for handling restartability in case of session’s failure.
• Documented processing times for each batch job as part of performance testing for initial and incremental loads.
• Involved in code migration from lower environments to QA and Production environments as part of deployments.
• Standardized scripts for cleaning up and restoring data, dropping and building indexes, disabling and enabling constraints in case of any reloads into the data mart.
• Created data visualization reports using Tableau on PDM for business users.
• Provided production support which include job monitoring, defect fixes, applying one time patches and new enhancements. Coordinated with all required teams as part of deployment and ensured jobs to run as per scheduled time without missing SLA.
• Helped offshore in understanding the project requirements, reviewed the code developed by them and provided inputs on best practices and tuning techniques.

Environment: Informatica Power Center 10.1.1/9.5.1 , Oracle 12c/11g, SQL Server 2012, Tableau 10, Toad, SQL Developer, Linux, SVN 1.7.6, Autosys 11.3

Confidential, Glendale, AZ

Responsibilities:

• Interacted with business analysts to gather requirements for different feeds and with subject matter experts to understand legacy system data models.
• Prepared design and data mapping documents based on data analysis on source system tables.
• Performed data profiling using IDE on source data after staging the tables into warehouse.
• Used transformations like Router, Filter, Aggregator, Joiner, Expression, Lookup, Sorter, Update Strategy, Normalizer and Sequence Generator in the mappings.
• Implemented slowly changing dimension type1 logic to capture the latest snapshot on tables used to generate the feeds in data mart.
• Improved performance by building indexes on source tables, applying hints on queries, and used partitions. Reduce the cache in lookup transformations by filtering unwanted records.
• Used debugger and session logs to identify any data issues and performance bottlenecks in the mappings
• Loaded source files with different formats such as pipe and comma delimited in both direct and indirect methods.
• Performed unit testing and documented the test results.
• Documented processing times for each batch job as part of performance testing for initial and incremental loads.
• Involved in migrating the code from lower to higher environment using repository manager.
• Worked with offshore team to help them understand requirements and reviewed the code developed by them.
• Provided production support for phase1 of the project which includes defect fixes and enhancements.

Environment: Informatica Power Center 9.1, Oracle 11g, SQL Server 2008, Toad, Linux, SVN 1.7.6

Confidential, Thousand Oaks, CA

Responsibilities:

• Interacted with the third party interface owners and analyzed the existing code to find bottlenecks if any.
• Coordinated with source system owners to ensure incoming data is in expected format as per requirements to match with existing ETL code.
• Parameterized values that are part of rationalization which can be updated or new values can be added to the existing list in future thereby removing dependency on the mappings.
• Implemented slowly changing dimension type2 logic to handle current and historical data for one of the dimension.
• Created reusable transformations if required and used across multiple mappings to reduce metadata.
• Scheduled the workflows using Informatica scheduler and added reusable success and failure email notifications.
• Involved in the optimization of various transformations like aggregator, lookup and joiner transformations.
• Added error handling for file transfers, email notifications and created environmental files to pass the parameters dynamically in shell scripts.
• Performed unit testing and documented test results for future .
• Involved in code migration from lower environments to QA and production environments as part of deployments.
• Involved in meetings with testing team to make them understand about the changes made in the mappings.

Environment: Informatica Power Center 9.1, 8.6.0, 7.1.0, Oracle 11g, TOAD, SQL Server 2008, Unix (AIX) 5.3v.

Confidential, San Francisco, CA

Responsibilities:

• Interacted with business analysts to understand requirements and business rules for building ETL logic.
• Staged data into warehouse and performed profiling and data analysis.
• Prepared design documents based on the requirements and mapping documents from analysis and profiling results.
• Built common set of mappings for different source systems and referred them through reusable sessions in different workflows.
• Involved in performance tuning such as reducing the lookup tables cache by filtering unwanted records at database level and implementing informatica partitioning.
• Added persistent cache for lookup table referred multiple times in mapping and enabled re-cache from lookup option to refresh the data for every load.
• Extensively used reusable capability of Informatica like reusable transformations, user defined functions, mapplets to reduce metadata.
• Performed unit testing and documented the test cases.
• Created views to collect session and workflow statistics from Informatica repository tables.
• Tuned SQL queries leading to significant improvement in batch processing time by using Explain plan.
• Created stored procedures for restartability and one time up updates in the tables.
• Mapped different data sources to the physical layer and created business model and mapping layer and presentation layer in OBIEE.
• Followed software development life cycle methodology (SDLC) in accomplishing the project.
• Used MKS integrity tool kit to keep track of SQL scripts (Versioning).

Environment: Informatica Power Center 8.6.0, OBIEE 11g, Oracle 11g/10g, Linux, SQL developer 2000, MKS Integrity 9.5