We provide IT Staff Augmentation Services!

Etl And Data Analyst Consultant. Resume

Foster, CitY

SUMMARY

  • 11 years of IT industry experience with expertise in Data Analysis, Design, Development, Testing and implementation of Data warehouse & Data marts with ETL, OLAP Tools, BI tools using Informatica Power Center v10.1, v9.x, 8.x, 7.x, Informatica B2B data exchange, CloudInformatica BDM, Informatica TDM, Informatica MDM 10.1, IDD, Entity - 360, Active Vios, IDQ 9.6, Enterprise Data Catalog (EDC). Db: Oracle 12c/11g/10g/9i Oracle Exadata, EDI, Netezza, SQL Server, Teradata 13.10, dB visualizer, BOXI, Tableau, Micro strategy, obiee, Attivio SDC., Salesforce CRM.
  • Extensive Knowledge in Dimensional Data modeling, Star Schema modeling, Snow-Flake modeling, Fact and Dimension tables, Physical and logical data modeling using Erwin data modeling tool.
  • Worked with Dimensional Data warehouse in Star and Snowflake Schemas, created slowly changing (SCD) Type I/II/III dimension mappings.
  • Worked on providing END to END Enterprise ETL solutions in the Data Integration with varied complexity and scale. Industry experience in Data migration, ETL and Database related Design, Development, Testing, Administration and ETL Platform Enhancement & Support.
  • Practiced Kimball DW/BI Lifecycle methodology in all the Data warehouse projects for the DataMart implementation.
  • Experience in Performance Tuning of Informatica Power Center ETL jobs, Oracle/Netezza SQL Queries and stored procedures.
  • Experience with the Informatica Data quality and Master Data Management products.
  • Designed, developed and implemented Master Data Management solution using Informatica MDM 9.6.1, 10v, Informatica.
  • Expertise in Master Data Management concepts, Methodologies and ability to apply this knowledge in building MDM solutions.
  • Hands on experience with Informatica MDM Hub configurations, Data Mappings (Landing, stagingand BaseObjects/ Datavalidation, Match and Merge rules, customizing/configuring Informaticadata directorIDD/applications.
  • Informatica B2B transformation, Informatica B2B data exchange.
  • EDI system Design and implementation.
  • Experience in defining andconfiguring landing tables, staging tables, baseobjects, lookups, querygroups, queries custom queries, packages, hierarchies and foreign-key relationships.
  • Expertise in Informatica MDM hub Match and Merge Rules batch jobs and batch groups.
  • Knowledge on implementing hierarchies, relationships types, packages and profiles forhierarchy management inMDM Hub implementation.
  • Experience in configuring Entity Base objects, Entity types, Relationship Base Objects, Relationshiptypes, Profiles usingHierarchy tool.
  • Experience inseveralfacets ofMDMimplementationsincluding DataProfiling metadata acquisition,data migration, validation, reject processing and Pre-Landing processing.
  • hands-on' lead for Informatica IDQ 9.6.1 Analyst, IDQ Data profiling using Analyst tool,
  • IDR Identity Resolution Address Doctor and Informatica Developer ETL Application and integration with Power Center workflows
  • Performed Informatica server administration work, Installations and upgrades.
  • Worked for over six years with Informatica Professional Services team at various client locations.
  • Working knowledge of Business Intelligence Tools Brio, Business Objects XI, SAS BI DI Studio.Rich experience in BI Reporting tools ( BOXI, Microstrategy). Correlate business reporting needs to be able to deliver the reporting solutions.Expert in designing most optimized ETL Mapping using Informatica Power center, Power Exchange.
  • Strong Experience in writing complex SQL Queries, PL/SQL stored procedures, triggers, creating Tables, Indexes, Views and Materialized Views.
  • Teradataexperience in designing, implementing and supporting applications usingTeradatautilities (Hands on experience in BTEQ, TPT load utilities (fastload, multiload utilities), View point, writing complex SQL queries involving aggregation, query tuning, Teradata performance tuning.
  • Expertise in UNIX shell scripting and PERL scripting, Windows and UNIX operating Systems.
  • Experience in configuring and scheduling and monitoring the batch jobs using workflow monitor, UC4 Scheduling, DAC Scheduling, Tivoli (TWS), Autosys and FTP methods, TIDAL scheduler.
  • Experience in the HP Asset Life Cycle Management (ALM)quality and tracker tool.
  • Strong managerial skills and good experience in managing and coordinating the project and providing project status to the stakeholders of the project and Project Estimates, resource loading.
  • Excellent problem solving skills with a strong technical background and good interpersonal skills.

PROFESSIONAL EXPERIENCE

Confidential, Foster City

ETL and Data Analyst Consultant.

Responsibilities:

  • Confidential (Aggregate Spend) consumer, Gilead send the processed files from Oracle EBS suite for expense data from EBS and Concur systems and DIS(Input sheet from Data stewards) to ODS( informatica for transformations and validation) enrich the data from Emea G360 with proper Profile and address and sends over the correct data after the validation occurs to the AGS in files and emails alert for warnings and errors for inconsistent data as per mapping rules. Informatica B2B parser transformation being used for the DIS File parsing the unstructured xml data from the file and loaded and send to AGS after processing.
  • Manufacturing Forecast accuracy and supply demand: Manufacturing Forecast accuracy and supply demand: This project is to report the accuracy of the forecast demand data vs the supply data over the next 5 years, based on the demand quantities over the period and the Purchase order the drug maker determines the production material qty needed for manufacturing. The source systems are from Demantra and Rapid-Response which provides the input and are sources the OLAP MFG warehouse creating the Data warehouse tables from file extract to Dimensions and fact extract to Fact table loads and creating the Reporting summary aggregate table for Reports. Tableau reports created for end user are schedule attainment, Forecast Attainment, API Demand reports in Microstrategy Reports with quick/context/global filters, parameters and calculated fields.
  • Reports: Schedule Attainment reports as part of ISCP process to see how each cycle of forecast comparing against actuals. Forecast Accuracy report built to see outlier’s items andrespond with solutions in the timely. Chem. Manufacturing reports built to identify the stock movements from warehouse to warehouse.
  • Created shell script for automation File archiving and validation script for RR source file with the estimated file list count for the latest set of files which are to be consumed by the MFG warehouse. Send notification mails after any process fails, success emails after the files are validated.
  • Provide Training to users.

Confidential

ETL Data Architect, Developer

Responsibilities:

  • My activities include assess the Current data model for Lending, Deposits, Wealth and KYC applications.
  • To address the gap for Customer Touch Point Like Unified Product Reference for all LOBs, Prospect db. and capture referral information, which are the value addition for the Bank.
  • Analyze the Current Golden source of Truth record for all the systems for Customer information from the Informatica MDM, system Lending, Deposit and Wealth data flow and rule engine.
  • Developed Boomi process using Disk Connector for CSV, FlatFile, and connections like Webservices, HTTP, SFTP, Google apps.
  • Automate the ETL informatica process for new applicant data from the bank website to the Oracle Test database.
  • Build validation processes and subprocess calls based on business decision (do not bank list lookups process and checklist rules for nationality, SSN etc ) for the Client onboarding process.
  • Build process for CDD (Due Diligence) lookup on questions and answers or enhanced DD process flows and manager approvals.
  • Extract New Prospects information from Flat files and Google Spreadsheet Documents.
  • Implemented document tracking process using the Message shape, Notify shape and Exception shape.
  • Developed Informatica syncup process for inbound and outbound flows, write target methods using Dynamic Insert/Standard Insert/update/delete to DB.
  • Worked on User management (adding users, assign roles and privilege, created custom roles for the account)

HP Enterprise Senior ETLDW- BI solutions Architect and Informatica

Confidential, CA

Responsibilities:

  • Other project: Activate New products information where a new product Order and Sell is captured and their activated status are captured. These products are activated by the Serial no and the Product Skus. There is a web service call which populates the Serial no. fetches Activate information status, then is updated to db tables. The ETL integration process calls the Activate information from the http as API call and reports as email notification multiple documents to and finally the generate a Special Delimited Report which is SFTP.
  • Capture and Create and Review the Business Requirement document for Product Quality project supporting HP acquire H3C, COM3 product lines.
  • Prepare the TSD and Implementation logic document as a part of deliverable for the PQ and Activate Project.
  • Design and implement the EDI/B2B transaction exchanges for BO with HP Reseller/ Trading partners.
  • Responsible for Build and testing the new EDI x12 (850) infrastructure for order fulfilment of Sales, Finance, logistics and analytical teams.
  • Design get and send Process send and get implement web API automation.
  • Developed informatica and boomi process to bring in data using Web services call to GET and POST HTTP API data via the Request and Respond method.
  • Oracle PaaS Solution Design for Absence Exception reporting between Oracle and Workday.
  • Oracle PaaS Solution Design for Mashup UI highlighting Workday planned absences within Oracle OTL
  • Trouble shoot of Oracle PaaS system on SQL queries in retrieving of the data.
  • Developed an error reporting flow where it will go and create an issue ticket in online Oracle cloud
  • Responsible for deploying the process into different environment
  • Scheduling the process as per the FSD requirement.
  • Maintaining the extensions and environments extensions
  • Implemented document tracking process
  • Coordinate with client, Functional Consultant to solve the issue of tickets.

Confidential, Foster City CA

ETL Manager/Sr. Developer.

Responsibilities:

  • Create Informatica Job, Workflow and Informatica worklets. Design the Workflow job sequence to load the Base Data, Staging Data and the final Fact data.
  • Design the Base Table, Loading strategies and Dimensional load, determine the Business Key column for the PK key comparison.
  • Design the Loading the strategy for Confidential reporting Tables like the Pre-approval Summary Facts, Post Marketing Case Details Dimension, Temporary table with all metrics from W PMCASE INFO DIM and W PA SUMMARY FCT tables, Metric Lookup table, Pre-approval Metrics Staging table, Pre-approval Metrics fact table from Staging table, Post Marketing Case Details with Subject Stratification Stage table, Post Marketing Case Details with Subject Stratification Dimension table. The SOURCE ID is the Composite Key of the ID (Natural Keys from the DIM tables) which is being collected in all the DIM / FCT tables for Joins and finally generates the AGG table where the rows/columns are flattened.
  • The ETL implementation of the Historical File Information for the Pre-Approval Files sources in FILE DETAIL Metadata Table, The Current / Latest File is maintained indicated by ACTIVE Flag Status, Old Files are marked as INACTIVE and the END Data is the Last date loaded, also this would maintain the File Seq. no and keep track of the count on the Source File records and Source File names.
  • Created Shell scripts File Mover scripts which can handle the Source and Target Paths for the File copy dynamically as per the Environment (DEV/TST/PRD).
  • Handled the Meddra PT /SOC term Validations using the Stored Procedures Views in the DB.based on which the SUMMARY FCT table for the Pre-approval data is loaded to the table.
  • Built the Technical design specification document.
  • Created Source and Target Table definition as per the user requirement and had business signed off. Performance tuned the mapping to help load the tables faster for large volumes and informatica transformations which require complex transformation. Applied Constraints and Indexes required for the Summary and AGG tables used in Reporting.
  • Managed resources to distribute the activities or the development tasks.
  • Carried out validation as per the Test case document. Prepare for the UAT and PROD deployment creating Deployment Checklist, and labeling the ETL objects and creating the dynamic Queries for ETL objects.

Confidential

ETL Consultant Lead

Responsibilities:

  • Designed and Developed and implemented ETL process and MDM handling for AHM and Complete Spend Health care providers.
  • Data integration for GCH- Veeva, (Veeva is a Salesforce SaaS CRM a cloud-based application).
  • Worked on GCH to VEEVA interface Daily Full load with truncate source gch Stage, daily incremental extracts and load to target Account tables, Stage and update the Address based on External Gilead IDs.
  • Integration with Veeva sales force data with Informatica tool.
  • Error Handling strategy and data reconciliation for Veeva GCH integration utilizes the Common Informatica Framework. Includes the error handing in the ODS tables Data integration for GCH- Veeva, Veeva a Salesforce SaaS CRM a cloud-based application Customer Expense extraction from EBS Oracle, concur to ODS via Informatica.
  • Designed and Developed and implemented ETL process and MDM handling for AHM and Complete Spend Health care providers
  • Project: JPSR (Japan drug Safety and Public Health Periodic Safety Report Reporting) ETL Manager/Sr. Developer.
  • The Confidential Reporting system is a custom in-house system developed system using Informatica and OBIEE. It comprises of two main components, the ETL and the meta-data repository. The ETL layer of Confidential Reporting system loads data from Confidential data sources into a database schema residing in the Argus Insight database
  • Create Informatica Job, workflow and worklets. Design the workflow job sequence to load the Base Table Data, Staging Data and the Final Fact data.
  • Design the Base Table, loading strategies and Dimensional load, determine the Business Key column
  • for the PK key comparison.
  • Design the Loading the strategy for Confidential reporting Tables. The reporting tables:
  • Pre-approval Summary Facts, Post Marketing Case Details Dimension, Temporary table with all metrics from W PMCASE INFO DIM and W PA SUMMARY FCT tables, Metric Lookup table, Pre-approval Metrics Staging table, Pre-approval Metrics fact table from Staging table, Post Marketing Case Details with Subject Stratification Stage table, Post Marketing Case Details with Subject Stratification Dimension table. The connections WIDs are used in the DIM tables and SOURCE ID as Composite Key of the ID (Natural Keys from the DIM tables) which is being collected in all the DIM / FCT tables for Joins and to generate the AGG (Aggregate tables) where the rows/columns are flattened.
  • Create snap process to point to Reports the Reporting db from the warehouse tables.
  • The ETL implementation of the Historical File Information for the Pre-Approval Files sources in FILE DETAIL Metadata Table, The Current / Latest File is maintained indicated by ACTIVE Flag Status, Old Files are marked as INACTIVE and the END Data is the Last date loaded, also this would maintain the File Seq. no and keep track of the count on the Source File records and Source File names.
  • Created Shell scripts File Mover scripts which can handle the Source and Target Paths for the File copy dynamically as per the Environment (DEV/TST/PRD).
  • Carried out validation as per the Test case document. Prepare for the UAT and PROD deployment creating Deployment Checklist, and labelling the ETL objects and creating the dynamic Queries for ETL objects.
  • Create deployment groups and do code promotion to UAT and Prod environment.
  • Help testing with the reporting member with the standard monthly reports using Microstrategy, by doing the validation at the backend. Help end UAT.

Confidential, Torrance, CA

Lead Informatica

Responsibilities

  • As Sr. ETL developer I was working on the SALES and INVENTORY data populating the Dimension and Fact tables using Informatica as tools.
  • Requirements gathering from business.
  • Prepare Source Target mapping document, Process Matrix document.
  • Source data was received from different sources for Specialty Health, MCKESSON Pharma and ABC in the form of delimited Flat files daily and weekly.
  • Responsable for the ETL to take care of the fréquence of the Load to the datawarehouse Oracle tables.
  • Upgraded Informatica MDM from 9.1 to 9.6.1
  • Configured Informatica Data Director (IDD) in reference to the Data Governance of users, IT Managers & Data Stewards.
  • UtilizedInformaticaIDQData Analyst, Developer fordata profiling and matching/removing duplicate data, fixing the bad data, fixing NULL values.
  • Cleanse and Scrub the Data in uniform data type and format applying MDM Informatica and IDQ tools and Load to STAGE and HUB tables and then to the EDW and Finally to Dimension and rollup/aggregate the data by the business grains into the FACT tables.
  • Developed business rules for cleansing/validating/standardization of data.
  • Configured Address Doctor to Validate, Parse, Cleanse, and Standardize North America Address data. Created and deployed new applications in Informatica Data Director and binding applications to a specific ORS.
  • Developed and reviewed configuration documentation and design documentation as well as analyzed functional specifications and user requirement documents.
  • Prepare Master data to populate the Dimension tables attributes using lookup transformation.
  • Prepare and validate Unix script to validate and FTP files from the Remote Server source directory and call them using informatica.
  • Deployment activity from Development to UAT and PROD.
  • Participated in feasibility study of Dell boomi as ETL tool.
  • Code review and incorporate and further changes as needed by the business.
  • Automating the workflow run and sending Alert email notification using Informatica.
  • Provided technical decision support for the system and business analysts.
  • Effectively coordinated between onsite and offshore teams for optimum utilization of the resources and time.

Confidential, Pleasanton, CA

Responsibilities:

  • Identify Business Requirements on discussing with various stakeholders Prioritize/Baseline/Track the requirements in Requirements Centre and create wireframe diagrams using MS Visio.
  • Drove the requirements gathering process through approval of the documents that convey their needs to management, developers, and quality assurance team.
  • Developed and updated functional use cases and conducted business process modeling to explain business requirements to QA teams.
  • System appreciation and Test Strategy document and QC execution Plan.
  • Collaboratively Develop ATDD’s with examples with engineering and Business users and conducted business process modeling to identify acceptance criteria for requirements
  • Ensure Requirements are baselined and signoff by business.
  • Review Test Scenarios, acceptance criteria documents and test cases through detailed steps developed by Offshore Testing teams.
  • Facilitated Daily Stand Up Calls and during Implementation phases
  • Reported the status of the project daily, weekly and on cycle basis as technical lead of the project during test execution phases.
  • Coordinated the UAT and ensured sign off.
  • Lead the production release and lead transition to support teams

Tools used: Informatica MDM, Database used: Oracle Exadata 11gR2

Confidential, Irvine, CA

ETL Informatica Team Lead, Data Architect

Responsibilities:

  • Responsible for support of business teams in addressing of operational system issues as well as requests for new capabilities
  • Integration Manager for different modules in Confidential for Cassiopae datawarehouse for managing Hyundai Leasing/leasepack and Financing/Daybreak application.
  • Develop the design to incorporate new ODS data warehouse and CRM datawarehouse to Report the Leasing and Financing Information to Credit Bureau companies.
  • ETL Integration for the interfaces with near real time data warehouse like CRM and EDW (Enterprise Data warehouse) and source and Target Stage data warehouse.
  • Provide the ETL High and Solution design for different interfaces for inbound and Outbound processes. Designs and implements complex ETL processes.
  • Implement the best practices, ETL Coding standards and guidelines for the development team and the Database standard naming conventions.
  • Requirements gathering with Functional team on reporting Metro 2 format for consumer reports to different Credit Bureau companies.
  • Responsible for the operational support of Informatica environment including but not limited to automation, job scheduling, dependencies, monitoring, maintenance, security, and administration
  • Implements on-going optimization and tuning of Informatica mappings using database portioning and Partition points within Informatica to run parallel threads for loading source data.
  • Design and Manage the file validation scripts which are specific to inbound interfaces (The credit Bureau files which are received from external vendors.)
  • Tibco JMS integration with Informatica for Real time updates for data warehouse.
  • Provide presentation on Solution design and Code walkthrough with the Architecture review board to the client.
  • ETL design to maintain Common error handling and logging mechanism framework. Prototype for the same was build and is being implemented by the team, which captures ETL run error, Job status, email to the admin and business stake holders and capturing the Etl last run date information.
  • Provide guidelines and inputs to Team of developers and module lead to complete development on each sprint of activities on the deadline. Make them understand the functional knowledge and provide them with solution on the business definition on the data mapping activities which is required for them to complete the development.

Database used: Oracle Exadata 11gR2

Confidential, Sunnyvale, CA

Informatica Team Lead and Integration ETL Architect

Responsibilities:

  • Manage the team by assigning the ETL tasks on the Mapping and database or the back end related level activities.
  • Provided inputs on the High Level Solution document and conduct review meeting on getting approvals from the business.
  • Handled few complex maps to handle the Hierarchy of customer products like the product code, desc, Region etc was taken care. Most of the table sources come from multiple Oracle legacy systems.
  • Unit testing data validations.
  • Creating Quality Management Plan, Test Strategy Documents.
  • Developed and updated functional use cases and conducted business process modeling to explain business requirements to QC teams.
  • Schedule the ETL using the DAC (Data warehouse Administration Console) scheduler.
  • The end result was to develop OBIEE reports in R12 environment taken care by the Reporting team for customer reporting.

Confidential

ETL Developer

Responsibilities:

  • The project consisted of providing a financial support system BI project Support and development of Business Intelligence Solution. It included providing Site BI solution to Citigroup Singapore to help the Business user and the IT operations in maintaining banking transaction and Business operations data warehouse. Worked closely with Client Services/Finance/Fraud & Risk team to gather requirements for designing the d/w model.
  • Created transformation logic such as Slowly Changing Dimension, Merge Join, Lookup, Lookup, Conditional Split, Aggregate, Derived Column, and Data Conversion Transformations.
  • Fine-tuned SQL Queries for maximum efficiency and performance, using explain plan, index and db partitioning.

Confidential

ETL developer

Responsibilities:

  • Carry out requirement analysis and Feasibility study.
  • Creation and validation of Informatica mapping design document.
  • Developed mappings, as per transformation rules.
  • Defined source and Target object definition.
  • Implemented all the loading strategy: full load, Bulk Mode, Incremental logic. Insert else update logic, Type 2, type 2 dimension.
  • Monitor jobs in Prod.
  • Code fix and troubleshooting.
  • Performance management.
  • Build data model.

Hire Now