[Audit Report on the Royalty Management Program's Automated Information Systems, Minerals Management Service]
[From the U.S. Government Printing Office, www.gpo.gov]

Report No. 97-I-1042

Title: Audit Report on the Royalty Management Program's Automated
       Information Systems, Minerals Management Service

Date: July 31, 1997

                  **********DISCLAIMER**********

This file contains an ASCII representation of an OIG report.  No attempt has been made to display
graphic images or illustrations.  Some tables may be included, but may not resemble those in the
printed version.

A printed copy of this report may be obtained by referring to the PDF file or by calling the Office
of Inspector General, Division of Acquisition and Management Operations at (202) 208-4599.
                  ******************************

United States Department of the Interior

OFFICE OF INSPECTOR GENERAL
Washington, D.C. 20240

MEMORANDUM

We found that the Program's automated information systems were not operating
efficiently.  However, there were no indications that these inefficiencies adversely
affected the Program's ability to perform its mission because Program personnel developed
supplemental systems on personal computers, or manual workarounds, to assist in meeting
the Program's royalty management responsibilities.  The systems were not operating
efficiently because current database structures were antiquated and difficult to modify and
enhance. As a result, the Program unnecessarily incurred, at a minimum, contractor costs
of $2 million annually for these automated systems that did not efficiently meet user needs.

In addition, the Program did not ensure that application software for its automated systems
was adequately tested or that supporting documentation was complete and current.  As a
result, the Program expended about $1.2 million annually to detect and correct data errors
and problems in application processing to ensure that data related to the collection and
distribution of rents, bonuses, and royalties were accurate. Adequate testing of application
software changes would have disclosed these problems before the changed software was
moved to the mainframe production environment for routine processing.

 A-IN-MMS-00 l-94

United States Department of the Interior

OFFICE OF INSPECTOR GENERAL
 Washington, D.C. 20240

Memorandum

To:  Assistant Secretary - Land and Minerals Management

From:     Robert J. Williams
     Assistant Inspector General for Audits

Subject: Audit Report on the Royalty Management Program's Automated Information
Systems, Minerals Management Service (No. 97-I-1042)

This report presents the results of our review of automated information systems in the
Minerals Management Service's Royalty Management Program. The objectives of the audit
were: (1) to determine the effectiveness of the Program's automated information systems
as they related to accounting for and processing bonuses, rents, and royalties received by the
Service for mineral leases from Federal and Indian lands and (2) to review the efficiency of
the Program's automated systems in performing their intended functions.

Our review disclosed that the Program's automated information systems were not operating
efficiently. However, there were no indications that these inefficiencies adversely affected
the Program's ability to perform its mission because Program personnel developed
supplemental systems on personal computers, or manual workarounds, to assist in meeting
the Program's royalty management responsibilities. The systems were not operating
efficiently because current database structures were antiquated and difficult to modify and
enhance. As a result, the Program unnecessarily incurred, at a minimum, contractor costs
of $2 million annually for these automated systems that did not efficiently meet user needs.

In addition, the Program did not ensure that application software for its automated systems
was adequately tested or that supporting documentation was complete and current. As a
result, the Program expended about $1.2 million annually to detect and correct data errors
and problems in application processing to ensure that data related to the collection and
distribution of rents, bonuses, and royalties were accurate. Adequate testing of application
software changes would have disclosed these problems before the changed software was
moved to the mainframe production environment for routine processing.

In the July 22, 1996, response (Appendix 4) from the Director, Minerals Management
Service, the Service disagreed with Recommendation A.1; concurred with
Recommendations B.1, B.3, and B.5; concurred "in principle" with Recommendations B.2


and B.4; and concurred "in part" with Recommendation B.6. The Service also provided
additional comments, which we incorporated into the report as appropriate. Based on the
response, the Service is requested to reconsider its response to Recommendation A. 1, which
is unresolved, and to provide additional information for Recommendations B. 1 -B.6 (see
Appendix 5).

The legislation, as amended, creating the Office of Inspector General requires semiannual
reporting to the Congress on all audit reports issued, actions taken to implement audit
recommendations, and identification of each significant recommendation on which corrective
action has not been taken.

In accordance with the Departmental Manual (360 DM 5.3), we are requesting a written
response to this report by September 3, 1997. The response should provide the information
requested in Appendix 5.

We appreciate the assistance of Minerals Management Service personnel in the conduct of
our audit.

 
CONTENTS

Page

INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

BACKGROUND ................................................... 1
OBJECTIVES AND SCOPE .......................................... 1
PRIOR AUDIT COVERAGE ......................................... 2

FINDINGS AND RECOMMENDATIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

A. AUTOMATED SYSTEMS ........................................ 3
B. APPLICATION TESTING AND DOCUMENTATION ............... 10

APPENDICES

1. CLASSIFICATION OF MONETARY AMOUNTS .................... 16
2. CHARACTERISTICS IDENTIFYING SYSTEMS IN NEED
OFREDESIGN...............................................17
3. NETWORK AND RELATIONAL DATABASES ................ 18
4. MINERALS MANAGEMENT SERVICE RESPONSE ............ 20
5. STATUS OF AUDIT REPORT RECOMMENDATIONS ........... 29

 
 
INTRODUCTION

BACKGROUND

The Minerals Management Service's Royalty Management Program is responsible for
collecting and distributing revenues generated from Federal and Indian mineral leases. To
help meet this responsibility, the Royalty Management Program uses two major automated
systems: the Auditing and Financial System, which was used to process information on sales
and royalty payments, and the Production Accounting and Auditing System, which was used
to process information on mineral production. In addition, the Program uses numerous
subsystems located on the mainframe, the client server, and individual personal computers
to enhance operations. For example, one subsystem, the Allowance Limit Exception Process
system, is used to perform exception processing, and another subsystem, located on
individual personal computers, is used to disburse royalties. Also, two other subsystems, the
Business Information System and the recently developed Royalty Management Program
Query System, provide states, Indian tribes, and other agencies with information related to
royalty payments and production that has been processed by the automated systems.

The Royalty Management Program's automated systems used for processing royalty and
production information were operated and maintained by American Management Systems
Operations Corporation at an annual cost of about $7.5 million. According to its contract
with the Service, American Management's responsibilities included the following:
(1) maintaining system software; (2) maintaining and developing application software;
(3) maintaining other software, such as teleprocessing and general utilities; and
(4) supporting operations such as hardware maintenance, tape mounts, scheduling of batch
jobs, database management, system maintenance, communications, and capacity planning.

During 1988 through 1992, the Royalty Management Program made significant
improvements to its automated systems, which included developing and implementing its
Business System Improvement Plan and the Common Reference Database. The $5.4 million
Improvement Plan was a multiyear effort to consolidate existing automated systems. The
improvements involved software modernization and functional enhancements to the Royalty
Management Program's automated systems. For example, the Common Reference Database
was created to consolidate common reference information pertaining to leases and payors
into one database. However, the original network database structure and operating principles
were retained. The Royalty Management Program has begun to modernize its reporting
input methods and its information distribution methods through the implementation of the
electronic data interchange and the client server.

OBJECTIVES AND SCOPE

The objectives of this audit were: (1) to determine the effectiveness of the Royalty
Management Program's automated systems in relation to the Service's ability to accurately
and timely collect, account for, verify, and disburse bonuses, rents, and royalties received for

1

 
mineral leases from Federal and Indian lands and (2) to review the efficiency of the Royalty
Management Program's automated systems in performing their intended functions. To
accomplish these objectives, we reviewed the software development and maintenance for
fiscal year 1995 of the major automated systems located on the mainframe computer at the
Service's Royalty Management Program office in Lakewood, Colorado. We also
interviewed Royalty Management Program and American Management personnel, reviewed
system documentation and resultant reports, became familiar with the automated system
programs, became familiar with the data structures, observed a disaster recovery test, and
analyzed system security. We did not perform on-line testing of system processing because
of the risk of test data corrupting the production data. We developed alternative testing
methods where possible.

This audit was made in accordance with the "Government Auditing Standards," issued by
the Comptroller General of the United States. Accordingly, we included such tests of records
and other auditing procedures that were considered necessary under the circumstances.

As part of our review, we evaluated the system of internal controls to the extent that we
considered necessary. The internal control weaknesses that we found are discussed in the
Findings and Recommendations section of this report. If implemented, the recommendations
should improve the internal controls.

We also reviewed the Secretary's Annual Statement and Report to the President and the
Congress for fiscal years 1992, 1993, and 1994, which is required by the Federal Managers'
Financial Integrity Act of 1982, to determine whether any reported weaknesses were within
the objectives and scope of our audit. We found that the Minerals Management Service had
reported no material weaknesses for fiscal years 1992, 1993, and 1994 that were related to
our objectives.

PRIOR AUDIT COVERAGE

During the past 5 years, the General Accounting Office has not issued any reports related to
the scope of this review. However, the Office of Inspector General has issued two reports
related to the scope of this review:  "System Change Control Procedures, Royalty
Management Program, Minerals Management Service" (No. 91-I-l 90), issued in November
1990, and "Compliance With the Computer Security Act of 1987, Royalty Management
Program, Minerals Management Service" (No. 91-I-924), issued in June 1991. These reports
stated that: (1) the Service's Configuration Management Branch did not ensure the technical
adequacy of software resulting from system changes and did not have a formal plan or
approach for conducting its configuration management reviews and (2) the Service needed
to improve its computer security plans. The recommendations in these reports have been
resolved through either additional information or action by the Service. However, the two
recommendations in the report on system change control procedures had not been
implemented because the deficiency related to the technical adequacy of software still
existed during our current review, as discussed in the Findings and Recommendations
section of this report.

 
FINDINGS AND RECOMMENDATIONS

A. AUTOMATED SYSTEMS

The automated systems that support the Royalty Management Program (the Auditing and
Financial System and the Production Accounting and Auditing System) were not operating
efficiently. However, there were no indications that these inefficiencies adversely affected
the Program's ability to timely and accurately collect, account for, verify, and disburse
bonuses, rents, and royalties because Program personnel developed supplemental systems

management responsibilities.  We evaluated the automated systems in part based on
guidelines contained in Federal Information Processing Standards Publication 106,
"Guideline on Software Maintenance." These guidelines are used to determine whether
existing automated systems should continue to be maintained or whether the systems should
be redesigned. We concluded that the automated systems needed to be redesigned because
their current database structures were antiquated and were difficult and costly to modify and
enhance and because the systems did not meet the needs of the Program's users. As a result,
in addition to the manual workarounds, the Program unnecessarily incurred, at a minimum,
contractor costs of $2 million annually for operating and maintaining the Auditing and
Financial System and the Production Accounting and Auditing System.

During implementation of the Business System Improvement Plan to consolidate existing
automated systems, the Program did not include provisions for changing database structures
or programming language; therefore, the Program continued to use network (nonrelational)
databases, even though these databases became obsolete in the 1980s. We also determined
that the automated systems met 8 of the 11 characteristics defined by Federal Information
Processing Standards Publication 106 for automated systems being potentially in need of
redesign. We based our evaluation on system change requests, testing, processes, and
documentation. Of the three remaining characteristics, two related to running applications
on hardware that was different from the hardware the applications were designed to operate.
We determined that these two characteristics were not applicable to the Program's automated
systems. However, we did not determine whether the automated systems met the remaining
characteristic because the Program did not have adequate documentation for us to make this
determination (see Appendix 2).

Maintaining network databases is labor intensive because this type of database is difficult
to change. For example, we reviewed the Program's August 1995 open system change
request report to determine whether the Program was adequately making requested changes
to the system. We found that, although approximately 100 user system change requests were
closed or canceled between January and August 1995, the total number of open change
requests increased from 359 to 373 during the same period. Additionally, 299 of the 373

`Workarounds are processes whereby individuals copy data from the mainframe into personal
computers and
review and analyze the data to accomplish what the automated system was unable to do.

3

 
open system change requests had been submitted prior to the beginning of fiscal year 1995
(October 1, 1994). Some change requests were dated as far back as January 1989; therefore,
changes could take up to 6 years to implement. We believe that the number of open change
requests is a primary indicator that the network databases were difficult to maintain, modify,
and enhance.

The Program encountered delays in modifying the automated systems so that data would be
processed in compliance with laws and regulations. For example, in 1988 the Program
codified regulations for assessing interest on unauthorized processing allowances; however,
development of an automated capability to assess interest did not begin until 1992. As of
October 1995, the application was still not fully functional and, as implemented, did not fully
meet user requirements. This was evidenced by the six system change requests submitted
by the users of the application.

Because the systems did not meet the needs of internal users, manual workarounds were
developed. The workarounds became an integral part of the process to support the automated
systems and became so routine that personnel did not consider the additional effort as
workarounds. For example, personnel in the Reports and Finance Division used databases
and spreadsheets run on personal computers to generate royalty disbursements and to account
for and prepare the Statement of Transactions (SF 224) reports and the annual Program
financial statements. These personal computer-based databases and spreadsheets have
become the Program's accounting and financial systems. These accounting capabilities that
were being accomplished through workarounds were intended to be implemented as part of
the Business System Improvement Plan. However, before the accounting capabilities were
fully implemented into the Auditing and Financial System, time had expired and funds were
depleted. We determined that the Program's priorities have emphasized improving external
clients' abilities to input data into the automated systems and to access previously processed
data rather than to improve the processing capabilities of the automated system.

                             In
the process of storing, accessing, and restoring redundant data, there is an increased risk of

determined that at least 26 percent of the data stored in the computer were redundant or
unnecessary. The maintenance of redundant data cost the Program approximately $107,520
for each required increase of storage capability within the mainframe computer. These
additional costs were limited to the data stored on the mainframe and did not include the
storage costs for data stored outside of the mainframe, such as data stored on tapes or other
electronic media and on personal computers.

2A flat file is a series of sequential records that contain the data that are processed and stored. The
Program
has implemented indexing, which allows direct access to individual records.

3Corruption is the altering of data that is due to erroneous software logic.

4

 
The Program spent about $7.5 million annually for its contractor to maintain, enhance, and
develop automated system applications, including costs for project management, facility
management, system management, data communications, technical plans and studies,
computer services, microcomputer support, and data entry. Researchers have determined
that about 25 percent of the maintenance activity and costs associated with using a network
database are "wasted," in that additional effort is required by the programmer for any kind
of software maintenance activities performed.4 We believe that there is a need for more
efficient and flexible systems that can more readily accommodate changes. There is an
alternative to the network database structure that could improve the efficiency of the
Program's automated systems: a relational database structure. Relational database structures
are generally immune to changes in access techniques and do not necessarily require changes
to applications (relational databases are discussed in Appendix 3). We believe that the
Program's automated systems can be operated efficiently if they are redesigned from the
existing network database structure to a minimum of a relational database structure.
Redesigning the systems should result in automated systems that would be:

- More flexible and better able to accommodate changes.

- Operated, maintained, and enhanced for approximately $2 million less annually through
a savings in contractor costs.

- Relied upon to perform required functions so that Program personnel could be used
more efficiently in meeting the Program's mission and goals rather than performing system
functions.

- User friendly by allowing ad hoc reporting and query capabilities by authorized users
rather than by requiring computer programmers to develop and to write code for a report or
a query.

- More efficient by eliminating flat files and, therefore, reducing the amount of
redundant data and the continuing requirement for additional storage capability.

We estimate that it could cost from $6 million to $10 million to convert the current
automated systems to a relational database structure. Our estimate was based upon the
Program's current automated systems file structure, the estimated number of transactions
processed monthly, the time required for data to be stored, and the amount of documentation
that supports the systems. We discussed our estimate with an industry expert, who agreed
that our estimate was reasonable. Through this conversion, we estimate that the Program
could save $2 million annually in contractor support costs by the increased efficiencies in
modifying, enhancing, and maintaining the automated systems. With the $2 million savings,
which does not include the increased efficiencies to be realized by Program personnel, the
Program could recover its conversion costs of $6 million to $10 million within 3 to 5 years.

4Source: C.J. Date, An Introduction to Database Svstems, Sixth Ed., Addison-Wesley, Boston, pps.
17-53.

5

 
Recommendation

We recommend that the Director, Minerals Management Service, include sufficient funds
in the Service's budget request to redesign the Royalty Management Program's mainframe
automated systems to operate, at a minimum, with relational databases so that the systems'
application processing can become more efficient.

Minerals Management Service Response and Office of Inspector General
Reply

In the July 22, 1996, response (Appendix 4) to our draft report from the Director, Minerals
Management Service, the Service disagreed with the recommendation. Accordingly, the
Service is requested to reconsider its response to the recommendation, which is unresolved
(see Appendix 5).

Recommendation. Nonconcurrence.

Service Response. The Service stated that our report offered "no compelling technical
reason to pursue" such system changes and that our report "overstated" the projected cost
savings. Additionally, the Service stated that making "potentially far reaching changes" to
its data architecture "makes little sense" today because the Service "is faced with the prospect

of considerable change" in the way it receives and processes royalty data.

The Service

further stated that recommendations made by the Royalty Policy Committee and the
"recently started" Compliance Reengineering Project and requirements of the recently
enacted Federal Oil and Gas Royalty Simplification and Fairness Act will cause the Service
to make changes in its system processing. In addition, the Service stated that it believes that
its "best course of action" is to use available funds to pursue its client server migration
strategy.

Office of Inspector General Reply. We believe that the Service should pursue
processing changes because the Service's current system: (1) requires maintaining
supplemental systems and workarounds; (2) has a high number of system change requests
which cannot be addressed in a timely manner; (3) has data stored in its mainframe which
is at least 26 percent redundant; and (4) meets 8 of the 11 characteristics of a system needing
redesign, as defined by Federal Information Processing Standards Publication 106. As such,
the Service incurs extra costs as a result of all of these deficiencies. We believe that our

estimate of the potential cost savings of making system changes may have been understated
instead of overstated because it was based only on the extra costs incurred by the contractor
for the noted deficiencies and did not include the associated salaries incurred by Program
personnel. We recognize that the Service is facing considerable changes in the way it

conducts business because of recommendations made by the Royalty Policy Committee and
the Compliance Reengineering Project and implementation of the Federal Oil and Gas
Royalty Simplification and Fairness Act. However, we believe that the Service could
incorporate the changes needed to respond to the recommendations and the Act and address

6

 
future changes in a more cost-ffective and timely manner by redesigning the Program's
automated system to operate with a relational database structure.

In addition, the client server migration strategy does not include updating the two mainframe
automated systems addressed in our finding: the Auditing and Financial System and the
Production Accounting and Auditing System. If the client server migration strategy was
revised to include the Auditing and Financial System and the Production Accounting and
Auditing System, the database structure would need to be changed to a relational database
structure.

Additional Comments on Finding

In its response, the Service disagreed with certain issues in the finding, which we have
addressed as follows:

- System Effectiveness. The Service stated that our draft report "implies that royalty
revenues are not being properly input, processed, accounted for, and disbursed in a timely
fashion. " We did not conclude or attempt to imply that royalty revenues were not being
properly and timely accounted for and disbursed but that the automated systems that support
the Royalty Management Program were not operating efficiently. This resulted in the
ineffective use of Program personnel to perform manual workarounds and in additional costs
for annual system operation and maintenance. Based on the Service's comments, we revised
the report to clarify this point.

- Database Structure.

The Service said that the mainframe applications database

structures should not be changed because network databases "are appropriate for batch, large
volume processing environments" and that "even today, it would be difficult to tune a
relational database to adequately support RMP's [Royalty Management Program] nightly
batch production schedules." However, the Service did not state its requirement that nightly
batch processing should be continued. Additionally, relational databases are capable of on-
line transaction processing, which could reduce nightly batch processing for updating the
database. Further, we have found that other Departmental agencies have moved batch, large
volume processing systems to the relational database structure, such as the Department's
Federal Personnel Payroll System. As we noted in the finding, relational database structures
can be efficient structures for operating large volume transaction processing environments.

- System Change Request. The Service disagreed that the backlog of system change
requests indicated that the mainframe application systems did not meet user needs or that
the systems were difficult to maintain, modify, and enhance. The Service stated that most
of the Royalty Management Program's system change requests "require little or no database
modification or that backlogs may be a function of new system enhancement requests, low
cost savings potential, limited resources, program priorities, etc." However, we believe that
the database structure can impact the backlog of system change requests because even a
request to change a column heading or to add or delete a field to a report requires a computer
programmer to modify a computer program under a network database structure. Also, the

7

 
Service did not note that in order for a problem, such as a new report or a new processing
function, to become a system change request, the problem is reviewed and approved by user
groups and committees and that all problems are not escalated to a system change request.
Therefore, the system change requests have met the Program's criterion for being a necessary
change to the systems. As noted in this finding and in Appendix 3, we identified the
difficulties in making changes, both related to the database structure and in report generation,
associated with network database structures. Further, we also identified the need for the
Royalty Management Program to consider changing its database structure based upon
information contained in Federal Information Processing Standards Publication 106.
Specifically, the Publication identified 11 characteristics that determine when systems should
be redesigned. The Program's systems met eight of these characteristics, two were not
applicable to the Program's systems, and we did not determine whether the Program's
systems met the remaining characteristic because of inadequate system documentation
maintained by the Program. The Service did not address this issue in its response.

- Workarounds. The Service identified workarounds for analytical usage of computer-
based databases, spreadsheets, and processes that would be used within the client server
architecture. Additionally, the Service stated that it "agree[d] that some `workarounds'
originated to compensate for processes that should have been performed by the mainframe."
It was these types of workarounds addressed in the report that were associated with the lack
of functionality and effectiveness of the mainframe applications. We do not dispute that
using personal computers to perform analyses outside the confines of the mainframe data
center is acceptable, but workarounds should not be used to supplement system weaknesses.

- Data Redundancy. We have revised the report to more clearly indicate that we believe
that the amount of data redundancy would be reduced by changing the Royalty Management
Program mainframe applications to a relational database structure.

- Potential Cost Savings. The Service disagreed with our use of the $7.5 million of
contractor costs as the basis for determining potential cost savings of $2 million. The
Service stated that by using the $7.5 million, we had included costs for project management,
facility management, system management, data communications, technical plans and studies,
customer services, microcomputer support, and data entry, which are not applicable to the
database structure. However, we used the total contract costs because we believe that all of
the costs are related to the use of a network database structure. We have revised the report
to clarify that the $7.5 million was the total annual contractor budget, including the costs the
Service identified.

The Service stated that only $3.3 million of the $7.5 million of contract costs is directly
related to the database structure (software development, software maintenance, and database
administration) and that one third of the $3.3 million is related to development and
maintenance of client server architecture. Thus the Service said that it believes the
"25 percent savings attributed to relational databases can only be applied to a cost base of
$2.2 million yielding an annual savings of $550,000" rather than the $2 million we
identified. However, we believe that costs associated with project management, system

8

 
management, technical plans and studies, and customer services are impacted by the database
structure and related system development, maintenance, and enhancement. For example, the
Service's client server data are extracted from the mainframe. As stated in the report and in
Appendix 3, a relational database structure would allow system development, maintenance,
and enhancement to be accomplished more easily because a relational data base is more
efficient and flexible and therefore better able to accommodate changes. Consequently, the
the number of outstanding system change requests would be reduced along with the time for
their resolution. Project management and systems management efforts would also cost less
because the system projects could be implemented and completed more efficiently and
timely. Additionally, if the systems were changed to a relational database structure, the
Service would be able to develop client server applications and technical plans and studies
faster and with minimal use of computer programmers because data could be directly
extracted from the database rather than by computer programmers writing code to extract the
data.  Further, because relational databases are easier to change, customer service
requirements could also be met more effectively. Therefore, we maintain that the
$7.5 million of contract costs could be reduced by approximately $2 million, as stated in our
report, if the Service changed its mainframe applications to a relational database structure.

The Service disagreed with our applying the 25 percent standard for "wasted efforts"
associated with network database structures, stating that the 25 percent "wasted effort" is
attributable "to data-dependence, not network databases" and that the Royalty Management
Program's database "is to a degree data-independent." (Emphasis in original.) However,
as discussed in the finding and in Appendix 3, network databases are normally data
dependent. Therefore, since the Service did not indicate the specific percentage of its
database that is data independent and provide support for that percentage, we continue to
believe that the 25 percent standard is applicable to the Service's network database structure.

The Service disagreed with our $6 million to $10 million estimate to convert to a relational
database structure, stating that "it could easily cost twice as much." The Service further
stated that we did not consider "lines of code, number of programs or function point counts";
differences in Database Manipulation Language; and the rewriting of on-line processing.
The Service based its opinion on the $7 million of costs it incurred to redesign its Auditing
and Financial System under the Business System Plan Implementation. While we did not
audit the costs of the Business System Plan Implementation to determine whether the
$7 million was spent effectively or efficiently or whether 50 to 60 percent of the code was
affected, we believe that the software development tools used today in developing relational
database structure applications make it easier to write and convert lines of code and
programs. In addition to the aspects described in this report and discussions with an industry
expert, we used information from other Federal agencies and private corporation
reengineering projects, as described in technical journals such as "IEEE Software,"
"Communications of the ACM," and the "MIS Quartely" as a guideline in developing our
estimated costs. Therefore, we maintain that the $6 million to $10 million is a reasonable
estimate of the costs to convert to a relational database structure.

 
B. APPLICATION TESTING AND DOCUMENTATION

The Royalty Management Program did not ensure that application software for its automated
systems was adequately tested or that supporting documentation was complete and current.
Although Federal Information Processing Standards Publications, Office of Management and
Budget circulars, and Program manuals require that testing be performed and that
documentation be complete and current, the Program did not ensure compliance with these
requirements. As a result, the Program expended approximately $1.2 million annually to
detect and correct data errors and problems in application processing to ensure that data
related to the collection and distribution of rents, bonuses, and royalties maintained by the
automated systems were accurate.

Testing

System testing ensures that software performs as intended to satisfy Program mission and
user requirements and that newly implemented software does not adversely affect other
system processes. However, we concluded that the Program did not test its software
adequately, as evidenced by the problems identified in the following examples:

- In our review of 85 system change requests completed between March and July 1995,
we found that 46 requests were necessitated by problems in job control language and in
modified application programs.'

- A problem was identified in March 1992 that dealt with the way the application
recorded lease acreage when a lease was located in multiple counties. The application
recalculated the acreage and distributed royalties based on erroneous acreage amounts.
Consequently, users of the automated systems manually entered the correct acreage
applicable to each county to ensure that the distributions were correct. As of August 1995,
this problem had not been corrected.

- Software was not modified promptly to prevent processing problems from recurring.
Instead, data were manually manipulated outside of normal processing that bypassed the
automated systems processing controls. Of the 22 documented instances of manual
manipulation, 10 occurred because of software performance problems, and 6 of these 10
instances were recurring problems. As a result, software deficiencies that caused the error
in the data were not corrected, and the data had to be corrected each time the software was
used in production.

`Job control language is the language that tells the computer system what programs to run and what
files
these programs will read or create.

10

 
Because the module did not work properly, 13 additional system change requests were
created, and the module corrupted the data in the Auditing and Financial System database.
The corruption of the data also caused a $2.6 million out-of-balance condition in the general
ledger, and the payor balances and detail records had to be corrected by manual manipulation
of the data outside of normal processing. In addition, the software module had been in the
library that had been termed the "emergency library" for approximately 2 years, although the
Program's procedures require that modules not working properly be run only temporarily
from this library.7 By running a program from the emergency library, the change control
procedures are bypassed, which increases the risk that an inappropriate program would be
run and adversely affect production information and data.

- The synopsis report for February and part of March 1995, which provided information
on aborted production jobs known as abends, identified 52 abends that resulted in 36 hours
         At least 19 of these 52 abends could have been prevented by
adequate testing. In addition, not all of the aborted critical production jobs were reported in
the synopsis reports prepared by the contractor, and, in some cases, the cause of an abend
identified on the synopsis report was never addressed. Thus there was little assurance that
all processing problems were identified and corrected. Adequate testing of application
software changes would have disclosed these problems before the changed software was
moved to the mainframe production environment for routine processing.

Documentation

The Program's automated systems were not adequately documented. Federal Information
Processing Standards Publication 105 and Office of Management and Budget Circular
A-127, "Financial Management Systems," recommend that documentation be kept up to date
and be readily available for review and evaluation to ensure that the automated systems are
functioning as intended. To test the automated systems' functionality using the automated
systems' documentation, such as the "RMP [Royalty Management Program] Systems
Manual," design documents, and the most current data set diagrams, we attempted to follow
the processing of royalty source documents through the Auditing and Financial System.
However, because the documentation was incomplete, we were unable to accomplish this
test. For example, the "RMP Systems Manual," which defines the Auditing and Financial
System modules' functions, did not identify all of the modules that were shown on the
current data set diagram, the programs that made up the modules did not contain information
that linked the programs together and the module's functionality was not completely

6A software module is a group of computer programs designed to perform a specific function or
process within
an application.

7 A library is a collection of programs or data files for a particular purpose.

8Abend is a shortened form of the term "abnormal end." An abend occurs when the computer is
presented with
instructions or data that it cannot recognize. An abend also can be the result of erroneous software
logic or
hardware failure.

11

 
identified, and the interrelationship between each module within the Auditing and Financial
System was not identified.

We also found that system design documentation was not readily available, with the
contractor estimating that it would take about 12 staff weeks to gather and provide the
necessary system design documentation. Without adequate documentation, the contractor
has to spend time unnecessarily to address system deficiencies and Program requirements.
For example, we found that:

- On April 14, 1995, because of a system processing problem, data related to about
19,000 documents were not processed. Although a report on the processing problem was
written on May 4, 1995, this problem was not addressed until May 16, 1995. The contractor
could not identify the source of the problem, and the resolution did not correct the cause of
the processing problem. Further, because the processing routine was not documented
adequately, the problem resulted in 2 lost days of processing royalty information.

- Conversion to Integrated Database Management System version 12 was delayed by at
least 2 months. Additionally, the Royalty Management Program Query System software was
to be delivered in June 1994; however, testing had not been completed as of July 1995.

Because of inadequate documentation, assurance that the Program's automated systems were
functioning as intended was based solely on an individual's knowledge of a specific module.
Therefore, the Program may not be able to recover from a system failure if any of the
personnel who individually have knowledge of the modules should terminate their
employment. Further, we believe that software modifications and enhancements could be
developed and implemented more timely and at less cost with better system documentation.

Recommendations

We recommend that the Director, Minerals Management Service:

1. Ensure compliance with and enforcement of the Royalty Management Program's
established system documentation and testing procedures that are identified in its documents
and manuals.

2. Establish effective controls to ensure that modified application programs and job
control language are adequately tested before being moved into production.

3. Correct application programs to reduce the use of manual data manipulation to correct
software problems.

4. Establish controls to ensure that aborted critical production jobs are identified and
corrected.

12

 
5. Document the applications to ensure that processes are functioning as intended by
including information that contains the linkage between programs within a module and
modules within an application.

6. Develop procedures to ensure that system design documents are maintained and are
readily available.

Minerals Management Service Response and Office of Inspector General
Reply

In the July 22, 1996, response (Appendix 4) to our draft report from the Director, Minerals
Management Service, the Service stated that it "agree[d]" with Recommendations 1, 3, and
5; "agree[d] in principle" with Recommendations 2 and 4; and "agree[d] in part" with
Recommendation 6. Although the Service generally agreed with our recommendations, we
are requesting additional information for all of the recommendations (see Appendix 5).

Recommendations 1 and 5. Concurrence.
Recommendation 6. Concurrence "in part."

Service Response. The Service said that a "joint contractor/RMP [Royalty Management
Program] team [would] develop a long term documentation plan which will revamp the
documentation process."

Office of Inspector General Reply. Because of the revision to the documentation
process, we request additional information to ensure that the documentation plan includes
procedures for: (1) documenting applications and their functionality and relationships within

a module and modules within an application; (2) maintaining system design documentation
for ready availability; and (3) ensuring compliance with and enforcement of the revised
procedures.

Recommendation 2. Concurrence "in principle."

Service Response. The Service stated that production problems were "not a major issue"
because of its existing controls, such as unit testing, system testing, and team leader code
review. Additionally, the Service stated that the incidents cited in our report were not
"catastrophic," were not a "good gauge of how adequately software is tested," and were "at
or below industry norms." However, the Service said that it will monitor the frequency and
severity of production problems and implement additional controls "if circumstances
warrant."

Offke of Inspector General Reply. We disagree that existing testing procedures and
controls are effective. As stated in the report, over half of the resolved system change
requests were caused by previous changes to job control language and by modified
application programs that had been moved to production. While the Service performs unit

tests and the requesting user "signs off "on user system change requests, the Service's

13

 
existing controls do not preclude or identify problems that extend beyond the system unit
or user when the changes are moved into production. Therefore, had the Service's testing
procedures and controls been effective, half of the completed change requests would not have
been created. We request that the Service provide additional information on its production
problem monitoring plan, including time periods of monitoring, acceptable frequency rates,
and indicators of severity. The titles of officials responsible for implementation should also
be provided.

While we did not find any incidents of major significance, the problems identified in our
report disclosed that the Service spent millions of dollars to correct problems that could have
been prevented through better testing. We do not believe that "industry norm" should be the
standard for determining whether adequate testing has been performed. In the September 26,
1996, report from the President's Council on Integrity and Efficiency "Review of
Application Software Maintenance in Federal Agencies," the Council cited inadequate
testing when only 16 percent of all releases created new problems. Of the 85 completed
system change requests we reviewed, over half had been created based on new, revised, or
modified releases of job control language or application programs moved to production.
Therefore, we concluded that testing was inadequate. Without an effective testing process,
there is little assurance that management controls are sufficient to safeguard an application's
integrity. In addition, whenever rileases into production, whether job iontrol orapplication
programs, create new problems, the result is unnecessary costs.

Recommendation 3. Concurrence.

Service Response. The Service stated that "consistent with program priorities," system
change requests would be implemented "to reduce manual data manipulation" and that users
would be "trained to avoid processing transactions that are known to cause system assurance
variations." The Service further stated that "many situations" cited in our report that caused
manual data manipulations "[had] been corrected" and that "every effort will be made to keep
manual data manipulation to a minimum."

Office of Inspector General Reply.  Although the Service agreed with the
recommendation and cited actions to be taken, we are requesting additional information
regarding implementation dates for the system change requests to reduce manual data
manipulation, the action plan for training, and the official responsible for implementation.

Recommendation 4. Concurrence "in principle."

Service Response. The Service said that it "believe[s] processes and controls are in
place to ensure that aborted critical production jobs are identified and corrected" and that
resolving aborted critical production job issues "receives the highest priority." The Service
further stated that the synopsis report we cited in our report is a "shift turnover coordination
report as well as a management report" and "is not the vehicle" used for identifying and
tracking processing problems until they are completed. Further, the response stated that our
audit report did not identify "any benchmarking criteria from comparable environments to

14

 
assess [the Service's] performance" but that "steps will be taken to ensure that the [synopsis]
report is complete and accurate."

Office of Inspector General Reply. We agree that the synopsis report is not the
Service's mechanism for identifying and reporting aborted critical production jobs. However,
we disagree that the Service has processes and controls in place to ensure that aborted critical
production jobs are identified and problems are corrected. Our report stated that the synopsis
report did not identify all production problems and that all production problems were not
reported to management and corrected. Our conclusions were based on reports generated
from the system that indicated when jobs were completed or aborted. In addition, the

Service stated that when a processing problem is identified, a problem report is opened in the
automated problem tracking system. However, we found that all processing problems were
not recorded in the tracking system and that, when recorded, they were not recorded timely.
Because the Program did not have "benchmarking" standards established to determine
whether its system was performing adequately, we used information from audit reports of
automated system processing prepared by other Federal agencies' Offices of Inspector
General and private accounting firms. These reports were critical of excess numbers of
aborted production jobs found at the agencies audited. For example, one audit report of a
Federal program stated that 21 "abends" occurred within a 2-month period and concluded
that occurrences of "any abnormal terminations in the production environment" were
"unacceptable." We found that the Service had comparable numbers of aborted production
job problems. We believe that the problems identified in our report could be resolved if the
Service developed better procedures and controls to ensure that all critical aborted production
jobs are recorded in the automated tracking system and subsequently corrected. As such, we
request that the Service provide information on actions it will take to ensure that problems
that caused critical production jobs to abort are identified and corrected and identify the
individuals responsible for taking such action.

15

 
APPENDIX 1

CLASSIFICATION OF MONETARY AMOUNTS

   Finding: Area

Automated Systems

Application Testing and Documentation

  Total

Funds To Be Put
To Better Use

$2.0 million*

$1.2 million

$3.2 million

*These funds do not include costs to convert to a relational database.

16

 
APPENDIX 2

CHARACTERISTICS IDENTIFYING
SYSTEMS IN NEED OF REDESIGN

Indicators That Automated Systems
Potentiallv Need To Be Redesigned

Systems fail frequently.

Systems' codes are more than 7 years old.

System program structure and logic
flow are overly complex.

Systems' codes are written for previous
generation hardware.

Systems are running in emulation
mode.

Systems have very large modules or unit
subroutines.

Systems require excessive resources.

Systems have hard-coded parameters, which are
subject to change.

Organization has difficulty in keeping
personnel capable of maintaining the system.

Organization has deficient
system documentation.

Organization has missing or
incomplete system design specifications.

17

Indicators Present at
Rovaltv Management Program

Yes

Yes

Yes

Not Applicable

Not Applicable

Not Determined

Yes

Yes

Yes

Yes

Yes

 
APPENDIX 3
Page 1 of 2

NETWORK AND RELATIONAL DATABASES

Network Databases

In network databases, the structure contains records, and each record in the database
represents all of the data necessary for the database. The records have multiple parent/child
relationships, known as sets. Network databases store the parent/child relationships as
physical pointers from one data record to another. However, the databases are very rigid.
The set relationships and the structure of the records have to be specified in advance.
Changing the database structure typically requires rebuilding of the entire database. In
addition, creating custom reports requires programs to be written, which can cause a backlog
of requests for the custom reports, and by the time the report is generated, the information
may be outdated or useless. Additionally, network databases are data dependent because
it is impossible to change the storage structure (how the data are physically stored) or access
technique (how the data are accessed) without affecting the application, probably drastically.
For example, it would not be possible to replace the indexed file with a hash-addressed file
without making major modifications to the application. Moreover, the portions of the
application requiring alteration may cause problems by changing the functionality that the
application was originally to perform. The typical network database structure is depicted in
Figure 1.

PAYORS

PRODUCTS
(Codes)

Payor 1

Payor 2

Payor 3        ED      03

Figure 1: Network database structure*

*Referent ed sources: James R. Goff and Paul N. Weinberg, LAN Time Guide to SOL,
McGraw-Hill, Berkley,
1994, pps. 43-52, and C.J. Date, An Introduction to Database Systems, Sixth Ed., Addison-Wesley,
Boston,
pps. 17-53.22.

18

 
APPENDIX 3
Page 2 of 2

Relational Databases

In a relational database, the applications are data independent (data independence is the
immunity of applications to change in storage structure and access technique). This implies
that the applications concerned do not depend on any one particular storage structure or
access technique. There is the freedom to change the storage structure or access technique
in response to changing requirements without having to modify existing applications. Data
in a relational database are accessed through the logical structure, not the physical structure.
To the user, the relational database is perceived to be made up only of tables (rows and
columns of data). Within each table at every row and column position, there is always
exactly one data value, never a group of several values, as contained in the records of the
network database structure. Further, there does not exist the concept of "record," that is, a
record that contains all the necessary information. Instead, the tables are joined together
through keys, and through the joining, a record could be created. With a relational database,
redundancy of stored data is reduced significantly, which further reduces the storage
requirements for the data. Redundancy of data is reduced because the data in the tables do
not also have to be stored as records. A relational database structure is depicted in Figure 2.

Figure 2: Relational database structure*

19

 
APPENDIX 4
Page 1 of 9

United States Department of the Interior

MlXERAlj MUAGEMENl'SERVICF,
   bhin~on, DC 20240

Memorandum

To , .

Mslmt Inspector General for Audits

\cting Deputy  Assistant Secretary, Land and Minerah Management

From . a

Subject:

mce of inspector Genwd Draft Audit Report A-N-MMS-OOlm94 `Roy&
Mmgment Program's Automated Infomutio~ Systems hberals Management
Service"

1 appreciate the opportunity to respond to this d&t repon on ON rq&y automated inform&n
8y~tem8~ We MB in general concurrence with six of the seven recommendations in the report.
We're ading ; N our gcnad comments on the audit findings and specify ones on the
recommem3lations.

Phse contact Bettine Montgomery at (202) 2084976 if' you have any fi,rtlw questions.

Attachment

20

 
APPENDIX 4
Page 2 of 9

MINERALS MANAGEMENT SERVICE RESPONSE TO DRAFT AUDIT REPORT
   "ROYALTY MANAGEMENT PROGRAM'S AUTOMATED
INFORMATION SYSTEMS, MINERALS MANAGEMENT SERVICE"

Audit Agency: Office of Inspector General (OIG)

Audit Number: A-IN-MMS-001-94

We welcome the opportunity to comment on this draft report. While we concur with many
facts in the report and have noted the exceptions, we must disagree with a number of OIG's
conclusions, and several of the recommendations. The following comments are offered to
clarify the issues and present a more balanced perspective to the readers.

SYSTEM EFFECTIVENESS

To state that the automated systems were not operating effectively (page 6) implies that
royalty revenues are not being properly input, processed, accounted for, and disbursed in a
timely fashion. No supporting evidence for this claim is offered in the report, and we believe
it is an inappropriate assertion. In fact, the Royalty Management Program (RMP) has never
failed to make monthly and semimonthly disbursements on time since the inception. The
OIG's own Federal Oil and Gas Royalty Management Act oversight reports have noted
substantial progress in RMP effectiveness. Moreover, OIG reports pursuant to the Chief
Financial Officers' Act have uniformly found the financial statements to fairly present
royalty information and have included no adverse findings with respect to system

efectiveness.

DATABASE STRUCTURE

Pages 6 through 9 of the report criticize RMP for using a network, as opposed to a relational
database structure, and advocate a costly redesign effort to establish a relational database
environment. The choice of database structure is a very complex decision and no expert
would suggest that the decision is a straight forward, either/or proposition.

On page 7, OIG contends that 1) RMP's current database structures were antiquated and
became obsolete in the early 1980's and 2) during implementation of the Business System
Improvement Plan to consolidate existing automated systems, the RMP did not include
provisions for changing database structures. When the RMP moved off its VAX systems in
1985, its open competition allowed prospective bidders to offer any database solution. All
bidders proposed IDMS/R (a network database) as most appropriate for our operational
environment. In 1985, when the decision was made to remain with a network database

