# Presentation: 355065

## Enterprise Information Strategies

**Oregon’s DataMart Solutions**
**Don Lew - State of Oregon, State Controller’s Div.**
- IMA - Tri-Cities Washington Chapter
- December 6, 2000

## Statewide Information Sources

- Accounting - Relational Statewide Accounting & Reporting System (R*STARS)
- Payroll - Oregon State Payroll System (OSPS)
- Budget - Automated Budget Information System (ABIS)
- Purchasing - Advanced Purchasing Information Control System (ADPICS)

## R*STARS - A short history

- R*STARS is Oregon’s Statewide Accounting System
- System was implemented March 17, 1995
- Operates on legacy computer system
- Data is stored in DB2 relational database in several hundred tables
- Emphasis:  OLTP (On Line Transaction Processing)

## System Strengths

- Statewide systems collect data effectively & efficiently
- R*STARS produces preformatted, standard reports
- Reports offer several “special selects” that provide  a limited degree of flexibility

## System Weaknesses

- Standard reports are still not flexible enough - they do not meet a significant percentage of agency needs
- Agency-specific production reports cannot be produced
- Does not address the need for unique, limited, ad-hoc reports
- Information is difficult to extract and use

## Initial Solutions:Mainframe Query Tools

- Nomad Qlist
- Easytrieve IQ
- Direct access to mainframe databases
- Required a high level of technical expertise
- Required high level of knowledge of the complex database structures

## Disadvantages of the mainframe tools

- Data was difficult to get to
- R*STARS data is designed for efficient On Line Transaction Processing, not ad hoc retrieval and reporting
- Data access tools were too technical and difficult to learn

## What did we really want?

- Data in accessible form
- Easy to use access tools
- OLAP (On Line Analytical Processing) capability
- Broad access to the data

## Accessibility Requirements

- Ease of use
- Cross platform capability in the query tool  (PC’s & Mac’s)
- Ad Hoc/Analytical capabilities
- Standard, customizable accounting reports
- Decision Support/Executive Information
- Capability to export data to other desktop tools

## Our solution:Statewide DataMarts

- DataMarts - Targeted databases containing statewide information
- Accounting DataMart
- Payroll DataMart
- Budget Information DataMart

## SCD Accounting DataMart

- Accounting data first loaded in April 1997
- The DataMart flows from the Statewide Financial Management System (SFMS) - Oregon’s central accounting system.
- Contents - Summary and Detail from March 1995 to the present
- Frequency of updates - once a week

## Information Protocols

- Data is extracted and loaded into a Sun Ultra 6000 “mini-computer”
- Agencies connect through statewide wide area network (TCP/IP)
- Connection protocol - ODBC
- Database - IBM DB2 (UDB)

## Contents of the Accounting DataMart

- 5 Financial Tables
- Accounting Transaction Detail
- 2 General Ledger Summary Tables
- Receipt Summary
- Document Tracking Summary
- 30 Descriptive Tables
- Account Names, Agency Names, etc.

## User Acceptance

- This was our top priority
- Task force reviewed data & selected specific tables
- Summary tables were straightforward
- GL Summary, GL Detail, etc.
- Detail table was ‘de-normalized’ to make it usable
- Accounting Event

## Data Validation

- Critical for usability of data
- Users
- Auditors
- Validation conducted each week
- DataMart compared to mainframe
- Record count
- Hash totals for key financial records

## Some Statistics

- Size - 92 Gigabytes
- Accounting detail - about 70 million rows
- Summary tables - up to 21 million rows.
- Contains data for all state agencies

## DataMart Users

- Agency Accountants
- Agency Budget Analysts
- Central Agencies
- State Controller’s Division
- State Division of Audits
- Legislative Fiscal Office

## Query & Reporting 

- What software can be used?
- Any ODBC compliant software
- M/S Access
- Lotus 1-2-3
- ACL
- Our preferred query & reporting tool is the Brio Enterprise suite.

## Security & Training

- User access based on RACF UserID
- Two levels of training
- Orientation to learn about the data
- Customized training in BrioQuery

## Information Control & Usage

- User agencies have control over their own information (through read-only files)
- Extraction of information is easier and cheaper than mainframe reports
- Agencies create customized reports
- Automatic report scheduling & distribution
- Via e-mail
- Via internet

## Types of Reports

- Monthly Financial Reporting:
- Detail Expenditures
- Trial Balances
- Budget Comparison Reporting
- Actual to Budget
- Remaining Budget
- Ad Hoc & Specialty Reporting

## Report Production

- Agencies produce their own Queries and reports.
- Agencies share reports
- Brio Repository is available for agencies

## Report Distribution

- Agencies produce reports & distribute manually
- Brio Broadcast Server
- Brio OnDemand Server

## Some examples - how one agency uses the DataMart

**Department of Human Services****State Office for ****Services to Children ****and Families (SOSCF)**

## SOSCF Sources of Data

- Statewide
- Accounting DataMart (SFMS/R*STARS)
- Payroll DataMart(OSPS)

- Agency Specific
- IIS DataMart
- TRACS DataMart
- AFCARS Data
- Adoptions Data
- Other Local Databases

## Division Goals in using a new query and reporting system.

- Replacement of inflexible, bulky paper reports.

## Provide Branch, Region and Program specific information to Budget Managers at all levels of the organization.
Improve the timeliness of Budget information being provided to managers in support of fiscal and program decision making.
Provide Branch Review teams and internal auditors with transaction level information specific to a branch, or activity, or program

- Improve the timeliness of Budget information being provided to managers in support of fiscal and program decision making.
- Provide Branch Review teams and internal auditors with transaction level information specific to a branch, or activity, or program

- Division Goals in using a new query and reporting system.

## SOSCF Reports

- From Statewide data sources
- Expenditure & Budget reports
- Agency-wide
- Region specific
- Branch specific
- Statewide field expenditures
- Payroll reports

- From Agency Specific data sources
- IIS DataMart - Adoptions & Safe Families Act reports
- SOSCF also merges data from different data sources (IIS & TRACS)

## Questions???

- Don Lew, Accounting AnalystState Controller’s Division(503) 373-7277 ext. 283Donald.E.LEW@state.or.us