Andy Zhang

I created the full solution for an automobile lead, sales, lease, and finance data warehouse from data modeling, ETL, reporting, and business intelligence.

Technologies used include T-SQL, SSIS, SSRS, SSAS, C#, ASP.NET MVC.

I implemented many change requests and enhancements to the client management system for the RBC wealth management group.

I fixed glitches in the new bulk trading tool and commission calculating applications.

Technologies used include ASP.NET, C#, MS SQL Server, and Oracle.

I developed many web and batch investment portfolio management applications including daily loading, transmission, and reconciliation of client positions and market data from various sources for operations, an investment model communications application for RBC Global Private Banking, equity transaction summary applications for compliance group, and more.

I converted all the existing applications written in MS-Access and Excel/VBA applications to web and Windows services applications and jobs—significantly increasing efficiency and reducing the cost and risks.

I also coded complex SQL scripts and stored procedures to handle a large amount of financial data.

In digital marketing, calculating different levels of leads to sales buy rate are key measures. We had coded complex T-SQL scripts to calculate various leads to sales buy rates. But there was a challenge to put these measures in a cube for data analysts and business users to do slice and dice themselves.

The task fell on me in the end. After intensive thinking and research, I recreated certain data models and redesigned cubes and successfully enabled users to be able to slice and dice to get those key measures themselves. With the new data models, SQL scripts to calculate those measures have also been simplified.

This methodology used has been applied to all similar cases in other subjects in the company.

Performance is very important to online reports. However many existing reports experienced performance issues when data were becoming bigger and bigger.

I took the initiative to solve the performance issue by redesigning certain tables, rewriting scripts and building proper indexes etc. All report performances have been improved in the end.

Some large reports even show up in seconds instead of minutes. In the end, users were very satisfied.

Some key data was stored as a JSON string in the OLTP database.
There were also different versions of JSON with the system upgrade as time went by. It was quite a challenge to load and parse the JSON data to the data warehouse.

I created an innovative way to accomplish this by using SSIS and SQL scripts to load and parse JSON data in different versions.

The solution saved a lot of time and kept data integrity and completeness in the data warehouse with each system upgrade of OLTP applications.