SUMMARY

• More than 10 years’ experience of Azure SQL database, Azure SQL Data Warehouse, SQL Server 2008/2012/2019 , Oracle 10g/11g, Access 2007/2010/2013 database.
• Proficient with full Microsoft Business Intelligence tools Azure Data Factory, Azure File Storage, SQL Server Intelligence Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Power BI reports.
• Have multiple extract, transform and loading (ETL) skill sets SSIS, Azure Data Factory, Python.
• Designed and implemented multiple data warehouses and cubes by SSAS.
• Developed reports by SSRS, Power BI Services, Tableau, Crystal Reports and Excel.
• Multiple professional experiences leading projects on Software Development Life Cycle (SDLC) by Agile Scrum Methodology for database design, data ETL, data warehouse design and SSAS cube creation, Excel, SSRS and Tableau reports generation.
• Built Key Performance Indicators in Cubes, created data mining model for business forecasting.
• Robust MS Access and Excel skills with Pivot tables, Power Pivot, Power Query and Macro by DAX or VBA.
• Strong TSQL programing for data cleansing, transformation, and table join, stored procedures.
• Imported and exported files like JSON, Excel, CSV, TXT, SQL tables between external data sources and internal SQL servers etc.
• Collected and analyzed business requirements from users, developed SSIS package to incrementally load data from ftp or Azure Blob Container into Azure SQL databases, designed and implemented data warehouse, scheduled daily loading jobs, created Power BI reports, and scheduled daily refreshment.
• Proficient with ETL/ report quality assurance, query/report performance tuning, data warehouses, cubes and reports after go - alive maintenance and enhancement.
• Professional experience of most advanced SQL Server 2008/2019 Database server installation, login and user creation, role, and permission configuration.
• Have strong business sense for business data analysis, strong corporate finance analysis ability for income statement, balance sheet and statement of cash flows, and excellent accounting analysis ability.
• Strong team player with great verbal, written communication, and interpersonal skills to lead and support coworkers to collect and analyze business requirements correctly, analyze data source, fix query logic and report quality assurance to complete work successfully.
• Quick learner, take initiator, hard worker, multiple tasks worker under pressure, willing to take responsibilities, excellent ability to approach problems effectively and trouble shoot users’ problems efficiently.
• Multiple industries’ experience in manufacturing, healthcare, retail, and Bank.

TECHNICAL SKILLS

Database: Azure SQL database and data warehouse, SQL Server 2008/2012/2019 , Oracle 10g/11g, MSBI 2008/2012/2019 , SSIS, SSAS, Access 2007/2010/2013 , Excel 2016, Unix.

Reporting: Power BI, SSRS, Tableau, SAP Crystal Reports 2008/2011, Access 2013, Excel 2016

Language: SQL, T-SQL, Excel Visual Basic, Excel DAX, Excel M code, Python.

Internet: HTML, MS SharePoint

MS Office: Word, Excel, PowerPoint, Access

PROFESSIONAL EXPERIENCE

Confidential

Data Engineer

Responsibilities:

• Built Azure data factory with pipeline to load json, csv files into Azure data warehouse, sent out daily loading summary (records such as extracted, Inserted, Updated, table names etc.) email to the team.
• Designed general ledger data warehouse, implemented, and incrementally loaded 12 different customer’s general leger files into sql database.
• Imported all kinds of files such as Excel, JSON, TEXT, CSV and tables from linked server into sql databases, exported sql tables to csv files by SSIS packages, and scheduled jobs based on daily or monthly.
• Developed some Power BI reports such as Visa and Mastercard Billing report and paginated report with cascading parameters as filters.
• Developed Alloy Data Reconciliation Power BI report with charts and drill though functions.
• Developed User Balances Excel power query report with date range as filters.
• Developed a query to generate calendar table.
• Created email groups, email box, database mail accounts and profiles, developed sql database email notification template including Job name, job owner, commend, failure step, failure message, run date time, duration etc.

Confidential

Sr. Application Developer

Responsibilities:

• Converted a series of batch files into a SSIS package, used a Foreach Loop Container to loop over a list of FromPatient and ToPatient pairs to merge 60 patient related tables; set dynamic FromDate and ToDate for the package parameters, set variables to pass over to the loop container, and update tables based on variables, loaded data log table by Execute SQL task, loaded SSIS error log table by Event Handlers, send summary email to BI team automatically, scheduled the job to automation the whole merging data procedure in production.
• Created Access Macro to Load Access tables into Excel tables, and then created a SSIS package to load Excel tables into SQL database, and loaded tables daily.
• Created Excel Macro to convert non structured Excel sheets’ patients’ data into standard table, then imported into SQL server, finally run the QA query to report on missing patients’ MRN number records, and sent results to users.
• Created a Tableau Dashboard for Research Trial Patients Data with multiple parameter filters like Age, Race, Gender, and Year at Registration Date etc.
• Developed various SAP Crystal reports for financial reports, configured multiple output formats filters (group by different hierarchies) in one report, configured FromDate, ToDate, PatientMRN filters, total records count etc.

