We provide IT Staff Augmentation Services!

Senior Data Warehousing Consultant Resume

Lexington, MA

SUMMARY

  • 12+ years of IT experience in Data warehousing and Business intelligence with emphasis on Project Planning & Management, Business Requirements Analysis, Application Design, Development, testing, implementation and maintenance of client/server Data Warehouse and Data Mart systems in the Healthcare, Financial, Retail, and Insurance industries.
  • Involvement in all phases of SDLC from project proposal, planning, analysis, development, testing, deployment and support.
  • Strong experience in Data warehousing concepts, Star Schema and Snowflakes Schema methodologies used in relational, dimensional modeling, Data Profiling and Data cleansing .
  • 12+ years of design and development of ETL (Extract, Transform and Loading data) methodology for supporting data transformations and processing, in a corporate wide ETL Solution using Informatica PowerCenter (9.x/8.x/7.x/6.x/5.x) and Data Stage (8.x/7.x).
  • 12+ years of experience with relational databases such as DB2 UDB, Oracle 8i/9i/10g/11g, Netezza, SQL SERVER 2000/2005/2008 , Teradata V2R5/V2R6.
  • 8+ years of experience in OLTP Modeling (2NF,3NF) and OLAP Dimensional modeling (Star and Snow Flake) using ERwin (conceptual, logical and physical data models) and Sybase PowerDesigner.
  • 8+ years of experience in UNIX Shell Scripting (IBM AIX, Sun Solaris) (KSH - Korn Shell Scripting).
  • 5+ years of Experience in BI reporting tools like SAP Business Objects XI & Cognos 8 BI (8, 8.2, and 8.3). Framework Manager, Report Studio, Query Studio, Event Studio, Metric Studio, Analysis Studio, Cognos connection .
  • 3+ years of experience in OBIEE/Siebel Analytics 10.x/9.x/8.x/7.x, OBI Application - EBS Adaptor (Financial Analytics, Order Management, Supply Chain).
  • Strong skills in SQL, PL/SQL packages, functions, stored procedures, triggers and materialized views to implement business logic in oracle database.
  • Experience with Database SQL tuning and query optimization tools like Explain Plan.
  • Experience in moving the Program Files (FTP) to the server by using the Tools like FileZilla, WINSCP, Putty and Telnet.
  • Strong Data cleansing / Data Migration experience using ETL Informatica and Data Stage.
  • Experience in POWEREXCHANGE to connect and import source data from external systems like DB2, COBOL VSAM, Mainframes, and AS/400.
  • Experience in identifying and resolving ETL production root cause issues. Expertise in maintenance, enhancements, performance tuning of ETL code.
  • Knowledge of Informatica administration in windows environment.
  • Developed test cases for business and user requirements to perform System / Integration / User Acceptance testing.
  • Provided off-hour and weekend production support on a rotational, on-call basis.
  • Carried out training sessions for end-users (Business users).
  • Strong analytical and problem solving skills.
  • Sound experience in Onsite-Offshore model project execution
  • Excellent Organizational and Interpersonal skills both as a Team Leader and as an Independent Player. Excellent written and oral communication skills.

TECHNICAL SKILLSPM Tools: MS Project Professional 2002, MS Excel / PowerPoint 97/2003/2007/2010

ETL Tools: Informatica PowerCenter 9.x/8.x/7.1/6.2/5.1, PowerExchange, Data Stage 8.x/7.x

Reporting Tools: Business Objects XI, OBIEE/Siebel Analytics 10.x/9.x/8.x/7.x, Cognos BI 8.3/7.x, MicroStrategy

Data Modeling: CA ERWin Data Modeler r7.3/4.x, Sybase PowerDesigner, MS Visio 2000

Databases: Netezza, Oracle 8i/9i/10g/11, DB2 UDB, Teradata V2R6/V12, MS SQL Server 2000/2005

Database Utilities: Aginity Workbench, TOAD 8.x, Toad for DB2 4.7, DB2 Load Utility, SQL*Loader

Job Schedulers: Tidal, AUTOSYS, Control-M

Operating Systems: Linux (Red Hat), UNIX (Solaris, AIX v5.2), Windows (NT, 2000, 2003, XP, Vista, 7)