21

 
APPENDIX 4
Page 3 of 9

structure, there were not relational databases that provided the required online or batch
performance to meet RMP's stringent production schedules. If the database structure became
obsolete in the early 1980's, surely one of the firms' bidding on our conversion contract
would have recognized this "fact" and bid a relational model.

Between 1988 and 1992 the Auditing and Financial System database was completely
redesigned as part of the Business System Plan Improvement (BSPI) project. As noted in
the OIG report, a network database structure was retained. Prior to implementation, both
IBM Corporation consultants and staff from the Office of Technology Assessment reviewed
BSPI project plans and designs. Both groups concurred with our approach and neither
suggested that a relational database would be more appropriate.

The network database in use by RMP, "IDMS," is widely used today. According to
Computer Associates, Inc., there are between 3,000 and 5,000 IDMS sites worldwide. The
larger companies use IDMS to process millions of transactions each day. Network databases
are not obsolete. They are appropriate for batch, large volume processing environments such
as RMP. Relational databases are better for dynamic report and decision support functions.
Even today, it would be difficult to tune a relational database to adequately support RMP's
nightly batch production schedules.

Our intent is not to engage in a debate over the relative merits of network vs. relational
database structure, but to impress upon the reader that RMP management was not remiss in
the 1980's nor is it now for opting to retain IDMS as our database model. The RMP long ago
recognized relational database strengths when it implemented its data warehouse based on
Microsoft's SQL Server. At the same time, RMP decided that a client server (relational)
architecture with a graphical user interface provided the best ease of use for our clients. It
has been RMP's documented strategy since 1992 to move all of its systems, except for a few
core batch processes, offits mainframe platform to a client server architecture. This fact was
not reflected in the OIG report.

