Closing the Tax Gap in Iowa

by Teradata Staff

The U.S. economic downturn and higher unemployment rates are squeezing already tight state-government budgets. That makes the job of state-government revenue departments all the more difficult. In Iowa, that has meant a renewed reliance on technology, and specifically, its enterprise data warehouse, to help close the tax gap and ensure a strong revenue base for the state.

In a typical year, the Iowa Department of Revenue processes more than 4 million documents as part of its tax collection function. In 2003, the state collected in excess of $5.9 billion dollars from individuals and businesses, and issued $672 million in refunds.

The Iowa Department of Revenue headed by Michael D. Ralston is committed to enhancing voluntary compliance and collecting all taxes due.

Toward those goals, the revenue department places emphasis on customer service and education, audit and collections processes and document processing systems. The revenue department’s efforts have yielded higher voluntary compliance rates in all tax types, while helping to eliminate many common filing errors.

Recognizing the important role of innovation and technology to agency efficiency and strong customer service, the department of revenue embarked on a data warehouse journey in November 1999 with its decision to invest in a Teradata ® Warehouse.

Business Objectives

Prior to adopting a data warehouse, the revenue department’s IT staff lacked the business software and tools to handle the intricacies of writing audit applications. The result was software applications that "were difficult and time consuming to complete," recalled Rhonda Kirkpatrick, data warehouse program manager for the State of Iowa. "We just didn’t have the resources to allocate to audit programs to keep up with supply and demand."

The department relied on mainframe applications and 20 disparate standalone systems and file extracts that couldn’t talk to one another. A positive step in consolidation began in 1997 when the state unveiled its new Integrated Revenue Information System (IRIS) that combines several of these systems together, but critical systems such as accounts payable and the data exchange from the U.S. Internal Revenue Service remained outside of IRIS.

The audit information generated from this non-relational database environment produced a paper document of "potential" leads which required manual review and evaluation prior to implementation. Kirkpatrick explained that the follow-up required to determine if an audit lead was productive took valuable time away from state examiners and auditors doing their job which is to ensure the equitable administration of Iowa’s tax laws and the efficient collection of state tax revenues.

During the 1997 Iowa Legislative Session a bill was passed that provided for Performance Based Procurement. This change in the law facilitated the Iowa Tax Gap Project by providing an innovative funding mechanism to acquire new technology to enhance audit information. In January 1998, the department initiated a Request for Information (RFI) process. In December 1998 a Request for Proposal (RFP) was released. On March 9, 1999, Request for Proposal responses were due.

The Teradata Choice

Working closely as a team, the Compliance Division and the IT Division issued a request for information, identifying their business needs. Based on the proposals submitted by 13 companies, including Teradata, the State decided to issue a formal Request for Proposal outlining their specific requirements and their technology of choice: data warehousing. After reviewing the proposals, interviewing existing data warehouse customers and talking to leading industry analysts, the Audit and IT Data Warehouse Project Team selected Teradata for their partner.

In August 1999 a contract was signed with Teradata for an Enterprise Data Warehouse. The hardware and software architecture for the Data Warehouse platform is defined in Appendix 1.

Kirkpatrick explained, "We selected Teradata because of the platform’s superior performance and Teradata’s impressive customer service record. We also believed that Teradata provided the best platform for growth."

Currently, one Teradata Professional Services staff member works full time alongside the revenue department staff, writing queries and ensuring the logical data model is fully operational. In the past 18 months, the department has reduced its reliance on Teradata Professional Services staff by expanding its technical staff.

As of December 2003, the State of Iowa has collected more than $34 million in unpaid taxes since deploying its warehouse. Iowa’s Revenue Department paid for its enterprise data warehouse with revenues generated as a direct result of the Tax Gap project.

Iowa Department of Revenue predicts sustained revenues of at least $10 million per year from the project. The department currently has 125 business users accessing the data warehouse on a daily basis and that number is planned to increase to more than 200 users.

The Enterprise Data Warehouse

In December 1999, work began to build the warehouse. By February 2002, the Logical Data Model was finalized and in March 2002 the Web-based Audit Support Component was partially implemented. The Physical Data Model was fully implemented in December 2002. In March 2003, both the Web-based Audit Support Component and the Business Objects interface were fully implemented.

The enterprise data warehouse consists of data from eleven major source systems including data from: internal source systems, other state agency source systems, U.S. Internal Revenue Service, and other third party data sources.

