PROFESSIONAL SUMMARY:

• 8 years of IT experience in Business Intelligence, TSQL, ETL and reporting in multiple industries such as healthcare, banking, manufacturing, retail and logistics
• Experienced in SDLC of SQL Server instances including business requirements gathering, development, testing, deployment, documentation, production, and maintenance
• Substantial and solid abilities in developing T - SQL objects, such as UDSP, UDF, and views, in troubleshooting, in debugging and in performance tuning
• Experienced in data modeling, dimensional modeling, and data warehousing.
• Highly proficient in ETL processing and different data tools including SQL Server, SSIS, Azure ADF
• Rich experience in data profiling, data cleansing, data integration, and data migration
• Extensive knowledge of Azure SQL Database, Azure SQL Data Warehouse, Azure Data Factory, AWS S3 Bucket, AWS Redshift, AWS Glue and Google Cloud Services
• Strong ability to understand business processes and to fulfill with effective solutions
• Experience in creating reports, dashboards and data visualization using Power BI, Tableau and SSRS
• Extensive knowledge of statistical analytics, data mining, machine learning and predictive modeling using Python, R, SAS and SPSS modeler
• Team player with polished communication and analytical skills as well as the ability to work independently and collaboratively

TECHNICAL SKILLS:

Database: SQL Server 2008R2, SQL Server, Oracle Database 11g R2, MySQL, MS Access, MongoDB, Hbase, PostgreSQL, Snowflake, Redshift

BI/Development Tools: SSIS, SSRS, SQL Profiler, Power BI, Tableau, Looker, Spotfire, Qlik, SAP ERP, SAS Enterprise Miner, Salesforce, Visual Studio, Google Analytics, RapidMiner

Programming Languages: TSQL, MySQL, Oracle Pl/SQL, C#, Matlab, R, Python, VBA

Big Data & Cloud Service: Azure (Azure Data Studio, Cloud Services, Azure SQL Database, Data Factory, Backup/Recovery), AWS (Computing Services, Storage Services, Security and Identity Services, Networking Services, management tools)

Data Modeling Tools: Erwin, Visio

Source Control: TFS, Git, SVN repository, Project Management Tools; Jira, Sharepoint, Trello

SDLC: Agile, Scrum

DWH: Kimball, Inmon, Star Schema, Snowflake Schema

PROFESSIONAL EXPERIENCE:

Confidential, NY

ETL developer

Responsibilities:

• Participated in business requirements gathering for ETL process and reporting via communicating with different functional departments and separate database development team.
• Applied DDL, DML for modifying tables and views including patients, physicians, diagnoses, medication, primary and secondary payer, etc
• Created and altered constraints such as primary key constraint, foreign key constraint, unique constraint and check constraint to implement data validation and to realize business logic.
• Wrote over 40 stored procedures to extract data from OLEDB source, configured the dataset in SSRS for reporting purpose, and fulfilled complicated business logic, for example, patients’ visits information archiving, diagnoses process data summary, etc
• Created over 10 user-defined functions, both single value and table value, to implement data processing and calculations.
• Conducted performance tuning by making use of the execution plan, adding non-clustered indexes, covering indexes on specific columns to speed up DQL processing.
• Applied to debug with the help of SQL profiler to regenerate and to address errors.
• Conducted the ETL process on data flow in SSIS. Designed, tested and deployed over 50 SISS packages to extract patient diagnoses data from multiple sources, such as OLTP databases, Excel files, and JSON files, to perform transformation and to load the data into the data warehouse.
• Make use of Glue to build ETL process to crawl part of functional department’s data such as diagnosis processing to Amazon Redshift.
• Performed initial load and incremental load using SSIS and stored procedures. Applied create date and modify date method or Timestamp to do the incremental load.
• Executed error handling by setting up components to hold the error data, and then send back to business users by email or to developers for debugging purpose.
• Took use of log provider to log information generated during pre-execute, post-execute, on-error, etc
• Applied slowing changing dimension (SCD) type 2 and type 4 with SCD component for the incremental load of certain dimension tables.
• Responded to the tickets from the helpdesk system and explored solutions to do troubleshooting by checking data sources, destination, data flow structure, and connections, etc
• Implemented performance tuning on data flow such as replacing asynchronous transformation with synchronous transformation, adjusting control flow structures, etc
• Applied debugging by extracting error information from logging files, checking error output from components in the data flow, and setting up data viewers, etc
• Utilized Tableau to design dashboard with maps to display revenues of different hospitals, bar charts to compare the patients' visits to different departments.
• Used filters in tableau to apply more conditions and implemented some basic statistics like visits by the department by month, diagnoses by the department, etc
• Kept dashboard to be secured, designed views, objects like embedded web pages, URLs, plug-ins, and controlled web view security options
• Implemented interactivity in dashboards like show filters on tableau desktop or on tableau server to show cards, filters and added tooltips to show assistant context.