SYSTEM CHANGE REOUEST @CR) BACKLOG

Pages 7 and 8 of the report intimate that a large backlog of SCR's would suggest problems
with network databases or that RMP's systems do not meet user needs. We have no quarrel
with OIG's factual statements pertaining to open SCR's. However, we are indeed concerned
with OIG's statement that "We believe that the number of open change requests is a primary
indicator that the network databases were difficult to maintain, modify, and enhance." The
OIG report fails to acknowledge that most of the SCR's require little or no database
modification or that backlogs may be a function of new system enhancement requests, low
cost savings potential, limited resources, program priorities, etc.

22

 
APPENDIX 4
Page 4 of 9

SCR's are frequently submitted on applications that are meeting all functional design
specifications. Examples include changing column headings, modifying sort sequences, or
adding a field. The RMP has encouraged employees to submit change requests and will
likely continue to have more SCR's on hand than can be worked in the near term. We
contend that SCR backlogs are largely independent of database structure and will exist in the
RMP environment in relatively the same magnitude regardless of whether a relational or
network database structure is employed.

WORKAROUNDS

The OIG report states on page 8 that because the systems did not meet the internal users'
needs manual workarounds were developed. Workarounds are processes where individuals
copy data from the mainframe into personal computers and use the data to accomplish what
the automated (mainframe) system was unable to do. We agree that some "workarounds"
originated to compensate for processes that should have been performed by the mainframe.
However, we offer another perspective on the workaround issue which in essence suggests
that it is preferable from both a cost and efficiency standpoint to perform many business
processes outside the mainframe computer environment.

