Awardee Information Management System:
Progress Report and Information Systems Analysis

The following progress report on the Awardee Information Management System provides a summary of the activity to date on the project and the remaining steps needed to create the proposed database system. This document focuses on the information systems technical requirements and only briefly describes the program area issues. The first phase of the implementation of the project only involves three program areas: Home, HTF, and LIHTC.

Background

In October 1997, David Greenes, Mary Larris, and Gracie Offield formed a work group to develop a design for a report that would provide information to management and the public regarding the funding provided by TDHCA. The work group met daily for several months to determine a report design that would meet the information needs for the agency. Home, HTF and LIHTC were the initial program areas studied. The initial design outline was created based on the program activities of these areas.

In January 1998, Gracie Offield was removed from the project to work on the CSAS PeopleSoft implementation. The PeopleSoft implementation was to have been completed by March, but did not actually wrap up until the end of June. In the interim, David Greenes and Mary Larris were removed from the project due to a conflict with the statutory requirements of their positions in the agency.

The AIMS project team is now composed of Gracie Offield, Juan Garzna, Alex Maldonado, and Jana Cormier (HTF). Alex Maldonado is the technical project leader. Jana Cormier is the program area liason. Gracie Offield and Juan Garzna are programmers on the project. Additional programmers will work on specific tasks associated with the project.

Project Description

The Awardee Information Management System was initially proposed because TDHCA currently does not have a central database that contains information on all awardees who receive funding, tax credits, or other compensation from the agency. The agency has established that there is a pressing need for a database that will provide uniform awardee and contract information for all program areas.

The technical goal for the AIMS project is to develop a database that will provide reporting data for analysis of contract performance to determine which awardees perform according to the standards set out in their contracts. Analysis of the data will help program staff determine how well each awardee fulfilled the objectives outlined in the initial contract or agreement. The database will be used for output purposes only.

Objectives

The AIMS project has the following objectives:

• Primary objective. Produce a report that will contain uniform information about every awardee who receives funds or other compensation from TDHCA. The report will also contain profiles of each of the awardee's contracts with the agency. The report will be generated on a regular basis to provide current information to executive management and program personnel.
• Secondary objective. Create a central database that will support the report described above.
• Additional objectives. Identify what information is currently stored on existing systems and to develop a plan for obtaining the information that is not currently captured.

Scheduling Considerations

The project can be broken down into four phases:

• First Phase (Deadline: January 1, 1999). The current database design includes 22 separate tables. The first phase of the project is to create the six core tables and create the initial web interface by January 1, 1999. The other tables will be created for the database but will not be populated with data until after the initial tables are working smoothly.
• Second Phase (Deadline: May 1, 1999). The second phase of the project will be to populate the remaining tables with data from the three initial program areas (Home, HTF, LIHTC). The second phase will also include refining any reports to accommodate the additional data.
• Third Phase (Deadline: September 1, 1999). The third phase of the project will be to begin incorporating the remaining program areas into the system. The core tables will be populated with data by September 1, 1999.
• Fourth Phase (Deadline: January 1, 2000). The fourth phase of the project will be to finish incorporating the remaining program areas into the system.

Requirements

In order to create a functioning database, the following issues must be addressed:

• Data Definition
• System Functionality
• Technical Specifications
  • hardware
  • implementation
  • information support
  • existing system modification

Data Definition. The data definition is a catalog of the specific information that is required for the database.

• Work completed. An extensive data definition has been completed. The data definition is based on the final proposed reports. The data definition has been attached to this progress report as Attachment A. A copy of the final proposed report has been attached as Attachment B.
• Work remaining. As with any large database project, there will be data inclusions and deletions in the future, but the impact of these changes will be negligible. The finalization of the data definition will require 10 hours of information systems time, mostly in the form of additional communication between team members and documentation of the new fields, if any.

System Functionality. The functional requirements of the database include what the database will do and how it will be used.

• Work completed. The general functional specifications for the project have been identified. The database will be created as a data warehouse. The data will be drawn from existing database systems in the agency through the use of interfaces. There will be no live data entry into the system. The data will be imported into the new data warehouse on a monthly basis. The frequency of data transfer will be increased after the initial system test.
• Work remaining. The functions of the project have been expanded. Executive management would like for the database to be accessible on the web. A web interface will be developed to allow users to access the data according to their specific needs.

The additional web functions must be documented. Currently, the database will only support report writing. If there are additional input or query functions required, those modifications must be analyzed. The remaining functional considerations will require input from the program areas and executive management. Completion of the functional design will require at least 40 hours.

Technical Specifications

The technical specifications for the creation of the database can be divided into four subparts:

• Hardware
• Implementation
• Information support
• Existing system modification

Hardware. The data warehouse will require the purchase of an additional server. Juan Garzna is researching the cost and availability of suitable servers.

Implementation. The technical requirements for the implementation of the AIMS project are:

• design of the database
• creation of interfaces that export data from existing systems
• creation of interfaces that insert data into the database
• batching and scripting of the interfaces to run automatically
• design of reports to display the data
• design of web interfaces to access the reports and data in the database.

Database Design. The design of the database is the most important element of the project.

• Work completed. The design for the database has been completed. The basic key structure has been determined for all the tables, the relational links have been worked out on paper, and a prototype of the major tables has been worked up in Access. The framework is solid. A copy of the database definition has been attached as Attachment C.
• Work remaining. As with any large database project, there will be modifications made to the design during the testing phase. There are a few outstanding data issues that must be resolved. These issues need input from the program areas and executive management. Completion of the data definition will require 10 hours of information systems time, mostly in the form of additional communication between team members and documentation of the new fields.

