Abhijit Kapare

Ph.: 713-***-****

Email: ac82r7@r.postjobfree.com

Data Modeler

SUMMARY:

6+ years IT experience with expertise in data modeling for transactional, data warehouse/ data mart development, SQL, data analysis for online transaction processing (OLTP) and data warehousing (OLAP) and business intelligence (BI) applications and data science.

●Data modeler with strong conceptual, logical and physical data modeling skills and has experience with requirements gathering, creating data mapping documents, queries.

●Different domain experience like banking, insurance, staffing, financial, etc. as a data modeler.

●Worked on various databases including Big Data (Hive/Impala – Cloudera/HortonWorks), Teradata, Oracle, DB2, IMS, SQL Server, etc.

●Worked extensively on reverse and forward engineering processes.

●Extensive experience in relational and dimensional data modeling for creating logical and physical design of the database and ER diagrams using data modeling tools like ERWIN and ER studio and PowerDesigner tools

●Worked on Teradata banking data warehouse industrial model and ARTS (Association for Retail Technology Standards) from Teradata.

●Implemented slowly changing dimensions (SCD) type I and type II in dimensional modeling as per the requirements.

●Strong understanding of principles of normalization, data warehousing concepts, fact tables, dimension tables, star and snowflake schema modeling.

●Experienced in writing SQL queries.

Education:

Masters from University of Delaware, DE, USA, Dept. of Computer Information Science, College of Engineering

[Started] Masters from Harrisburg University, PA, USA in Information Systems Engineering Management (ISEM)

Bachelor of Technology from Dr. D. Y. Patil University, Navi Mumbai, India

Technical Skills:

Data modeling tools: ERWIN 7.x 8.x, 9.5.2, ER Studio, Power Designer

Reporting tools: Oracle Reports, Business Objects, and Crystal Reports, SSRS

ETL tools: Informatica Powercenter 8x, 9x, SSRS, Microstrategy, Datastage

Databases: Oracle 10g/11g, Teradata, DB2, MS access, SQL server, Big Data (Hive, Impala, etc.)

Operating systems: Linux, UNIX, Windows

Others: MS Office, Toad, and SQL Developer

Work Experience:

Nationwide Insurance, Columbus, OH (Accenture) March 2018 – Jan 2019

Data modeler

An insurance and a financial company based in Columbus, OH. 53rd Fortune’s ‘best company to work for’. Multiple projects going on on the financial domains related to life insurance. Heavy lifting of data is required with the proper analysis, coding and performance testing. Project involves conducting the research and analysis on the data and providing the top notch solution for the business efficiency.

ROLES AND RESPONSIBILITIES

●Working on enterprise level modules for the insurance client to perform the data modeling and data architectural related tasks and providing solutions for the data migration on various platforms from hierarchical to relational databases to unstructured databases

●Leading efforts of physical, logical and conceptual data modeling for the team; OLAP for data warehouse and DataMart developments using Ralph Kimball methodology as well as OLTP models and interacting with all the involved stakeholders and SME’s to derive the solution; facts and dimensions for the dimensional modeling data architecture as well as relational data modeling for the transactional systems like in-house applications and policy, claims applications, etc.

●Policy and claims applications to model the tables according to the 3NF modeling and working on resolving many to many relationships, bridge tables, reference tables, master data, etc.; lead the modeling efforts on multiple enterprise level projects; Data strategy for data governance to understand the data for strategies such as master data management, BI (Business Intelligence) or other data consumption platforms

●Data analysis on policies, insurance holders, agreements, claims, transactions, etc. subject area connections and providing solutions to resolve the gaps in the dataflow or leading the discussions with the associated stakeholders and SME’s to resolve the gaps.

●Policy and claims applications to model the tables according to the 3NF modeling and working on resolving many to many relationships, bridge tables, reference tables, master data, etc.Working on Rubymine for the automation flow of testing and Informatica PowerCenter for the data migration and running the workflows.