In discussing the role of centralized data processing, the Gartner Group in a recent study
estimated that by 1998 at least 70 percent of new applications will be under the direction and
control of business units. The RMP has placed powerful personal computers on every
employee's desktop and established an extensive training program in personal-computer
based programming languages. This was done to advance and encourage end user
computing at RMP which parallels and complements our client server strategic direction.
In the future, we anticipate more, not fewer, workarounds as employees become more adept
at using the tools at their disposal to more efficiently do their jobs and perform tasks that
were heretofore the exclusive domain of data processing professionals. We fully expect
personal computer-based databases, spreadsheets, and processes to become an increasingly
integral part of RMP's accounting and financial systems. We do not believe that processing
beyond the confines of the mainframe data center needs to be stamped out at all cost.

DATA REDUNDANCY

Page 9 of the report states "the Programs'
redundant data in flat files." It goes on to
required because 26 percent of the data
unnecessary.

use of network databases required storage of
say that as a result additional disk storage is
stored on the mainframe is redundant and

23

 
APPENDIX 4
Page 4 of 9

SCR's are frequently submitted on applications that are meeting all functional design
specifications. Examples include changing column headings, modifying sort sequences, or
adding a field. The RMP has encouraged employees to submit change requests and will
likely continue to have more SCR's on hand than can be worked in the near term. We
contend that SCR backlogs are largely independent of database structure and will exist in the
RMP environment in relatively the same magnitude regardless of whether a relational or
network database structure is employed.

