APPLICATIONS ANALYST & DATABASE SUPPORT - DIS AND LPMS MAINTENANCE FY2012 1. BACKGROUND. The U.S. Army Corps of Engineers has the mission to maintain the congressionally authorized navigation channels in a safe, efficient and environmentally sustainable manner. To accomplish this mission navigation data are essential to support sound defensible decisions. The Corps Institute for Water Resources Navigation Data Center has developed navigation databases of lock and dredging data in response to this data requirement. To maintain the systems the Corps has identified the need to secure an applications analyst, non-personal professional services for assistance in the technology maintenance of the Navigation Data Center's (CEIWR-NDC) current Dredging Information System (DIS) and the Lock Performance Monitoring System (LPMS). In addition, the need extends to the requirement to have services for maintenance of a historical Oracle data instance and an Oracle data warehouse. These services will be provided to the NDC, Alexandria, VA. DIS is currently a web based Oracle application. Data is entered into the database via a web browser by district dredging personnel. The program manager reviews the data for inconsistencies via web based reports, SQL queries and MS Access queries and reports. The database resides on WPC21 in Oracle 10gAS/10gR2. LPMS is currently a web based 10gR2 Oracle application. Data is entered locally by lock personnel via the web and edited online via PL/SQL and Javascript programs. Online reports are also available. More complex reports using larger data sets need are available thru the Oracle data warehouse (LPMSW) and pre-2000 archived data (EXWS). 2. SCOPE The scope of this work is to provide technical and analytical support services to operate and maintain the navigation databases of LPMS, DIS and the lock data warehouse. The work requires knowledge of the employment of all hardware, software and services in the area of Oracle database configuration and design, to both Intranet/Internet and screens for both data entry and reporting across the Corps of Engineers network backbone. The knowledge shall include: Oracle, 10gAS/10gDS, Oracle versions 11gR2 and 12g for Solaris and Linux; Windows XP/Windows 2007 desktop and Linux; SQL; Analytical SQL, Oracle Web tools including the Apache Listener; current versions of Oracle Developer and Designer; Oracle PL/SQL; Oracle Spatial; Oracle Map-viewer; Oracle APEX; Oracle Text; Oracle JVM; Oracle XDB; Oracle APEX; HTML; HTML 5; XML; XSL; XSLT; KML; Javascript, Java; PERL, PERL DBI, MYSQL, PHP, AJAX and wireless protocol access; MS Access 2007; Location mapping services; Google/Earth mapping service and client. In addition, knowledge about RFID, TCP/IP, capture and transmission of NMEA data and integration with an Oracle application is required. 3. APPLICABLE DOCUMENTS. Army Regulation (AR) 25-1 Army Knowledge Management and Information Technology AR 380-380 Automation Security TB 18-100 Army Automation Life Cycle Management Section 508 amendment to the United States Workforce Rehabilitation Act of 1973 4. TASKS: The contractor personnel shall execute the following tasks as specified below. The percentage for each task is an estimate of resources required for the task. a. Maintain development and test environments for or accessible by the Government in a Solaris 10/Linux hardware environment for LPMS and DIS in ORACLE versions 9i, 10gR2/10gAS, 11gR2 and 12g; Oracle packages APEX, Spatial, Open Source (i.e. JAVA, Javascript, Perl, PHP, MySQL, AJAX, Wiki etc.), OraTweet. (12.0%) b. Optimize LPMS data entry performance including reducing online data to current and last 2 years of data, providing multiple backup sources of archived data. (5.0%) c. Maintain and improve the AIS data by refining programs that decode the encrypted data stream into data suitable for insertion into the production LPMS Oracle database. The programs will be refined to work with input from the MVN and Tenn-Tom locks and other date feeds as they become available. (5.0%) d. Maintain the LPMS tools menu including the data quality console, DBMS job monitor, Schema Analyzer, Problem Tracker, Data Quality Console, Security/Tracker Audit Trails and Ad Hoc query builder for all the LPMS/DIS schemas including Q0DIS222, Q0PMS444, Q0PMS666, Q0PMS888, Q0ALL888, Q0WHR888, Q0RCK888 and Q0HNT888. (17.0%) e. Maintain custom overlays (navigation charts, etc.) into a viewer interface based on nautical charts in S57/S100 format converted to a kml file format. Viewer currently displays in a Google Earth interface using an Enterprise Google Earth Pro Corpswide license. This may have to change if the Corps standard viewer changes and/or a standard map repository is designated. (5.0%) f. Provide technical support to maintain, modify and enhance the data feeds to the OMBIL OLAP application, including adjustments to summary table calculations and any ancillary reports. (5.0%) g. Modify DIS screens, reports and triggers to eliminate the CG_REF_CODES table, redundant fields and normalize the DB design. This include creating master table lookup tables with the appropriate foreign key constraints and RMS integration. (26.0%) h. Maintain the AIS receivers and perform vessel table reconciliation for MVN and the Tenn-Tom to support the collection of AIS data. Maintain a version of the AISEP module on WPC56 and on the MVN AIS server, as long as it is available, to display Google Earth when the primary box is down for patching or repair. (11.0%) i. When required, modify versions of Reports to pull monthly, quarterly and annually by district, division, waterway and ALL. (7%) j. Provide a drill-down capability from existing LPMS data warehouse reports to provide reconciliation analysis for data variances. (7%) 5. ACCEPTANCE CRITERIA. Services provided will be evaluated on the contractor's completion of the tasks outlined above. 6. PROGRESS REPORTING. The contractor shall inform the Contracting Officer's Technical Representative (COTR) of any problems that cannot be resolved within (1) business day. The contractor must provide the COTR with alternatives to reach resolution for problems of the magnitude stated above. The contractor shall report monthly on the number of hours of effort expended, and description of each task completed. 7. PLACE OF DELIVERY. Upon request local services may be required on site. Otherwise remote connection software (VPN), secure shell telenet software and instructions, if available, will be provided to the contractor. The production systems are on WPC21 and WPC56 in Portland, OR and production work must be done in this environment. Development and test environments are maintained by the contractor. 8. DELIVERY SCHEDULE. The total period of performance shall be 365 days from the notice to proceed. Tasks A thru J total 100% for the project. Each task is weighted percentage of the total project and maybe invoiced as tasks are accepted as complete by the COTR. As many of these tasks can be done in parallel, a monthly invoice of work performed may be submitted. 9. GOVERNMENT FURNISHED EQUIPMENT. Not applicable. 10. SECURITY REQUIREMENTS. It is required that the mandatory security clearance and access approvals for all contract personnel be in place the day of award. 11. Contract Data Requirements Lists (CDRLs) and Data Item Descriptions (DIDs). Not applicable. 12. PERIOD OF PERFORMANCE: 12 MONTHS FROM DATE OF AWARD.