Languages: SQL, Oracle PL/SQL, Korn Shell Scripting (KSH)

Version Control: MS Visual Source Safe (VSS), IBM Rational ClearCase, WinCVS

Others: WINSCP, PUTTY, Kitty, FileZilla, FTP, SFTP, SCP, Telnet, Ipswitch WS FTP Professional 9.01

TECHNICAL SPECIALTIES

DWBI: Data Modeling, Data Warehousing/Data marts, Business Intelligence, ETL Informatica, Informatica with Teradata, Data Stage, OBIEE/Siebel Analytics 10.x/9.x/8.x/7.x, Cognos 8.x BI Reporting, Netezza, DB2 UDB, Microsoft SQL Server 2008, Oracle PL/SQL, Stored Procedure/Triggers/Functions and UNIX.

PROFESSIONAL EXPERIENCE

Client - Confidential

Location: Lexington, MA

Role: Senior Data Warehousing Consultant

Responsibilities:

  • Business requirement gathering by conducting several iterative Joint Application Design (JAD) sessions and interviews with the business user community and business analysts for the new application or to handle a change request.
  • Business requirement analysis, designing and building ETL architecture building blocks.
  • Creating High Level design document translating business requirements into functional specifications and writing pseudo codes and translate them into technical specifications doc.
  • Creating a design that effectively uses high volume of real time data using UNIX Korn shell scripts, ETL tool Informatica and Oracle DB Stored Procedures, Triggers, Functions, etc.
  • Actively involved in designing, developing, creating mappings, mapplets using Informatica source, warehouse and loading the data.
  • Code Review and Performance tuning of Informatica mappings and sessions and SQL queries written in Oracle SQL and PL/SQL.
  • Work with testing team to prepare business test case scenarios that ensures effective testing by following the life of a requirement.
  • Work in conjunction with various other IT teams like Siemens eCube Financials, eGate, Open Link, Siemens eCube Clinics, Open Link, eGate etc. to ensure smooth flow of patients’ real time data related to Admission, Discharge and Transfers (ADT) among various clinics.
  • Preparing and executing Production implementation plan with IT Admin team to deploy all the code and complete the data load in Production environment.
  • Prepare and publish weekly status report and schedule weekly status meeting with all stakeholders to effectively monitor and report the progress of the project.

Environment: UNIX, Informatica 9.1/9.5, Oracle 10.x/11, Netezza, Aginity Workbench v4.2.x, SQL Server Management Studio 2008, Toad for Oracle 12.1, WinCVS, Winscp, Lotus Notes 8.5, Putty/Kitty.

Client - Project: Confidential

Location: Los Angeles, CA

Role: ETL Solution Architect/Project Lead

Responsibilities:

  • Business requirement gathering by conducting several iterative Joint Application Design (JAD) sessions and interviews with the business user community and business analysts for the new AGDM application.
  • Business requirement analysis, designing and building ETL architecture diagram.
  • Based on the requirement prepare the detailed project plan using MS Project and maintain and keep it up-to-date throughout the implementation of the project.
  • Designing and Building data marts using dimensional modeling concepts.
  • Building conceptual and physical data model using CA Erwin.
  • Creating High Level design document translating business requirements into functional specifications and writing pseudo codes and translate them into technical specifications doc.
  • Created a design that effectively uses DB2 Load utility of DB2 UDB, UNIX Korn shell scripts and ETL tool Informatica to handle over 60 million transactions per month.
  • Actively involved in designing, developing, creating mappings, mapplets using Informatica source, warehouse and loading the data.
  • Performance tuning of Informatica mappings and sessions and SQL queries written in DB2.
  • Led the Business Objects reporting team to capture user requirements and build universe and several canned BO reports.
  • Led Testing team to prepare business test case scenarios that ensures effective testing by following the life of a requirement.
  • Act as a subject matter expert for offshore application software developers and engineers.
  • Preparing and executing Production implementation plan with client IT team to deploy all the code and complete the data load in Production environment.
  • Prepare and publish weekly status report and schedule weekly status meeting with client to effectively monitor and report the progress of the project.

Environment: UNIX, Windows XP Professional, Informatica 9.0.1, Business Objects XI 3.1, DB2 UDB, Toad for DB2 v4.7, CA Erwin Data Modeler v7.3, MS Project Professional 2002, DB2 Load utility, Ipswitch WS FTP Professional, Kitty, Putty

