The Ohio State University Alumni Association - IMPACT to AIM Database Synchronization

Arisen served as the technical lead and managed the technical tasks and activities of the team that completed the IMPACT to AIM Database Synchronization project from November, 2007 through August, 2008.

The Ohio State University had a non-normalized database that stored demographic data for OSU students, alumni, and donors. This database was maintained by the Alumni Development Information Services (ADIS) department at the university. The name of the database is IMPACT (Information Management for Philanthropy, Alumni, Communication and Tracking).

The Ohio State University Alumni Association (OSUAA) is a non-profit organization that manages relationships with alumni of the university. To assist in this process the OSUAA purchased a web-based management system for non-profit organizations and had it customized to fit their needs. The customized version was named AIM. The OSUAA needed a process to automatically synchronize names, addresses, and other demographic data with the IMPACT database on a nightly basis.

Services provided by Arisen as part of the synchronization project included:

• Working closely with Alumni Association IT Director and Database Administrator (DBA) to quickly understand the business process for the synchronization project
• Managing technical tasks and providing technical direction to development team for synchronization stored procedure development including optimization and error handling
• Helping to define data transformations needed to transform data from non-relational University database to highly normalized Alumni Association database
• Helping refine data difference algorithms used to determine which records had changed in the IMPACT database since no change dates or other reliable indicators existed
• Suggesting technique for writing scripts in a way that allowed them to be tested against one set of databases in a test environment and deployed to other databases in production environment quickly and without alteration – speeding deployment and eliminating errors. Implemented technique using Synonyms.
• Coordinating deployment to test and production servers
• Creating ASP.NET web application to allow OSUAA personnel to request University ID numbers for new Alumni Association enrollees.

In the course of these above duties, Arisen's technical administration responsibilities involved use of the following tools:

• Visual Studio 2005 – C#
• SQL Server 2005 –T-SQL, SQL Management Studio, SQL Server Profiler, SQL Server Integration Services
• ASP.Net
• .Net Framework 2.0
• ADO.Net
• Subversion

Project Size and Complexity:

• The synchronization process required over 300 tables and hundreds of stored procedures
• Hundreds of thousands of client, address, and demographic records needed to be checked and synchronized nightly
• The synchronization process required several intermediary databases to handle the IMPACT snapshot, the difference comparison with the prior baseline, and the data transformation prior to pushing the transformed data to the AIM database
• Detecting data changes in IMPACT was more difficult because it did not have any reliable indicator that a record had changed. For example, there were no change dates on the records that needed to be synchronized. This added significant complexity to the entire process. It required the use of a baseline database and a difference comparison of hundreds of thousands of records against a nightly snapshot. The development team was not permitted to request any schema changes to the IMPACT database.
• There were no singular primary key values for tables in the IMPACT database. So a mapping had to be made to map composite denormalized values in the IMPACT database to the unique key values.