Advanced Data Tools Logo
spacer
Consulting Training Tech Info Successes User Groups Our Company spacer
   

Over the next several years, online access will be available to more than 2.3 million producers.

USDA - Farm Service Agency
2002 Brio Achievement Award Winner for Best Government Solution

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.

Executive Summary

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.

Background

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

Solution

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.

Results

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.

   
spacer
spacer