Sql Server Data
Resume:
JEREMY HU
Summary:
●* years of professional experience as SQL Server/ BI developer using Microsoft SQL Server 2014/2012/2008R2/2008/2005, SQL Server Management Studio, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS).
●Involved in system analysis, design, development, testing and implementation of projects using Software Development Life Cycle (SDLC).
●High Proficiency in Data Modeling, Data Architecture and designing Enterprise Data Warehouse, Data Marts and Dimension Databases (Dimension Table & Fact Table).
●Adept in complicated query development for various business applications using Store Procedures, Triggers, Views, User-defined Functions and Cursors.
●Extensive Experience in RDBMS applications in Stored Procedure, User Defined Function, Temporary Table, Table Variable, CTE and Table Value Parameter.
●Strongly Proficient in Normalization and de-normalization techniques for OLTP and OLAP systems in creating Database Objects like Tables, indexes and Constraints (Primary key, Foreign Key, Unique, Default).
●Profound Experience in optimizing the performance of queries and SQL server database using execution plan, SQL Profiler and Database Engine Tuning Advisor.
●Expertise in creating SQL Server SSIS packages (ETL) to import, extract and transform data between databases and external data sources like flat file, excel and XML.
●Efficiently Proficient in using SSIS Control Flow (For each Loop Container, Execute SQL Tasks, Script Task) and SSIS Data Flow (Derived Column, Conditional Spilt, export column)
●Highly Knowledgeable in the application of Event Handlers, Loggings, Checkpoints, Breakpoints and Debugging.
●Superior ability to load and transform data dynamically by using SSIS packages (like Slow Changing Dimension, Change Data Capture).
●Leading experience in developing reports using SSRS including Sub Reports, Drill through and Drill down Reports.
●Created various SSRS Reports using Filters, Parameterized Reports, Linked Reports and Snapshots.
●Strong experience in deploying reports on report server by using SSRS, Report Builder and MS SharePoint.
●Comprehensive understanding in using Tableau to implement various reports like Charts, drill down and parameterized reports.
●Strongly Skills in Designing, Creating, Deploying and Processing of Cubes using SSAS.
●Strong understanding in MVC model, adding functions on front end using C# and JavaScript.
●Ability to use MDX queries in OLAP databases to support analysis needs and data retrieval.
●Expertise in data analysis like Cluster and Classification by using data models like K-means, Neural Network and Decision Tree.
●Excellent communications, programming and analytical skills to solve problems independently or as part of a team.
Skillset:
●Database: SQL Server, MS Visual Studio 2012 and SQL Server 2012 data tools,
RDBMS, Oracle, Data Modeling, Data Extraction, SQL reports development and data
Analyses with expert-level development and analysis, SQL Profiler
●Reporting: Oracle, SQL Server 2012, SQL Server Reporting Services SSRS, SSIS, Excel, Micro
Strategy Reports and Application Development, Crystal Report Design and
Development, PowerPoint, Visio, Tableau expert reports developer.
●Programming Languages: SQL, JAVA, C#, .NET, C++, C Compiler Language, MDX
●Scripting Languages: XML, HTML, Python, JavaScript, jQuery
●Operating Systems: Windows, Linux, Mac-OS
●Tools and Applications: Software AG Designer, Visual Studio 2015/2017, Matlab, R, Hadoop, EMR, Tableau, MS Office
Professional Experience
The Central Alabama Recipient Eligibility System (CARES), Montgomery, AL 10/2015-Present
Sr.MS SQL Server/(SSMS/SSIS) Developer
The Centralized Alabama Recipient Eligibility System (CARES) Program was initiated as a result of the Patient Protection and Affordable Care Act of 2011 (hereinafter the ACA) to handle the significant changes to eligibility determination criteria for Medicaid and the Children’s Health Insurance Program (CHIP) programs. The CARES solution is an enterprise product developed and configured as web-based portal solution. It provides caseworkers and citizens easy access to submit and analyze insurance eligibility information. This program comprises the State of Alabama’s Medicaid Agency, Department of Public Health (ADPH) Alabama Department of Human Resources (DHR), Department of Mental Health (AFMH), Department of Senior Services (ADSS) and others. My major responsibility was to facilitate Hospital Presumptive Eligibility (HPE) file Retro and County Moves process using stored procedure and implement HPE file automation using SSIS Package.
Responsibilities:
●Gathered business and data requirements from business analyst and stakeholders, leading data analyst to provide solution and facilitate data architecture.
●Developed stored procedure to implement HPE file Retro segments generation.
●Proficiently applied multiple data structures like CTE, table variable and temporary table to fix exception buckets in HPE file like Null SSN and invalid Zip code.
●Implemented date segments generation in HPE file using cursor, pivot and dynamic SQL.
●Wrote complex T-SQL queries like loop and nested query to fix overlapping date enrollment issue.
●Involved the design and development of data migration, tracking updates using Merge statement in SQL.
●Applied multiple join operations for data mapping, which is used to build export table mapped to CRM.
●Designed ETL process to facilitate HPE file automation process in CARES 2.0.
●Handled and optimized bulk load (million records) from database to CRM using SSIS package.
●Implemented dynamic loading in SSIS package using precedence constraint.
●Used sequence container to maintain SSIS package consistency.
●Scheduled daily or monthly job for reports generation in SSIS packages.
●Created error handling process using transaction and try catch block in SQL query.
●Assisted DBA to resolve data lock issue based on strong background of isolation level.
●Implemented SSIS package deployment using TFS and deployed into Azure environment for further testing.
●Used Execution Plan, SQL Profiler and Index Wizard to optimize queries, locate and eliminate bottleneck and tune the performance of databases.
●Fine-tuned complex SQL queries and Stored Procedures to increase performance via using efficient data structures like common expression table (CTE).
●Restructure address update process, using stored procedures to communicate between middle layer and database.
●Fixed UI issues like adding role restrictions and alert messages using C#, JavaScript and .Net based on MVC model.
●Optimized search function in Accounting Module using C# and jQuery.
●Worked on sprint planning and product backlog items refinement after receiving approval from stakeholders as per agile methodology.
●Involved in the SDLC in product backlog items investment, development and refinement.
Environment:
MS SQL Server 2012/2014, visual studio 2015/2017,Software AG Designer 9.12, SQL Profiler, MS SQL Server Integration Services (SSIS), Team Foundation Server, Share Points.
Arch Insurance Group, Jersey City, NJ 03/2015-09/2015
Sr.MS SQL Server/ (SSMS/SSIS/SSRS/SSAS) Developer
Arch Insurance Group (Arch), a division of Arch Capital Group Ltd., is a market-leading specialty insurer. The company provides a wide range of property, casualty and specialty insurance for corporations, professional firms and financial institutions across the United States and Canada. The project was to develop Data Warehouse Application to achieve successful delivery of business intelligence information to the entire company. My main responsibility was to improve the existing ETL packages, create ETL packages for migrating data from different data sources to DW and generate reports into the reporting repository.
Responsibilities:
●Gathered business requirements, definition and design of the data sourcing and data flows, data quality analysis, working in conjunction with the data warehouse architect on the development of the logical data models.
●Used Ralph Kimball Approach to implement Star Schema for data warehouse architecture on the logic development.
●Reviewed logical and physical data models for new requirements after discussion with the technical team and application users as per agile methodology.
●Designed and implemented complex SSIS package to migrate data from multiple data sources for data analyzing, deploying and dynamic configuration of SSIS packages.
●Designed high level ETL (Extraction Transformation Loading) architecture for overall data transfer from the OLTP to OLAP with the help of SSIS.
●Developed highly complex SSIS packages with large data set by using various Tasks, Containers, Script Tasks and transformations like For each Loop Container, Fuzzy Lookup and Conditional Split.
●Created SSIS packages for Dynamic Loading, Slow Changing Dimension, Data Importing and Cleansing.
●Applied C# for script task, which is primarily to control the flow of SSIS packages.
●Used various event handlers to log process information into SQL tables.
●Wrote T-SQL queries, common table expressions, Store Procedures and used them to build packages.
●Involved in providing logging, auditing and packages configurations for make the packages more flexible by using SSIS. Used different techniques of performance tuning in SSIS as well as involved in handling the errors.
●Generated reports using Global Variables, Expression and functions using SSRS.
●Identified the database tables for defining the queries and defined dataset for report generation.
●Generated Sub-Reports, Drill down Reports, Drill through Reports and Parameterized Report.
●Created and managed stored procedures, indexed views, database constraints and user defined function to meet business requirements and policies.
●Implemented OLAP Cubes, Facts and Dimensions for providing summarized and aggregate views of large sets of data.
●Wrote MDX scripts to create named sets and calculated members and created reports by dragging data from cube in SSAS.
●Converted SSRS reports to Tableau when required.
Environment:
MS SQL Server 2012, MS SQL Server Management Studio, SQL Profiler, Index Tuning Wizard, MS SQL Server Integration Services (SSIS), MS SQL Server Analysis Services (SSAS), MS SQL Server Reporting (SSRS), C#, Tableau
Avenue E Health Strategies, NYC, NY 10/2013-02/2015
MS SQL Server/BI Developer
AEH is a subsidiary of Sudler & Hennessey, a global healthcare communications network serving clients from 63 offices across 34 countries. The project was to implement ETL packages and automate reports to support the new referral web portal for members and provider where service providers can see their appointments, check patient medical history as well as members can book an appointment, reschedule appointment, cancel an appointment and do live chat with doctors.
Responsibilities:
●Developed various T-SQL stored procedures, views and adding/changing tables for data load, extraction and transformation.
●Designed, reviewed, and created primary objects (views, indexes, etc.) based on logical design models, user requirements and physical constraints and also performed Table Partitioning on very large scale databases.
●Created effective functions, appropriate Triggers to facilitate efficient data manipulation and data consistency.
●Extensively used SSIS for transferring data by utilizing various control flow and data flow elements and also involved in end-to-end unit testing.
●Involved in SSIS Packages to transfer data from various sources like Text Files, XML Files, SQL Server, Excel and Access to SQL Server.
●Applied various data transformations like Slowly Changing Dimension, Aggregate, Sort, Multicasting, Conditional Split, Derived column using SSIS for ETL Processes.
●Wrote T-SQL queries, Store Procedures and used them to build packages and handled Change Data Capture (CDC) to maintain the history of the data.
●Updated, debugged, tuned and proposed changes to the existing SSIS packages.
●Created standard reports Subscription and Data Driven Subscription.
●Defined report layouts including report parameters and wrote queries for drill down reports using SSRS 2012.
●Developed various Service management reports, utilizing dynamic and cascading Prompts, sub-reports, charts, parameterized reports and dynamic reports using SSRS.
●Created Ad-hoc reports and modified them by using and MS Report Builder. Created Cache reports and Snapshot reports for retrieving stored reports.
●Responsible for performance tuning by optimizing indexes, SQL queries, stored procedures to improve the quality of software and improve the tuning of queries.
●Performed tuning of query, triggers, and stored procedures, their testing focusing on stability and execution plan which has resulted up to 40% increase in performance of numerous modules.
●Performed daily tasks including backup and restore by using SQL Server tools like SQL server Management Studio, SQL Server Profiler, SQL Server Agent, and Database Engine Tuning Advisor.
●Involved in Planning, Defining and Designing data based on business requirements and provided documentation.
Environment:
MS SQL Server 2012, Microsoft Visual Studio, SQL Server Management Studio, SQL server Integration Services, SQL server Reporting Services, SQL Server Profiler, MS Office2007, Index Tuning Wizard
BNY Mellon, Jersey City, NJ 02/2012-09/2013
MS SQL Server Developer/(SSMS/SSIS/SSRS) Developer
BNY Mellon is a global investments company dedicated to helping its clients manages and service their financial assets throughout the investment lifecycle. Using its online banking, the clients can pay bill and transfer money easily and conveniently. The clients can also view balances, statements and transactions anytime. My responsibilities involved in designing, implementing, managing and troubleshooting databases for mission-critical business applications.
Responsibilities:
●Involved in Planning, Designing database using Erwin on business requirements and provided documentation.
●Transformed complex business logic into Database design and maintaining it by using SQL objects like Stored Procedures, Views.
●Developed Database Triggers to enforce Data integrity and additional Referential Integrity.
●Normalized database using Boyce-Codd normal forms.
●Write extremely complex Queries using joins, User Defined Functions (UDF), Common Table Expression (CTE).
●Assisted DBA in implementing Data and Database Security using various Default Database and Server roles and also by creating User-defined as well as Application roles.
●Developed physical data models and created DDL scripts to create database schema and database objects.
●Setup backup and restoration jobs for development and QA environments.
●Managed index, statistics and optimizing queries by using execution plan for tuning the database.
●Created and modified Clustered and Non-Clustered Indexes to optimize the queries using Index Tuning Wizard.
●Design, development, testing and implementation of ETL for consuming source data into the reference data system after cleansing, standardization and matching and merging.
●Generated packages using different Transformations like Lookups, Derived Column, Merge Join, Fuzzy Lookup, Union all, Script component and etc.
Environment:
Windows7, SQL Server 2008, SSIS 2008, SQL Server Management Studio, SQL Server Index Tuning Wizard, SQL Server Agent, SQL Profiler, Database Engine Tuning AdvisorErwin 8.2.5, MS Visio
International Oncology Network(ION) Solutions, Burlington, MA 11/2010-12/2011
SQL Server/BI Developer
Serving more than 55% of the country's community oncology market, ION Solutions provides group purchasing solutions, practice efficiency tools and education that help oncology practices operate more effectively. The project was to provide ETL automation and reporting support to 'Site Alert' - a monthly reporting system to data analytic team, marketing and finance department, and other business clients that need operation and market data.
Responsibilities:
●Extensively used Joins and sub-queries for complex queries involving multiple tables from different Database.
●Implemented constraints on tables using some basic requirement.
●Developed Complex Queries in T-SQL, Stored Procedures, and Views with best execution plan.
●Created SSIS packages using different talks and transformations and moved data from various sources to appropriate destinations.
●Responsible for Developing, Monitoring and Deploying SSIS packages.
●Extensively used SSIS transformations such as lookup, merge, derived column, data conversion and aggregate etc.
●Maintained Batch logging, Error logging tables and handled errors using Event handlers.
●Translated requirements into production of both ad hoc and recurring reports and datasets.
●Designed, Developed and Deployed reports such as Tablix Reports, Matrix Reports, Parameterized, Drilldown, Ad hoc and Sub-reports using Report Designer and Report Builder 3.0 and Generated Reports using Global Variables, Expressions and Functions.
●Involved in writing Query for generating drill down reports, identified and worked with the parameters to generate parameterized reports along with that extensively used global variables, expressions and functions.
●Scheduled the monthly reports to run automatically.
●Optimizing Stored Procedures and long running queries using indexing strategies and query-optimization techniques.
Environment:
SQL Server 2008, MS SQL Server Management Studio, Query Analyzer, Index Tuning Wizard, MS SQL Server Integration Services (SSIS), MS SQL Server Reporting (SSRS)
Education:
Master in Data Engineering, Stevens Institute of Technology, NJ
Certificates:
R Programming Language by Johns Hopkins University on Coursera.
Contact this candidate