●Data modeling using IBM Data Architect for the team to understand the data relations and characteristics and to give a blue print of the data structure using the logical and physical data models.

●Leading efforts on testing the automation/manual test queries on Netezza and Oracle databases and discussions with the stakeholders.

●Environment: IBM Data Architect, DBeaver, IBM Netezza, Oracle, SQL Developer, TOAD for Oracle, RubyMine, Informatica PowerCenter client 12

Discover Financials Services, Riverwood’s, IL July 2017 – Mar 2018

Data modeler

Discover is a direct banking and payments company offering credit cards, banking products, loan and payment services. The project involves ingesting the legacy and the Teradata data into the new Big Data platform. My role is to model the enterprise level solutions to deliver the optimum value to the end consumers.

ROLES AND RESPONSIBILITIES

●Leading data modeling efforts and providing solutions on ingesting the data into the new Hadoop big data platforms.

●Working on providing data models for multiple features through data to multiple stakeholders to help them analyze the data on graph databases or for artificial intelligence capabilities.

●Working on Fraud, speech detection application feed designs and designing hive tables with optimized parameters.

●Lead modeling efforts on XML data ingestion into the Hive platform.

●Working on ER Studio v17 for the logical and physical model generation for Hive on top of HDFS and then generating the tables/views.

●Interacting with the data engineers for data loading issues, performance optimizations, partitioning, bucketing, compaction level properties, transaction table properties for updates and inserts of the data.

●Providing the end customer with the performance optimizations through data modeling solutions.

●Working on tokenization of the data to hide the sensitive and critical risk data from the consumers, analyzing and profiling the data and discussions with the data governance, data architecture regarding certain CRE or Critical risk elements.

●Discussions with the in house ETL product teams on the hive capabilities and corresponding features feasibility aspects from the data modeling standpoint.

●Fulfilling requirements of multiple consumers of the data by taking into account the usage and statistical models design into consideration for Neo4j, SAS, AWS (Amazon Web Services) synch etc.

●Meeting the application requirements for multiple teams by designing tables following the architectural flow.

●Interacting with the architects, Business analysts, data engineers to gather the data modeling design requirements.

●ENVIRONMENT: Big data/Hadoop/Hive/HDFS, Teradata 14.0, Data science (Python, R, Neo4j), Artificial Intelligence

Allegis Group, Hanover, MD Jan 2017 – July 2017

Data modeler

Allegis Group is the largest supplier of staffing in the US. The project involved integration of multiple operating

divisions information in oracle and Hadoop clusters and finding the solutions for merging the data and provide the

enterprise level solutions. Use case using Hadoop/cloudera was successfully implemented.

ROLES AND RESPONSIBILITIES

●Use case analysis using Cloudera/Hadoop solutions and discussing the framework and the high level strategies with the Cloudera vendors.

●Lead the efforts to analyze the data coming from multiple divisions and find the solutions to get the enterprise level view for the stakeholders.

●Working on ERWin for the oracle and Hadoop db’s structures to create the hive and impala structures and maintain the technical and business metadata for the organization.

●Creation of external tables and views in Hive and impala layers.

●Managing the metadata for both the Hive and impala tables/views.

●Used Hue browser for analyzing/querying impala and hive structures.

●Designing he models at the enterprise level using ERWin 9.5.2 and syncing it with the Informatica Metadata manager for the metadata.

●Providing solutions for the faster retrieval of data from the impala layer by discussing with the SME’s and cloudera administrators.

●Working along with the ETL developers using Informatica BDM (Big Data Management) and SQOOP teams to coordinate the data loads into the HDFS and then creating a hive table on top of that.

●Working with multiple teams including Informatica IDQ (Data quality), Informatica metadata manager teams to sync the metadata from Erwin to the enterprise repository.

●Discussing data loading issues with the Informatica PowerCenter and analyst tool developers and help resolving them.

●Working with the source systems like SalesForce and PeopleSoft to analyze the data before modeling it on the warehouse as facts and dimensions in Oracle.

