- Over 8+ years of IT experience in Data Architecture, Data Modeling, Designing and Data Analysis with Conceptual, Logical and Physical Modeling for Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) and Data Warehousing.
- Experienced in setting up connections to different Databases like Oracle, SQL Server, DB2, NoSQL databases, Teradata and NETEZZA according to user’s requirement.
- Extensive experience in designing the Data models for OLTP & OLAP database system.
- Gathering and translating business requirements into technical designs and development of the physical aspects of a specified design.
- Expert knowledge in SDLC (Software Development Life Cycle) and good experience in the field of business analysis, reviewing, analyzing, and evaluating business systems and user needs, business modeling, document processing.
- Expertise using of ERWIN, Toad, Embarcadero ER/Studio & Power designer for developing Conceptual, Logical & Physical models (ER Models).
- Possess strong Conceptual and Logical Data Modeling skills, has experience with JAD sessions for requirements gathering, creating data mapping documents, writing functional specifications.
- Experience in Ralph Kimball, Data Vault and Bill Inmon approaches.
- Experienced in SQL, PL/SQL package, function, stored procedure, triggers, and materialized view, to implement business logics of oracle database.
- Performed Data cleansing and Data profiling for detecting and correcting inaccurate data from the databases and to track data quality and to assess the risk involved in integrating data for new applications.
- Experienced in writing and optimizing Queries with all major databases like Oracle, Exadata, SQL Server, Teradata in large data warehouse (OLAP) environments
- Experienced in Teradata RDBMS using Fast load, Fast Export, Multi load, T pump, and Teradata SQL Assistance and BTEQ Teradata utilities.
- Experience with various Online Analytical Processing tools (OLAP) like designing Data Marts and Data Warehouse using Star Schema and Snowflake Schema in implementing Decision Support Systems, fact and dimension tables modeling of data at all the three levels: view, logical and physical.
- Extensive experience on usage of ETL & Reporting tools like SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS)
- Experienced with SQL Server and T - SQL in constructing Temporary Tables, Table variables, Triggers, user functions, views, Stored Procedures.
- Worked on ETL tools like Informatica, Ab Initio and Data Stage; Created source to target mapping documents for the ETL developers and helped them understand complex transformations involving business input.
- ETL and data integration experience in developing ETL mappings and scripts using Informatica Power Center 9.x/8.x/7.x/6.x and Power Mart 6.x.
- Expertise and well versed with various Ab Initio Transform, Partition, Departition, Dataset and Database components.
- Created, documented and maintained logical and physical database models in compliance with enterprise standards and maintained corporate metadata definitions for enterprise data stores within a metadata repository.
- Assisted in the oversight for compliance to the Enterprise Data Standards.
- Experience working on Informatica MDM to design, develop, test and review & optimize Informatica MDM
- Extensive experience in Normalization (1NF, 2NF, 3NF and BCNF) and De-normalization techniques for improved database performance in OLTP and Data Warehouse/ Data Mart environments.
- Proficient in Normalization (1NF, 2NF and 3NF) and De-normalization techniques for improved database performance in OLTP, OLAP, Data Warehouse and Data Mart environments.
- Experience in the Oracle development tool set Including PL/SQL, SQL*Plus, SQL Loader.
- Knowledge of Business Intelligence tools like Tableau and QlikView for visualization of data and ability to perform quick analysis.
- Extensive experience in creating UI visualizations with Tableau Desktop, Tableau Server and Tableau Reader in various versions of Tableau9.x/8.x/7.x/6.x
- Highly skilled in Tableau Desktop for data visualization, Reporting and Analysis, Cross Map, Scatter Plots, Geographic Map, Pie Charts and Bar Charts, Page Trails and Density Chart.
- Proficient at creating Reports, Pivot Tables, VLOOKUP, Hook-up, Index, Graphs, and Match in MS Excel; Proficient at utilizing MS PowerPoint, MS Access, and MS Word.
- Enthusiastic and project-oriented team player with solid communication and leadership skills and the ability to develop different solutions for challenging client needs.
Data Modeling Tools: Erwin, Visio, IBM Infosphere, Oracle Designer, Power Designer and ER Studio, Toad
Database: Oracle 7/8/9i/11g/10g, SQL Server, Teradata, MS Access
ETL: Informatica 9.1/7.1.3/7.1.1 , DataStage, SSIS
Operating Systems: Windows 9x/NT/XP/2003/VISTA/7/8, Linux, Unix
Business Applications: MS Word, MS Excel, MS PowerPoint, Visio
Reporting Tools: Tableau, Cognos 8/10, Microstrategy, QlikView, SSAS
Programming Languages: C, C++, Java, Python
Other Tools: JIRA, SQL Navigator, Teradata SQL Assistant
Confidential, Cincinnati, OH
Sr. Data Modeler
- Gathered and translated business requirements into detailed, production-level technical specifications, new features, and enhancements to existing technical business functionality.
- Created OLTP, ODS and OLAP architectures
- Responsible for the analysis of business requirements and design implementation of the business solution.
- Developed Conceptual, Logical and Physical data models for central model consolidation.
- Worked with DBAs to create a best fit physical data model from the logical data model.
- Conducted data modeling JAD sessions and communicated data-related standards.
- Used Erwin for effective model management of sharing, dividing and reusing model information and design for productivity improvement.
- Redefined many attributes and relationships in the reverse engineered model and cleansed unwanted tables/ columns as part of data analysis responsibilities.
- Designed logical and physical data models, Reverse engineering, Complete compare for Oracle and SQL server objects using Erwin.
- Construct complex SQL queries with sub-queries, inline views as per the functional needs in the Business Requirements Document (BRD).
- Customizing given default ODS packages from Ellucian in framework manager and publishing to Cognos connection for reporting purpose.
- Involved in designing and developing SQL server objects such as Tables, Views, Indexes (Clustered and Non-Clustered), Stored Procedures and Functions in Transact-SQL.
- Develop new processes using PRO*C, PL-SQL and Unix Shell Script as per business needs.
- Handled code defects, enhancements and release management using tools like Rational Clear case and Rational Clear quest
- Performed performance tuning for faster retrieval of data using various optimizer hints such as Parallel, Ordered, Append and Use Hash.
- Involved in creating reports using Universe, Stored Procedures and Excel files as Data Sources.
- Extensively worked on Data Warehousing Design Methodology, Data Warehouse Concepts.
- Interacted with customers regularly to generate the reports required and to resolve the existing issues.
- Developed process methodology for the Reverse Engineering phase of the project.
- Involved using ETL tool Informatica to populate the database, data transformation from the old database to the new database using Oracle.
- Involved in MDM (Master Data Management)
- Worked on data cleansing using the cleanse functions in Informatica MDM.
- Involved in the creation, maintenance of Data Warehouse and repositories containing Metadata.
- Modelled new data models based on user requirements and updating existing data model. Created new metadata and updating existing metadata.
- Involved in the critical design review of the finalized database model.
- Involved in the study of the business logic and understanding the physical system and the terms and condition for database.
- Created documentation and test cases, worked with users for new module enhancements and testing.
Environment: Erwin, Toad, Cognos, Informatica, Teradata, Oracle 11g, PL/SQL, SQL Server, MS Excel, MS Visio.
Confidential, Cleveland, OH
Sr. Data Modeler / Data Analyst
- Involved in requirement gathering and data analysis and Interacted with Business users to understand the reporting requirements, analyzing BI needs for user community.
- Involved in logical and physical designs and transforming logical models into physical implementations.
- Normalized the data up to 3rd Normal form.
- Created Entity/Relationship Diagrams, grouped and created the tables, validated the data, identified PKs for lookup tables.
- Worked on Business as usual (BAU) Operating Model defined and agreed in the Blueprinting phase
- Involved in modeling (Star Schema methodologies) in building and designing the logical data model into Dimensional Models.
- Documented the source to target mappings for both data integration as well as web services.
- Implemented OLTP databases and Data Marts with Oracle RAC and Exadata
- Experience working with MDM team with various business operations involved in the organization
- Worked on different data models for Claims, Members and Providers for different claim types, for different Health Partner Incentive programs.
- Utilized Erwin’s forward/reverse engineering tools and target database schema conversion process.
- Designed the data marts in dimensional data modeling using star and snowflake schemas.
- Redefined attributes and relationships in the reverse engineered model and cleansed unwanted tables/columns as part of data analysis responsibilities.
- Identify the PK, FK relationships across the entities and across subject areas.
- Responsible for managing development with regards to BI reporting
- Support Data Quality Managers to develop tools for data governance and master data management
- Developed ETL routines using SSIS packages, to plan an effective package development process, and design the control flow within the packages.
- Extensively worked with SCD Type-I, Type-II and Type-III dimensions and data warehousing Change Data Capture (CDC).
- Took active role in the design, architecture and development of user interface objects in QlikView applications. Connected to various data sources like SQL Server, Oracle and flat files.
- Presented the Dashboard to Business users and cross functional teams, define KPIs (Key Performance Indicators), and identify data sources.
- Worked on MongoDB database concepts such as locking, transactions, indexes, Sharding, replication, schema design.
- Extensively worked with Set Analysis. Expert in writing complex set analysis expressions in QlikView applications. Created and Scheduled Weekly QlikView reports to distribute on email using NPrinting and publisher.
- Created and maintained logical, dimensional data models for different Claim types.
- Designed data flows that extract, transform, and load data by optimizing SSIS performance
- Worked with slowly changing dimensions (SCDs) in implementing custom SCD transformations.
- Involved in loading the data from Source Tables to Operational Data Source tables using Transformation and Cleansing Logic
- Worked on all data management activities on the project data sources, data migration.
- Worked on creating DDL, DML scripts for the data models.
- Worked on stored procedures for processing business logic in the database.
- Performance query tuning to improve the performance along with index maintenance.
- Worked on the reporting requirements for the data warehouse.
- Created support documentation and worked closely with production support and testing team.
Environment: Erwin 9.5/8.2, Oracle 11g, Crystal Reports, Toad, MongoDB, Windows OS, DB2, Python, Teradata, SSRS, SQL, QlikView 11.6, Informatica 11.
Confidential, Kansas City, KS
Sr. Data Modeler / Data Analyst
- Conducting the meetings with business users to gather data warehouse requirements.
- Worked on managing Source Control System on GIT.
- Worked on designing the whole data warehouse architecture from scratch, from ODS to data marts
- Conducted team meetings and Joint Application Design (JAD) session
- Gathered requirements and created use case diagrams as part of requirements analysis.
- Developed 3NFnormalized Logical and Physical data base models to design OLTP system
- Developed Staging jobs where in using data from different sources like flat files, excel files, Oracle database
- Created dimensional model for the reporting system by identifying required dimensions and facts using ER Studio 9.5.
- Produced functional decomposition diagrams and defined logical data model.
- Implemented Referential Integrity using primary key and foreign key relationships in Teradata.
- Ensure data warehouse and data mart designs efficiently support BI and end user
- Collaborate with BI teams to create reporting data structures.
- Responsible for cleansing the data from source systems using Ab Initio components such as Join, Dedup Sorted, Denormalize, Normalize, Reformat, Filter-by-Expression, Rollup
- Worked with the Business Analyst, QA team in their testing and DBA for deployment of databases in the correct Teradata Environment, business analysis, testing and project coordination.
- Generated ad-hoc SQL queries using joins, database connections and transformation rules to fetch data from legacy Oracle and SQL Server database systems.
- Writing complex SQL queries to analyze source data and communicating data quality issues to the business.
- Translated business concepts into XML vocabularies by designing XML Schemas with UML
- Exhaustively collected business and technical Metadata and maintained naming standards
- Good Knowledge on using ER Studio for reverse engineering to connect to existing database to create graphical representation in the form of Entity Relationships and elicit more information.
- Created physical data models for Teradata for data model deployment.
- Identified and tracked the slowly changing dimensions and determined the hierarchies in dimensions.
- Worked with ETL teams to create source and target definition, design mappings, create repositories and establish users, groups and their privileges, using Informatica Designer, Workflow and Repository Manager.
- Enhance process for capturing and maintaining metadata from all data warehousing components
- Used Teradata for OLTP systems by generating models to support Revenue Management Applications that connect to SAS.
- Extensively involved in Ab Initio Graph Design, development and Performance tuning.
- Created the data model for the Subject Area in the Enterprise data Warehouse (EDW).
- Consulted with client management and staff to identify and document business needs and objectives, current operational procedures for creating the logical data model.
- Worked on P&C Insurance background
- Participated in performance management and tuning for stored procedures, tables and database servers.
- Worked in Agile Environment using tolls like JIRA and Version One.
- Facilitated in developing testing procedures, test cases and User Acceptance Testing (UAT).
- Responsible in integrated the work tasks with relevant teams for smooth transition from testing to implementation phase.
Environment: ER Studio 9.5, SQL Server 2014, Teradata 14.0, Oracle 11g, Informatica, SAS, JIRA, Version One, Tableau, Ab Initio
Confidential, ATLANTA, GA
Data Modeler/ Data Analyst
- Worked with Business users for requirements gathering, business analysis and project coordination.
- Developed a Conceptual Model and Logical Model using Erwin based on requirements analysis.
- Created various Physical Data Models based on discussions with DBAs and ETL developers.
- Worked on data mapping process from source system to target system.
- Created dimensional model for the reporting system by identifying required facts and dimensions using Erwin.
- Extensively used Star and Snowflake Schema methodologies.
- Developed and maintained data Dictionary to create Metadata Reports for technical and business purpose.
- Worked on Performance Tuning of the database which includes indexes, optimizing SQL Statements.
- Used Model Mart of Erwin for effective model management of sharing, dividing and reusing model information and design for productivity improvement.
- Investigated data quality issues by data profiling on the source system data and regional data warehouse data.
- Implemented Forward engineering to create tables, views and SQL scripts and mapping documents.
- Prepared data dictionaries and Source-Target Mapping documents to ease the ETL process and user's understanding of the data warehouse objects
- Translated business concepts into XML vocabularies by designing XML Schemas with UML.
- Used Normalization (1NF, 2NF & 3NF) and De-normalization techniques for effective performance in OLTP and OLAP systems.
- Good understanding and experience with the entire data Migration process from analyzing the existing data, cleansing, validating, translating tables, converting and subsequent upload into new platform.
- Created documentation and test cases, worked with users for new module enhancements and testing.
- Worked with business analyst to design weekly reports using combination of Crystal Reports.
- Understood existing data model and documented suspected design affecting the performance of the system.
- Extracted data from databases like Oracle, SQL server and DB2 using Informatica to load it into a single repository for data analysis.
- Involved in development and implementation of SSIS, SSRS and SSAS application solutions for various business units across the organization.
- Experienced in migration and cleansing rules for the integrated architecture (OLTP, ODS, DW)
- Wrote DDL and DML statements for creating, altering tables and converting characters into numeric values.
Environment: Oracle SQL Developer, Oracle Data Modeler, SSIS, Business Objects, Oracle12c, SQL server 2012, SQL Assistant 13.11, data stage 8.1, DB2, Informatica Power Center 9.5, PL/SQL.
- Design databases (schemas) to ensure that the relationship between data is guided by tightly bound Key constraints.
- Involved in Business Requirements, System analysis and Design of the Data warehouse application.
- Data Analysis Primarily Identifying Data Sets, Source Data, Source Meta Data, Data Definitions and Data Formats.
- Designed Physical and Logical Data model and Data flow diagrams.
- Created database objects like Tables, Views, Stored Procedures, Functions, Packages, DB triggers, Indexes.
- Worked on various tables to create Indexes to improve query performance. Also worked on partitioning large Tables using Range Partitioning, creating Index Organized Table, Rollback Table spaces.
- Wrote several Queries and Procedures.
- Built database objects like Tables and Views.
- Database Design (Relational and Dimensional models) using Erwin.
- Defined both logical views and physical data structure using Erwin.
- Converted all Oracle ETL Packages to Informatica Mappings and created workflows/Sessions.
- Developed Informatica Mappings using various Transformations and PL/SQL Packages to extract, transformation and loading of data.
- Filtered and Loaded data from different formats of data sources into Database Tables.
- Extracted required data from the database tables and exported the data to different sources in different formats.
- Worked with several tools to access and perform several operations on database.
- Experience in Generating Reports.
- Developed Data entry, query and reports request screens and tuned the SQL queries.
- Used joins, indexes effectively in where clauses for Query optimization.
- Assisted in gathering requirements by performing system analysis of the requirements with the technology teams.
Environment: Toad, SQL*Plus, SQL*Loader, Oracle 10g, MS SQL Server, Informatica 8.5, Erwin, Windows XP, UNIX.