WORKAROUNDS

The OIG report states on page 8 that because the systems did not meet the internal users'
needs manual workarounds were developed. Workarounds are processes where individuals
copy data from the mainframe into personal computers and use the data to accomplish what
the automated (mainframe) system was unable to do. We agree that some "workarounds"
originated to compensate for processes that should have been performed by the mainframe.
However, we offer another perspective on the workaround issue which in essence suggests
that it is preferable from both a cost and efficiency standpoint to perform many business
processes outside the mainframe computer environment.

In discussing the role of centralized data processing, the Gartner Group in a recent study
estimated that by 1998 at least 70 percent of new applications will be under the direction and
control of business units. The RMP has placed powerful personal computers on every
employee's desktop and established an extensive training program in personal-computer
based programming languages. This was done to advance and encourage end user
computing at RMP which parallels and complements our client server strategic direction.
In the future, we anticipate more, not fewer, workarounds as employees become more adept
at using the tools at their disposal to more efficiently do their jobs and perform tasks that
were heretofore the exclusive domain of data processing professionals. We fully expect
personal computer-based databases, spreadsheets, and processes to become an increasingly
integral part of RMP's accounting and financial systems. We do not believe that processing
beyond the confines of the mainframe data center needs to be stamped out at all cost.

DATA REDUNDANCY

