PROFESSIONAL SUMMARY:

• Five - year experience of business intelligence, ETL, and reporting in various industries such as insurance, healthcare, and banking.
• Experience in SDLC of SQL Server instances including requirement gathering, development, testing, debugging, deployment, documentation, production, and maintenance.
• Proficiency in ETL (extracting, transforming, and loading) process and different data tools, such as SQL and SSIS (SQL Server Integration Services).
• Experience in data profiling, cleansing, integration, migration, and presentation.
• Solid abilities in developing T-SQL stored procedures and troubleshooting, debugging, and performance tuning in SQL Server related tools.
• Advanced knowledge in creating complex reports with data visualization tools such as SSRS (SQL Server Reporting Services) and Power BI by using T-SQL scripts and stored procedures.
• Experience in data modeling, dimensional modeling, data mart design, and implementation.
• Ability to understand business processes and to implement effective and efficient technical solutions.
• Experience in conducting both JRD and JAD sessions with end users, management team, tech team members, and QA teams.
• Team player with polished communication and analytical skills, with the capability of working both independently and collaboratively.

TECHNICAL SKILLS:

Databases: SQL Server 2008R2, SQL Server 2012-2017, Oracle Database 11g R2, MS Access

Structured Query Languages: T-SQL, MySQL, Oracle PL SQL

BI/Development Tools: Visual Studio, SSIS, SSMS, SSDT, SQL Profiler

Reporting Tools: Power BI, Tableau, SSRS

Programming Languages: C#, SAS, R, Python, Visual Basic

Big Data & Cloud Service: Azure (Azure Data Studio, Cloud Services, Azure SQL Database)

Data Modeling Tools: Erwin, Visio

Source Control: TFS (Team Foundation Server), SVN repository

Collaboration Tools: SharePoint

Job Scheduling & Work Automation: SQL Server Job Agent

SDLC: Agile, Scrum

DWH: Kimball, Inmon, Star Schema, Snowflake Schema

PROFESSIONAL EXPERIENCE:

Confidential, New York, NY

ETL/BI Developer

Responsibilities:

• Translated business requirements to technical counterparts based on gathering, clarifying, and analyzing end users’ requirements after performing JAD sessions.
• Documented data mapping files to support end-to-end ETL processes, including creating/modifying 30+ tables like feedback, review, follow up, etc. in current database and data warehouse.
• Defined ETL processes by creating 60+ SSIS packages for extracting, validating, transforming, and loading data from heterogeneous sources (flat files, Excel, Access, and OLAP data warehouse) into the proper data marts.
• Performed initial and incremental load on new incoming data. Processed data profiling and data cleansing with pre-staging and staging table.
• Inserted and updated data (income, tax, interest rate, etc.), especially performed SCD (slowly changing dimension) Type 2 and Type 4.
• Set event handlers, utilized custom logging with execute SQL task, script task and log providers to provide information on each execution and to send summaries to users and ETL team via email.
• Handled and deployed 60+ SSIS packages on development and UAT environments.
• Tuned the existing SSIS packages with new T-SQL statements to perform data validation, data processing, data correction, and other functionality in SSIS.
• Utilized parallelism, partitioning, batch processing, and indexing strategies to facilitate large volume of data extracts and loads.
• Created 80+ T-SQL batches, stored procedures, user-defined functions in SQL Server.
• Developed 20+ views to create the custom tables and to prevent unauthorized access.
• Created scalar-valued and table-valued functions in SQL Server to implement complex business calculations (principal, remaining balance, AR, AP, etc.).
• Implemented transaction statements on DML activities and performed locks (exclusive lock, shared lock, etc.) to improve performance and to protect data integrity.
• Used CTE to do data cleansing, to remove expired data and replicate records, to fetch the latest modified data, and to implement hierarchy concepts in the organization.
• Involved in performance tuning of database and T-SQL queries using execution plan. Created indexes(clustered index, non-clustered index, filtered index, column store index) on database objects.
• Designed drill down, drill through reports like individual retirement plans and life insurance product reviews for clients in SSRS.
• Created monthly premium snapshot reports based on the data analysts’ requirements and utilized data-driven subscriptions.
• Generated KPI dashboard reports using DAX and visualizations with Power BI according to the measure and dimension required by end users.
• Collaborated with team members using TFS for source control and for maintaining repository of T-SQL objects, SSIS packages, and reports.