●Working on facts and dimensions on oracle to present the master and transactional data to the customer in the reporting layer.

●Analyzing the aggregates and and interact with the reporting team about the same for the visualizations.

●Interacting with Tableau and Webfocus reporting teams to analyze the reporting requirements and help creating the dashboard.

●Working on the OLAP for datawarehouse and datamart developments as well as OLTP models, both and interacting with all the involved stakeholders and SME’s to derive the solution.

●SQL querying for data profiling and data analysis was used to verify the data and the volumes.

●Part of team of business analysts, data analysts, developers and testers, Database administrators, etc. to implement the whole SDLC project life cycle for the use case and the other projects.

●Working with BSA’s, solution architects, developers and other stakeholders to gather data requirements.

●ENVIRONMENT: Oracle 12g, Hadoop/Hive/Impala, MS Windows, Informatica Powercenter, Tableau, etc.

Amica Mutual Insurance Company, Lincoln, RI Nov 2015 – Dec 2016

Data modeler

ROLES AND RESPONSIBILITIES

●Working on the enterprise level modules for the insurance client to perform the data modeling and data architectural related tasks and providing solutions for the data migration on various platforms from hierarchical to relational databases to unstructured databases.

●Working on OLAP for datawarehouse and datamart developments using Ralph Kimball methodology as well as OLTP models and interacting with all the involved stakeholders and SME’s to derive the solution.

●Working on facts and dimensions for the dimensional modeling data architecture as well as relational data modeling for the transactional systems like in-house applications and policy, claims applications, etc.

●Working on policy and claims applications to model the tables according to the 3NF modeling and working on resolving many to many relationships, bridge tables, reference tables, master data, etc.

●Lead the modeling efforts on multiple enterprise level projects.

●Data strategy for data governance to understand the data for strategies such as master data management, BI (Business Intelligence) or other data consumption platforms.

●Collection of data grievance and complaints data to model it in the database for the user consumption. Creating transactional in-house systems for grievance data.

●Created logical and physical models and ER diagrams using PowerDesigner modeling tool for the relational and dimensional data modeling.

●Forward and reverse engineering DDL for the SQL Server 2012, DB2 and Teradata environments.

●Collected technical and business metadata and maintained naming standards by working along with the architects, data governance, business analysts and developers, SME’s, etc.

●Interacted with the solutions architects for the framework analysis for multiple modules like claims, policy, etc. for timely delivery of the data into the new system for transactional analysis for .NET framework team and then to redirect the output to the reporting platform on to the Teradata.

●Working with the third party vendors like Guidewire developers to lead the data migration projects in order to make the new systems more efficient and the design the model to get the data from Guidewire to the data warehouse for reporting using Microstrategy.

●Lead the interactions as a data modeler with the reporting team, BI specialist’s to analyze requirements and the data from the transactional systems to get migrated into the data warehouse by the ETL teams using Datastage and model the structure according to the business requirements reported from Microstrategy.

●Providing solutions for the business issues and overcoming the technical difficulties by providing the optimum model to solve those issues to satisfy all the stakeholders involved.

●Working on multiple area’s like Auto, policy, claims, life insurance, etc.

●Interacting with the business analysts, architects, ETL teams using Datastage to build a sophisticated modeling solution for the customers.

●Lead discussions on designing the models on enterprise level.

●Worked on SQL Server, DB2 and Teradata platforms.

●ENVIRONMENT: PowerDesigner 15, SQL Server 2008,2012, Teradata 12, MS Excel, MS Windows, Datastage, Microstrategy, etc.

JPMorgan and Chase, Jersey City, NJ July 2015 – October 2015

Sr. Data Modeler/Data Architect

JPMC is one of the oldest financial institutions in the USA with assets of $2.4 trillion in 60+ countries. I was working on the enterprise architecture group working with multiple lines of businesses. I was heavily involved in the migration project for all the LOB’s to analyze the data elements to be consumed by LOB’s such as consumer banking, cards, wealth management, Investment banking, business banking, etc. and verify the logical and physical data modeling practices by indulging in multiple review board discussions.