Page 9 of the report states "the Programs'
redundant data in flat files." It goes on to
required because 26 percent of the data
unnecessary.

use of network databases required storage of
say that as a result additional disk storage is
stored on the mainframe is redundant and

23

 
APPENDIX 4
Page 5 of 9

The OIG apparently assumes that data redundancy will disappear in a relational environment.
This is a notion which we dispute. In a paper by Donald K. Burleson exploring the relative
advantages and disadvantages of relational and network database architectures, he noted
with respect to storage requirements (DASD) that relational databases do not always make
the most efficient use of such storage and may even consume more storage than a network
database. In the RMP environment performance is a maior issue.
                                                  J    Most likely because of

performance needs, it would become necessary to denormalize the data and provide summary
tables. Relational databases thus accumulate redundant data just as network databases do.

The OIG report fails to recognize any of these factors in computing added storage costs
attributable to network databases. We submit that data redundancy is an operational reality
in both database environments and that the differences between the two will be immaterial.

POTENTIAL COST SAVINGS

On pages 9 through 11, OIG observes that RMP spent about $7.5 million annually for its
contractor to maintain, enhance, and develop automated systems. It notes that researchers
have determined that about 25 percent of maintenance activity is wasted in a network
database environment; therefore, RMP must be wasting about $2 million, annually. The OIG
estimates it could cost from $6 million to $10 million to convert RMP's current automated
systems to a relational database structure, which could be recovered via the annual $2 million
savings in 3 to 5 years.

