1 / 14

State of Wisconsin

State of Wisconsin. Department of Revenue Data Warehouse Presentation August 16, 2000. Agenda. $. $. Purpose of the WIRED Project Development Timeline Technical Process Subject Areas Development Challenges Lessons Learned Audit Bureau Changes Audit Selection Efficiencies

stephencoleman
Download Presentation

State of Wisconsin

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. State of Wisconsin Department of Revenue Data Warehouse Presentation August 16, 2000

  2. Agenda $ $ • Purpose of the WIRED Project • Development Timeline • Technical Process • Subject Areas • Development Challenges • Lessons Learned • Audit Bureau Changes • Audit Selection Efficiencies • Demonstration • Summary of Advantages • Questions & Answers $ $ $ $ $ $

  3. Purpose of the WIRED Project • Generate sufficient audit candidates to enable work units to produce an additional $4.78 million in revenue • Increase flexibility and efficiency of access to Sales & Use and Corporate Tax Data • Develop plan for data warehouse growth and support • Increased familiarity and comfort with data warehouse concepts and tools • Improve efficiency of business tax audit project selection Warehouse for Integrated Revenue Enterprise Data

  4. Development Timeline Dec Jan Feb Mar Apr May Jun Business Requirements Definition for Pilot Data Architecture- Pilot Data Access and Transformation Strategy Implement ETL Requirements Develop Business Objects Universes Data Validation System Testing User Acceptance Testing Pilot Production Loading & Revalidation Train Developers Train Users

  5. Technical Process - High Level Users - Business Objects & Web MetaData Browser Existing NT Web Server • Data loaded on a monthly basis • Corp Extract and Match file are used to drive other monthly reporting needs New Registered Non-Filer Corp File Corporate Tax System DB/2 New Relational DB/2 Data Warehouse, Metadata Repository Existing DB/2 Extract Table New Relational DB/2 Operational Data Store Transformation, Cleansing & Loading Transformation and Aggregation Existing Matched Key File New Detail Flat File Sales & Use Tax System IMS

  6. Technical Process – Focus on ETL DW Taxpayer Fact Load DW Taxpayer Fact S/U Return Fact Load DW S/U Return Fact Corp. Return Fact Load DW Corp Return Fact Aggregate S/U and Corp Fact In the DW: - Aggregate S/U & Corp to tax year - Create S/U & Corp non-filer records - Create “Invalid” dimension values ODS S/U Taxpayer Mainframe S/U Return Detail Extract S/U & Load ODS Load Corp Non- Filers to ODS Corp. Taxpayer Extract Corp & Load ODS Corp. Return Detail In the ODS: - Data Validation

  7. Subject Areas Tax Registration Penalties Apportionment Audit History Losses Assets Credits Receipts Subtractions

  8. Development Challenges • Scope of project • Using Match file in the S/U & Corp match-merge process • Matching and aggregating of tax return data • S/U Timestamp • Method for handling Corp and S/U Non-Filers • Method for handling S/U Audit History data • Corp Extract errors • Speed of loading the ODS and DW

  9. Lessons Learned • Clear scope is critical to success • Quality is more important than quantity • Build transition into the project plan

  10. Re-engineering the Audit Bureau Each functional unit (corporate, sales, field and Nexus) worked independently Little opportunity for Audit staff to share information and work collaboratively on projects No system support for Audit projects Little communication and planning for upcoming project with DOR units outside of Audit (e.g. central files, mailing, etc.) Functional unit members combined into new, multi-functional team Multi-functional team collaborates on audit projects based on shared information from the data warehouse Staff has opportunity to learn new IT skills Staff has opportunity to take part in progressive and innovative project Before After

  11. Efficiencies in Audit Selection • Decrease in average and amount of time per field audit selected and assigned • Data Warehouse provides easy way to investigate taxpayer groups and segments • Ability to investigate multiple data points for a taxpayer

  12. Demonstration

  13. Summary of Advantages • Common Definitions for Business Terms and Data • Highly Flexible Environment • Ease of “getting the data out” • Faster Response to Requests for Reports and Data • Reduction of staff effort to answer a question • Standardized Way of Approaching a Question • Construction of selection logic • Extraction and transformation of data • Data are pre-validated • Report sharing through the Business Objects repository

  14. Questions & Answers

More Related