Client: Confidential

Location: Costa mesa, CA

Role: Informatica ETL Architect/Lead

Responsibilities:

  • Conduct a gap analysis between Current and Future Service and Application model options and identify required changes to processes and technology and successfully implement the organizational redesign.
  • Business requirement gathering by conducting several iterative Joint Application Design (JAD) sessions and interviews with the business user community and business analysts for the new CCMS application.
  • Business requirement analysis, designing and building ETL architecture diagram.
  • Designing and Building data marts using dimensional modeling concepts.
  • Building conceptual and physical data model using ERwin and Sybase PowerDesigner.
  • Preparing the macro design document and creating High Level design document translating business requirements into functional specifications and writing pseudo codes.
  • Translate functional specifications document into technical specifications explaining business and technical transformation rules.
  • Prepare business test case scenarios using Requirements Traceability Tool (RTT) that ensures effective testing by following the life of a requirement.
  • Act as a subject matter expert for Application Software developers and Engineers.
  • Actively involved in designing, developing, creating mappings, mapplets using Informatica source, warehouse and loading the data.
  • Scheduled the workflows using Informatica scheduler and enhanced the data mart as per business specifications.
  • Developed ETL Design Documents and managed the Business rules and data validation.
  • Involved in writing UNIX parameter files for Informatica jobs.
  • BTEQ, MultiLoad, Fast Load and Fast Export scripts were created as needed for data migration and ongoing data load.
  • Designed MultiLoad scripts to load data into multiple tables based on MultiLoad Layout Labels.
  • Generated reports using BTEQ.
  • Created trigger file to create indicator files for continues workflows using command task.
  • Worked with transformations such as expression, filter, router, update, union, lookup, rank, connected stored procedures, aggregator, and sequence generator, Sorter, joiner transformation.
  • Performance tuning of Informatica mappings and sessions and resolve performance bottlenecks.
  • Customized the existing dimensions and fact tables.
  • Experience in building and deploying an OBI repository (Physical, Business and presentation model) to create Dashboards, answers, ibots, creating dimension hierarchies and level-based measures.
  • Added disparate sources to business model objects and modeling aggregate tables to speed processing.
  • Proficient in using the time-series functions to support historical time comparison analysis.
  • Experience in using OBI answers to run queries to test and validate the repository.
  • Experience in creating requests, adding filters, creating prompts, and creating pivot tables using OBI.
  • Developed the new metadata definitions, new Subject areas in the OBIEE Repository which involved working with the Physical, Business Model and Mapping and Presentation Layers of the Repository.
  • Configured dimensional hierarchy and implemented them in the appropriate reports.
  • Configured Aggregate tables in order to use them in the applicable reports.
  • Created reports in the OBIEE Answers and configured the results in different views such as Table view, Pivot table view, Chart view, Funnel view, Narrative view etc based on the requirement.
  • Created OBIEE Dashboard Prompts, Intelligence Dashboards to accommodate the reports that are built in Answers.
  • Modified the existing reports with necessary configuration changes in front end as well as in the back end (.rpd).
  • Customized .rpd to extract more business functionality from the existing Out-of-box configurations.
  • In addition to the new development work, maintained or modified the existing reports in sync with the up-to-date requirements which in turn required modifications to the existing metadata repository.
  • Integrated Hyperion Essbase metadata to OBIEE.
  • Used Oracle BI Delivers Intelligence Agent iBots to notify the business users.
  • Use ClearCase for version control across common source code used by developers.
  • Deploy all the code that has to be migrated to QA/Production environment.
  • Coordinate with System Testing and UAT team to analyze and resolve opened defects.

Environment: UNIX, Windows vista/7, Informatica 9.0.1, Data Stage 8.x, MS SQL Server 2008, Teradata, OBIEE 10.x, TOAD, Oracle11g, DB2 UDB, Sybase PowerDesigner

Client: Confidential

Location: Saint Louis, MO

Role: Informatica ETL Architect/Lead

