Skip Maine state header navigation
A Publication Featuring The Information
Services Technology of Maine State Government
| Volume V, Issue 11 | December 2002 |
|
|
In the spring of 2001, Maine Revenue Services (MRS) started populating its data warehouse by loading federal individual income records information, the IRS Business Master File (IRS corporate information), and Maine individual, corporate and estate tax returns from the Maine Automated Tax System (MATS). The warehouse supports easy access to business data, and on-line analytical processing (OLAP). The goal is to provide information enabling improved employee productivity, and better customer service through more accurate assessments and focused collection efforts. Warehouse use has already resulted in a $300,000+ addition to State coffers from desk audits of individual tax returns.
BACKGROUND Traditional transactional systems store data in normalized relational databases, which use entity-relational modeling techniques to reduce data redundancy. Databases are designed to support On-line Transaction Processing (OLTP), which capture and process business transactions. MATS is a traditional OLTP system based on CICS on-line transactions and a nightly batch cycle.
Alternatively a data warehouse supports OLAP with data that is accessible and navigable. The warehouse is composed of fact and dimension tables. The fact tables are generally numeric and additive such as "tax" or "income". Every fact table contains a set of two or more dimensional foreign surrogate keys, which join the respective dimension tables. Dimension tables contain textual attributes fields that are the basis for constraining and grouping the data within the data warehouse queries. For example, "residency", "location," "filing status", and "date processed" are dimensions. Dimensions improve efficiency and presentation. We have loaded data from MATS to staging tables and have started building dimensions.
Traditionally, data staging areas include the storage and the set of processes that clean, transform, combine, and prepare the source data for use in the data warehouse. This includes extracting, loading to staging tables, generating surrogate keys, loading dimensions, loading fact tables, and generating aggregate summary tables. MRS’ users currently have direct access to staging tables via Oracle Discoverer while the dimensional modeling is being developed.
Congratulations to the Taxation folks and those who support them on their first place finish in the 2002 Digital State Survey, Part III Taxation section. This is a tremendous accomplishment and well deserved. Also, congratulations to Education and Transportation/GIS for their 14th place finish. It just goes to show that we are investing our limited resources wisely and effectively! http://www.centerdigitalgov.com/center/02top25states-pt3.phtml CIO Harry Lanphear |
WAREHOUSE OBJECTIVES
TECHNOLOGY Warehouse data is stored in a database housed in the Bureau of Information Services’ (BIS) Oracle Environment. The data is loaded from the IBM mainframe to Oracle using MVS SQL LOADER. A menu driven on-line for quick look-ups of lists and individual detail records is available. This is an Intranet system built with PL/SQL servlets that are stored in the ORACLE database and send HTML and Java script out to web browsers.
MRS will continue loading data from MATS and other sources, improve and further refine the dimensional modeling, rewrite the Intranet application with JAVA Server Pages, and implement Oracle’s fine grain access control and fine grain auditing, and Dicoverer9i web version.
I want to thank the following for their role in the success of the warehouse project: Mike Berube, and John Hawkes from BIS’ database team, and John Dewitt, Kelly Hann, Kathy Burton, James So, Sandy Caruso, Becky Clark, Keith LaRochelle, and Linda Russo from MRS’ systems team.