The RMP disputes this analysis. First, we believe the $7.5 million base figure is wrong. The
OIG estimated annual cost savings by multiplying the referenced 25 percent figure times the
annual $7.5 million contractor budget. The OIG presumed that the entire contract budget
($7.5 million) was for maintenance, enhancement, and development of automated systems.
However, the only contractor task areas affected by a database structure total $3.3 million
($1.4 million for software development, $1.4 million for software maintenance, and $0.5
million for database administration). Other contractor task areas which comprise the balance
of the $7.5 million budget are project management, facility management, system
management, data communications, technical plans and studies, customer services,
microcomputer support, and data entry. They are all database structure independent and will
cost the RMP the same amount in a relational database environment.

Regarding the $3.3 million, we conservatively estimate that at least one-third of the money
spent in these task areas is done in support of client server (relational) development and
maintenance activities. Therefore, the alleged 25 percent savings attributed to relational
databases can only be applied to a cost base of $2.2 million yielding an annual saving of
$550,000. This is substantially less than the $2 million presented in the OIG report. It

rather than the 3 to 5 years postulated in the OIG report.

24

 
APPENDIX 4
Page 6 of 9

We believe the source used to derive the 25 percent wasted effort attributable to RMP's
network databases: C.J. Date, an Introduction to Database Systems, Sixth Ed., Addison-
Wesley, Boston, pps. 17-53, is misconstrued by the OIG. The alleged 25 percent wasted
effort is mentioned in a section devoted to data-dependence, not network databases.*********
Our question is whether such generalizations have any applicability in the RMP systems
environment and if so, to what extent. The RMP's database is to a degree data-
independent. We believe the OIG is basing estimated cost savings and subsequent
recommendations on a weak foundation that is not persuasive. To apply a 25 percent cost
saving to all relational databases or to suggest that the RMP network structure wastes money
to this degree is not a correct extrapolation. Absent a more authoritative and technical
analysis focused more directly on RMP systems, we must reject OIG's analysis.

We disagree with the $6 million to $10 million estimate to convert to a relational database
structure and believe it could easily cost twice as much. There is no indication that lines of
code, numbers of programs or function point counts were considered. These are factors that
carry a far greater importance in estimating conversion cost than current file structures,
monthly transactions or the amount of documentation. The BSPI project cost nearly $7
million in 1990-92 dollars and only affected 50-60 percent of existing code. Based on
RMP's conversion experience, major recoding is required to adjust for differences in
Database Manipulation Language. Conversion to a relational database would also require
massive code changes. In addition, all current online processing would have to be rewritten.

Software Testing

On pages 12 through 14, OIG concludes that the Program did not test its software
adequately. This argument is based upon change requests to job control language, a single
problem with leases which span multiple counties, our performing 22 nonstandard database
interventions to correct inaccurate data, and the occurrence of ABENDS (abnormal ends) to
production jobs streams. What is lacking in the report is any benchmarking criteria from
comparable environments to assess our performance in this regard.

