Loading...
HomeMy WebLinkAbout06/15/2010, C5 - FOXPRO APPLICATION REPLACEMENT - TIMECARDS/SCHEDULING counat M.U. 6-15-10 j ac En as RepoRt CITY O F SAN LUIS O B I S P O FROM: Debbie Malicoat, Acting Director of Finance& Information Technology Rachel Messner, Administrative Analyst SUBJECT: FOXPRO APPLICATION REPLACEMENT—TIMECARDS/SCHEDULING RECOMMENDATION 1. Award a contract to IntelliTime for replacement of the FoxPro timecard application. 2. Award a contract to Telestaff for replacement of the Fire Department's FoxPro scheduling application and the Police Department's Speedshift scheduling application. 3. Authorize the City Manager to execute these contracts totaling$142,034. DISCUSSION Background As reported to the Council in October 2009, the City has been well-served over the past 25 years from high-quality integrated FoxPro applications developed and maintained at a very low cost in contracting with a local programmer (Bonnie McKee). These applications are the user interface software for a wide range of mission-critical applications throughout the organization, including land use inventory, geographic information system (GIS), payroll timecards and work orders. The applications automate City business processes and are accessed by all departments. However, as discussed at that time, it is necessary to begin replacing these applications with one or more "packaged" systems due to a lack of continuing support for them. While this will require an unplanned resource commitment to do so, this replacement is necessary in delivering mission- critical, day-to-day services to our community in an efficient, reliable and cost effective manner. On February 23, 2010, the Council approved the request for proposals (RFP) for the replacement of these FoxPro applications with a project budget of$1,150,000. Council also directed staff to return with recommendations for contract awards. Selection Process Since many of the vendors indicated that they could meet most (if not all) of the FoxPro application replacement needs, with the exception of the timecard applications, two evaluation committees with members from the affected departments were formed: 1. Geographic information system (GIS), permitting and work order applications 2. Timecards and scheduling applications Because of the distinct nature of these two components and the specificity of the vendor solutions for each, they have been treated as two separate tracks of the project. Staff recommend moving forward at this time with only the Timecards/Scheduling piece because the vendors have been cs-� FoxPro Application Replacement—Timecard/Scheduling Page 2 selected and are available to begin the next stage of the project. A vendor has not yet been selected for the GIS/Permitting/Work Orders piece, however, and so this track may not be ready to move forward for several months. It is in the City's best interest to not wait any longer than necessary to replace the timecard and scheduling applications due to the lack of support and maintenance resources for them. The Timecard/Scheduling Committee thoroughly assessed the RFQ submittals, analyzed vendor qualifications and performed background checks with similar agencies that use their software to narrow the list of firms to the top vendors that could best meet the City's software replacement needs: Kronos; Sungard; and Telestaff. Key criteria in selecting these finalists included the ability to meet most, if not all, of the replacement needs and operate in the current technology environment (such as Microsoft SQL Server database: the only exception to this is Telestaff, which specializes in Fire staff scheduling and time reporting). These finalist vendors received the RFP that focused on terms and conditions, installation schedule and price, with references to detailed documentation generated from group and one-on-one briefings in describing the workscope. In March, staff received information regarding another vendor, IntelliTime, from a survey on timecard programs conducted by the California Society of Municipal Finance Officers (CSMFO). After conducting background research on the company's programs and speaking with a staff member from another city, it was determined that IntelliTime provided a unique solution for both timecards and scheduling from a single source and so should be included in the RFP process. IntelliTime was provided with all project information previously given to the other vendors. They also presented a product demonstration to staff. The RFP deadline was extended for all vendors to allow time for IntellTime to complete their proposal. Final vendor selection was based on the following criteria: 1. Understanding of the work required by the City. 2. Quality, clarity and responsiveness of the proposal. 3. Demonstrated competence and professional qualifications necessary for successfully performing the work required by the City. 4. Recent experience in successfully performing similar services. 5. Proposed approach in completing the work and ability to implement the replacement in a timely manner. 6. References. 7. Background and related experience of the specific individuals to be assigned to this project. 8. Proposed compensation. As reflected above, the contract awards are not based solely on price, but on a combination of factors such as user preference, program functionality, vendor availability and required information technology resources. CSS=� O FoxPro Application Replacement-Timecard/Scheduling Page 3 Cost Estimates Vendors provided cost estimates based on their understanding of the City's needs. In addition, in some cases subsequent negotiations or clarification resulted in revised cost estimates. As summarized below, these cost estimates range from about $30,000 to $120,000 and include: 1. Base software 2. Software modifications 3. Installation/system integration 4. Data conversion 5. Training Timecards _ Scheduling Vendor Initial Fee ` Annual Fee Vendorµ_ Iratigl Fee _ Annual Fee._ Kronos $121,683 $11,408 IntelliTime $89,095 $1.1,844 IntelliTime $98,445 $11,844 Telestaff $38,589 $7,699 Sungard $29,971 $4,421 Additionally, creating an interface to the City's payroll program, Pentamation, will add $5,000 to the total project cost regardless of the selected timecard vendor. Committee Recommendation Based on the above factors, the committee selected the following vendors: 1. Timecards— IntelliTime 2. Scheduling—Telestaff Timecards. Although Sungard was the most cost effective option, it was not selected because the committee does not believe that it provides the same level of functionality that the other programs offer. Kronos was not selected because their program is the most expensive option, and staff overwhelmingly preferred the functionality and usability of the IntelliTime program. Scheduling. IntelliTime does offer a public safety scheduling solution and purchasing both programs from them would provide the advantage of a single vendor for both programs. However, Fire Department staff have a very strong preference for the Telestaff program and do not believe that IntelliTime has the experience or program functionality to handle their complex scheduling needs. Additionally, Telestaff substantially reduced their quote in an effort to gain the City's business, which made their scheduling program the most cost effective. CONCURRENCES The Timecard/Scheduling Committee, as well as the Police and Fire chiefs, concur with this recommendation. Additionally, all City departments have been extensively involved in the FoxPro application inventory, IT needs assessment, RFQ and RFP procedures, vendor demonstrations, and selection process. �S'3 I FoxPro Application Replacement—Timecard/Scheduling Page 4 FISCAL IMPACT The approved overall project budget is $1,150,000, and includes both the GIS/Pennitting/Work Orders and Timecards/Scheduling components. The Timecards/Scheduling component represents $181,500 of the project budget. The vendor quotes for this piece total $142,034; therefore, sufficient funding exists in the project budget to move forward with this component at this time. The annual maintenance and licensing fees total $19,543. The total project costs by vendor are below: Vendor Initial Fees Annual Fees IntelliTime-Timecards $98,445 $11,844 Telestaff- Scheduling $38,589 $7,699 Pentamation-Payroll Interface $5,000 Total $142,034 $19,543 ALTERNATIVES 1. Select different vendors. This is not recommended because the committee made the selections based on extensive research and background checks of all vendors. Additionally, the committee was unanimous in their selection of the IntellTime timecard program, and Fire Department staff have a very strong preference for Telestaff based on their years of experience and their ability to accommodate Fire's complex scheduling needs. 2. Defer or re-phase the project. Given the lack of viable alternatives, it will not be possible to support and maintain these applications if the project is deferred (which is one of the reasons why it is so important to complete their replacement quickly). The City can no longer wait several years to address these applications due to the lack of support and maintenance resources. 3. Do not replace these applications. Given the importance of these FoxPro applications in supporting a wide range of mission-critical applications throughout the organization, the ability of these programs to increase productivity and improve customer service and the lack of viable alternatives to replacing them with a packaged system, we do not recommend this option. ATTACHMENTS FoxPro Application.Replacement Project Plan T:\FoxPro Application Replacement\Council Meetings\CAR 6-15-1 O.doc Attachment­ P ject Plan - F0XPR0_ APPLICATION RE-PLACEMENT October 1, 2009 PURPOSE Respond to the lack of reliable, continuing support for the City's customized Visual FoxPro applications by replacing them with one or more packaged systems that will meet a variety of user needs, allow integration among programs and retain historical data. OBJECTIVES AND DELIVERABLES By March 31, 2010 1. Complete application inventory, identify replacement priorities and determine application groups ("clusters") as appropriate. 2. Select vendor(s) and begin replacing existing Visual FoxPro applications with appropriate packaged systems in accordance with the City's Information Technology(IT) Acquisition and Support Policy. 3. Allocate appropriate resources to acquire and support the replacement applications. 4. Develop training strategy for all City staff who use these applications. In the Interim Given the lack of vendor resources, it will be very difficult to support and maintain these applications in the interim (which is one of the reasons why it is so important to complete their replacement quickly). In-house IT staff has some ability to make minor programming changes. For this reason, any changes to these programs until they are replaced will need to be limited to those that are essential for mission- critical functions and within the capacity of existing staff. BACKGROUND The City currently uses 35 applications written and customized in Microsoft Visual FoxPro. These applications were created over the past 20 years by an outside, one-person consulting fine (Bonnie McKee), who maintained and modified them as necessary. These applications are the user interface software for a wide range of mission-critical applications throughout the organization, including land use inventory, geographic information system (GIS), payroll timecards, and work orders. The applications automate City business processes and are accessed by all departments. Most of the applications are front ends/user portals to Microsoft Structured Query Language(SQL) databases. There are two distinct challenges facing the City in supporting these applications: 1. Obsolescent Software. Microsoft has stated that it will no longer support Visual FoxPro nor will it release any further versions. The City has very limited ability to support and maintain these custom Visual FoxPro applications since this is a disappearing programming language and the code libraries that power the City's applications have been customized by Ms. McKee, who recently passed away. Support for both Visual FoxPro and ESRI's Map Objects (which powers the maps in the City's applications) is scheduled to end within the next few years. The specific version of Map Objects currently used by the City has been end-of-life for several years. For this reason, the four ,}-year 1ttachment_ FoxPro Application Replacement Project Plan Page 2 Capital Improvement Plan (CIP) in the 2009-11 Financial Plan showed converting these applications to Microsoft's current".NET" framework in 2011-13 at an estimated cost of$370,000. 2. No Diable Support for Existing Visual FoxPro Applications. The obsolescence of the software language has been compounded by the recent and sudden death of Ms. McKee. In our efforts to find replacement contract programmers, it has become clear that due to the outdated software and lack of programming documentation, it will not be possible to contract with programmers both willing and capable of maintaining the City's current applications. In short, the challenge facing the City has shifted from converting these applications to more current software to the more difficult and resource-intensive task of replacing them entirely. IT Acquisition and Support Policy In 2001, the City contracted with the Gartner Group for a five-year IT Strategic Plan. At that time, Gartner advised against using locally-developed, customized software for mission-critical applications; and the recommendations of the IT Strategic Plan regarding system acquisition and support were subsequently adopted as part of the City's IT Acquisition and Support Policy. However, for many years prior to the IT Strategic Plan, the City was already contracting with Ms. McKee for design, development and maintenance of customized Visual FoxPro applications. While this approach was not consistent with the IT Acquisitions and Support Policy, the City was well-served for many years by these custom, integrated applications provided at a low cost that did an excellent job of meeting City needs. However, because the consultant is no longer available, and successors have been unable to support these applications, we now recommend using the packaged system approach in accordance with the IT Acquisition and Support Policy. This policy outlines three perspectives when acquiring IT systems: 1. Risk assessment 2. Locally-developed versus packaged systems features advantages and disadvantages 3. Decision-making strategies: decentralized versus centralized Strate,gv Selection: Risk Assessment Low Risk High Risk Not Mission-Critical C Mission Criticality ' Highly Mission-Critical Familiar Technology C Technical Complexity C New Technology Single Department C Organizational Complexity C Organization-Nide Small, Simple Data Base C Application Size C Extremely Large and Complex Local Development Paeka e System Acquisition All of the factors except for technical complexity place most of the City's Visual FoxPro applications in the"packaged system" category. C16-7 FoxPro Application Replacement Project Plan AttaChMent,_ ,=ftge 3 Acquisition and Development Strategies L Local Development ■ Application fully customized to meet local ■ Package provides pre-defined functionality; requirements. changes should be limited to formatting issues. ■ Relatively lower cost. ■ Relatively higher cost. ■ Ability to meet City programming and data base ■ Selection criteria can include its ability to meet requirements. City data base standards. ■ Design limited to local knowledge. ■ Design reflects thinking of a number of agencies. ■ Support limited to small, local organizations— ■ Support provided by the vendor as part of the often a single person. package,with assistance by user groups. ■ Changes in requirements can be identified and ■ User groups and vendor can jointly develop implemented locally. modifications over time as needed to meet changing requirements. ■ There are inherent risks in new development ■ Ability to select system that is already efforts: operational, and conduct reference and • Incorrect or incomplete design. background checks before acquiring. • Faulty programming. • Incomplete or faulty testing. ■ Tendency to-pave the cow path" by ■ Tendency to re-look at business policies, programming to current City business policies, practices and procedures for.improvement as part practices and procedures. of the acquisition and implementation process. Again, each of these features supports using packaged systems in replacing our existing Visual.FoxPro applications. Decision-�Mahin .Strafe Department Decision centralized Decision • Application is not part of strategic system • Application is part of a strategic system initiative. initiative. • Application can be supported on current City- • Application requires introduction of new standard computers, operating systems and technology: or requires system upgrades to networks without upgrades or deterioration in support the new application or maintain system system performance. performance. • All application users are in one department. • Application users are in more than one department. • :all information is generated and entered by one • Information is generated and entered by more department. than one department. • The application is not required for the operation • The application is required for the operation of of other applications, systems or work outside of other applications, systems or work outside of the the department. department. • Interfaces to existing or proposed applications • :application may need to integrate with and systems are Well defined and requirements applications in other departments beyond well- can be met. defined standards. Aft FoxPro Application Replacement.Project Plan chme� Based on the factors above, it makes sense to replace these applications with one or more packaged systems rather than continuing to use individual customized programs; and for the selection process and subsequent implementation to be highly coordinated, with oversight by the IT Steering Committee (ITSC). CURRENT SITUATION The following chart lists each Visual FoxPro application currently in use by all City departments. It identifies the department responsible for managing the application as well as other departments that use and depend on the application to do business: Departments That Use the Application Managing Public Comm P7RccAdmin City Human Finance Application Department Police Fire Works Utilities Dev Arty Res &IT Planning Comm Dev x x x x Applications Unreinforced Comm Dev x x x Masonry Planning Comm Dev x Library Building Comm Dev x x x x x Permits Plan Review Comm Dev x x x x x x x Violation Comm Dev x x x x x x Tracking Land Use Comm Dev x x x x x x x x x x Land Use Comm Dev x x z Maintenance Planning Job Comm Dev x x Tickets Building Damage Comm Dev x x x x x X Assessment Fixed Assets Finance&IT x Timecard Finance&IT x x x x x x x x x x Fire Calendar Fire x Fire Fire x Prevention Weed Fire x Abatement Fire Hose Fire x Tracking(?) Overtime& Fire x Staffing FozPro Application Replacement Project Plan A clamant' .-,Pa ge 5 Departments That Use the Application Managing Public Comm Parks& City Human Finance Application Department Police Fire Works Utilities Dev Ree Admin Arty Res &IT Plan File Public Works x x x x Management Bid Lists Public Works x Encroachment Public Works x x x x GIS Service Public Works x x x x x x x x x x Requests Parks Service public Works x Requests Streets Service Public Works x Requests Building Maint Service Public Works x Requests General Service Public Works x x x x x x x x x x Requests Traffic Model TAZ Output Public Works x Processor Payment Vouchers/Pur Public Works x x x x x chase Orders GeoData Public Works x x x x x x x x x x Overtime Public Works x CIP Project Public Works x Management Trees Service Public Works x Request Keytrack Public Works x Industrial Waste Permit Utilities x Tracking Utilities Work Utilities/ Order Comm Dev x x x x Water Utilities x x x Conservation *Excludes FoxPro applications no longer in use or not in need of replacement Achment FoxPro Application Replacement Project Plan ,,Page 6 PROJECT STRATEGY AND SCOPE Project Strategy 1. Complete inventory of all Ksual FoxPro applications. Meet with representatives from each department to determine which applications are managed and used by them and for what purpose. Also determine departments' application needs and desired functionality. Identify application groups and replacement priorities. 2. Complete high-level assessment of other IT needs Gather information from department contacts on three other areas: a. Processes automated with software other than Visual FoxPro that work well. b. Processes automated with software other than Visual FoxPro that don't work well. c. Processes not currently automated that should be. While these other unmet needs may not be addressed in this process, they are important to consider in evaluating vendors. 3. Select vendors. Prepare and issue Request for Qualifications (RFQ) to identified vendors in order to assess their qualifications in meeting City application needs. Based on responses, prepare and issue Request for Proposals (RFP) to most qualified vendors based on application groups. Proposals will be evaluated by appropriate review teams using several criteria in assessing which systems best meet the City's needs, including responsiveness to the RFP; results of demonstrations and reference checks; implementation strategies and schedule; system cost for both acquisition and ongoing support; and ongoing system support needs by in-house staff. There will be one RFQ issued to identified vendors. However, based on the vendor responses to the RFQ, as well as the designation of application groups, it may be necessary to develop multiple RFPs to meet the wide variety of application needs and functionality. 4. Develop maintenance plan. Determine the amount of staffing and financial resources necessary to maintain and support the new applications. 5. Implement system. Develop and implement replacement systems based on identified priorities and application groups. 6. Train users Develop and implement comprehensive strategy to coordinate the training of all City employees on the replacement applications. Project Scope The City currently uses 35 custom applications written in Visual FoxPro. Many of these are mission- critical applications that affect every department and must be replaced with systems that will meet our short-term needs as well as address long-term functionality and integration issues. Under the City's IT Acquisitions and Support Policy, packaged systems should be used in making this replacement. It would be preferable to move to an organization-wide, over-arching software application solution. � s-�a !loch mee FoxPro Application Replacement Project Plan ge 7 However, given the market place, it is more likely that existing applications will be replaced by several vendors, with applications clustered by similar groupings (such as permits, land-based/GIS applications and work orders). Many of the Visual FoxPro applications, such as work orders and building permits, are mission-critical to departmental operations. Other applications, such as land use inventory and timecards, are also mission-critical and affect organization-wide operations. As noted above, initial replacement plans focused solely on converting these applications to a more current software program, such as ".NET." Accordingly, as part of the four-year CIP, the 2009-11 Financial Plan showed converting these applications in 2011-13 at an estimated cost of$370,000. However, the City can no longer wait several years to address these applications due to the lack of support and maintenance resources. In the present situation, three broader concerns emerge: 1. How do we support these applications in the near-term? 2. What should be our longer-term strategy? 3. How do we get there from here? KEY PROJECT ASSUMPTIONS 1. We will be able to adequately support these applications with in-house resources until they are replaced. 2. We will be able to quickly select a packaged system for each application group at a reasonable cost. 3. These solutions will meet our short and long-term needs; maintain the current level of operational efficiency; allow program integration; and retain historical data. PROJECT CONSTRAINTS AND LIMITATIONS Project Constraints 1. Urgency to replace applications within a short time frame while keeping long-term needs in mind. 2. Commitment of significant financial and staffing resources in a challenging fiscal environment. 3. Need for stakeholder support and buy-in for the project. 4. Standardization of custom applications. 5. Integration of applications and prevention of overlap. 6. Highly unlikely that there will be one vendor with an acceptable universal solution. 7. Training users on new applications. 8. Ongoing support and maintenance of new programs. Project Limitations 1. Under the proposed packaged system approach, replacement applications will not be customized. However, City staff have become accustomed to using applications customized exactly to their specifications and that can be quickly modified at a low cost. It is highly unlikely that any packaged program will be able to deliver the exact functionality that users have become accustomed to. On the other hand, the benefit of the packaged systems is the greater assurance of their viability as ongoing application support is not dependent on one individual. 12 6 FoxPro Application Replacement Project Plan Aftch ent _flege 8 2. It will be difficult to balance the need to make good long-term decisions with the need to also act quickly in selecting and implemeting replacement applications. PROJECT ORGANIZATION AND TEAM Sponsor: Director of Finance & Information Technology Oversight: IT Steering Committee (ITSC) Project Manager: IT Manager Team: Department Heads (from departments managing applications); Application Administrators; Finance & IT Administrative Analyst; IT staff STAKEHOLDERS Stakeholders include all City employees and the community at large that benefits from the cost-effective services performed and information provided by these applications. PROJECT TASKS AND SCHEDULE A.Initial Project Planning , 1. Identify department contacts for all applications. Admin Analyst 8/28/09 2. Complete application inventory and identify"what and Application Administrators 9/24/09 why" for each. 3. Determine application groups, assess priorities, identify top Application.Administrators; 9/28/09 vendors for each application and review project plan. ITSC 4. Present project plan to Council Director of Finance& IT; IT 10/20/09 Manager; Admin Analyst B.Vendor Identification and Qualifications 1. Prepare draft RFQ. IT Manager; Admin Analyst 9/28/09 2. Review and approve RFQ. ITSC; Application 10/5/09 Administrators; IT Staff 3. Send RFQ to selected vendors. Admin Analyst 10/12/09 4. Conduct pre-qualification teleconference(s). ITSC; Application 10/20/09 Administrators; IT Staff; Admin Analyst 5. Submit qualifications package. Vendors 11/2/09 6. Evaluate submittals; conduct finalist interviews as needed; ITSC; Application 11/23/09 participate in vendor demonstrations and/or city visits as Administrators; IT Staff, necessary; finalize application groups and selected vendors. Admin Analyst C.Vendor Selection 1. Prepare draft RFP(s) for selected vendors. Admin Analyst; IT Manager 12/8/09 2. Review draft RFP(s). ITSC; Application 12/18/09 Administrators; IT Staff 3. Approve RFP(s) and appropriate funds. Council 1/5/10 4. Send RFP(s) to selected vendors. Admin Analyst 1/6/10 5. Submit proposals. Vendors 2/8/10 0 57-�-1a- FoxPro Application Replacement Project Plan - -rage 9 6. Evaluate proposals and conduct interviews as necessary; ITSC; Application 3/26/10 prepare recommendation to City Manager. Administrators; IT Staff; Admin Analyst 7. Award contract(s). City Manager 3/31/10 D.Implementation* 1. Prepare detailed project implementation schedule. IT Manager; Admin Analyst 4/15/10 Application Administrators; Vendor(s) 2. Convert databases, complete installation and modify Application Administrators; IT 8/15/10 departmental procedures as needed to implement Staff;Vendor(s) applications. E.Training* _ 1. Identify training needs and develop strategy. Application Administrators; IT 4/15/10 Staff; Admin Analyst Vendor(s) 2. Finalize training schedule and delivery. Application Administrators; IT 5/15/10 Staff; Admin Analyst Vendor(s) 3. Conduct staff training. Vendor(s) 8/15/10 F.Project Wrap-Up . 1. Troubleshoot and complete all aspects of project Application Administrators; IT 9/30/10 implementation. Staff 2. Present report on lessons learned to ITSC. IT Manager; Application 10/30/10 Administrators; IT Staff; Admin Analyst * Each identified application group may have its own implementation and training time schedule. However, they will all follow the same process. PROJECT RESOURCES It is too early to identify the financial resources necessary for this project; however, significant staff resources will be required. As reflected in the 2011-13 CIP, simply upgrading to a ".NET" system was estimated to cost approximately $370,000. Accordingly, replacing all Visual FoxPro applications is likely to cost significantly more. Based on the experiences of other organizations, rough estimates range from $500,000 to $1.5 million. However, we will not have a clearer idea of likely costs until completion of the RFQ process.