Confidential, New York, NY

SQL/ETL developer

Responsibilities:

• Gathered business requirements by participating JRD sessions and translated into appropriate BRD after JAD
• Created and altered mapping documents to support ETL process from multiple data sources such as OLTP, OLAP, CSV file, JSON file and other sources to new data mart
• Engaged in the Agile development environment. Communicated with the product team, sales team and front end developers as well as business owners to confirm and understand the business requirements changes back and forth
• Wrote 40+ stored procedures to realize the business and commercial logic, for instance, business owner monthly payment, average transactions per pos terminal by month
• Coded dynamic SQL in stored procedures to increase the flexibility and functionality
• Created 20+ views with aggregations to support internal data analysis
• Created and altered 30+ user-defined functions to realize complicated calculations (such as principal balance by category by month, the average number of transactions and ticket size by card groups and season) in SQL Server
• Created constraints such as foreign key constraints, unique key constraints, check constraints to implement business rules and to conduct data validation using TSQL
• Implemented performance tuning of TSQL queries using the execution plan. Created Indexes like non-clustered indexes, covering indexes in tables to speed up the DQL process
• Tuned over 60 TSQL statements, such as CTEs, User stored procedures, indexes to optimize package performance and replace the slow tasks and components
• Conducted debugging with the help of SQL profiler to locate the wrong codes or stored procedures which generate errors and then correct it
• Applied performance tuning in SSIS process including existing SSIS packages with new TSQL statements to perform data validation, data cleansing, data aggregation or other functionalities and utilizing parallelism, index strategies to facilitate the large volume of data extracting and loading
• Defined ETL process by creating 50+ SSIS packages for extracting, validating, transforming and loading data from multiple sources such as OLTP, excel files, XML files, CSV file into the proper data mart
• Performed initial load and incremental load on new data marts, Inserted and updated data like income, asset, tax amount, APR, fees and performed SCD type 2 and type 4
• Created staging tables with DDL to store data for transformation and complex calculated data
• Utilized execute package task to set up parent-child packages and applied sequence and foreach loop containers
• Set up event handlers, utilized custom logging with execute SQL task, script task and log providers to provide information for every execution and send the error messages to business users or front-end team via email
• Handled and deployed 60+ packages on the dev server and UAT environments
• Parameterized connection managers. Deployed packages using project deployment mode on the test server
• Conducted debugging by checking logging files to regenerate the error, setting up event handler to capture errors automatically. Created checkpoints to start over where execution failed and breakpoint allowing the execution to be paused to review the status of the data

Confidential, NY

SQL/Report developer

Responsibilities:

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

Confidential

SQL BI developer

Responsibilities:

• Wrote stored procedures and views on SQL server to extract and transform data for reporting tools including SSRS and Power BI
• Participated in the data modeling stage with the data modeler when designing a new data mart.
• Used indexes, hints, views and the execution plan to increase the performance of the query of stored procedure. Ensure the reports could be utilized by business users properly and quickly
• Utilized two import methods of Power BI, import mode and direct query. Made use of SSIS to combine data from different sources to create a proper database in SQL server for Power BI
• Made use of data page, relation page and query editor to create proper extra tables, adjusted required data format and rounding in Power BI, to meet the requirements of reports.
• Utilized DAX functions and queries to create new measures, columns, and tables. Performed time intelligence, mathematics calculation in Power BI. Applied regular business calculation in reports.
• Constructed reports based on the requirements of the business process in layout and function. Made use of different views, slicers, what if parameter and importing views including hierarchy slicer, enabled bookmark pane and selection pane, added drill through and conditional formatting, modified cross filters to create dynamic reports.
• Controlled security in row-level, data level, report level and sever level in Power BI desktop and server, with help from DAX functions, filters, and roles. Scheduled data refreshing with the gateway.
• Maintained and supported existing daily SSRS reports according to the requirements. Modified the layout of reports by adding or modifying parameters, data regions, and report properties.
• Shared reports using SharePoint and utilized snapshot and caching to increase the performance of Server

Confidential

SQL/Report developer

Responsibilities:

• Participated in requirements gathering through JRD meeting and discussion about the application of requirements through JAD
• Generated regular weekly reports with data region displaying data with grouping, aggregation, conditional formatting
• Made use of drill-down, drill-through to build connections between multiple reports. Made use of the different types of parameters including drop-down list parameter, cascading parameter to create dynamic reports. Constructed scorecard reports evaluating the performance of objects based on KPIs.
• Cooperated with the test team to fix the error on the Dev server and Production server by altering stored procedures, checking format and layout of reports, ensure the accuracy of expressions, variables reference.
• Created or altered stored procedures in SQL Server, applied report and group parameter to improve performance in SSRS
• Used Native mode to deploy reports and shared within the company
• Rendered reports in CSV, Excel, and pdf format in correct and accurate layout
• Helped the maintenance department on the Help Desk system of the company, solved issues of the product. Contacted with the business directly to maintain the internal department’s reports
• Generated and maintained the tables in SQL server to support the building of SSRS report
• Calculated columns, flags and perform string manipulation in the SQL server. Executed regular paperwork to help financial department, credit department and accounting department using excel, access
• Wrote or modified over 30 existing user-defined functions and stored procedures. The utilized system defined functions and stored procedures for assisting the transformation
• Added indexes, hints and utilized execution plan, SQL profiler and database engine tuning advisor to refine the scripts, improve performance
• Filled regular auditing and accounting forms in paper and excel to aid department operation. Made use of statistical functions in Excel, created pivot tables

Confidential

SQL Developer/analyst

Responsibilities:

• Performed data migration from a stable database to function-based data warehouses on a daily and weekly basis with an execution plan to ensure the performance.
• Created 60 + SSIS packages for extracting data from databases like SQL Server, oracle pl SQL, MySQL, loading the data into different destinations such as OLEDB, flat file, XML etc.
• Performed data migration from stable databases to the function-based data warehouse or data marts on a daily and weekly basis
• In MS Access, wrote over 20 macros in visual basic/VBA to perform small-batch data loading, automation and data preprocessing
• Built and configured SSIS ETL based solution for unit testing and QA test for production systems and scheduled the tasks by SQL server job agent
• Deployed SSIS package in .net to development and test servers, while also set up configuration files based on designed ETL strategy from BRD
• Maintained the existing ETL packages, including SSIS packages and stored procedures, according to the metrics and developed SCD in type 2 and type 4
• Used SSRS reports and functions, such as dashboard, drill down, drill through, scorecard, parameters to visualize business information such as customer premium rate and average fees for decision-making usage
• Utilized dynamic SQL, parameters, control flow statements, etc. into customized queries in SQL server for requirements of users and clients
• Normalized tables, performed joins and operators, created indexes, implemented transactions and checkpoints to improve the performance in the current workflow
• Created custom logging to log required information items, and put script task in the event handler to send email alerts
• Aggregated the querying logs and rejected data in ETL process into documents for future error handling