Sql Server Data

Jiahui Feng

Summary:

Highly qualified MS BI consultant with more than 7 years of experience in implementing end-to-end BI solutions using the Microsoft BI Stack within several industries viz. Banking, Health, Financial Services and Insurance.

Involved in gathering business requirements from clients through Joint Requirement Development sessions for functional and non-functional requirements.

Extensively worked on OLTP Relational Schema with Normalization and OLAP Dimensional Modeling using Star and Snowflake schema.

Hands on experience with Erwin to forward and reverse engineer data models.

Extensively created and debugged large T-SQL database scripts and batches to facilitate effective database management.

Highly Proficient in T-SQL programming in creating stored procedures, triggers, views and user defined functions on SQL Server.

Created partitions on tables, indexes and index views as well as querying and managing partitions from optimization point-of-view according to the business criteria.

Optimized T SQL Scripts by analyzing Execution Plans as well as using Microsoft Tools like SQL Profiler and Database Tuning Advisor.

Experience in creating efficient ETL Strategy for initial load into the databases as well as incremental load.

Experience in Importing/Exporting Data from various data sources like Excel, XML, Oracle and Flat file using BCP, Bulk Insert & SSIS package.

Experience using many of the SSIS transformations like Slowly Changing Dimension, Audit, Aggregate, Lookup, Fuzzy Lookup, Conditional Split, Derived Column, and Multicast.

Designed ETL packages using proper Loggings and Checkpoints, secured packages with Encryption in conjunction with passwords.

Good understanding of OLAP data warehouse design practices to include the design of cube objects, the design of optimal and efficient cube processing strategies based on infrastructure as well as cube size.

Well versed in creating Parameterized Reports, Drill down, linked, sub cascading Reports and Reports using SQL server Reporting Services.

Developed Report Models based in Relational databases and created Ad Hoc reports to the specific users in the requested formats Using Report Builder

Proficient in using Report Manager for setting up roles, security on the reports and creating Standard and Data Driven Subscriptions in Delivery of Reports.

Education:

Masters of Science

Skills:

Management Studio, ER-Win, MS Visio, MS SQL 2008/2008 R2/2012, T-SQL, BIDS/SSDT, SSIS, SSAS, SSRS, SharePoint, Power BI, Power View, Power Pivot

Windows Server 2003/2008/R2, 7/8

Projects Summary:

Baystate Financial, Boston, MA 06/2014 - Present

MS SQL BI Developer

Founded in 1901, Baystate Financial is one of New England’s oldest and largest financial services firms offering comprehensive, individualized financial strategies for clients.

The main goal of the project is to develop a new Data Mart use Kimball methodology and integrate it with existing OLTP databases with efficient ETL strategy.

Responsibilities:

Gathered business requirements as endeavor that will facilitate the planning and the implementation phase of a data warehouse for reporting.

Conducted JAD sessions with different Auditors, Business Users to capture and document the BRD and transform the relevant /approved BRD into SRS and FRS.

Developed and created logical and physical database architecture using Erwin.

Created stored procedure for auditing purposes as well as triggers for data consistency checks.

Examined and evaluated the data structures in the source and destination databases / staging Tables.

Used various transformations such as Derived Column, conditional split, fuzzy lookup, fuzzy grouping, etc. to filter bad data.

Identifies and implemented slowly changing dimension to maintain current information and history information in dimension tables.

Used best practices for SSIS design such as Master Child architecture to leverage parallel execution of various packages.

Managed SSIS Package Configurations using parameters to make them dynamic, when deploying to production.

Optimized the performance of slow executing SSIS packages by making changes as Control Flow and Data Flow like avoiding blocking transformations, increasing buffer size, making master and child as separate process, parallel execution, etc.

Designed and developed SSIS Package template, so similar development practices are followed by all SSIS developers for logging, configurations and error handling.

Created QA (Quality Assurance) scripts and sanity checks to further test ETL packages by comparing QA output with output from ETL package.

Developed complex reports like Drill capabilities, Complex freehand SQL queries, Functions, Master Detail, and Cross Tab with multiple breaks.

Extended reports as well as invoked embedded functions and integrated assemblies using expressions.

Created canned and ad-hoc Reports using SQL 2012 Server Reporting Services and Excel services.

