[COMPANY NAME] Data Integrity Recommendation
Background: [COMPANY NAME]’s iMIS database has been expanding rapidly and therefore, is absorbing lots of disk space. This is compromising the performance of the MS SQL server. One major reason for this is due to how [COMPANY NAME]’s data is stored and used. [COMPANY NAME] as a business rule does not purge records that are no longer active whether they are member or non member records. The rationale is that [COMPANY NAME] would still want to be able to market to these individuals. In addition, all non members are marked with a member type of “Z” and any member record that lapses is also changed to a “Z” member type. Another issue is that there are duplicate records in the database that are deemed “legitimate”. For example, a member that works for a company which belongs to more than one Chapter could have multiple records in the database. If he/she belongs to 3 different chapters then his/her record will be in the database three times.
Recommendation: The Database Manager of [COMPANY NAME] recommends that data records that are older than five year be stored and warehoused in a separate database. This will insure complete optimization of [COMPANY NAME]’s current hardware and enhance the integrity of the data.
Proposed Solution: I: Database Cleanup: To accomplish this, a database “clean-up” is required. This would consist of getting a current data count; and warehousing historical data where the most recent activity is older than five years. [COMPANY NAME] staff will still have access to historical data for reporting and marketing purposes. II: Eliminate Duplicates: To eliminate the use of “legitimate” duplicates, the Database Manager will also explore the possibility of recreating the relationship of members to chapters: A one-to-many relationship where Mr. A can have one member record and have that record linked to multiple chapters.
Plan of Action: Database Assessment 1. Do a complete audit of customer records and the activities attached to such records. Breakdown as follows: a. b. c. d. e. f. g. Total record count. Total active record count. Total record count by Member Type Total active record count by Member Type Total active record count without activity. Total inactive or deleted record count by Member Type Total inactive or deleted record count by Member Type without activity. h. Total activity rows count. i. Total activity rows count by Activity Type. 2. Meet with Membership and other departments to discuss database audit. The goal here is to determine what data to purge and the Activity “cut-off” point with which everyone would agree. Data Warehousing 1. A data warehouse is a repository of an organization’s computer generated data set up in a way to facilitate fast retrieval. This data is warehoused as opposed to permanently destroyed so that it could be used for future analysis and queries. A database would be created to house the historical data. (All records with the most recent activity older than 5 years.) It will be structured exactly as the [COMPANY NAME] current iMIS_Live database and accessible to staff for report generation and data analysis.
Re-creating Member – Chapter Relationship A test database would be created to configure the relationship between the member records and their Chapters.
In the test database, a user defined table will be created with multi-select fields where one record would be linked to multiple Chapters. A query would be run to isolate “legitimate” duplicate records that are the result of companies belonging to multiple chapters. Duplicate records would then be merged into one and attached to each Chapter that the individual belongs to. Conclusion: The goal is to purge and warehouse the appropriate amount of data from the operational system freeing up as much disk space as we can to optimize the server’s performance, while at the same time cleaning up the [COMPANY NAME] member data so that users can rely on its integrity.