Data Sql Server

Jignesh Patel

Phone #201-***-**** Email **-*****************@*****.***

PROFESSIONAL SUMMARY:

Diversified development experience on Database Design, Architect and Development in SQL server 2012/2008R2/2008/2005/2000 and Oracle 11g/108/9i.

Worked with various domains like healthcare, finance, banking, manufacturing, telecom, direct mail, email, education loan system corporate, vendor, transportation finance and trading data.

Excellent technical and analytical skills with clear understanding of data architecture, metadata, data lineage docs, DW artifacts, design goals of ER modeling using ER/Studio for OLTP and dimension modeling for OLAP.

Worked with huge data 40 TB in size.

Experience in working with various data source like SQL Server, MySQL, Oracle, DB2, Systema, Access DB, XML, Excel, Flat Files and CSV files.

Worked on change data capture, historical and incremental data.

Expert in designing metadata data model for audit purpose at enterprise level.

Enthusiast QlikView developer for a wide variety of business applications and dashboards.

Hands-on experience with Informatica Master Data Management (MDM) 9x Multidomain Edition Configuration.

Hands-on experience working with a data virtualization tool Denodo.

Expert in handling late arrival records, fixing orphan, missing records in DW.

Created master and child packages, package configurations, auditing, logging and custom error handling in SSIS.

Expert in creating jobs, alerts, SQL mail agent, and schedule DTS/SSIS and Datastage Packages using Control-M.

Excellent in High Level Design of ETL DTS Packages & SSIS Packages for integrating data using OLE DB connection from heterogeneous sources (Excel, CSV, Oracle, flat file, Text Format Data) by using multiple transformations provided by SSIS such as Data Conversion, Conditional Split, Bulk Insert, merge and union all.

Expert in creating indexes- clustered, non-clustered, filtered index, Query Optimization, Views, complex Stored Procedures, user defined functions, cursors, derived tables, common table expressions (CTEs) and Triggers to facilitate efficient data manipulation and data consistency.

Worked extensively on system analysis, design, development, testing and implementation of projects (SDLC) and capable of handling responsibilities independently as well as a proactive team in agile.

Worked in project management tools like JIRA, TFS, SVN and Accurev for version control.

Self-learning ability and leadership quality.

Specialties:

RDBMS/Databases: MS SQL Server 2000/2005/2008, Oracle 8i/9i

Data warehouse tools: OLAP, SQL Data Transformation Services (DTS)

Data Modeling/ETL tools: ER/Studio, ERWIN, and Microsoft Entity Framework

Languages: C, C++, Java Script, C#, PERL, T-SQL, PL/SQL, Oracle 9i and 10g, HTML, XML

EDUCATION:

Sant Gadge Baba Amravati University B.S in Mechanical Engineering

PROFESSIONAL EXPERIENCE:

Teleworld Solutions Inc, Chantilly, VA March 2015 – Present

Position Title: ETL/DW Architect

Project: Digi TV

Worked as consultant to lead one or more simultaneous ETL projects for multi-year business intelligence and data warehousing initiative.

Responsibilities include full lifecycle project management, solution design, and overall ETL architecture:

Assess requirements for completeness and accuracy and determine if requirements are actionable for the ETL team.

Perform impact assessment and scope the size of the effort based on the requirements

Developed full SDLC project plans to implement the ETL solution, including identifying resource requirements

Manage the build phase and quality assure (peer review) the code to ensure that it meets the requirements and adheres to the ETL architecture.

Generated periodic reports based on the statistical analysis of the data from various time frame and division using SQL Server Reporting Services (SSRS).

Developed various operational Drill-through and Drill-down reports using SSRS.

Develop reports using complex formulas and to query the database to generate different types of ad-hoc reports using SSRS.

Monitored and killed processes that create deadlocks within the database.

Wrote complex Stored Procedure, Queries, Triggers, Functions, Indexes, User-defined functions and Cursors for the application.

Served as a SSRS 2008 Reports Developer and SQL Server Developer in support of Service Management System.