The enterprise data warehouse model contains over 2,550 data elements from the eleven major source systems. Through the development of business logic and query routines, specific data is matched from a number of diverse source systems to identify areas of tax non-compliance. Each match routine developed generates a list of potential audit leads. For each lead, data is gathered from the diverse source systems to provide a complete view of the information available on each source system. The leads produced from each audit program via the data warehouse are then loaded to the Web-based Audit Support Component application for assignment and further investigation. The data gathered for each lead from the various source systems are made available to the business users in a report format to provide them with a complete view of information and assist them in their audit activities.

The Wed-Based Audit Support Component application automates and centralizes all audit activities performed by the department. The audit support component supports audit performance, tracking of all audit activities, and maintains data to provide a wide variety of key management reports.

Key audit activity events for each audit trigger movement of data about that audit back to the data warehouse. From the data warehouse, audit data then moves to the department's integrated tax system, Information Revenue Integrated System or IRIS. Based on specific criteria and data for each audit transaction, IRIS automates the audit results such as generating a tax due notice or issuing a refund.

Upon completion of the final status events for each audit in IRIS, a refresh of data is sent from IRIS to the data warehouse on a daily basis. Final audit result data then moves from the data warehouse to the audit support component for final status updating of each audit.

"The Teradata system assists our examiners and auditors by giving them more information so they work smarter and are less intrusive to compliant taxpayers," Kirpatrick says. "Teradata is helping us get the information into the business user’s hands - they know what data they need and how they need to use it."

In summary, the enterprise data warehouse (EDW) assists with the following:

Lead Generation - By applying specific business rules, the EDW provides a list of potential audit leads that based on specific criterion have not complied with Iowa taxation laws. These leads are loaded from the data warehouse into the Web-based Audit Component application for further investigation.

Web-based Audit Component Application - This is a case management application on a separate hardware platform from the EDW. However, this application uses Teradata as the database. Potential non-compliance cases are worked and tracked by the examiners and auditors using this system. Data from the EDW are used to calculate taxes due and recorded in the application.

Value Added Application - Through query development, the value added application brings together data from a variety of source systems in the EDW, a single view of data to support each potential audit lead. This application gets the data into the business users' hands to assist the users in the day-to-day audit and examination activities. Data is extracted from the EDW using MS Access screens and Business Objects reports.

Business Insights

For the Iowa Department of Revenue, the Teradata Warehouse is helping state auditors and examiners do a better job of identifying non-compliant taxpayers. Soon, the warehouse will help them go a step further, to see the reasons behind non-compliance.

"We want to analyze audit data and results to determine the cause of non-compliance. Was it an education issue? Was it a tax law issue? Was our tax form not clear?"

Kirpatrick's team has recently added a capability to refine the audit-match process by being able to validate an identification number that appears to be incorrect by cross-referencing a name or street address.

"In the past, when we did mainframe matches, we couldn’t handle the complexity we can now," Kirkpatrick says.

Over the next 12-18 months, new external sources of data will be integrated into the data warehouse, including sources such as census data, Yellow Page information, and corporate information sources. This additional data will enhance tax compliance analysis by providing a broader base of information and complementing existing information.

The Business Objects component will be enhanced to provide power users with greater capability to analyze "what if" scenarios; for example, about how changes in tax reporting requirements would affect taxpayers' compliance capabilities. The Business Objects component will also be expanded to create additional end-user reports, which will in turn help the agency make better management decisions.

Enhanced modeling will be developed to empower the department to better measure tax non-compliance issues and to better understand characteristics behind non-compliance, such as the need to provide and promote improved educational tools for our customers. This will help the department become more proactive in ensuring tax compliance, while better serving its customers.

Revenue department officials anticipate at minimum a 10 to 15 percent increase in audit-program efficiency as a result of the data warehouse.

Why Teradata?

In choosing Teradata as its data-warehousing partner, Iowa’s tax sleuths hope to have a system that will continue to pay dividends.

"Teradata provided the best platform for scalability and enhancement. Right now we are focused on tax returns and non-filers, but soon we hope to expand our base of users to include our policy people who want to use the warehouse to do ‘what-if’ scenarios for the legislature," states Kirkpatrick.

For example, the Teradata Warehouse could help determine the fiscal impact of eliminating interest income for all individual income tax.

"I’m really excited about the future of our data capabilities with Teradata. Armed with more accurate information at their fingertips, our auditors and examiners can work smarter and be more productive," says Kirkpatrick.

