R -- Database Managment

Amendment 3 - questions/answers26. Is it expected that the current delivery method will suffice, or should a standard delivery method be included as part of the proposed effort? FCA expects that contractor will develop an application that will automatically load the received datasets into SQL Server and that any errors will be logged and sent to appropriate FCA staff for their action. 27. What is the preferred method for handling errors in uploaded data? Do we have to develop any workflow process to handle data validation errors? If so, can you describe the process that needs to be developed? FCA expects that the contractor will develop an application that will automatically load the datasets into SQL Server and that any errors will be logged and sent to appropriate FCA staff for their action. 28. What kind of technology infrastructure exists today for the different agencies to upload the files? Is it .NET based or java based? Does the agency have any preference to which technology should be used for building the web interface to upload the data? The SOW does not request that a web interface be developed to upload the data. This application should be built using Microsoft Visual Studio. 29. The SOW states that data files are in various formats (Access, Excel, and delimited text); however, there is a later statement in the SOW that states "the contractor will automate the process that takes the submitted XML files from the FCA's web site and loads them into the target database." I am confused as to whether the data files are in raw formats (Access, Excel, text delimited) or if the files are already in a standard XML format. FCA currently receives files in Access, Excel and comma-delimited format. The institutions may choose to submit the files in XML in the future. 30. Can you please clarify the format of the data files? ? If the files are in raw format, is it required to first turn the raw format into XML, and then load the XML file into the database? No. ? Is there a reason that raw formatted files cannot be directly loaded into the database? No. 31. The FCA will continue to accept new data elements. Is there a change control policy in place for modifying the XML schema? No, FCA accepts any format. Are there any constraints on the type of software for building the user interface? FCA is not requesting that a user interface to the institutions be built. FCA wants an application to be developed that will automate the loading the files received from the institutions. This application should be built using Microsoft Visual Studio. 32. Under the project background section there is a desire to continue to accept new data elements from institutions when they want to provide more information to FCA while under the Scope section there is a requirement to develop an xml schema to replace the current ad-hoc method of data submission. Is there a change control policy in place for modifying the xml schema? Can there be a freeze on the XML schema while development is taking place? No, FCA will continue to accept new data elements during the duration of this contract. 33. In the introduction paragraph under SCOPE, it says ?The database structures will be populated with data using XML and also using the current methods described above.? Does FCA want to continue to be able to receive data transfers from the district offices or other providers in MS Access, MS Excel and text files? Yes. Does FCA want the contractor?s DBA to manage ongoing loading of such data? No. 34. In the introduction paragraph under SCOPE - Is there a specific reason that FCA prefers XML-format? Does FCA plan to use this data for other purposes, or do they simply favor XML for other reasons? An XML format can affect performance on large uploads and requires larger storage space than other formats. XML is vendor neutral and provides maximum flexibility to the institutions in the generation of their data sets. 35. In Task 2 - Does FCA want to make the upload user-interface available to all of the 95 offices, or just the 5 district offices? FCA has developed a method for the institutions to submit the datasets on FCA?s web site. As Task 2 is defined in the SOW: The Contractor will automate the process that takes the submitted files and loads them into the target database. The Contractor will also automate the process of identifying new data fields that are submitted by the institutions. Additionally, the Contractor will work with FCA to develop checks between this new data system and FCA's Call Report System (CRS) to ensure the data in this new data system are reasonable before the data are made available to FCA's users. 36. In Task 2 ? ?the Contractor will work with FCA to develop checks between this new data system and FCA's Call Report System.? Does each upload to the central db at OE need to be checked against the CRS each time or is the check against the CRS a one-time check to verify the data from the new solution is coming in correctly? Each upload needs to be error checked and validated. 37. In Task 2 ? What would FCA like to check in relation to the Call Report System (CRS) and will the CRS continue to operate after the implementation of the new system? Can we get a description of what the CRS is and how the data is verified currently? The total book volume received in the datasets for an institution is compared to the total loan volume for the institution in CRS. This is done with a simple query of CRS and the two values should be within 10% of each other. FCA may want to add several other simple validations. 38. In Task 2 ?Where/When is the CRS currently run (in the main database, through another program?)? It would be helpful to get the specifications on how this system runs and what it does to check the data so that we can write a program that will be compatible. See answer to question 37. Tasks 3 & 4 - Data Collection/Storage/Mapping 39. Data Collection, Mapping, Views ? Does FCA have an estimate of the amount of data? For example, the number of records overall? The number of records uploaded monthly? AgFirst District data set is provided in a zipped MS Access database for each institution. The total district data is approximately 150MB zipped and includes over 150,000 data rows. The Texas District provides data for its 20 affiliated associations. The submission includes active and inactive loans as well as informational records on gross participation credits. A typical monthly submission from this district is about 3Gs and includes around 70,000 data rows. The Agribank District provides two databases. Total size is approximately 600MB and includes around 355,000 data rows. The Wichita submission is approximately 20MB and includes almost 50,000 data rows. There are approximately 23 other institutions that do not use one of the above service providers. We refer to these as the Independents. 40. In Task 3 - Is the focus of the data to monitor banks and banking activities, or to monitor the performance of the loan portfolio in relation to timely payments and performance? Or both? FCA wants to measure credit quality and mission performance. However, Task 3 is about data collection and storage. 41. In Task 3 - Does FCA need any reports that would calculate functions relative to mortgage repayment schedules or amortizations? No. 42. In Task 4 ? FCA has outlined a requirement to automate the mapping of new data sets: ?The data maps, related look-up tables and scripts will be automated to the maximum extent possible?. What is the expectation for this requirement since new data fields can be added by any of the 95 contributing offices without giving FCA prior warning? FCA would like the data load system that the contractor will develop to alert selected staff when new data elements are received from the institutions. FCA will then determine whether the new data elements fit an existing columach association? May we please have an example of the formats and data in the current Access, Excel and delimited text files? Are examples available of the data to be uploaded? FCA is currently receiving the loan datasets in about 30 different formats. This situation is not expected to change. See also question 25. 24. Can you tell me the total number of XML files that this project will involve and how many database files will need to be created? So, is this 1-1 database mapping or 1 to multiple? See question 25. 25. What is the current process of uploading the files by various agencies? Email, upload from a web site, FTP, etc? How do external agencies submit data to FCA, using a front end web application or via FTP? The primary mode of data submission is via CD. The districts prepare the data extracts and burn then to a CD. The CD is received in the geographic field offices and the contents are manually copied to the McLean server. The exception to this process is the institutions which do not utilize a bank and their service providers. For these institutions the data is transmitted via the FCA E-portal and then copied to the McLean server. FCA is currently receiving the loan datasets in about 30 different formats. This situation is not expected to change. The majority of the data is received from one of four district banks (see question 39). There are three institutions that provide unique data extracts. The remaining institutions provide a limited data extract of approximately 70 elements. These remaining institutions are not identical but do follow an agreed to format with minimal variation. Data from the district banks is received periodically (monthly or quarterly) via CD. The other institutional data is received via the FCA E-portal. The raw data is copied to a server for storage. The data for each institution is individually batch processed when needed. While the SOW must be able to work with our current protocol for data transmission and format (as discussed previously), the protocol may evolve toward more e-portal transmissions. Nevertheless, loading of data will remain a batched process for the foreseeable future. Currently the data processing is a very labor intensive and manual process. FCA personnel must link the raw data file to an MS Access file which runs a series of queries to translate the submitted data format and fields to my standard data set. We also add the Institution identifier and data keys at this point. Aberrations in the received data is identified through query failures and resolved. There is a unique set of queries for each of the submitted format. The result is a data set consisting of 500 data element in 5 tables. The total book value for a processed institution is compared to the CRS reported loan volume for validation. Note, we process each data file (ie institution) individually and must repeat the process for each data submission. A key component of this project is to automate the protocol for processing of the data. FCA expects that the contractor will develop an application that will automatically load the datasets into SQL Server and that any errors will be logged and sent to appropriate FCA staff for their action. The output will be a consolidated data set at recurring points in time (quarterly). After the data is loaded into the tables, views will be created in an existing SQL Server installation. FCA will develop its own reports that query the data in the new tables and/or views. The Farm Credit Administration (FCA) has an immediate need for services from a qualified person(s) who is knowledgeable of and experienced in relational database management design and development, as well as data loading and mining operations. The Contractor will assist FCA in developing a database to accumulate and store the loan portfolio data submitted by FArm Credit System institutions to the FCA. The database will enhance the data interrogation capabilities of the FCA's examination and regulation and policy development staff by consolidating System data into a single database with comparable data fields. The data tables will facilitate macro analysis of oversight of the System. Interested parties should contact contact@example.gov for a complete Statement of Work. Questions will be received through November 30, 2007, at 2:00 PM EST. An amendment will be posted answering questions December 4, 2007. Proposals should be sent to contact@example.gov no later than 3:00 PM EST, Wednesday, December 12, 2007. CLAIMS FOR RELIEF The FCA is a nonappropriated fund activity not subject to GAO's bid protest provisions. Any claim for relief filed by an offeror shall be adjudicated by the FCA Contracting Officer. If the parties cannot reach an agreement mutually satisfactory after diligently pursuing a solution through discussions and negotiations, the FCA Contracting Officer shall make a final decision. The FCA Contracting Officer's decision shall prevail unless the offeror submits an appeal to the FCA Chief Financial Officer (CFO) Stephen G. Smith. The decision of the CFO shall be final and binding on the parties.