ROLES AND RESPONSIBILITIES

●Lead the project for gathering requirements for the data element mappings and analyzing earlier models for all the LOB’s.

●Communicating with business stakeholders to understand the reporting requirements and identifying the source to certify the elements.

●Lead in the data certification process as a POC data modeler for certifying the elements in all LOB’s throughout Chase on the migration project.

●A centralized role involving working on multiple logical and physical data models to find the authoritative source for shared data elements across the LOB’s.

●Forward and reverse engineering for certain oracle and Teradata databases to identify certain data elements in use and study their relationships to analyze their use in the old and new data warehousing systems.

●Exhaustively collected business and technical metadata and maintained naming standards

●Worked on 3 layered Teradata architecture: staging, integration and semantic and consulted on naming standards and the modeling issues to the business stakeholders.

●Utilized data warehousing concepts and worked on normalized/de-normalized table structures in dimensional modeling and created aggregate tables.

●Interacted with multiple BSA’s, SME’s for all LOB’s to discuss possible overlap of the information to be taken into consideration to build the one ‘true source of information’.

●Interacted with BI developers for business requirements gathering to generate the report elements.

●Used Erwin for reverse engineering to connect to existing database and ODS to create graphical representation in the form of Entity Relationships and elicit more information

●Worked on IBM banking data warehouse industrial logical model on multiple subject areas.

●Worked with Database Administrators, Business Analysts and Content Developers to conduct design reviews and validate the developed models

●Analyzed aggregate tables and semantic layer modeling with BI developers and business SME’s to come up with the physical design.

●Using Erwin’s roll up and Roll down features after analyzing the business requirements.

●SQL querying for data profiling and data analysis was used to verify the data and the volumes.

●Worked on IBM banking data warehouse model to normalize all the entities and then de-normalize them for the customer consumption in the semantic and shared semantic layer of the architecture to be consumed by multiple LOB’s.

●Analyzing the data elements mappings and the data dictionary for the physical models and submitting to the central hub for metadata repository.

ENVIRONMENT: Erwin 7.3, Oracle 11g, Teradata 12, MS Excel, MS Windows, SAS, Cognos, Informatica etc.

Payless Shoesource, Topeka, Kansas July 2014- July 2015

Data Modeler

Payless ShoeSource is the largest specialty family footwear retailer in the Western Hemisphere with their motto ‘Get More, Pay Less’. There were multiple projects I worked on including Store of the Future, Master Data Management, etc. Worked on logical and physical modeling techniques on multiple databases like DB2, Oracle, SQL Server, Teradata, etc. Worked on both OLTP and OLAP projects.

ROLES AND RESPONSIBILITIES

●Leading the project in requirement analysis, design, enhancements and implementation and working with the data governance group at the enterprise level.

●Forward and Reverse engineering using Erwin 9.5. Simultaneous modeling on OLTP and OLAP projects.

●Worked on Canonical modeling for OLTP projects and interacted with the principal software architects to conduct the design reviews.

●Creating and ensuring the feasibility of the logical and physical design models using Erwin 9.5.

●Worked on creating star and snowflakes schemas.

●Worked on Master data Management (MDM) Hub and interacted with multiple stakeholders.

●Fast paced environment demanded solution delivery on multiple modules simultaneously going live, like Taxing, Sales Audit, Retail, Merchandising, etc.

●Working with BSA’s, solution architects, developers and other stakeholders to gather data requirements.

●Worked on BI requirements for Teradata environment and mapping documents for the ETL tool (ODI) using Erwin Data Modeler 9.5. Communicated with the reporting team (Microstrategy) for their requirements on staging to BTT, SLT tables and views.

●Worked with the SOA (Service Oriented Architecture), PIM (Product Information Management) for MDM Hub as well as Java developers.