Confidential

Sr. Data Warehouse and Report Developer

Responsibilities:

• Leaded full SDLC as scrum master with Agile scrum methodology, designed Sales data warehouse for Merchandising department: collected business requirements from internal clients and sales reports, translated into business intelligence requirements, did data discovery and data mapping, designed data warehouse, extracted, transformed and loaded data into fact table and dimension tables, implemented daily incremental loading to data warehouse, developed cube, deployed SSIS package into production system, scheduled the job, and maintain the production, generated SSRS, Tableau, Excel reports from data warehouse and cube, did data quality assurance.
• Designed and implemented General Ledger data warehouse and cube for Finance department: collected business requirements, studied financial and accounting reports, architected data warehouse model, coded initial full loading and daily incremental loading to fact and dimension table, developed cube, deployed to the production, subscribed daily email to indicate data warehouse and cube processes status.
• Coded daily or weekly QA scripts to monitor data warehouses and cubes data accuracy, and scheduled jobs to subscribe email to BIAdmin group.
• Trained the users to customize the data set in M code in Excel pivot table by connecting with cubes, run SQL/MDX queries by Power Query in Excel by connecting with data warehouse tables or cubes.
• Modified “DayName” field in Date dimension table, added “ItemCost”, “ExtendedCost” fields in Sales fact table and cubes, added “Yesterday”, “WeekToDate”, “YearToDate”, “Rolling12Weeks”, “PriorRolling12Weeks” in Date dimension table; added “Margin”, “Margin %”, modified the store location hierarchy, created Key Performance Indicators, created Data Mining model in cubes.
• Helped consulting company to complete the reports about Front of Store Initiative Tracking, fixed the query logic and tune the performance, made it refreshment automation by macro in Excel workbook and maintained the reports.
• Re designed and developed a SSRS report for Vendor Finance Management to replace a wrong report by Cobol about event number, event start date, event stop date, SKUs’ number, units sold, vendor number, vendor name, scan down dollar amount for one specific event number. The total dollar amount will be reimbursed to company from vendors. And developed another SSRS report for all overlapped events to get SKUs, units sold, vendors, total scan down dollar amount at same event SKUs, same event stores and same event dates for any transaction period.
• Created a slowly changing dimension table for Pharmacy Cardinal Daily CatalogID pricing log, based on National Drug Code as business key as type 0 field, used EffectiveDate, EndDate, CurrentFlag to track the drug price changing, all other fields changing as type 2 fields.
• Developed SSRS report by coding complex nested loop query for Accounting department about account payable year to end report with Invoice, date, vendor, postdate, store, account, amount, discount, creator, PO and retail.
• Made pivot table in Excel for making strides fundraising event analysis by store, by pharmacy, by region, by district, by store ranked, by pharmacy ranked by using Power Query M code, made it daily refreshment automatically in program period.
• Created a TLOG audit table per day per store customer counts, units sold, basket size for merchandising store and pharmacy store in production, did full initial loading and daily incremental loading, and scheduled a job for QA script and email to BIAdmin group weekly.
• Successfully completed adjusted Week 53rd project: created adjusted views for sales transaction tables, adjusted fiscal calendar table; adjusted Sales cube, Inventory cubes; adjusted core SSRS reports; helped users to modify the daily routine queries with adjusted views and tables; created new adjusted fiscal calendar chart.
• Fixed a Merchandising Sales Dashboard SSRS report: questioned sales type - promotion calculation, corrected business requirement for promotion, wrote query for promotion calculation for original offshore report developer.
• Fixed a Store Overtime Summary SSRS report requested by store operation manager, solved store data missing, optimized the report format, removed redundancy filter, made Tableau visualization chart for detail store sales analysis.
• Developed basket analysis Ad hoc reports about return with receipts and without receipts.
• Did lots of business analysis among transaction log tables, promotion event price, sales trans counts, layaway trans counts, commercial trans counts, discount, returns, sales price, tax, payment type, account categories.
• Created Complex workbooks and Dashboards by connecting to SQL data warehouse in Tableau desk, and published and scheduled job on weekly basis in Tableau server.
• Did lots of quality assurance for offshore team’s reports: business requirement analysis and recollection, query logic correction, data validation, report performance tuning.
• Developed Tableau report ----Daily Dept Comp Stores Report, includes today Sales/ Scan Margin this year, today last year, this week this year, this week last year, month to date, year to date, variance, variance percentage etc.
• Created Tableau report for Week To Date, Month to Date Comp store, with Region, with District, with District with Drill Down.

Confidential

Sr. BI Developer

Responsibilities:

• Leaded full SDLC as scrum master with Agile scrum methodology, designed data warehouse for financial department: collected business requirements from internal clients, translated into business intelligence requirements, did data discovery and data mapping, designed relational database, loaded data into relational database, designed data warehouse, loaded data from relational database into data warehouse, generated SSRS reports, did data quality assurance, deployed package into production system, scheduled the job, and maintain the production.
• Loaded APL flat file from shared drive into SQL database, split the file into required columns into specific tables using TSQL while loop, put the APL file back to archive folder with current date, updated table with nested IF Else in SQL database based on the UPC code in the split tables, generated log table, deployed SSIS package into SSMS production system and scheduled the job for this whole package.
• Created Complex workbooks and Dashboards by connecting to SQL data warehouse in Tableau desk, and published and scheduled job on weekly basis in Tableau server.
• Designed a data mining model of wine sales for Sales and Marketing department,: used wine database, built data source view, applied data mining wizard to choose key, input and predict, took decision tree mining model algorithm to narrow down target and potential customers by wine buyers, gender, occupation, yearly income and family members etc., evaluated the model with current customers data, deployed solution to SSMS production as a powerful decision support mechanism for marketing development analysis.
• Made SSRS reports and deployed into SSMS includes parameter reports with filters, matrix reports, dashboards for financial departments and store line management.
• Configured ODBC connection and designed excel macro with VBA to extract data from remote SQL server.
• Built a dynamic Excel financial report to show department results for current period vs. last period vs. same period last year as well as year-to-end current period vs. last year; allowed dynamic views for different time period chosen, and built controls with macro button to automatically ensure accuracy.

Confidential

Data Analyst

Responsibilities:

• Created SSIS package for incremental loading using hash values to insert or update rows in data warehouse: created three tables in SSMS for staging table, updated table and destination table. The updated table was added the surrogate key comparing to staging table, and the destination table had the surrogate key column as primary key, alternate key column, a new HashValue column and other columns same as staging table. In SSDT created package with tasks such as Execute SQL task for SQL Count Current Rows, Execute SQL task for SQL Audit Begin, Execute SQL task to delete staging table and updated table, Data Flow task 1 to import source data, Execute SQL task to clean data in staging table, Data Flow task 2 to load data from updated staging table into Destination table, Execute SQL task to update Destination table and finally Execute SQL task for SQL Audit End.
• Developed reports, dashboards, and scorecards using Tableau.
• Implemented SSIS package for data warehouse dimension tables loading for sales.
• Implemented SSIS package for data warehouse fact tables loading for sales.
• Created SSIS package that imported the data from a list of files into SQL database table and then moved these files with current date included in the file names to a new archive location.
• Created SSIS package to load a non-standard text file in which a separator row precedes a product record row that was followed by an indefinite number of spare parts rows and parsed it into properly normalized product and spare parts destination tables by using the Script component with C#.
• Designed a Sales Revenues report with parameters as filter from SSAS Cube using MDX to analyze Products sales by product, product line, region, country, periods etc.
• Created new database, new tables, loaded flat/ Excel files to SQL new database by bulk insert, bcp or Import/Export wizard, created indexes, joined multiple tables on specific condition, applied single row functions to do data cleansing, calculation or conversion into new data format, and generated multiple types of reports.
• Moved the data between Hadoop big data and SQL server database by running sqoop import/export shell program.

Confidential -Kansas City

Data Analyst

Responsibilities:

• Designed a SSRS matrix report, Surgery Revenues Analysis for Saint Luke’s Hospital of Kansas City through our Supply Chain and Operations management department as a coop project. Gathered business requirement, did data discovery from the hospital databases, translated business requirement into technical specification, and developed the report: added the surgery cube as data source, created MDX query to build dataset, added parameters as filters to check different locations’ surgery sales revenues according to different surgery types, surgery names, adults or youths and children etc.
• Designed a SSRS matrix report, Length of Stay Analysis for Saint Luke’s Hospital of Kansas City which analyzed providers and days in advanced to book surgery, made the detail table, row percentage report, column percentage report, detail surgery counts reports, column chart, pie chart and column chart with running total value chart: created stored procedure to build dataset, the main idea was to define different periods by case when T-SQL; created customer defined function to handle multiple selection parameters, added parameter datasets to display different report contents and appearance.

Confidential

Data Analyst

Responsibilities:

• Created SSIS package to update an Employee Pay History table from a SQL Server Human Resources database.
• Created an Account Payable report in SSRS to list all invoices with no or partial receipt with a balance criterial based on current date, colored the numbers based on values of balance through report parameters, and set interactive sorting function on balance value by two conditions at the same time: In account receivable database, created a stored procedure as dataset, used Case When T-SQL to divide receipt aging logic; added report parameters to display different color background for requested receipt balance by setting the expression with switch function.
• Created a navigation form in Access database for different brand electronic products testing follow up including different brand Products List form, Search form, Product Specification form, Product Testing Situation, Product Evaluation form and added Macros to have the tabs/windows on the