Environment: SQL Server 2016, T-SQL, SSIS, SSRS, Power BI, SQL Profiler, Erwin, TFS

Confidential, Baltimore, Maryland

ETL/BI Developer

Responsibilities:

• Used DDL and DQL for creating and modifying 30+ tables and views (patients, physicians, diagnosis, equipment, facility, medication, primary and secondary payer, etc.).
• Created constraints (primary key constraint, foreign key constraint, check constraint, etc.) to implement business rules and to do data validation using T-SQL.
• Wrote SQL queries with techniques such as CTE, wildcard, system & user-defined variables, system & user-defined stored procedures, etc. to perform data cleansing, preventative error handling, data manipulation, and so on.
• Implemented complicated business logic like patients information archiving, using stored procedure, dynamic SQL, table variables, temp tables, while loops, cursors, and so on.
• Used joins, operators, and subqueries for complex T-SQL queries involving multiple tables.
• Developed 10+ user-defined functions for data processing and for data calculation (string split, business days, subtotal and total bill amount, inventory turnover, etc.).
• Utilized execution plan and SQL Profiler to optimize queries and to enhance database performance.
• Added non-clustered indexes and covering indexes to improve the performance of data retrieval.
• Managed SQL Server 2012 in development and test environments.
• Interfaced and communicated with team lead, managers from various departments (administration, pharmacy, operating room, etc.), and BA for gathering user requirements and wrote functional requirements based on user requirements.
• Designed 50+ SSIS packages to automatically extract patient feedback data from multiple sources (CSV files, OLTP databases, Excel, etc.), to perform data profiling, transformation, and to load into the patient care date mart.
• Used healthcare data code such as ICD-10, CPT-4 to retrieve medical record data.
• Maintained daily/weekly/monthly incremental loads on billing, procedure, and other required data.
• Created both ETL and ELT, and implemented parallelism and bulk inserts for loading roughly 50 million data.
• Responded to tickets from Helpdesk system and troubleshot the existing issues (data, code, connection, etc.) to solve the problems.
• Performed unit testing on 50+ SSIS packages, and collaborated with offshore QAs during the testing period. Worked with DBA in package deployment and automatic job scheduling using SQL Server Job Agent.
• Handled SSIS errors with event handlers, redirecting error output, data viewers, checkpoints, breakpoints, customized logging, log providers, and auditing, etc.
• Wrote/tuned 60+ T-SQL statements, including stored procedures, CTEs, indexes, etc. to optimize SSIS package performance and to replace the slow tasks and components.
• Generated/maintained SSRS reports for claim billing, payment, patient clinical history, and health plans, using techniques like cascading parameters, multi-select parameters, custom functions, etc.
• Utilized expressions and parameters to handle conditional formatting, filtering, and grouping in SSRS.
• Designed KPI dashboards using Power BI desktop. Employed stack bar charts, and scatter plots for different dimensions (physician, primary diagnosis, responsible party, procedure, etc.).
• Transformed old reports in SSRS to Power BI with original layouts and contents by modifying the expression to DAX function and embedded datasets to shared views.
• Created scorecard and dashboard reports in Tableau using bullet graphs, box plots, reference distributions, and dual axes charts.
• Developed and documented SQL Server stored procedures, ETL processes, SSIS packages, and SSRS reports for the development team.
• Added BI projects and SQL Server instances to TFS for version control and for bug tracking.

Environment: SQL Server 2012, SQL Server 2014, SSIS, SSRS, Tableau Desktop 9.x, Erwin, Power BI, TFS

Confidential

SQL/BI developer

Responsibilities:

