SUMMARY:

• 8 years of professional experience in Data Modeling/ Analysis for Online Transactional processing (OLTP) and Online Analytical processing (OLAP)/ Business Intelligence (BI) systems.
• Experience in all phases of Data Modeling - Conceptual, Logical and Physical modeling.
• Data Modeling experience using tools likes Erwin and MS Visio along with understanding of using Forward and Reverse engineering functionality.
• Exposure in overall Software Development Life Cycles including Define, Analysis, Design, Development and Testing to solidify client requirements in conjunction with Software Developers.
• Developed, documented and preserved logical and physical database models in agreement with enterprise standards and preserved corporate metadata definitions for enterprise Operational data Stores.
• Good Experience in projects related to all aspects such as Data Modeling, Design and Development, and Data Analysis for both OLTP and Data warehousing environments.
• Understanding of concepts like Star-Schema Modeling, Snowflake Schema Modeling, Fact and Dimension tables (Relational and Dimensional).
• Comprehensive familiarity and experience in normalization/de-normalization, data extraction
• Good knowledge using views, Indexes, Joins Subtype, Supertype in Data modeling.
• Created and maintained documentation for data models including detailed descriptions of business entities, attributes, and their relationships.
• Developed data mapping spreadsheets for (ETL) team with source to target data mapping with physical naming standards, datatypes, volumetric, domain definitions, and corporate meta-data definitions.
• Worked on Property and Casualty insurance company as an Underwriter. Strong understanding of rating in Property and Casualty.
• Experience in working with industry standard analysis techniques to uncover client requirements and identifying the roles and deliverables in the Organization by creating Use Case Models, Data Flow Models, Workflow Analysis, Functional decomposition Analysis, Object Modeling along with Activity Diagrams, Sequence Diagrams and State Diagrams using Rational Rose and Microsoft Visio. Experience to work in both, business and technical areas, to deliver optimal business process solutions.
• Test Procedures based on the Design Document and User Requirement Document for the Unit, Integration, Regression, Functional, and User Acceptance Testing (UAT).
• Expertise in SQL and PL/SQL programming -Functions, Stored Procedures and Triggers.
• Experience in Data Analysis using PL/SQL. Conducted meeting with System Analysts to work on Canonical schema design with System Analyst.
• Analyzed performance turning of SQL scripts to improve performance of the scripts.
• Highly skilled in System Analysis, ER/Dimensional Data Modeling, Database design and implementing RDBMS specific features.
• Worked extensively on Erwin and ER/Studio in several projects in both OLAP and OLTP applications.
• Performed Data Profiling to find out reusability and issues.
• Experience in extracting, transforming and loading ( ETL ) data from spreadsheets, database tables and other sources using Microsoft SSIS.
• Implemented Slowly Changing Dimensions in Dimension tables as per the requirements.
• Created Design documents for ETL developers to understand data transformations.
• Excellent communication and presentation skills.

TECHNICAL SKILLS:

Operating System: Windows XP pro, Windows 7

Data Analysis: Requirement Analysis, Business Analysis, Detail Design, Data Flow Diagrams, Business Rules, Data Modeling, System Integration and team building.

Database: MS Access, SQL Server 2008/2012/2014, DB2 z/OS 9.1, Teradata, Informix 9.x/10.x/11.x, MySQL 5.x, ODBC 3.x, Oracle 11g

Data Modeling Tools: ERwin 3.5.2/4.x/7.x/8/9.5, MS Visio, ER/Studio

Languages: SQL, Visual Basic, XML, PL/SQL, T-SQL

Applications: MS Office, Oracle, SQL Developer, SSMS

BI/ETL Tools: DataStage 8.0/7.5.2, SSIS, Informatica 7.1.3, Business Objects, Cognos, SSRS

PROFESSIONAL EXPERIENCE:

Confidential, Erie, PA

Sr. Data Modeler/Data Analyst

Responsibilities:

• Performed code-decode (reference data) analysis for the utilization in other projects. Modified reference database with new records.
• Created DDL scripts to create database or make modifications to it and reviewed with the Dev team and DBA.
• Assisted lead Data architect to gather data warehouse requirements from business users
• Reviewed requirements and designed data model for the Data warehouse to be developed.
• Analyzed database requirements in detail with the project stakeholders by conducting Joint Requirements Development sessions
• Developed a Conceptual model using MS Visio based on requirements analysis for Home, Auto, Boat, and PCL.
• Developed Logical and Physical database models to design OLTP system for applications (Dspro Web, Underwriting Review, Endorsements)
• Created dimensional model for the reporting system by identifying required dimensions and facts using Erwin.
• Worked with Database Administrators, Business Analysts and Developers to conduct design reviews and validate the developed models
• Used Erwin for reverse engineering to create graphical representation in the form of Entity Relationships.
• Developed, documented and preserved logical and physical database models in agreement with enterprise standards and preserved corporate metadata definitions for enterprise data stores.
• Maintained corporate metadata repository.
• Used Model Mart of Erwin for effective model management of sharing, dividing and reusing model information.
• Enforced referential integrity in the OLTP data model for consistent relationship between tables and efficient database design.
• Conducted design walk through sessions with Dev team to ensure that reporting requirements are fulfilled for the business.
• Developed SQL scripts and wrote stored procedures, triggers, and cursors.
• Performed data validation (of ETL processes, schema and DDL check, data flow from data warehouse to application databases, data marts, return marts, referential data, repositories).
• SQL Query performance tuning to identify and understand performance of the database tables.
• Worked on mapping business requirements to Business data model (IAA) and helped understand System Analyst to map it to do Canonical mapping (XML schema design).
• Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into data warehouse database.
• Analyzed Business Requirements, performed gap analysis and segregated them into high level and low level Use Cases, Activity and sequence diagrams (UML Diagrams).
• Involved in the meeting with Business Process Owners, SME (Subject Matter Experts) and store users for Requirement gathering in Definition Stage.
• Assigned tasks among development team, monitored and tracked progress of project following Agile methodology.
• Reverse engineered the reports and identified the Data Elements (in the source systems), Dimensions, Facts and Measures required for new enhancements of reports.
• Created action plans to track known open issues and action items related to the project.
• Designed Policy Hierarchy dimensions and Loss Hierarchy dimensions to handle reporting through SSRS (Sql Server Reporting Service).
• Developed the required data warehouse model (SQL Server) using Star schema for the data repository from source systems.
• Exhaustively collected business and technical metadata and maintained consistency applying naming standards.
• Developed Source to Target Data Mapping, Data Profiling, Transformation and Cleansing rules for OLTP and OLAP.

Environment: Erwin r7.3/r8/r9.3, MS Excel, MS Word, Agile, Rapid SQL, MS Visio, Sql server, Oracle, DB2, Sql server Management Studio

Data Modeler

Responsibilities:

• Exposure to ER/Studio for logical and physical data modeling, database design, star schema, snow flake schema design, data analysis, documentation, implementation and support.
• Gathered the various reporting requirements from the business analysts.
• Mapped the old requirements with the new requirements and communicated it with the business groups for the gap analysis.
• Reverse engineered the reports and identified the Data Elements (in the source systems), Dimensions, Facts and Measures required for new enhancements of reports.
• Designed a Star schema for the detailed data marts (Teradata) and Plan data marts involving conformed dimensions.
• Ensured the feasibility of the logical and physical design models.
• Created and maintained the Data Model repository as per company standards.
• Responsible for defining the naming standards for data warehouse.
• Reviewed Stored Procedures for reports and wrote test queries against the source system (SQL Server) to match the results with the actual report against the Data mart (Oracle).
• Helped with data modeling and defining the conceptual and logical model of the data warehouse (Teradata).
• Created action plans to track known open issues and action items related to the project.
• Developed and maintained data dictionary to create metadata reports for technical and business purpose.
• Performed Data Profiling to understand irregularity and issues, Data mapping, Transformation from Source to Target Database..
• Worked on the Snow-flaking the Dimensions to remove redundancy using ER/Studio.
• Worked closely with the ETL SSIS Developers to explain the complex Data Transformation logic.
• Created ETL Jobs and Custom Transfer Components to move data from Oracle Source Systems to SQL Server using SSIS.

Environment: MS Excel, MS Word, MS Access, Oracle, Quality Center, Rapid SQ, ER/Studio, Teradata.

Business Analyst/Data Modeler

Responsibilities:

• Worked at conceptual/logical/physical data model level using Erwin according to requirements.
• Mapped the old requirements with the new requirements and communicated it with the business groups for the gap analysis.
• Involved in requirement gathering and database design and implementation of star-schema, dimensional data warehouse (Teradata) using Erwin.
• Assessed data from various sources and generated conceptual and logical database designs (Oracle) using Erwin data modeling tool.
• Developed and maintained data dictionary to create metadata reports for technical and business purpose.
• Responsible for gathering the requirements from a complex set of stakeholders and coordinating them in PMI
• Worked on requirements, analysis and design, project management, testing (functional and user acceptance tests)
• Identify timelines for the different kinds of testing to be performed and estimate the risks involved.
• Performed test data analysis and ensure data requirements are met prior to start of various testing phases.
• Responsible for scheduling meetings with users, SME’s and stakeholders to identify problems resolve issues and improve the process to ensure a stable and accurate solution.
• Created test plans to detail the scope and level of testing that needs to be performed and documented them on PMI share point.
• Assisted the lead data architect in data modeling, data structure and data table layouts required for consolidation of dissimilar databases.
• Analyzed Business Requirements, performed gap analysis and segregated them into high level and low level Use Cases, Activity and sequence diagrams (UML Diagrams).
• Responsible for writing Functional Requirement Specifications (FRS) and point of contact for System Requirement Specification (SRS).

Environment: MS Excel, MS Word, MS Access, Oracle, Quality Center, Rapid SQL, Erwin, SQL server