Sr. Data Modeler/data Analyst Resume
Reston, VA
SUMMARY
- Over 9+ years of working experienced as a Data Modeler and Data Analyst with high proficiency in Requirement Gathering and Data Modeling including design and support of various applications in Online Transactional processing (OLTP) and Online Analytical Processing (OLAP), Data Warehousing concepts and ETL Environment and Hadoop environment.
- Experienced in Developing Conceptual, logical models and Physical Database Design for OLTP and OLAP systems using ER Studio and Erwin.
- Experienced Hadoopecosystem components like Hadoop, MapReduce, HDFS, Sqoop, Pig, and Hive.
- Experienced in Star Schema and Snowflake Schema, FACT & Dimensions, Additive and Semi Additive Facts, Base Level Fact, Aggregate Fact, Fact Less Fact, Grain, Data Sparsity, Degenerate Dimensions, Junk Dimensions, Conformed Dimensions, Hierarchies of Dimensions and Rapid Changing Dimensions.
- Excellent experienced in Delivering Conceptual, Logical and Physical Data Models for Multiple projects involving various Enterprise New and Existing Applications and Data Warehouse.
- Experienced with Normalization (1NF, 2NF, 3NF,BCNF), Super Types, Sub types, Discriminators, Domain, Referential Integrity Rules and Denormalization Techniques for improved Database Performance in OLTP and Data Warehouse/Data Mart Environments.
- Experienced in understanding & working knowledge of CDC (Change Data Capture), SCD (I, II, III) and Hybrid SCD.
- Excellent experienced in system Analysis, Relational and Dimensional Modeling and Implementing RDBMS specific features.
- Experienced in Identifying Entities, Attributes, Metrics, and Relationships also assigning keys and optimizing the Model.
- Experience in using SAS to read, write, import and export to another data file formats, including delimited files, Microsoft Excel, PDF.
- Excellent experienced in Developing Transactional Enterprise Data Models that strictly meet Normalization Rules, as well as Enterprise Data Warehouses using Kimball and Inmon Data Warehouse Methodologies.
- Experienced in SQL programming Skills and Developed Stored Procedures, Triggers, Functions, Packages, Views and Materialized views using SQL/PL SQL. Performance Tuning and query Optimization Techniques in Transactional and Data Warehouse Environments.
- Experienced in Data Transformation and Data Mapping from source to target Database Schemas and Also Data Cleansing.
- Experienced in using SSRS and Congo’s to create and manage reports.
- Experienced understanding and exposure to SQL queries and PL/SQL stored procedures, triggers, functions and packages.
TECHNICAL SKILLS
ETL: Informatica Power Centre 9.x/8.x, SSIS.
Data Modeling and Data Analyst: Erwin r9.6, r9.5, 9.x, 8.x, ER Studio, Power Designer, SAS, SPSS.
Languages: R, Python, SAS, XML, SQL, PL/SQL, T - SQL
Databases: Oracle, DB2, SQL Server 2012/2008, Teradata, Netezza.
Job scheduling: &other tools TFS 2015, TOAD, SQL Developer, Quality Center, Lync, Spark and WinScp.
Environment: UNIX, Windows, Linux
Methodologies and Others: SDLC Agile, Waterfall, V-model, STLC, PL/SQL, SQL, UNIX Commands.
Big Data Tools: Hadoop, Hive, Pig, Map Reduce, HBase
PROFESSIONAL EXPERIENCE
Confidential, Reston, VA
Sr. Data Modeler/Data Analyst
Responsibilities:
- Gather and refine business requirements and manage and coordinate data analysis and data modeling projects across business units partnering with business and IT support managers to identify and refine business requirements for data modeling support in light of general business development objectives.
- Defined operational data modeling requirements, validation of content, and problem solving parameters.
- Design and develop reports and formal presentation slides to monitor business critical results, define and evaluate problems, and recommend solutions for business improvement.
- Involved in requirement analysis, pig and hive script development, writing and implementing Ad-hoc queries in Hive and Pig forDataAnalytics.
- Developed, documented and preserved logical and physical database models in agreement with enterprise standards and preserved corporate metadata definitions for enterprisedatastores.
- Identified and Analysis of various facts from the source system (OLTP) and Business Requirements to be used for the Data Warehouse.
- Responsible for BigDatasolution development using PIG and Hive as per PDM design.
- Project data from various online transaction processing (OLTP) applications and other sources is selectivity extracted, related, transformed and loaded intoTeradataWarehouse using BTEQ, MULTILOAD, FASTLOAD and FAST EXPORT.
- Designed Star Schema, Hierarchies and Snow Flake Schemas for reporting as per BRD.
- Analyzed and Defined Primary keys, Surrogate keys, Dimensions and Fact According to Business Requirements and created document for Views and Mapping Sheets to Map Source columns from source to target.
- Involved in Loading/Migration of Ad-hocdatainto Hive from Teradata database and done DataAnalysis using Hive on POC
- Generated Data Definition Language (DDL) scripts using the Data Modeling Tool and manually for the creation of Physical objects like Tables, indexes, constraints, Views and Materialized Views.
- Reviewed the code written in Hive, Pig or Python and prepared a new Hive Script to run with the mockdata& test the output; Raised defects ifdatadoesn't match with the output from the model.
- Used Data Governance for working on Data Quality and Data Integration in SOA environment.
- Developed Source to TargetDataMapping,DataProfiling, Transformation and Cleansing rules for OLTP and OLAP.
- Reviewed Change Management Requests for DDL to implement changes and worked on Enhancing and Monitoring SQL Queries using SQL Server management S.
- Involved in Datamigration from Teradata intoHadoopusing sqoop and flume.
- Developed ETL mappings, transformations using Informatica Power centre. Implemented source and target definitions in Power centre Designer
- Designed Policy Hierarchy dimensions and Loss Hierarchy dimensions to handle reporting through SSRS (SQL Server Reporting Service)
Environment: Erwin 9.6/ 9.5, Hadoop, Hive, Pig, Map Reduce, SQL, Teradata SQL Assistant, Netezza, XML, PL/SQL, Python, Informatica, SAS, SSIS, SSRS, Share point, Oracle 12c, ODI, OWB, PL/SQL,, SQL Server, Microsoft SQL Server Management Studio, Microsoft outlook,, Microsoft Excel.
Confidential, Bellevue, WA
Sr. Data Modeler/Data Analyst
Responsibilities:
- Participated in Requirement Gathering session with Business Users and Sponsors to understand and document the Business Requirements as well as the Goals of the project.
- Interacted with users, Business Analysts and database administrators in the process of updating thedatamodels in 3NF and HTML reports.
- Analyzed Logical Data Model (LDM) and Physical Data Model from OLTP systems included documentation of all Entities, Attributes, Data Relationships, Primary and Foreign key Structures, Allowed Values, Codes, Business Rules, Glossary Terms, etc.
- Identified and Analysis of Various Facts from the Source System and Business Requirements to be used for the Data Warehouse (Kimball Approach).
- Exhaustively collected business and technical metadata and maintained consistency applying naming standards.
- Checked for all the Modeling Standards including naming standards, entity relationships on Model and for Comments and History in the Model.
- Used Map Reduce &done dataprocessing by Map Reduce Job.
- Developed the logicaldatamodels and physicaldatamodels that capture current state/future state dataelements anddataflows using ER Studio.
- Developed complex ETL code in the project using Informatica with Teradata Loaders (MLoad, Tpump and Fast Load)
- Parse structureddata(Tables & files), semi structuredData(XML) & unstructureddata (Server Logs) using Pig & Hive.
- Analyzed and Defined Primary keys, Surrogate keys, Dimensions and Fact According to Business Requirements.
- Reviewed the existing Informatica jobs and prepared schema documents for the tables in Oracle which had to be pushed to theHadoopFile System
- Developed mappings using various transformations like update strategy, lookup, stored procedure, Router, Filter, sequence generator, joiner, aggregate transformation and expression.
- Written scripts for loading data to target data warehouse using MLoad, FLoad and BTEQ scripts.
- Worked with establishing and enforcing SOA Data Governance mechanisms in large scale SOA environments.
- Developed Logical data model using ER/Studio and created Physical Data Models using Reverse Engineering.
- Extensively usedSASMacro options such as MPRINT, MLOGIC and SYMBOLGEN to test and debug existing macro programs.
- Updated and deleted column definitions as per Requirement Document According to Referential Integrity rules.
- Built a highly successful multilevel tree model for forecasting at any level and aggregation at any level in SQL environment and ported to Oracle Environment.
- Created DDL scripts using ER studio and Co-coordinated with DBA's to apply the Data Model changes.
Environment: ER/Studio, Hadoop, Hive, Pig, Map Reduce, SQL, Teradata SQL Assistant, Netezza, XML, PL/SQL, Python, Informatica, SAS, SSIS, SSRS, Share point, Oracle 12c, ODI, OWB, PL/SQL,, SQL Server, Microsoft SQL Server Management Studio, Microsoft outlook,, Microsoft Excel.
Confidential, Indianapolis, IN
Sr. Data Modeler/Data Analyst
Responsibilities:
- Interacted with Business users to analyze the Business Process and Requirements and Transforming Requirements into Conceptual, logical and Physical Data Models, Designing Database, Documenting and Rolling out the Deliverables.
- Coordinated Data Profiling/Data Mapping with business subject Matter Experts, Data Stewards, Data Architects, ETL Developers, and Data Modelers.
- Improved Reporting/Ad-hoc query Performance by Suggesting indexes &Aggregations.
- Created and Maintained Physical & Business Model in ERwin.
- Worked on General Maintenance work to maintain and improve the already Existing Models and related a list of domains in ERwin and worked on building up the Data Dictionary for the company.
- Synched up the Models by reverse engineering, Compare Model and Merge Model from Database to the Original Models.
- Worked with Teradata utilities like BTEQ, Fast Export, Fast Load, Multi Load to export and loaddatato/from different source systems including flat files.
- Used reverse engineering for a wide variety of relational DBMS, including MS Access, Oracle, Netezza and Teradata to connect to existing database and create graphical representation using Erwin.
- Identified and tracked the slowly changing dimensions (SCD I, II, III & Hybrid/6) and Determined the Hierarchies in Dimensions.
- Worked on importing data from clients and processed the data usingSAS. Ensuring that finalSAS output is in the correct directory.
- Wrote database triggers in T-SQL to check the referential integrity of the databases.
- Create and maintained Metadata including table, column definitions.
- Created DDL scripts for implementing data modeling changes. Created ERwin reports in HTML, RTF format depending upon the Requirement, published Data Model in Model Mart, created naming convention files, Co-coordinated with DBA to apply the Data Model changes.
- Used Model Mart of ERwin for Effective Model Management of sharing, dividing and reusing Model Information and Design for productivity improvement.
- Created tables, views, sequences, indexes, constraints and generated SQL scripts for implementing physicaldatamodel.
- Assisted the ETL Developers and Testers During the Development and Testing Phases.
- DevelopedSASPrograms based on wide usage of BaseSAS,SAS/Macro facility to generate tables, listings, reports.
Environment: Erwin, SQL, Teradata SQL Assistant, Netezza, XML, PL/SQL, Python, Informatica, SAS, SSIS, SSRS, Share point, Oracle 12c, ODI, OWB, PL/SQL,, SQL Server, Microsoft SQL Server Management Studio, Microsoft outlook,, Microsoft Excel.
Confidential, Anaheim, CA
Sr.Data Analyst/Data Modeler
Responsibilities:
- Analyzed the data models of the source systems (OLTP) to develop Data Models for OLAP systems for BI reporting.
- Performed Reverse Engineering of the legacy application using DDL scripts in Erwin, and developed Logical and Physicaldatamodels for Central Model consolidation.
- Normalized the Tables/Relationships to attain at optimum and efficient Relational Schemas.
- Worked with DBAs to create a best fit PhysicalDataModel from the LogicalDataModel using Erwin.
- Designed Snowflake Schemas by normalizing the dimension tables as per the Requirement.
- Migrated Data from various sources into main Database and performed necessary conversions. Created and Modified stored procedures, Functions and Views.
- Worked with Data Governance team and Data Compliance teams to maintain the Data Models, Metadata and Data Dictionaries to define source fields and its definitions.
- Optimized performance using Data Validation and Data cleansing and used statistical procedures like PROC FREQ, PROC MEANS, and PROC UNIVARIATE
- Created base tables, views, and index. Built a complex Oracle procedure in PL/SQL for extract, loading, transforming thedatainto the warehouse via DBMS Scheduler from the internaldata. Helped in designing views to pivot and aggregate thedatato create meaningful reports for business users.
- Worked on Teradata SQL query optimization techniques like: EXPLAIN feature, COLLECT, STATISTICS option, Secondary Indexes (USI, NUSI), Partition Primary Index (PPI), Volatile and global temporary tables etc.
- Analyzed data usingSASstatistical procedures such as Proc Means, procFreq, Proc Format, Proc Report, Proc SQL and Proc Tabulate..
- Involved in developing Metadata Repository, Data Dictionary documentation using interface diagrams and flow charts.
- Used ETL (SSIS) to develop jobs for extracting, transforming and loading data into data warehouse from various sources like Netezza, Oracle, Flat File and Excel.
- Created Databases for OLAP Metadata catalog tables using forward engineering of Models in Erwin.
- Developed system Flow andDataFlow diagrams for the proposed systems. Involved in the Critical Design review of the Finalized Database Model.
- Helped in Migration and Conversion ofDatafrom the TERADATA Database into Oracle Database.
- Designed Mappings using Informatica coming from different sources like Databases, Flat Files into Oracle staging area.
Environment: Erwin, SQL, Teradata SQL Assistant, Netezza, XML, PL/SQL, Python, Informatica, SAS, SSIS, SSRS, Share point, Oracle 12c, ODI, OWB, PL/SQL,, SQL Server, Microsoft SQL Server Management Studio, Microsoft outlook,, Microsoft Excel.
Confidential, NYC, NY
Data Analyst
Responsibilities:
- Conducted Logical Data Analysis and Data Modeling JAD sessions, communicated Data-Related standards.
- Used Model Mart of ERwin for Effective Model Management of sharing, dividing and reusing Model Information and Design for productivity improvement.
- Created Data Mapping documents from source to target systems and performed Data Profiling in the source systems.
- Performed User Acceptance Testing (UAT), Unit Testing and Documenting.
- Redesigned some of the subject areas and introduced some new Entities and Attributes as per the Requirements and Also Supported Database Modifications.
- Designed different type of Star Schemas for detailed Data Marts and Planned Data Marts in the OLAP environment.
- Written SQL Queries, Triggers, and PL/SQL Procedures to apply and maintain the Business Rules.
- Implemented mapping techniques for Type 1, Type 2 and Type 3 slowly changing dimensions.
- Utilized T-SQL extensively in order to replaceSSIStransformations in order to improve the performance of the packages
- Worked on the UNIX scripts for running the workflows and threshold check for the incoming files.
- Redefined many Attributes and Relationships in the Model and cleansed unwanted Tables/Columns as part of Data Analysis Responsibilities.
- Identified and processed the Facts and Dimension Tables.
- Created datasets in T-SQL, stored procedures forSASReports
- Worked with report Developers for building OLAP CUBEs for ad hoc reporting.
- Assisted end users, operations and testing teams by providing them with the Necessary Documents.
Environment: Erwin, SQL, PL/SQL, SAS, SPSS, SSIS, SSRS, Informatica, Oracle, Teradata, Netezza, DB2, T-SQL.