Responsibilities:

  • Duties during Application Functional Design Approach: The core of the Data Warehouse design process was an iterative Joint Application Design (JAD) and involved close interactions with business and IT Subject Matter Experts (SME) on a daily basis.
  • Discuss and gather feedback on application details, mapping business processes to UI elements, overall application navigation and UI flow, high level rules and validations and high level integration needs using Business Objects XIR3.
  • Worked with Data modeler to build conceptual and physical data model using ERwin and Sybase PowerDesigner.
  • Technical Design Overview phase had following duties: The functional specification was translated into a technical design to support configuration and build of the application.
  • Upon completion of design, major responsibility involved handing over and communicating the complete design to offshore team to develop the code and resolve any technical and design issues faced by the team.
  • Responsibilities included designing architecture and developing complex Informatica mappings and Data Stage jobs including Type-II slowly changing dimensions.
  • Worked with Informatica Power Center to create mappings for extracting data from various databases sources which include MS SQL Server 2008, DB2, ORACLE, COBOL VSAM and Flat files.
  • Act as a subject matter expert for Application Software developers and Engineers.
  • Used Mapping Designer, Workflow Manager, Workflow Monitor extensively to create, execute and monitor a mapping’s run process.
  • Extensively worked with various transformations like expression, filter, joiner, aggregator, lookup, rank, sequence generator, sorter, etc.
  • Extensively worked on performance tuning and monitoring of programs, ETL procedures and processes.
  • Collected performance data for sessions and performance tuned by adjusting Informatica session parameters.
  • Created pre-session and post-session shell scripts and mail-notifications.
  • Performed bulk data load from flat files to Teradata DB using Fastload and Multiload.
  • Used Requirements Traceability Tool (RTT) that ensures effective testing by following the life of a requirement.
  • Installation and configuration of the OBIEE platform as well as the scheduling weekly sales summary by creating ibots.
  • Created two business models in the .rpd (Subject Area’s) one for upper management - scorecard, and other for regular business users - weekly sales.
  • Installed and configured the OBIEE/Siebel Analytics Server.
  • Mapped Dashboard field requirements to Siebel OLTP/OLAP Physical/Business Model.
  • Performed Dimensional modeling, Identify Fact tables & Measures, Identify Dimension Tables to store business attributes, Identify Aggregate tables & their definitions.
  • Proposed/Recommended design alternatives to confirm to the business requirements, Interact with Business Owners/end-users.
  • Created materialized views to fasten slower queries with complex joins in them.
  • Designed OBIEE/Siebel Dashboards and performed custom formatting with html tags in Dashboard.
  • Established access rights for pages in the Dashboard for different user groups (Object Level Security).
  • Understood Metadata Reporting/Dashboard requirements and created new Dashboard templates/mockups for GUI finalization from Business users and helped in BRD creation.
  • Designed and customized OBIEE/Siebel Answers Views, charts and created Filters.
  • Created presentation layer catalog folders, to show different views of the business model to different users and assigned group permissions to the catalog folders.
  • Use CVS for version control across common source code used by developers.
  • Added disparate sources to business model objects and modeling aggregate tables to speed processing.
  • Proficient in using the time-series functions to support historical time comparison analysis.
  • Experience in using OBI answers to run queries to test and validate the repository.
  • Experience in creating requests, adding filters, creating prompts, and creating pivot tables using OBI.
  • Experience in Sales, Financial, Order Management and Fulfillment Analytics.
  • Installed and configured the Oracle Business Analytics Warehouse (OBAW).
  • Proficient in Installing Informatica PowerCenter and used the Informatica Designer to configure the OBAW components.
  • Designed, examined Source Dependent Extract (SDE) mappings and Independent Load (SIL) ETL mappings.
  • Customized the OBAW using ERP (Oracle EBS), CRM (Siebel) and PeopleSoft data.
  • Experience in Building Slowly Changing Dimensions.
  • Worked with the various enterprise groups to document user requirements, translate requirements into system solutions and produce development, testing and implementation plan and schedules.
  • Worked with Cognos 8 BI Framework Manager to create BI metadata modeling. Experience in using Query studio, Report Studio in creating ad-hoc and complex reports.
  • Knowledge transfers with team members for efficient design of the mappings and programs.
  • Worked on information that is secure, accurate, consistent, organized, and timely as in an enterprise or corporate claims data warehouse.