Generated different schedules for report delivery to e-mails and shared directories; enabled snapshots, subscriptions, caching to improve the response times and reduce traffic to the report server.

Designed strategies for securing the reports created depending upon the level of sensitivity of information that the report contained and for improving and further leveraging these reports in order to transfer / convert existing reports to SQL Server Reporting Service.

Environment: MS SQL Server 2012/2014, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, SharePoint 2010, Power Pivot, SSDT

Century Bank, Medford, MA 08/2012 – 05/2014

MS SQLBI Developer

The largest family operated bank in Massachusetts. Founded in 1969, own 27 branches in 19 different cities and towns throughout the regional Boston area.

The major scope of the project was to design and implement a relational Data Mart based on existing data warehouse for Personal Borrowing for better analysis and generating respective reports based on the business requirement.

Responsibilities:

Actively involved in gathering system specification requirements for the project through JRD sessions from business users and Subject Matter Experts(SMEs)

Involved in creation of functional requirement specifications (FRD) and supporting documents for business systems, experience in database design process and data modeling process.

Examined and evaluated the data structures in the source and enhanced the old logical and physical database design to fit new business requirement, and implemented new design into SQL Server.

Worked with database objects like Stored Procedures, User Defined Functions, Triggers and Indexes using T-SQL to create complex scripts and batches.

Involved in designing conceptual, logical and physical dimension data model of Data Mart for catering business users report requirements.

Involved in adding Dimensions and Facts to data mart for Borrowing based on existing Data warehouse.

Managed existing ETL strategy to extract, clean and deliver data to OLAP environment and ensure accuracy, consistency and integrity of the data mart.

Created and deployed SSIS packages using various Transformations such as Fuzzy Lookup, Fuzzy Grouping, Aggregate and Derived Column Transformations to make data standardized.

Handled the rum time errors in SSIS packages by setting up SSIS loggings and custom loggings wherever required.

Involved in package troubleshooting utilizing error logs, checkpoints, breakpoints and data viewers.

Implemented package configurations on the development, test, and production environments.

Wrote SQL queries on data staging tables and data warehouse tables to validate the data results.

Analyzed queries for optimization and performance tuning using SQL Profiler and Database Tuning Advisor.

Followed the regular reporting life cycle Analysis, Design, Development, Testing and Scheduling to develop User Interface Reports.

Designed and deployed reports with Drill Down, Drill Through and Drop down menu option and Parameterized and Linked reports.

Delivered on-demand and scheduled reports on daily, weekly, monthly and quarterly Reports including current status for business analysis or management decisions using SQL Server Reporting Services.

Environment: MS SQL Server 2008 R2/2012, SSIS, SSAS, SSRS, Erwin, Excel, Windows server, Flat Files, SharePoint, Power Pivot

Willoughby Insurance, Buffalo, NY 11/2011 – 07/2012

SQL/ETL Developer

Willoughby Insurance serving the community of Buffalo and all of Western New York for over 60 Years. As Certified Insurance Counselors, the Willoughbys have the knowledge and expertise provide services about auto insurance, home insurance, life insurance and motorcycle insurance.

The project scope is maintain and optimize existing databases for Auto Insurance, implement SSIS packages to perform ETL process, and responsible generate related reports base on the requirement.

Responsibilities:

Involved in various data modeling designs which include Conceptual design and to identify entities and the relationships among them.

Created and optimized objects such as: Stored Procedures, Triggers, User define functions, Indexes, Views, and Index views.

Maintained data integrity by creating various constraints and DML triggers the relational databases.

Designed a database optimization plan involving the creation of Table Partitions and the use of appropriate Indexes as recommended by Microsoft’s Index Guidelines.

Designed the entire ETL process involving the access, manipulation, analysis, interpretation and presentation of information from both internal and secondary data sources to the business.

Developed complex mappings using SSIS to transform and load the data from various source systems like Excel, Flat files, Oracle into the SQL Server target staging database.

Implemented Error Handling during ETL load in SSIS packages to identify dimensions and facts that were not properly populated.

Involved in implementing proper package configuration features and finally securing the packages by using password and encryption methods.

Monitored all the packages that were scheduled. Involved in debugging the Packages by utilizing SSIS features like breakpoints, Data-Viewers and custom loggings.

