Crawford County of Pennsylvania
Crawford County government began an initiative to implement systems that would integrate and enhance their use of data and information for county government decision making and process management. The initiative was directed by the county commissioners and intended to incorporate a variety of financial data sources into a single analytical data warehouse environment. The immediate objectives were focused on county fiscal budget management and monitoring as well as general information sharing between all county departments. A suite of Oracle tools was selected for this purpose. Avanco International, Inc. was selected as a solutions vendor to understand the business requirement and utilize the chosen toolset to develop a solution that facilitates county objectives.
Avanco assisted in hardware selection and completed all installation and configuration of the Oracle Application Server including the Oracle database environment. Following the implementation of the Oracle infrastructure Avanco completed research on county requirements for an integrated decision support solution. Department managers and county commissioners were interviewed by Avanco consultants to ascertain their business and reporting needs. A particular focus was placed on financial analysis. Avanco designed a financial data warehouse based on a dimensional modeling methodology, built automated extract routines, and utilized Oracle Data Warehouse Builder to perform recurring data loads of financial information on a weekly basis. A diverse set of decision support reporting functions were developed including: online financial reports, ad-hoc data analysis, parameter driven executive systems and analytical views. The developed applications were utilized by non-technical staff and managers to quickly analyze data in a manner that was previously thought to be impossible.
Avanco developed a secure internet web portal and Corporate Dashboard to replace the original HTML menu system to facilitate decision support analysis. The implementation of the Crawford County portal application utilizing Oracle Portal offers anytime / anywhere access to critical information in a fully secure and timely manner for better decision making. County officials are pleased with the ability to access critical information in a fully secure and timely manner for better decision making. The new environment has fostered forward thinking to accentuate these new effective methods of communicating and sharing information.
Montgomery County, Maryland
The Division of Solid Waste Services at Montgomery County, MD maintains the Customer Service Information Database (Oscar) which is used by county customer service call center representatives to collect information about trash pickups, missed collections, recycling pickups, special collections, special needs exemptions, and blue recycling bin requests among others.
The Division selected Avanco to expand the capabilities of Oscar for the future and offer additional data and information to staff members. Avanco provided a Certified Oracle DBA to conduct an audit of the database instance and report on the the long-term integrity of the system, security concerns, a maintenance plan and schedule, and upgrade plan. As a second step in the process, the system was upgraded and modified based on the recommendations of the study. Avanco provides ongoing support to Montgomery County in the form of Database Administration, web development, Forms development, studies, and "push" reports generated nightly and distributed by email to contractors and County managers.
Pennsylvania Department of Conservation and Natural Resources
The DCNR is responsible for the development and maintenance of the Parks Reservation and Revenue System (PRRS) application which was originally deployed in 1997. Since that time, code changes have been continually made with little revised documentation. DCNR has a very limited staff and over this period there had been a 100% turnover in staffing in key positions, thus no historical information is available to target where changes have occurred. The PRRS application is comprised of 68 forms, 47 reports and 176 tables. It has been estimated that 30% of the forms are no longer used.
Vendor support was required for Oracle Forms design services along with maintenance assistance and planning for the redesign and upgrade of the enterprise application.
Avanco was chosen to develop a technical approach to assist DCNR with upgrades, code development and system redesign efforts. DCNR looked to Avanco to provide 5 critical deliverables during this initial effort. These included:
- Entity Relationship Diagrams
- Data Flow Diagrams
- Functional Hierarchy Diagrams
- An assessment of each Diagram to determine potential program problem areas
- Identification of unused forms, reports and tables
- Impact Analysis of proposed future changes to the system.
Each module was systematically analyzed and reviewed independently and in conjunction with all other application modules. Documentation was developed based on a reverse engineering approach and delivered to DCNR along with observations and recommendations. Product and system upgrade support was provided during the project.