Environment: Informatica 8.6.1, Data Stage 7.x/8.x, MS SQL Server 2008, Teradata V2R6, Cognos 8.3, Oracle 10g, DB2, COBOL VSAM, UNIX, Korn Shell Scripting (KSH), OBIEE/Siebel Analytics 7.7.1, Windows XP, SQL, PL/SQL, WINSCP, PUTTY, ERwin, TOAD, Sybase PowerDesigner

Client: Confidential

Location: Minneapolis, MN

Role: Informatica ETL Architect/Lead

Responsibilities:

  • Business requirement gathering by studying the current enterprise model.
  • Designing and implementing an architecture which addresses functional requirements, meets the requirement for current year and scalable for future growth, reduce technology diversity and system complexity.
  • Utilized ValuePrint, an internal tool which assists in developing the business case.
  • Interview Subject Matter Experts and IT team and develop foundational understanding of current system architecture.
  • Develop high-level estimate using Microsoft Project Plan, schedule, resource plan and risk analysis.
  • Summarize results of analysis and prepare for stakeholder review.
  • Develop a full lifecycle proof of concept to further explore selected technology option.
  • Supplement financial analysis by comparing performance over time across key value drivers using Value Analytics.
  • Actively involved in designing, developing, creating mappings, mapplets using Informatica source, warehouse and loading the data.
  • Developed complex Data Stage jobs including Type-II slowly changing dimensions.
  • Scheduled the workflows using Informatica scheduler and enhanced the data mart as per business specifications.
  • Developed ETL Design Documents and managed the Business rules and data validation.
  • Involved in writing UNIX parameter files for Informatica jobs.
  • BTEQ, MultiLoad, Fast Load and Fast Export scripts were created as needed for data migration and ongoing data load.
  • Designed MultiLoad scripts to load data into multiple tables based on MultiLoad Layout Labels.
  • Generated reports using BTEQ.
  • Created trigger file to create indicator files for continues workflows using command task.
  • Worked with transformations such as expression, filter, router, update, union, lookup, rank, connected stored procedures, aggregator, and sequence generator, Sorter, joiner transformation.
  • Managed development and production environments using Power Center, Oracle (PL/SQL) in a distributed environment. Monitored the load jobs, error log files for regular load and interacting with end users.
  • Created new Analytics Reports, published and scheduled reports using Oracle BI Publisher.
  • Utilized Oracle BI Answers to build OBIEE reports, charts and pivot tables.
  • Used column formatting functions in Oracle BI Answers.
  • Developed Oracle Business Intelligence Interactive Dashboards.
  • Used Oracle BI Delivers Intelligence Agent iBots to notify the business users.
  • Fixed data discrepancies in Analytics reports that were identified when compared to the OLTP database values.
  • Customized the existing dimensions and fact tables.
  • Maintained both development and production environments since a long time using Power Center, Oracle (PL/SQL) under Unix and NT. Monitoring the load jobs, checking the error log files for regular load and interacting with users were my primary focus areas.
  • Worked with Cognos 8 BI Framework Manager to create BI metadata modeling. Experience in using Query studio, Report Studio in creating ad-hoc and complex reports.
  • Worked with QA and System Testing team to migrate the code in other environment, analyzed and resolved open defects.

Environment: UNIX, Windows XP, Informatica 8.x, Data Stage 7.x, Siebel Analytics eBusiness 7.x, Teradata, Cognos 8.3, TOAD, Oracle9i, MS SQL Server, DB2 UDB

Client: Confidential

Location: Minneapolis, MN

Role: ETL Lead

Confidential is a Fortune 500 financial services organization with dual corporate headquarters based in Minneapolis, Minnesota and Appleton, Wisconsin. Main objective of the project was to merge the two systems OASIS Accredited and OASIS Data Warehouse as access to Accredited was very restricted and there were huge number of duplicate records available in the accredited systems and OASIS Data Warehouse.

