This is a keep going project. Whenever new data automation is needed in the department, I will work on it. I have developed many SSIS packages for this purpose and deployed the packages and scheduled the package jobs so that the data automation can be done.

As the transportation analytics and operation research (TAOR) department gets new physical machines for SQL Servers and new SQL Server edition, database migrations and new data sychronizations are required. I performed the database migrations and resolved any migration issues due to database versions, cross network domain security restrictions, data mismatches, performance degration due to virtual machine configuration and database migration time window restrictions.   

The TAOR department has an algorithm pool for demand forecast time series and we forecast package volume weekly for each hub for more than 4000 Hubs. Some of the forecasts have low accuracies due to low quality data or non-stationary data. Based on machine learning technology and time series algorithms I developed two new algorithms/models to enhance forecast accuracies for the bad data cases. The test showed the two algorithms could really get better results than other off-shelf algorithms for some cases. 

The target is to make daily forecast on different service types for the next year for each hub (total number of hubs is more than 4000) for the volume of small packages. It is not feasible to directly apply time series algorithms to this task due to huge amount computation and very bad accuracies. Then the stratigy and algorithms were developed. Time series algorithms applied to forecast monthly volume for each hub. Then I developed an allocation algorithm based on history data to get allocation percent for each hub for each day in the future. The new algorithm can allocate monthly volume to daily volume to improve the accuracy and drastically reduce the computation cost.

Whenever find slow performance in SQL Server databases, I do the performance tunig which includes: create suitable indexes to speedup queries, modify stored procedures to change the query structures and change stored procedures into SSIS packages to implement parallel executions. 

My job in this project included data collection from production server and remote oracle databases, data cleanse, data aggregation and query optimization so that the Power BI dashboard reports can run quickly with accuracy. 

This project dealt with medical information related to doctors, nurses, physician assistents, hospitals, medical offices, drug stores, medical related lisences and policies. Working on HPCC (High Performance Computing Cluster) system with 400 nodes to do big data ETL solution that filters, cleanses, compares data and transforms data into specific formats required by different clients. Delivered different solutions for 12 different clients (CVS, Walgreens and many hospitals). The language used is ECL (Enterprise Control Language) which is similar to HADOOP and was developed by LexisNexis

Collection data from hundreds of data sources and using 400 hundred servers for MapReduce calculation, an unsupervised clustering model is established based on algorithms of feature matching such as company names, addresses, contact information, business owners and other published public information. The model groups data records into clusters so that each cluster represents a business identity in USA. This is a team work and a big data project. I was one of the major player in the team. The clustered data is served as the base for modeling credit score evaluation, fraud detection, auto insurance and many other fields.

Finished three database migrations using T-SQL stored procedures and SSIS packages to centralize and normalize customer data to reduce a lot of cost for data maintenance. The major challenges were: the source databases have different table schemas than the target database so that I have to make sure records are logic and consistent after migration; need to work with business analyst to clean a lot of conflict records/results; the migration process have to be done in limited time windows so that the normal business should not be interrupted and hence the performance speed of the migrations must be high enough.

Developed most of the SSRS (SQL SERVER Reporting Services) reports for the Ista B2B sites and played the administration role to manage this report web site.

Designed database tables for very large volume data. This has been done by using database partition, index partion, carefully picking data types for table columns, carefully design table indexes and creating tables to save aggregated data. In the implementation phase, stored procedures were carefully developed and performance tuned.Sliding window skill was applied when phase in and phase out data in partitioned tables.

Design and lead a team implementing the EmailSniper system that works with Port25 email software.  EmailSniper system is used to send campaign emails and transactional emails, to track the status of sent emails, to generate statistics reports and status reports of the emails. The system handles up to 100,000 emails per hour without noticeable impact on database performance.

FinCast (expense/budget/revenue forecast strategy) projects. It used a web component OWC to develop so that the web page looks just like Excel worksheet and also has the Excel formular functionality. My responsibilities include design, setting coding standards, code review, deciding technical solutions and providing technical support for the off-shore team.

Innovatively developed an organization chart style fund allocation application that uses drag-drop to set up fund structures and computes the fund return allocation to the investors. Comparing to an old Excel application there are multiple times of increase in performance and easy of use. The main technology used was Microsoft Vector Markup Language (VML).