• Specified scrum methodology during the process of development considering SDLC.
• Engaged in daily scrum meetings with members from different teams for cross-team cooperation in agile mode.
• Prepared BRD and FRD by communicating with front-end web developers, end users and management, tech members, QA testers in JRD and JAD sessions as well as data mapping discussions.
• Encapsulated complex SQL statements into views and ad-hoc reports and displayed the final result to end users.
• Wrote T-SQL queries to focus on data integrity, and to deliver data consistency across branches in risk areas (credit risk, interest rate risk, exchange rate risk, etc.).
• Expert in join statements and operators in T-SQL to merge 30+ tables into one across 40+ variables such as CapEx, book-to-price, sales growth, market capitalization, total accruals, etc.
• Proficiency in normalization and de-normalization of existing tables for faster query retrieval.
• Wrote T-SQL scripts to do data cleansing and preventative error handling: deleted missing or unqualified values in columns like closing price and E/P ratio, removed any outliers present in the variables.
• Created SAS macros with parameters to observe the cumulative abnormal returns on the target stocks and established a remote connection to the WRDS Unix server to run SAS script.
• Used data manipulation packages in R language. Performed all kinds of descriptive analysis, correlation analysis, and data visualizations, taking good use of PerformanceAnalytics, psych, dplyr and ggplot2 packages with R.
• Tuned 20+ existing T-SQL scripts with execution plans and database tuning advisor using indexes, UDSPs, and UDFs.
• Involved in complete SSIS life cycle in creating SSIS packages, building, testing, deploying, executing packages in both environments (development, UAT, and production).
• Developed 30+ SSIS packages to perform incremental load process, by using transformation components in data flow task like lookup, conditional split, derived column transformation, merge join, and by using tasks and containers, like script task, execute SQL task, foreach loop container.
• Designed error handling and custom logging in SSIS packages using SQL Server log provider and execute SQL task.
• Created monthly SSRS reports on interest payment and remaining balance calculation to provide data visualization, such as line chart, bar chart, conditional formatting, with visually appealing and reports interaction.
• Utilized drill down, drill through, and scorecard report with dynamic grouping and interactive sorting in SSRS for business decision, like market share performance, industry overview and so on.
• Built portfolio summary reports using expressions, dynamic matrix, drill down and indicators in SSRS to help presentations on different portfolios, like tech, energy, transportation.
• Designed KPI dashboard reports for management using advanced filtering, analysis, drill down and parameters in Tableau.
• Developed story dashboards with quick filters on demand in Tableau Desktop and published them on Tableau Server for presentation.

Environment: SQL Server 2012, SSIS, SSRS, Tableau Desktop 7.0, Tableau Online, Erwin, Visio, Excel, SAS, R

Confidential

SQL/SSRS developer

Responsibilities:

• Collaborated and Communicated with BA, end users and developers to translate from business understandings in BRD to technical languages in FRD and to ensure business and system requirements are met.
• Wrote 60+ stored procedures to provide values for datasets and parameters in SSRS reports.
• Created complex SQL queries with techniques like subquery, wildcard, join, CTE, view, etc. to combine various tables into a single result set, and to perform business calculations.
• Used SSRS to summarize the financial accounting information for each demander and supplier, such as current inventory (raw materials, work-in-process, and finished goods), the total debt amount, account payable, account receivable, realized cash flows, etc.
• Generated cached and snapshot reports on existing customer financial information, and set subscriptions on data-driven reports as required by business development department.
• Designed and created 10+ report templates, including list, matrix, cascading parameters, multi-option parameters, drill through, sub report, column charts, line charts, and pie charts, to show the short and long term profit & loss rate on the products in the business portfolios in SSRS.
• Developed 30+ ad-hoc reports with complex T-SQL scripts based on various user requirements.
• Scheduled report deliveries on a daily, weekly, monthly, quarterly basis utilizing the standard and data-driven subscriptions in native mode.
• Supported Marketing department to integrate all the product information in visualized posts and interactive dashboards by using T-SQL and SSRS.
• Maintained 20+ reports in SSRS, including troubleshooting and performance tuning in stored procedures and report components.
• Participated in designing destination tables in SQL Server database and implementing ETL processes to migrate data from third-party files to data marts.
• Identified and documented approved SSRS report standards and best practices for tech team.
• Attended meetings with test team and tech team lead to perform regular code review and to ensure reports on UAT servers were correctly performed.

Environment: SQL Server 2008 R2, SQL Server 2012, SSIS, SSRS, Excel