|
The Farm Service Agency Financial Data Warehouse was
awarded the 2002 Brio Achievement Award (Co-sponsored by Forbes,
Computerworld, and Intelligent Enterprise magazines) for the Best
Government Solution.
Jeffrey O'Connell, CPA, USDA, Farm Service Agency,
Kansas City Finance Office, receiving the Award from Brio
Brio Improves Accounting Operations
Management for USDA Farm Service Agency - Brio Provides Quick Data
Access, Accurate Reporting and Significant Time and Labor Savings
Advanced DataTools has implemented a prototype with FSA to map
the sources of revenue using ESRI’s ArcIMS and ArcExplorer.
In 1999, the USDA Farm Service Agency (FSA) implemented a new
CORE financial accounting system and decided to concurrently implement
a new data warehouse to provide decision-maker access to financial
information. The data warehouse was designed to meet growing analysis
and reporting needs driven by FSA’s strategic goals, by eGov
initiatives, and by Federal government management reporting mandates.
Using the Brio Performance Suite and the data warehouse, nearly
1,200 accountants and financial managers at all levels of the FSA
now manage their own program and payroll budgets. They can now quickly
respond to a producer's request for detailed financial data, including
even the details behind a 1099 Tax report received by the producer.
In October 2002, data warehouse access deployment began to more
than 10,000 staff in all 2,540 US counties. Over the next several
years, online access to the Brio solution will be available to more
than 2.3 million producers.
The Farm Service Agency administers, manages, and controls legislation
passed by Congress to assist the agricultural community. FSA provides
program support to producers and the administrative support needed
to maintain those programs. This legislation includes loans and
subsidies to producers, conservation programs, price and inventory
stabilization programs, foreign export of agricultural products,
foreign and domestic food donations, and disaster relief.
FSA’s Financial Management Division goals for 2001-2005 include:
- Allowing customers to share data and receive services electronically
via the Internet
- Improving the quality, availability, and speed with which information
is shared with the public, other agencies, and private sector
entities
- Having a financial information system that can produce auditable
financial statements and provide reliable and data for decision-making
- Fully implementing the DCI Act of 1996 to maximize debt collection
- Implementing an automated HR system that increases the usefulness
and availability of HR information for workforce planning/reporting
and decreases the resources required for personnel administration
Staff in state and county offices were held accountable for their
budgets, but were lacking timely budget management reports.. Budget
and fund status reports for states and counties were usually more
than two months old. Headquarters held a significant portion of
a local office's budget in reserve until late in the fiscal year,
when any excess would be allocated elsewhere. Detailed payroll information
was simply not available. County office expenditures could be reported
incorrectly, and a county could be told they were over budget, but
they had no way access details. Producers’ farm loan debt
information was scattered across multiple systems with no consolidated
view of outstanding balances and payment history.
The Commodity Credit Corporation had no way to get needed reports
directly from the CORE financial system. Instead, data was entered
into the CORE financial system then transferred to a mainframe reporting
system, which generated text files that were downloaded into Lotus
1-2-3, where macros were used to produce the required reports.
The old reporting system was antiquated, unfriendly and required
specialized programmers to generate reports. Ad hoc queries had
to be programmed by a handful of developers who also developed and
maintained approximately 300 standard reports. State and county
hardcopy loan summary reports containing old data were mailed every
6 weeks
The FSA data warehouse (SUN E6500 with 12 CPUs, 6GB of memory,
and 264 disks (2.4TB) using an IBM Informix database server) is
loaded with fresh data every night, after which the Brio Broadcast
Server generates over 200 new reports . Most decision-makers find
these pre-built Brio reports ideal for their needs.
With Brio Intelligence, local office managers view a variety of
reports empowering them to track loan payments, expenses, ensure
vendors are paid on time and do future projections of lease agreements.
State offices access pre-built payroll reports that are QA’d
by accounting staff, compared with the General Journal and OMDR
(modified detail record) and delivered within 10 days of payroll.
At headquarters, Brio is used to track national wage & time
reporting, and perform cost benefit analyses. Users can immediately
identify budget and payroll coding errors. Employees can change
their W-4 and request to see the detail of the changes in their
payroll within days rather than 6 or 8 months later, as used to
be the case.
New data marts are under development. They include a Payments data
mart to consolidate and track producer payments, and a Public Access
data mart to allow producers online access to loan information and
FSA services and programs.
FSA’s goals are being met through the use of the data warehouse
and Brio reports and queries. FSA is better able to manage farmers’
debt and payment collections facilitated by a consolidated view
of producers’ loans and payment history through the data warehouse.
State and County Executive Directors manage their budgets with data
that is only one day old. Improved information enables better decisions
to guide the agency.
According to Jeffrey O’Connell, CORE Data Warehouse Project
Manager and Accountant, Accounting Systems & Planning, “Brio
is our future, our flagship software, and is what runs this office.
I am now training headquarters, state, and county office staff in
financial data of every form and fashion – I don’t know
where we’d be without it.”
More than 1,200 decision-makers regularly use Brio. In October
of 2002, rollout of the data warehouse began to the remaining counties
(2,540 in all, with over 10,000 staff). Reports are routinely generated
for managers at all levels, Congress, and other agencies. Employees
have been trained to use Brio, and frequently select data, then
use Brio, or export data to spreadsheets for analysis.
As employees retire, they are not being replaced, yet due to the
data warehouse and Brio, the workload remains manageable despite
these smaller staff levels. Using Brio, monthly, quarterly, semi-monthly,
and annual fiscal closings of key financial periods are completed
on time. Significant savings in mailing and mailroom personnel expense
have been realized.
Advanced DataTools Corporation - FSA Consulting Partner
Advanced DataTools Corporation provided the design, software development
and implementation consulting support to FSA to build the award-winning
data warehouse and implement their Brio solution. Advanced DataTools
has also implemented a prototype to map commodity payments, office
expenses, and crop disaster payments using ESRI’s ArcIMS and
ArcExplorer.
"Without ADTC's invaluable assistance to our data warehouse
efforts, FSA would be years behind on our current deployment schedule.
The personnel from ADTC are well-trained and meet FSA's needs for
time and cost savings in our complex data warehouse efforts. We
greatly appreciate all the help that Lester and his staff have given
us over the last four years. Each year we find ourselves moving
forward in amazement at the progress that we have made. ADTC was
giving critical assistance all along the way." - Jeffrey O'Connell,
CPA, USDA, Farm Service Agency, Kansas City Finance Office, November
2002.
|