Used SSIS and T-SQL stored procedures to transfer data from OLTP databases to staging area and finally transfer into data warehouse.

Extensively involved in generating reports like matrix, parameterized, Charts and Graphs like Bar Chart (Side by Side, Stacked), Line graphs, Pie Charts and drill through reports using SSRS.

Developed and deployed data transfers packages to the appropriate destinations using SSIS.

Designed dashboard using SSRS to represent metadata for the migration audit database and developed many other reports for data integrity check between source and target systems.

Experience in creating Jobs, Alerts, SQL Mail Agent, and schedule SSIS Packages.

Created SQL server configurations for SSIS packages.

Used SSIS transformations to implement the Slowly Changing Transformation, to maintain Historical Data in Data warehouse.

Designed and implemented user log in and security in SSRS.

Involved in deployment of various SSRS Reports in Test and Production environments. Also, in charge of production support. Responsible for creating reports based on the requirements using SSRS 2008.

Administered interface to organize reports and data sources, schedule report execution and delivery, and track reporting history using SSRS 2008.

Coordinate with the Quality Assurance team to plan and execute QA testing and resolve of any defects

Take an active role in joint solution design and be an advocate for the ETL team in the process

Environment:

MS SQL Server 2008 R2, SSRS, MS ETL SSIS, T-SQL, Visual Studio 2010/2008, TFS.

The Child Center of New York, NY March 2014 – March 2015 MS SQL/ETL Developer

Project: Data Services

Job Description:

The Child Center of NY reaches out across diverse ethnic populations to create a supportive, caring place for children and families at risk. All programs promote emotional wellness and secure attachments to parents and other adult caregivers. The aim of the project was to design and developed Data warehouse using SSIS and generating customer profile reports and analytical reports to support the management team using SSRS.

Responsibilities:

Designed physical and logical data model using ER studio. Created data model using reverse engineering in ER studio.

Responsible for forward and reverse engineering to create and modify existing database model using ER studio.

Created stored procedure using nodes method to load XML file data into SQL server tables.

Wrote User defined functions, views, trigger, and indexes for various purposes based on the requirements.

Rewrote poorly performance stored procedure, functions, triggers, views and indexes.

Created reconsolidation reports for conversion purpose using various joins.

Responsible for creating batch file to deploy various database objects such as tables, stored procedures, indexes in various environment using SSIS.

Implemented complex business logic with case expression, cast and convert, group by, top operator, while loop and common table expression using complex t-sql queries.

Created packages using the various control flow task such as data flow task, execute SQL task, Execute package task, File System task.

Used various data flow transformation in packages based on the business need such as Data conversion, condition split, multicast, union all merge, merge join, sort, etc.

Extensively used variables, break point, check point, logging, package configuration and event handler in SSIS packages to meet the business needs.

Created various staging tables for conversion purpose to cleaning the data while converting from old system to new system.

Created configuration file for various SSIS packages to make package dynamic for deployment in various environment.

Involved in creating jobs and alerts and scheduling it on daily, weekly and monthly bases.

Extensively used maintenance plan, SQL server profiler and database engine tuning advisor to optimized poorly performing queries.

Rewrote various business process and tested result in MS Excel using various functions and sub query with not exists.

Involved in updating functional requirement document after development and created documentation for deployment team.

Project Environment:

MS SQL Server 2008R2, Windows 2003 Server, SQL Server Management Studio(SSMS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Business Intelligence, IIS, ER studio, Microsoft Visual Studio 2008, ERWIN, SQL Profiler, MS Excel.

CITI Bank, New York, NY April 2013 – March 2014

Position Title: SQL /Data warehouse Programmer

Project: Actimize/ Data Services/ EDW

Job Description:

Project is designed to implement Actimize’s Suspicious Activity Monitoring (SAM) and Transaction Monitoring System based Corporate, Trade and Transport finance data. Data warehouse is designed to help Actimize’s DSS get the most out of their operational data, and provide them a powerful decision making framework based on the massive amount of data the network generates daily. It has multiple source system data such as LoanIQ, ACAR Stucky and Vendor Finance etc.

Responsibilities:

Analysis/Design, Development of Databases, SSIS packages, Web Application, related Web services.

Maintenance and production support of Databases, SSIS packages, Web Application, related Web services and databases

Involved in creating technical design documents, process flow diagrams and technical specs, DW artifacts, Data Lineage doc, Deployment scripts and ETL/DW manual and setting up Dev and QA environment for DW.

Interacting with other IT personnel in order to provide technical expertise and services.

Provide necessary production support for existing Applications, Services and Processes.

Designed Enterprise Operational Metadata data model for audit purpose at enterprise level.

Designed and Implemented robust ETL template to fit Enterprise Operational Metadata data model and to support all existing and upcoming projects to enhance audit/metadata capturing functionality.

Provide Technical direction in design, development and systems integration including client engagement from definition phase through implementation.

Designed QlikView dashboard to represent metadata for the enterprise audit database and developed many QlikView application for QVD generation.

Hands-on experience with Informatica MDM Hub configurations - Data Mappings (Landing, staging, cross reference and Base Objects), Data validation and exceptions, Match and Merge rules.

Hands-on experience with MDM Hub for creating queries and packages to provide data view to data manager and configure the batch jobs to execute the landing, stage, load, match and merge process.

Hands-on experience on data virtualization tool Denodo platform to connect various data sources like to jdbc, odbc,ws, xml,delimited files and ldap.

Created base view, derived view, projection, selection, union, minus, flatten views and interface to provide unified virtual data layer using Denodo.

Worked as Denodo Admin to create databases, users, assigning roles, import/export database and scripts vql and properties and configured cache database.

Created jobs to refresh/invalidate cache using Denodo scheduler.

Worked with business analyst to design ETL architecture to build KYC/SAM for Actimize application.

Designed efficient dynamic SQL to find and load delta records set from any source system (UDB's)

Heavily worked on partitioning tables to improve performance for data load.

Performed reverse engineering to create mapping document from existing legacy applications.

Coordinated all aspects of the project with other teams, such as Data Architecture, DBA, Quality Assurance, Support, Infrastructure and management.

Expert in Performance tuning and optimization.

Communicating status and issues in a clear and professional manner

Participate in Disaster Recovery Activity, develops improvements for performance, availability and reliability.

Performing code reviews to enforce coding standards, code consistency, use of design patterns and adherence to application design

Responsible for planning and providing weekly status

Environment:

MS SQL Server 2012/2008, ETL/SSIS, MDM Hub 9x, Denodo Platform, RDM, Collibra, T-SQL, Toad, Visual Studio 2010/2008, TFS, VSTS and Windows 2008, Visual Source Safe.

CareCentrix, Hartford, CT February 2012 – April 2013

Position Title: ETL/DW Programmer

Project: Operational Dashboard/ EDW

Job Description:

Project is designed to develop OPS Dashboard BI Solution application and Data warehouse to help CareCentrix DSS get the most out of their operational data, and provide them a powerful decision making framework based on the massive amount of data the network generates daily. The platform Hosts network data such as I3-Phone System, Intake98, Queue Manager(eBin), Claims98, Unity, SMS, Deloitte and Manual Data etc. OPS Dashboard incorporates a powerful data analysis and automation engine used to model and automate business processes.

Responsibilities

Involved in creating technical design documents, process flow diagrams and technical specs, DW artifacts, Data Lineage doc, Deployment scripts and ETL/DW manual and setting up Dev and QA environment for DW.

Managed team of ETL developers using scrum call and database administrators in agile environment.

Established ETL infrastructure, ETL/DW standards and ETL naming conventions.

Evaluating the latest versions and educating other developers with new changes.

Created and maintained the overall and detailed project plan(s) and supervise the DW ETL process.

Created and maintained data architecture designs to increase robustness, performance and scalability.

Design and build efficient SQL queries, analyze query cost comparison, use indexes efficiently.

Work with internal customers to identify requirements for ETL processes.

Expertise in Extraction Transformation Loading (ETL) process, Dimensional Data Modeling experience using Data modeling, Star schema/ Snowflake modeling, Fact and Dimensions tables, dimensional, multidimensional modeling and De-normalization techniques.

Thorough understanding of Kimball and Inmon methodologies.

Created complex script for Oracle 10g in Toad v9.6 and T-SQL in SSMS in SQL Server 2012, used various performance tuning techniques including filtered index, cluster/non cluster/covering index, bookmark/key lookup, execution plan and stored procedures.

Designed architecture for ETL template packages which would run dynamically in any environment with custom error handling, custom email notifications and with auditing functionality.

Designed flat/de-normalized staging structure for temporary sitting data from various source systems like Oracle 10g, DB2, legacy SQL server, Flat Files, CSV and Excel loads.

Expertise in design and implementation of Slowly Changing Dimensions (SCD) type1 and type2.

Designed and developed SSIS packages (model package) to validate, extract, transform and load data from OLTP system, flat tables to OPS-Dashboard (data warehouse).

Designed Historical and Incremental ETL architecture to extract data from DB2 (Unity Billing), SQL Server (Deloitte DB).

Responsible for ETL package tuning and performance as millions of records get processed every day. Solid work experience with huge database 15 TB in size.

Responsible for creating and scheduled jobs and email alert to call the packages and Stored Procedures as per business requirement.

Environment:

Oracle 10g, Toad v9.6, MS SQL Server 2012, MS SQL Server Reporting service 2012 SSRS, MS ETL SSIS 2012, T-SQL, Visual Studio 2010/2008, Team Foundation Server (TFS), VSTS and Windows 2008, IBM System I Navigator v6.0, AS400 DB2.

International Specialty Product, Hartford, CT August 2010 – February 2012

Position Title: SQL /Data warehouse/ SSIS/SSRS Developer

Project: MATRIX (Data Warehouse), Zycus, ASPEN

Job Description:

ISP is a leading global supplier of specialty chemicals and performance-enhancing products for a wide variety of personal care, pharmaceutical, performance, beverage, and industrial applications. ISP produces more than 400 specialty chemicals, which it markets and sells worldwide. The project goal is to design and develop an enterprise data warehouse, design ETL packages to load data from OLTP, heterogeneous sources like AS400 and excel loads.

Responsibilities

Worked with business users, to identify the business requirements and worked in agile environment. Designed packages which would run dynamically in any environment.

Involved in designing the data warehouse (TYPE 1) and core Database for the system using Snowflake Schema including tables, Stored Procedures, Indexes etc. in SQL Server 2008.

Developed SSIS packages to grab daily transactional data from 3 different servers (Europe, Asia and Western) using ForEachLoop transformation using single ETL package.

Developed ETL to apply foreign exchange rates to transactions records coming from Asia, Europe to flatten data into USD.

Designed and developed SSIS (ETL) packages (Model Package) to validate, extract, transform and load data from OLTP system, STG, SPG (Staging Database) to MATRIX (warehouse).

Designed and developed SSIS packages (Model Package) for Historical Load and Incremental Load (Daily Updates) to feed the data warehouse from different sources like Maximo, Cognos, Gateway, AS400, Flat File, CSV Files, and Excel 2007.

Developed Cascading, Drill-down Reports to get monthly card details and profits using SSRS 2008R2.

Involved in deploying, configuring and managing reports using Report Manager and Report Builder.

Generated periodic reports based on the statistical analysis of the data using SQL Server Reporting Services (SSRS).

Involved in scheduled jobs to call the packages and Stored Procedures as per business requirement.

Responsible for performance monitoring and tuning of TSQL queries using SQL Profiler and database engine tuning advisor.

Environment:

MS SQL Server 2008 R2, SSRS, MS ETL SSIS, T-SQL, Visual Studio 2010/2008, Team Foundation Server (TFS), VSTS and Windows 2003, AS400 DB2.

Contact this candidate