Written T-SQL test scripts for data verification and data validation before and after data loads respectively.

Produced and Documented test summary information and signed-off testing to facilitate implementation in production environment.

Created several reports using SSRS like parameterized report, drill through reports, linked and sub reports.

Created reports with bookmark and document map inside them to navigate the items inside the reports.

Managed very large reports that return a large volume of records and optimized their performance using techniques like cascading parameters and filters.

Deployed SSRS reports to the reporting server and assisted in troubleshooting deployment problems.

Create caching report, snapshot to reduce the burden of report server. Along with creating snapshot, both standard subscription and data-driven subscription.

Optimized reports by optimizing the embedded queries, or optimized the stored procedures that generate them.

Environment: MS SQL Server 2008R2, T-SQL, MS Visio, Windows Server, SSIS, SSAS, SSRS, Access, Excel Power

Pivot, SharePoint

Kaleida Hospital, Buffalo, NY 07/2010 – 10/2011

MS SQL Ser SSRS/SSIS Developer

Kaleida Health is the largest healthcare provider in Western New York. Founded in 1998, the organization supplies the area's eight counties with health services throughout five different hospitals and separate medical ctr.

The Project responsibilities are create a new data mart for the health and patient services of the hospital, perform ETL processing and build SSRS project to generate reports with desired format to present statistics information according to business requirements.

Responsibilities:

Participated in gathering User Requirements and System Specifications for dimensioning modeling of data warehouse.

Involved in designing conceptual, logical and physical model of the relational databases (OLTP and OLAP).

Involved in designing and applying constraints and rules for data integrity.

Managed the entire ETL process involving the extraction, analysis, transformation, loading and presentation of information to the end users.

Analyzed different heterogeneous sources like Excel, Flat Files, and SQL Server for ETL strategy that needs to be implemented with the help of SSIS packages.

Created staging database to perform various ETL data massaging operations such as data profiling, data cleansing and data verification.

Implemented Data conversion and data migration techniques at necessary times through the project life cycle.

Implemented transactions in SSIS by appropriately setting Transaction Option property to Supported, Not-Supported and required based on the type of strategy being implemented.

Written test scripts in T-SQL to validate data on staging tables and data warehouse tables to validate the data results.

Built SSRS project to generate reports with desired formats (Word, PDF, Excel, etc.) to present statistics information according to business requirements.

Created Ad-hoc Reports using Report Builder and Report Models using Business Intelligence Development Studio to provide a template for end-users to create reports by themselves with a preselected group of relational tables.

Environment: MS SQL Server 2008R2/08, T-SQL, BIDS, SSIS, SSAS, SSRS, Access, Excel.

Power Pivot

Hebei Jinmao Group, Hebei, China 09/2008 – 04/2010

MS SQL Developer

Hebei Jinmao Group is a multi-industry company in China, build in 1995 has sub-companies include real-estate, finance, IT and international trade.

The project goal was perform data migration from the legacy system to new system finish system upgrade.

Responsibilities:

Created documentation for the information collected from the users and got the approval to proceed further.

Analyzed BRD to create FRD by conducting JAD meetings.

Created T-SQL scripts to generate all the tables and relationships based on the finalized ER-Diagram.

Created Stored Procedures, Views, and User Defined Functions to facilitate the automation of data entry by a front end application.

Utilized the Dynamic SQL for user customizable queries to be answered by the SQL Server.

Implemented indexes on tables and views for optimum performance tuning.

Responsible for identifying sources, creating staging database, data warehouse dimensional modeling (Facts and Dimensions).

Designed the ETL process using SQL Server SSIS Packages which the data sources include Flat files, Access, and Excel.

Created SSIS packages for loading the data from various sources involving flat file and Excel.

Involved in database maintenance tasks like de-fragmentation, re-indexing, and recompiling.

Wrote custom T-SQL stored procedures and triggers to improve performance, preserve referential integrity, and provide additional application functionality.

Optimized the existing queries to improve performance with the use of Profiler and Execution Plans.

Environment: MS SQL Server 2000, 2008, T-SQL, Windows Server, SSIS/RS, SSMS, Erwin, TFS, Power Pivot, SharePoint

Contact this candidate