In summary, a Teradata enterprise data warehouse has helped Iowa’s Department of Revenue realize the following benefits:

  • Optimized Revenue and Promoted Voluntary Compliance. Directly related to this program an additional $35,000,000 in tax revenues have been generated since implementation in early 2000. The program continues to build a strong revenue stream with anticipated revenues of at least $10,000,000 annually.
  • Improved Accuracy and Greater Efficiency. The integrated database structure enables the matching of data from diverse source systems to perform sophisticated queries with drill-downs to analyze and verify the data to identify and generate improved and more accurate tax gap leads. Value added data is now available to the state auditors and examiners for each audit lead, providing them with a single view of data to support their work activities. The data warehouse structure supports a repository for the automated movement of data to and from other key legacy systems.
  • Increased Management Reports. The data warehouse stores a wealth of data and through the use of query and analysis tools, the department has increased its ability to measure results and obtain the data to support key business questions and further enhance programs and activities.

Appendix 1: Technical Summary

HardwareWorldmark 4800 Dual-Node Server, 720GB Raid 1 Disk Array
ApplicationsTeradata V2R4.02 Relational Data Base Management System (RDBMS) on Unix Operating System and SQL 2000 on Windows 2000. Scheduled to upgrade to Teradata V2R5. Teradata ODBC Driver. Teradata FastLoad and MultiLoad. Basic Teradata Query Language (BTEQ). Queryman. Arcmain NT.
Professional servicesBusiness Discovery, Installation Services
Data Warehouse Consulting, Logical Data Modeling Services
Data Mining and Modeling Services
Partner productsBusiness Objects Query Tools

The hardware and software architecture for the Web-based Audit Component Application database is defined below. This architecture supports operational performance and configuration flexibility.

  • Worldmark 4400 Server
  • S-50 Development Server
  • Microsoft Windows NT Operating System
  • Teradata V2R4.1 Data Base
  • Windows 2000
  • Teradata ODBC Driver
  • Arcmain NT
  • Allaire Cold Fusion
  • Atanasoft T-Lets Java Applet

About Iowa Department of Revenue

The Department of Revenue is organized into five divisions: Compliance Division, Revenue Operations Division, Technology and Information Management Division, Internal Services Division, and Property Tax Division. The Iowa Tax Gap project and data warehouse are coordinated by the Compliance Division, Don Cooper, Administrator. For more information check www.state.ia.us/tax/.

About NCR Teradata

Teradata, a division of NCR Corporation, is the global leader in enterprise data warehousing and enterprise analytic technologies and services. Based in Dayton, Ohio, NCR (www.ncr.com) employs approximately 29,300 people worldwide. For more information check www.teradata.com.

About Iowa

Iowa became the 29th State to join the United States in 1846. It is known as the Hawkeye State, and Des Moines is the capital city. Iowa is centrally located in the heart of North America and it is bordered by two great American rivers (the Mississippi and the Missouri) on its east and west sides. It has a rich agricultural tradition and ranks first in the nation with corn and soybean production as well as in hog production from its 93,000 farms. The largest source of personal income for Iowans is manufacturing, followed by services, retail/wholesale trade and government. Iowa has three state universities, 62 public and private colleges and 28 community colleges. According to the most recent census figures, Iowa's estimated population is 2,926,324. For more information check www.iowa.gov.


Some Questions for Further Analysis and Discussion

  1. What is the purpose of the data warehouse?
  2. Is the enterprise data warehouse part of a data-driven Decision Support System? Why or why not?
  3. Is the Web-based Audit Support Component Application plus the enterprise data warehouse a data-driven DSS?
  4. What decision support technologies were used?
  5. Does the data warehouse support any transaction processing?
  6. What is the major benefit of the enterprise data warehouse (EDW)? Why?
  7. Should other taxing authorities create this type of decision support capability? Why or why not?
  8. What problems or difficulties do you anticipate with the use of this type of decision support solution?

Please cite as:

Teradata Staff, "Closing the Tax Gap in Iowa", February 21, 2004, at URL DSSResources.COM.


Dan Conway, Director, Public Relations, Teradata, a division of NCR Corporation, provided final permission to publish this case study at DSSResources.COM on February 20, 2004. Conway's email address is dan.conway@ncr.com. The original case study was posted at the Teradata website in 2002. This case study was posted at DSSResources.COM on Saturday, February 21, 2004.

DSSResources.COM editor, Dan Power, had a phone interview with Rhonda Kirkpatrick, Data Warehouse Program Manager, State of Iowa, on Friday, Feb. 6, 2004 to confirm and update the information in the original Teradata case.


Original case © 2002 NCR Corporation. Used at DSSResources.COM by permission. Product names and trademarks may be trademarks and/or registered trademarks of their respective companies.

This case study is provided for informational purposes only. DSSResources.COM makes no warranties, express or implied, about this case summary.