Export Interfaces. An export interface is a program that runs on an existing database to extract data to be loaded in to the AIMS database.

• Work completed. The interface design documents have been completed and reviewed by the programmers working on the project.

• Work remaining. There are 22 tables in the AIMS database. Each table will require a separate interface. The interfaces for the 6 core tables will probably require 50 hours to create and refine. The remaining 16 tables should only take 20 hours each.

For each program area included in the AIMS project, the minimum time required for interfaces exporting data is:

             6 tables *  50 hrs  =  300 hours
            16 tables *  20 hrs  =  320 hours
            Total                   620 hours

The current estimate of the minimum time required to create export interfaces for the initial three program areas is 1860 hours.

Import Interfaces. An import interface is a program that loads data into the AIMS database tables. The import interfaces will take less time to write. Only one interface is needed for each table.

• Work completed. The database was originally going to be created in DB2 but now it will be created in Oracle. Table definitions and load scripts have been written for DB2.
• Work remaining. There should not be too many changes to the scripts on Oracle. The minimum time required for finalization of the import interfaces is:

  
  22 tables  *  10 hrs  =  220 hours
  
  

Interface Batches. The interfaces that export data from the existing systems and the interfaces that load the data into the new systems will have to run automatically at night. The group of processes that run the interfaces are referred to as interface batches.

• Work completed. Very little work has been done on this segment of the project. Copies of the interface batches from the CSAS implementation have been printed out and reviewed.
• Work remaining. The batch scripts need to be written. The batching of the interfaces will take a significant amount of time. For each system in each program area, a batch job must be created that will coordinate the execution of each interface. There will also be a batch job to import data from each area. The coordination of the batch jobs will be facilitated by use of semaphore files. The estimated time required to write the batch scripts and fine tune them is 100 hrs.

Reports. Concerning the reporting functions related to the database design:

• Work completed. A draft design of the primary report has been created.
• Work remaining. The report design must be translated into actual reports. The estimated time required to create the report is 100 hrs. I have attached a copy of the report design.

Web Access. Executive management would like for the database to be accessible on the web. A web interface will be developed to allow users to access the data according to their specific needs.

• Work completed. The initial design did not consider web access. No work has been completed.
• Work remaining. The web interface will be designed and implemented. The completed reports can be published as html documents and placed on the web. When the MHT division web pages were developed, it took one programmer 3 months to get all of the functionality completed. The web development for this project will most likely have a similar level of complexity. Therefore, the time estimate for this portion of the project is 400 hours.

Information Support

There is data included in the data definition that is not currently tracked by the agency programs. This information will need to be obtained by the program areas.

David Greenes asked each program area to determine the amount of time that it will take for staff to gather and input the information required for the AIMS project. The initial response indicated that there was a significant time component involved.

• Work completed. A plan was devised to try to minimize the amount of time spent by TDHCA staff gathering the additional data. Essentially, the strategy adopted was to import the existing information into the AIMS system and then generate a questionnaire to send to the awardees for them to fill out and return to TDHCA. A copy of the proposed format of the questionnaire is attached to this document.
• Work remaining. The questionnaire will need to be translated into a report. The amount of time that will be required for the translation of this questionnaire into a document that is generated from the database is 40 hours.

Modification of Existing Systems

In order for the proposed system to function efficiently, all information required for the report must be captured on an existing information system. The technical requirements for the modification of current systems is different for each program area as described below:

Home. The Home system on Genesis currently has the capacity to capture about half the information required by the AIMS system.

• Work completed. The missing information has been identified. The Home system does not have the capacity to capture the level of detail required for the report sections dealing with loan administration; leveraged sources; professional fees and costs; consultants, developers, and other contract payees; and organization principals and officers. It can capture substantially all of the information regarding awardees; contract specifications and dates; and budget and expenditure amounts. The Home system can capture some of the information regarding demographics of the families served by the contract.
• Work remaining. Patti Truette is the developer of the Home system. The estimated time required for him to modify the Home database to accept the additional information is 85 hours.

Housing Trust Fund. HTF currently captures some information on Genesis and some in Excel spreadsheets. The system used by HTF is inadequate. Also, there is no efficient way to automate the export of data from Excel. The system for Housing Trust Fund needs to be completely moved to Genesis or to another database system that can be accessed by an interface program to extract its data.

• Work completed. The Housing Trust Fund staff has worked hard on the AIMS project and, in so doing, has defined more or less what they want their new system to do. Juan Garzna has met with them to discuss creating their system on Oracle to run off of the intranet.
• Work remaining. Modification of the Housing Trust Fund system will take 300 hours.

Low Income Housing Tax Credits. LIHTC uses a third-party software product from AOD. The AOD system used by LIHTC has the capacity to capture about 85% of the information requested for the AIMS project. Most of what isn't captured by LIHTC is not relevant to Tax Credits. Modification of the AOD system cannot be done at TDHCA. All modifications have to go through AOD or a contracted COBOL programmer who knows AOD software.

Program Area Requirements

The program areas will be responsible for entering data into their current systems. LIHTC has prepared a detailed estimate of the amount of time it will take to enter historical data. The HTF will enter its historical data within six months. Home has not prepared a detailed analysis.

Overall Conclusions

The AIMS project is coming along well, despite the loss of several months of activity. The design is sound and there is a realistic timeline in place.

There are no foreseeable major problems that will prevent timely implementation of this project. There are no other competing projects that might cause delay of this project. This project has the full support and commitment of the ISSW division.

Note: Attachments A, B, and C have been omitted from this example.