These incidents are not catastrophic. While they are useful in identifying trends, they
certainly are not a good gauge of how adequately software is tested. In fact, a 1994 internal
study found system failure rates of less than 1 percent and concluded that more stringent
testing procedures were not needed. We firmly believe that our testing standards and
procedures are consistent with industry practices and that system production migration
failures or any other events cited by the OIG will be at or below industry norm s.

********* To quote the text, "If applications are data-dependent, such changes will typically require
corresponding changes to be made to programs, thus tying up programmer effort that would
otherwise be
available for the creation of new applications. It is still not uncommon, even today, to find that 25
percent or
even more of the programming effort in an installation is devoted to this kind of effort. It follows
that the
provision of data independence is a major objective of database systems."

25

 
APPENDIX 4
Page 7 of 9

Comments on Recommendations

Al. Include sufficient funds in the Service's budget request to redesign the Royalty
Management Program's mainframe automated systems to operate, at a minimum, with
relational databases so that the systems' application processing can become more effective
and efficient.

DISAGREE. The OIG report offers no compelling technical reason to pursue changes of this
magnitude and the projected cost savings are overstated. Moreover, we believe the Program
would be ill-advised to embark on a wholesale redesign of our database architecture at this
time. The RMP is faced with the prospect of considerable change in the way we do business
and in our systems environment. The Royalty Policy Committee recommendations and the
proposed Federal Oil and Gas Royalty Simplification and Fairness Act, if enacted, will
fundamentally alter data received and processed. Similarly, our entire approach to
compliance, exception processing, and data verification will be subject to change as the
recently started Compliance Reengineering Project develops its recommendations. With
such potentially far reaching changes on the horizon, it makes little sense to change data
architectures today.

The merits of relational databases do not escape us. The RMP's current day-to-day databases
are stored in a network based model where high transaction processing rates are required.
The data that is warehoused is stored in a relational database model. Network-centric
technologies and client server based solutions are exploding in the market place. They
promise to obviate the need to alter fundamental database architectures as system users can
interact with the data and process information transparently.

We believe that the best course of action is for the RMP to use available funds to pursue its
client server migration strategy which takes full advantage of relational database concepts.
Under this strategy, functionality will be steadily moved off the mainframe. All new systems
and reengineered applications will also reside in the client server environment. Scarce
software development resources will yield a higher return if used in response to newly
mandated changes, reengineered business processes, and deployment of emerging
technologies.

Bl. Ensure compliance with and enforcement of the Royalty Management Program's
established system documentation and testing procedures that are identified in its documents
and manuals.

AGREE. Compliance with documentation standards has not always been consistent. Our
FY 1996 internal review of systems maintenance concurred with this finding and
recommended that a joint contractor/RMP team develop a long term documentation plan
which will revamp the documentation process. We expect to have this plan completed by
the end of the fiscal year.

26

 
APPENDIX 4
Page 8 of 9

Compliance with and enforcement of testing procedures are already in place through the use
of quality assurance checklists and unit and test procedures and documentation. The users
sign off on all user SCR's, thus indicating a review or acceptance of testing.

B2. Establish effective controls to ensure that modified application programs and job
control language are adequately tested before being moved to production.

AGREE IN PRINCIPLE. While there may be incidents of problems in production, their
infrequency suggests this is not a major issue. Existing controls include unit testing, system
testing, and team leader code review. We will monitor the frequency and severity of
production problems and implement additional controls if circumstances warrant.

B3. Correct application programs to reduce the use of manual data manipulation to correct
software problems.

AGREE. Consistent with program priorities, SCR's intended to reduce manual data
manipulation will be implemented. In addition to application software changes, users will
be trained to avoid processing transactions that are known to cause system assurance
variations. Many situations that caused manual data manipulations cited by the OIG have
been corrected. However, the total elimination of these procedures is not possible, as there
are situations that are outside the control of users, applications, and production control.
Every effort will be made to keep manual data manipulation to a minimum.

B4. Establish controls to ensure that aborted critical production jobs are identified and
corrected.

AGREE IN PRINCIPLE. We believe processes and controls are in place to ensure that
aborted critical production jobs are identified and corrected. When a processing problem is
identified, a problem report (PR) is opened in NETMAN, the automated problem tracking
system. Depending upon criticality, the problem is either resolved quickly by appropriate
personnel or transferred to a SCR where tracked until resolved. Resolving aborted critical
production job issues is an activity that receives the highest priority. The synopsis report

cited in the OIG report is a shift turnover coordination report as well as a management report.
It is not the vehicle by which processing problems are identified and tracked until
completion. Regardless, steps will be taken to ensure that the report is complete and
accurate.

27

 
APPENDIX 4
Page 9 of 9

B5. Document the applications to ensure that processes are functioning as intended by
including information that contains the linkage between programs within a module and
modules within an application.

AGREE.  While design documentation does not always reflect maintenance changes,
program synopses are kept up to date and complete, programs are self-documented with
extensive comments, and operations documents describe file and program linkages and
subsystem relationships.  Testing and real-life production processing, rather than
documentation alone, ensure that processes are functioning as intended. Nonetheless, as
noted in recommendation B 1, we will review our approach to documentation and implement
new procedures consistent with revised documentation plans.

B6. Develop procedures to ensure that system design documents are maintained and are
readily available.

AGREE IN PART. (See responses to recommendations B 1 and BS.)

28

 
APPENDIX 5

STATUS OF AUDIT REPORT RECOMMENDATIONS

Finding/Recommendation
  Reference

Status

Action Required

Al .

Unresolved.

Reconsider the response to
indicate how the mainframe
applications will be changed
included in the client server

B.1, B.5, and B.6

Management concurs;
additional information
needed.

B2 .

B3 .

Management concurs;
additional information
needed.

Management concurs;
additional information
needed.

B4 .

Management concurs;  Provide an action plan that
additional information  includes titles of officials
needed.       responsible for implementation.

29

migration strategy, and provide an
action plan that includes target
dates and titles of officials
responsible for implementation.

Provide information on the plan
for documenting applications and
their functions and relationships,
maintaining system design
documentation, and ensuring
compliance with and enforcement
of the revised procedures.

Provide an action plan that
includes the time periods of
monitoring, acceptable frequency
rates, and indicators of severity.
The titles of officials responsible
for implementation should also be
provided.

Provide implementation dates for
the system change requests to
reduce manual data manipulation,
an action plan for training, and
titles of officials responsible for
implementation.

 
Sending written documents to:            Calling:

Within the Continental United States

U.S. Department of the Interior
Office of Inspector General
1849 C Street, N.W.
Mail Stop 5341
Washington, D.C. 20240

Our 240hour
Telephone HOTLINE
l-800-424-5081 or
(202) 208-5300

TDD for hearing impaired
(202) 208-2420 or
l-800-354-0996

Outside the Continental United States

Caribbean Region

U.S. Department of the Interior
Office of Inspector General
Eastern Division - Investigations
1550 Wilson Boulevard
Suite 410
Arlington, Virginia 22209

(703) 235-9221

North Pacific Region

U.S. Department of the Interior
Office of Inspector General
North Pacific Region
238 Archbishop F.C. Flores Street
Suite 807, PDN Building
Agana, Guam 96910

(700)550-7428 or
COMM 9-O11-671-472-7279

 
Toll Free Numbers:
l-800-424-5081
TDD l-800-354-0996

FTS/Commerciai Numbers:
(202) 208-5300
TDD (202) 208-2420

HOTLINE

1849 C Street, N.W.
Mail Stop 5341
Washington. D.C. 20240