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
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
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
- .Net Framework 2.0
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.