●Interaction with BI developers and BSA’s to understand the reporting requirements and providing the source information with data profiling and analysis on the transactional systems for the warehouse.

●Conducting the review sessions with the architects and modelers and transferring the knowledge to the Technical leads and the software engineers.

●Delivering optimum solutions on data migration and enhancing model design as per the business and IT requirements and issues.

●Interacting with the third party service providers for software solutions for the client to provide the most efficient and cost effective solutions.

●Interacted with the solutions architects for the framework analysis for multiple modules like sales audit, retail, etc. for timely delivery of the data into the new system for transactional analysis for .NET framework team and then to redirect the output to the reporting platform on to the Teradata.

●As a supplementary part of data review board, interacted with the multiple customers for the true source of the data and its profiling.

●Lead discussions on designing the models on enterprise level.

●Worked with a team on certain projects as well as solo on some projects like sales auditing, taxing for the international retail, etc.

ENVIRONMENT: Erwin 9.5.2, SQL Server 2008/12, DB2, Mainframe z/OS 9.1/ LUW, Oracle 11g, Teradata 12, MS Excel, MS Windows, SQL Server Management Studio 2012, ODI, Microstrategy, Java, .NET etc.

ACE Insurance (Private Risk Services), Basking Ridge, NJ Mar 2014 – July 2014

Data Modeler

ACE Private Risk Services was recognized as the best insurance company for high net worth (HNW) clients by Private Asset Management (PAM). The goal of this project was to reverse engineer and get logical and physical models out of the current databases and modify as needed and to create an enterprise level model repository so all the stakeholders including the IT and business users could access the models and generate the reports as per their needs using Erwin Web Portal and also maintain the versions of Erwin using Erwin Mart.

ROLES AND RESPONSIBILITIES

●Analyzing entities and relationships for billing and policy administration for insurance domain.

●Developed Logical and physical models according to the requirements.

●Was involved in reverse/forward engineering 40+ databases mostly SQL server, MS access, Mainframe z/OS 9.1, DB2, SAS, etc.

●Worked on Erwin 9.5 Marts and Web Portal and was in the Enterprise architecture team and maintained the models on web portal for the North America division.

●Used version control feature in Erwin Web Portal to control the model versions and incorporated the ALTER scripts into the current model.

●Interacted with DBA’s, architects, managers, business/data analysts, data, conducted interviews with SME’s

●Used forward engineering feature in Erwin for generating the DDL’s using the physical data model.

●Used Erwin Query tool for generating the customized reports and also generated data dictionary.

●Involvement in enterprise architecture for POC’s like big data analytics.

ENVIRONMENT: Erwin 8.0 & 9.5.2, SQL Server 2008/12, DB2, Mainframe z/OS 9.1, SAS, MS Excel, MS Windows, SQL Server Management Studio 2012, Crystal Reports 9, JIRA, TFS

Siri infoSolutions, Edison, NJ Oct 2013 – Mar 2014

Data Modeler

●Logical and physical database models to design OLTP system for applications using Erwin.

●Forward engineering to create a physical data model with DDL that best suits the requirements from the logical data model using Erwin for effective model management of sharing, dividing and reusing model information

ENVIRONMENT: ERWIN 7.3, oracle 11g, SQL, MS Excel, Microsoft windows, TOAD from Quest software, OBIEE

University of Delaware 2011 – Jan 2012, 2012 Dec – 2013 April

Research Analyst (Multiple Projects)

ROLES AND RESPONSIBILITIES:

●Working on SQL and data analysis of the genetics/proteomics data.

●Using joins, substrings, etc. SQL functions to identify the patterns in the genetic and proteins.

●Using R and other statistical methods to analyze the data.

●Perl/Python used for data manipulation and data extraction

●Project involved finding multiple patterns in the data using various statistical and profiling methods.

ENVIRONMENT: MySQL, Perl, Python, Windows XP, R language, statistics

Contact this candidate