Responsibilities:

  • Interacted with Business Analyst (Line of Business) to understand requirements and translated them into the appropriate technical solution.
  • Created data modeling for staging area as well as Data warehouse (Star Schema) database.
  • Extensively worked on developing Informatica Mappings, Mapplets, Sessions, Workflows and Worklets for data loads.
  • Extensively used ETL to load data from wide range of sources such as flat files (CSV, fixed-width or delimited).
  • Performance tune ETL mappings, Maintenance and enhancements of ETL code.
  • Responsibilities included designing and developing complex Informatica mappings including Type-II slowly changing dimensions.
  • Taken part of Informatica administration. Migrated development mappings as well as hot fixes them in production environment.
  • Experience with Database SQL tuning and query optimization tools like Explain Plan.
  • Developing PL/SQL Packages, procedures, Functions, Triggers, Cursors as per the business requirements.
  • Validated report results with the source data and sanctioned the reports to production environment.
  • Created Reports in Report studio comprising of list reports, cross-tab reports and chart reports, Maps, Repeater tables using underlying multiple query structure by using filters, calculations, complex expressions, conditional formatting, and prompts including cascading prompts.
  • Carried out training sessions for users to learn how to execute Cognos reports in Cognos connection.
  • Responsible for preparing test cases and technical unit testing for the developed reports.
  • Responsible for to analyze and apply new requirements to existing reports.
  • Continuously monitor the accuracy of the data and the content of the delivered reports.
  • Involved in Onsite & Offshore coordination.
  • Responsible for to provide production support and resolve issues.

Environment: Informatica PowerCenter 6.x/7.x, Cognos, Erwin 4.x, Oracle, PL/SQL, SQL Server 2005, Teradata, Windows 2003, IBM AIX5.3, Korn Shell Scripting (KSH).

Client: Confidential

Location: Hyderabad, India

Role: Senior ETL Developer

Responsibilities:

  • Co-ordination with client side source system IT Team (OLTP) and business users to define agree and document incoming data mapping specifications.
  • Involved in Requirement gathering.
  • Creating the Technical design specifications based on Client functional specifications.
  • Created data modeling using Erwin for staging area as well as Campaign Analysis (Promotion and Coupon) Data mart (Snowflake Schema).
  • My roles also include, Coordination with offshore in development activities.
  • Provided project estimations to client and upon agreement with client, executed the project as per plan.
  • Worked with Informatica Power Center to move data from multiple sources into a common target area such as Data Marts and Data Warehouse.
  • Worked extensively on different types of transformations like Joiner, Aggregator, Router, and Expression, Normalizer, connected / unconnected Lookups, Data Masking transformation and Update strategies.
  • Responsible for the Power Center development of international Data Warehouse (IDWH).
  • Used POWEREXCHANGE for SAP R/3 to connect and import source data.
  • Designed mappings which identify duplicate customer records. Match and merged records based on business rules. Created customer master (reference) data management MDM .
  • Experience with Database SQL tuning and query optimization tools like Explain Plan.
  • Developing PL/SQL Packages, procedures, Functions, Triggers, Cursors as per the business requirements.
  • Extensively used ETL to load data from wide range of sources such as flat files (CSV, fixed-width or delimited).
  • Created OLTP source metadata reports using Informatica Data Explorer (IDE).
  • Performance tune ETL mappings, Maintenance, enhancements and deployment of ETL code.
  • Involved in migrating the Mappings, Sessions, Workflows from Test environment to Production environment.
  • Exposed the columns in the Presentation layer in the proper Presentation catalog.
  • Responsible for creating additional Users and maintaining Visibility Privileges using Analytics Administration on Repository side aswell as front end to display reports based on the business user for that particular area.
  • Created reports for analyzing the Duration open of the Activity for each organization by the Sales Person and for analyzing the Revenue generated and the Amount expected from each organization.
  • Generated reports for analyzing the revenue generated by various consultants in different departments.
  • Developed Role-Based Dashboards for Executives, Account Managers and Account Supervisors.
  • Performed QA and Code review for my code and other developer’s code.
  • Experience in creating and writing Unix Shell Scripts (Korn Shell Scripting - KSH).
  • Worked with QA and System Testing team to migrate the code in other environment, analyzed and resolved open defects.

Environment: Informatica Power Center 8.6.1, PowerExchange, Siebel Analytics 7.7.1, Oracle, PL/SQL, SQL Server 2005,DB2,SAP R/3,SAP BW, Teradata V2R6, Erwin Data Modeler r7.3, AutoSys, Toad, RHE Linux 4.0, Korn Shell Scripting(KSH).

Hire Now