Andy Zhang

I designed and developed a comprehensive data warehouse solution for automobile lead, sales, lease, and finance operations, covering data modeling, ETL processes, reporting, and business intelligence. I leveraged a robust technology stack, including T-SQL, SSIS, SSRS, SSAS, C#, and ASP.NET MVC, to ensure seamless integration, efficient data processing, and insightful analytics.

In digital marketing, accurately measuring lead-to-sales conversion rates is essential. To achieve this, we developed complex T-SQL scripts to calculate various buy rates. However, a significant challenge arose—integrating these key metrics into a cube that would empower data analysts and business users to independently slice and dice the data.

After extensive research and strategic problem-solving, I redesigned critical data models and restructured cubes, successfully enabling users to access and analyze these key measures seamlessly. Additionally, the revised data models simplified the SQL scripts used for calculations, enhancing efficiency.

This innovative methodology has since been applied across multiple domains within the company, ensuring consistency and streamlined data analysis.

Optimizing performance is critical for online reports, but as data volumes grew, many existing reports began to experience significant slowdowns.

Recognizing this challenge, I proactively redesigned key tables, optimized scripts, and implemented proper indexing strategies to enhance performance. As a result, report execution times improved across the board, with some large reports now loading in seconds instead of minutes.

Ultimately, users were highly satisfied with the enhanced efficiency and responsiveness.

Key data was stored as JSON strings in the OLTP database, with different versions evolving alongside system upgrades. This posed a significant challenge in loading and parsing JSON data into the data warehouse efficiently.

To address this, I designed an innovative solution leveraging SSIS and SQL scripts to seamlessly process JSON data across multiple versions.

This approach streamlined data integration, preserving integrity and completeness with each OLTP system upgrade while significantly reducing processing time.

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 the compliance group, and more. Existing applications written in MS-Access and Excel/VBA were converted 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.