Department of Finance
Financial Audit Division
The Office of the Legislative Auditor (OLA) is a professional, nonpartisan office in the legislative branch of Minnesota State government. Its principal responsibility is to audit and evaluate the agencies and programs of state government (the State Auditor audits local governments). OLA's Financial Audit Division annually audits the state's financial statements and, on a rotating schedule, audits agencies in the executive and judicial branches of state government, three metropolitan agencies, and several "semi-state" organizations. The division also investigates allegations that state resources have been used inappropriately. The division has a staff of approximately fifty auditors, most of whom are CPAs. The division conducts audits in accordance with standards established by the American Institute of Certified Public Accountants and the Comptroller General of the United States.

Consistent with OLA's mission, the Financial Audit Division works to:
Promote Accountability, Strengthen Legislative Oversight, and
Support Good Financial Management.
Through its Program Evaluation Division, OLA conducts several evaluations each year and one best practices review.

OLA is under the direction of the Legislative Auditor, who is appointed for a six-year term by the Legislative Audit Commission (LAC). The LAC is a bipartisan commission of Representatives and Senators. It annually selects topics for the Program Evaluation Division, but is generally not involved in scheduling financial audits.

All findings, conclusions, and recommendations in reports issued by the Office of the Legislative Auditor are solely the responsibility of the office and may not reflect the views of the LAC, its individual members, or other members of the Minnesota Legislature.

This document can be made available in alternative formats, such as large print, Braille, or audio tape, by calling 651-296-1727 (voice), or the Minnesota Relay Service at 651-297-5353 or 1-800-627-3529. All OLA reports are available at our Web Site: http://www.auditor.leg.state.mn.us

If you have comments about our work, or you want to suggest an audit, investigation, evaluation, or best practices review, please contact us at 651-296-4708 or by e-mail legislative.auditor@state.mn.us

Representative Tim Wilkin, Chair
Legislative Audit Commission

Members of the Legislative Audit Commission

Ms. Peggy Ingison, Commissioner
Minnesota Department of Finance

We have conducted an information technology audit of information warehouse data integrity controls. The primary purpose of this audit was to determine if the Department of Finance had controls to ensure that data in its information warehouse was both accurate and complete. Our audit assessed the adequacy of controls as of November 2003.

We conducted our audit in accordance with auditing standards generally accepted in the United States of America contained in Government Auditing Standards, issued by the Comptroller General of the United States. Those standards require that we obtain an understanding of management controls relevant to the audit. The standards also require that we design the audit to provide reasonable assurance that the Department of Finance complied with provisions of laws, regulations, contracts, and grants that are significant to the audit. The department’s management is responsible for establishing and maintaining the internal control structure and complying with applicable laws, regulations, contracts, and grants.

Information technology audits frequently include the review of sensitive security data that is legally classified as nonpublic under the Minnesota Data Practices Act. In some cases, to protect state resources and comply with the Minnesota Data Practices Act, we must withhold security-related details from our publicly released report. When these situations occur, we communicate all pertinent details to agency leaders in a separate, confidential document. For this audit, we issued a separate, confidential document to the management of the Department of Finance.

This report is intended for the information of the Legislative Audit Commission and the management of the Department of Finance. This restriction is not intended to limit the distribution of this report, which was released as a public document on February 12, 2004.

/s/ James R. Nobles /s/ Claudia J. Gudvangen

James R. Nobles Claudia J. Gudvangen, CPA
Legislative Auditor Deputy Legislative Auditor

End of Fieldwork: November 14, 2003

Report Signed On: February 9, 2004

Audit Participation

The following members of the Office of the Legislative Auditor prepared this report:

Claudia Gudvangen, CPA Deputy Legislative Auditor
Christopher Buse, CPA, CISA, CISSP Information Technology Audit Manager
Neal Dawson, CPA, CISA Auditor-in-Charge
Mark Mathison, CPA, CISA Information Technology Auditor

Exit Conference

We discussed the results of the audit with the following staff of the Department of Finance at an exit conference on February 5, 2004:

Peggy Ingison Commissioner
Anne Barry Deputy Commissioner
Lori Mo Assistant Commissioner, Accounting and
Information Technology Services
Jean Henning Chief Information Officer
Tim Willson Systems Software Supervisor
Darryl Folkens Information Technology Specialist

Report Summary

Overall Audit Conclusions

The Department of Finance has controls to ensure that data housed in its information warehouse is both accurate and complete.

Background

This information technology audit assessed the adequacy of data integrity controls in the Department of Finance’s information warehouse. Data integrity controls are those controls that help ensure both the accuracy and completeness of data. Our audit focused on security controls, procedures used to load data from production business systems, and the synchronization of data maintenance between production business systems and the information warehouse.

