Texas Department of Housing and Community Affairs

Awardee Information Management System:
Progress Report and Information Systems Analysis

Background

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 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

• 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

• 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

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

• 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

• 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.

• 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

• 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

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

Current Schedule
Task	Hours	Deadline
Functional Requirements
Determine web functionality	40	August 15, 1998
Hardware Requirements
Research cost of server	15	July 30, 1998
Purchase server	?	August 6, 1998
Implementation
Data Definition	10	September 15, 1998
Functional Design	40	September 15, 1998
Export Interface - HOME	620	Initial export interfaces for six core tables by October 15, 1998
Export Interface - HTF	620	Initial export interfaces for six core tables by October 15, 1998
Export Interface - LIHTC	620	Initial export interfaces for six core tables by October 15, 1998
Import Interface	220	October 15, 1998
Batching	100	Initial batches by December 1, 1998
Reporting	100	Initial reporting based on six core tables by December 15, 1998
Web Access	400	Web development for standard views of six core tables by November 1, 1998
Queries and initial reports by December 15, 1998	100	Not determined
Modification of Existing Systems
HOME	85	All changes to support initial download of data by September 15, 1998
HTF	300	All changes to support initial download of data by September 15, 1998
LIHTC	?	Not determined

Overall Conclusions

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.

Interested in courses related to this page or a printed version? See the resources page.

Return to the main menu of this online textbook for technical writing.