The information warehouse contains a wide range of accounting, payroll, and personnel data. With over 373 million rows of data in 140 tables, the warehouse serves the ad hoc reporting needs of most state agencies. In fact, for the month of December 2003, over 1200 employees from 60 different state agencies retrieved data from the information warehouse.

Chapter 1. Introduction

This audit analyzed how the Department of Finance controls the accuracy and completeness of data in its information warehouse. Controlling information warehouse data integrity is vital because state agencies use this system to support their daily business operations and make strategic planning decisions. In fact, many standard accounting, payroll, human resources, and budgeting reports have been eliminated because state agencies can now retrieve this same information from the warehouse.

The information warehouse began operations in 1995 with 20 users. As illustrated in Figure 1-1, the number of state agency employees who have queried information from the warehouse has steadily increased since that time. For the month of December 2003, over 1200 employees from 60 different state agencies retrieved data from the information warehouse.

The volume of data in the information warehouse has also increased to meet the diverse needs of state agencies. The number of database tables has grown from approximately 58 in 1995 to nearly 140 today. These tables now contain over 373 million rows of data.

The tables in the information warehouse contain a vast array of accounting, budgeting, payroll, and personnel data. The department obtains this data from the state’s central accounting and payroll systems, commonly referred to as the Minnesota Accounting and Procurement System (MAPS) and the State Employee Management System (SEMA4). As depicted in Figure 1-2, the department extracts the data from these systems, converts it to a consistent storage format, and loads the information warehouse tables.
Figure 1-2
Populating Information Warehouse Data Tables

Source: Auditor prepared.

Employees in state agencies use reporting software, such as Seagate Crystal Reports or Microsoft Access to retrieve information from the warehouse. As illustrated in Figure 1-3, these reporting programs locate the computer that houses the information warehouse and establish a connection to its database management system. The programs then issue a request for data, written in a special language called Structured Query Language (SQL). The database management system processes these requests and returns the results.

Our data integrity audit included a review of the procedures and tools used to protect information warehouse data from unauthorized changes. We also analyzed controls over loading information warehouse data tables. Finally, we analyzed how the department synchronizes data maintenance between the statewide business systems and the information warehouse. Chapter 2 discusses the scope of our work and the conclusions that we reached.

Chapter 2. Warehouse Data Integrity Controls

Chapter Conclusions

The Department of Finance has controls to ensure that data housed in its information warehouse is both accurate and complete.

Data integrity controls are those controls that help ensure both the accuracy and completeness of data. In an information warehouse, processes used to load tables must include well-defined data integrity controls. Those controls help ensure that data copied to the warehouse is identical to the same data found in the production business systems, such as the Minnesota Accounting and Procurement System. Data also must be tightly secured once in the warehouse. Strong security controls help protect data from losing its integrity through unauthorized changes. Finally, in some cases, information system professionals must correct data errors in production business systems. When this occurs, organizations need controls to synchronize data maintenance between its production business systems and its information warehouse. Without synchronization, production business system data fixes could lead to a gradual degradation of information warehouse data integrity.

Audit Objectives and Methodology

We designed our data integrity work to answer the following questions:

Did the department have appropriate security administration procedures to prevent unauthorized changes to information warehouse data?
Did the department have procedures to ensure that data loaded in the information warehouse is accurate and complete?
Did the department have procedures to synchronize data maintenance between its production business systems and its information warehouse?

To answer these questions, we interviewed the information system professionals in the Department of Finance who managed the warehouse and designed its data integrity controls. We also analyzed security data from the operating system and the database management system underlying the information warehouse. Finally, we analyzed network security controls using specialized vulnerability assessment software.

We obtained our evaluation criteria from the Control Objectives for Information and Related Technology (COBIT), published by the Information Systems Audit and Control Foundation. The COBIT Framework includes 34 high-level control objectives and 318 detailed control objectives, grouped in four domains: Planning and Organization, Acquisition and Implementation, Delivery and Support, and Monitoring. We also obtained evaluation criteria from publications provided by hardware and software manufacturers whose products were used to build the information warehouse.

Conclusion

The Department of Finance has adequate data integrity controls for its information warehouse. We found that the department implemented appropriate security controls to prevent unauthorized changes to information warehouse data. Furthermore, the processes used to load warehouse data included sufficient data integrity controls. Finally, the department had sufficient controls to ensure that data maintenance performed on production business systems would be synchronized with its information warehouse.

During our audit, some minor internal control weaknesses came to our attention. We communicated those weaknesses to the employees who oversee the daily operations of the information warehouse.