Final_SOW_datawarehouse.docx

FCA REQUEST FOR PROPOSAL

SCOPING AND DISCOVERY FOR DATA WAREHOUSE SOLUTION

Background The Farm Credit Administration (FCA or Agency) was created in 1933 through an Executive order of President Franklin D. Roosevelt. The Agency’s powers and authorities are derived from the Farm Credit Act of 1971, as amended (Act), U.S.C. §§ 2001 et seq.

As an independent agency within the executive branch of the Federal government, the Agency is responsible for regulating and supervising the banks, associations, and related entities of the Farm Credit System (FCS or System), including the Federal Agricultural Mortgage Corporation (Farmer Mac). The Act states that the overall objective for the FCS is to improve the income and well-being of American farmers and ranchers by furnishing sound, adequate, and constructive credit and closely related services to them, their cooperatives, and to selected farm-related businesses. The FCS is the oldest of the financial government-sponsored enterprises.

Mission Statement

The FCA’s mission is to ensure a safe, sound, and dependable source of credit and related services for agriculture and rural America. We accomplish our mission through two basic functions:

1. Issuing Regulations and Implementing Public Policy - The Agency’s Office of Regulatory Policy (ORP) establishes regulations and policies that interpret the Act and other laws applicable to the System and Farmer Mac, as well as reviews and takes action on corporate and related service applications.

2. Identifying Risk and Taking Corrective Action - The Agency’s Office of Examination (OE) conducts examinations of FCS institutions to monitor, oversee, and in some cases supervise ongoing activities to ensure they are safe and sound and meet their public mandate to serve all eligible borrowers.

In support of our mission, we perform economic and financial analysis; provide pertinent information to the Federal Executive Branch, Congress, the institutions we regulate, and the public; and plan and manage Agency resources.

FCA Strategic Plan FCA’s strategic plan recognizes the need to evaluate risk and provide timely and proactive oversight to ensure the safety and soundness of the System and Farmer Mac. The plan outlines several specific areas where enhanced analytical capabilities are desired including:

· Enhancing our use of institution and System-wide loan data in examination and policymaking.

· Promote a vibrant program of System-wide risk supervision that uses stress testing, research, and analysis to identify emerging systemic risks, and provides proactive examination direction and policy guidance for use internally and externally.

· Support policy and regulatory positions with sound regulatory, economic, legal, and financial analysis.

· Ensure that staff provides prompt and comprehensive information to the FCA Board and remains flexible and responsive to the Board’s priorities so that the Board will be better able to make fully informed, arm’s-length decisions.

FCA Data and Tactical Objectives In today’s fast-changing financial environment, we need to continually evaluate how best to fulfill our mission to remain an effective and efficient regulator. Technology is critical to our ability to carry out our mission. Therefore, the Agency seeks to leverage our improved data by investing in technology and analysis tools to advance our examination and policymaking roles. A prioritized list of FCA’s data sources can be found in Appendix 1.

1. FCA evaluates and analyzes the System through two (2) primary datasets from each financial institution in the System:

a. The Call Report - comprises institutional information such as balance sheet, income statement, key financial ratios, and other financial and referential information in 48 tables and is collected quarterly beginning with the quarter ended 3/31/1989.

b. The Loan database - contains granular information on the accounts and exposures held by each institution. This database consists of 4 base tables and 22 referential tables beginning with the quarter ended 9/30/2012.

Our primary objective is to integrate the Call Report and the Loan database in such a way as to expediently and easily interrogate the data. The scope of this contract entails discovery assessments of these datasets, along with other sources as deemed appropriate, and the development of a plan for the design, development, deployment and maintenance of the data architecture which supports the Agency’s analytical needs.

2. In addition to evaluating System risk, FCA management monitors and evaluates Agency operations through the following essential databases:

a. Budget- FCA budget allocations and actual expenses

b. Employee Time – data on employee time by project and office

c. Exam Oversight - database is set up with a hierarchy of examination category, topic, and procedures which includes exam guidance information in each of the areas. In addition, for each institution examined at a particular time frame, examiners input plan information, work summaries, and findings into the database for the different areas.

FCA seeks assistance assessing these datasets to design a platform or structure to integrate them to support timely, efficient, and meaningful reporting. Optimally, the data warehouse will integrate with or be part of the Call Report and Loan database data warehouse to allow analysis of operations relative to System risk.

3. FCA has additional datasets which we would like to integrate into the enterprise data platform and interrogate for information and reporting purposes (see Appendix 1).

4. We seek assistance in developing an enterprise solution which foresees the long-term expansion and improvement of the data warehouse environment. In addition, we expect the enterprise architecture to support the agency’s commitment to, and utilization of, analytical tools such as Microsoft SharePoint, IBM Cognos, MapInfo or ESRI geographic information systems, and SAS.

FCA’s Risk Project FCA recognizes that achievement of the tactical and strategic goals is a multi-year and multi-phase project. Accordingly, FCA initiated the Risk Project in early 2013 to evaluate and direct resources to address the initiatives. The project workgroup is led by FCA’s Application Team in our Office of Management Services (OMS) and includes business user representatives from the Office of Examination (OE), Office of Regulatory Policy (ORP), Office of Secondary Market Oversight (OSMO) and the Farm Credit System Insurance Corporation (FCSIC).

In late 2013, FCA acquired IBM Cognos as the Agency’s business intelligence tool. The implementation of Cognos, particularly creating the data infrastructure necessary to access and analyze the Call Report and Loan database in the most efficient and effective manner, has been challenging. We need to implement a data warehouse to support our desire for improved data analytics, reporting and visualization.

Additionally, the Risk project workgroup identified a need to better evaluate our requirements and develop a data strategy as an enterprise solution. As a result, the Risk project workgroup plans to procure two contracts with the following objectives:

1. Scoping and Discovery contract – this contract will seek assistance to translate business user requirements into actionable data architecture specifications to achieve our desired analytic environment. The contract will require a detailed design plan to develop a data architecture solution for our critical data sources (the Call Report and Loan database) while considering our future plan to incorporate additional databases as outlined in Appendix 1.

2. Data warehouse contract – this contract will require the development, implementation, including knowledge transfer necessary for ongoing maintenance of the data warehouse solution recommended in the scoping and discovery contract.

This request for quote is for the Scoping and Discovery contract only.

Scoping and Discovery Objective The objective of this procurement is to obtain the services of an experienced technical and business team to lead the FCA’s requirements activity resulting in a documented, feasible, and actionable approach for a short, intermediate and long term enterprise data management plan.

Scope This procurement entails the scoping and discovery phase to assess and document business and technological requirements, review current data sources, and design and document a data environment for our critical databases (the Call Report and Loan database). The data solution must be scalable and adaptable to allow for the eventual inclusion of FCA’s additional databases listed in Appendix 1, as well as evolving business needs. The data solution must also be maintainable by Agency staff, preferably through knowledge transfer and utilization of BI tools, software and applications, currently in use by Agency personnel.

The work will be conducted at the Farm Credit Administration headquarters, 1501 Farm Credit Drive, McLean, VA 22102-5090. However, portions of the deliverables may be conducted offsite with FCA prior approval and conditions.

Services and Deliverables The contractor will perform a scoping and discovery activity for the design and development of a data warehouse solution which supports the analytical needs of the agency. This phase is expected to deliver an actionable plan, including requirements and design specifications, for subsequent deployment and maintenance of the resultant data warehouse that will initially include only data from the Call Report and Loan database. The contractor will be responsible for:

1. Conducting sufficient discovery to obtain an understanding of FCA’s business needs and data environment. The contracted services are expected to:

· Communicate with Agency stake holders to gain a comprehensive understanding of the business and technical requirements. Work with Agency employees to document and formalize the requirements into an actionable requirements list which will support educated assessment of data strategies including costs, benefits and alternatives.

· Review and assess FCA’s current IT infrastructure and data structure, including data content, relational structure, and operating environment. FCA stores its data in Microsoft SQL Server databases and Appendix 1 lists the SQL databases assumed applicable to the warehouse project. Additional documentation and technical specifications will be obtained as part of the contracted discovery scope.

· Facilitate communication and bridge understanding between the technical and business stake holders to design an analytical environment which can support the growing and changing analytic needs of the Agency. Business stakeholders have drafted examples of data analysis and reports (Appendix 2) to illustrate desired capabilities for the data warehouse. Understand that the examples are not an end-goal, but illustration of the broad type of requirements sought. FCA seeks assistance to translate the stakeholder’s examples into specific requirements for technological support.

· Ensure constant and transparent communication between FCA’s business users and FCA’s IT professionals through regular and documented meetings

2. Delivering discovery documents, analysis and conclusions. The documents will become resource material for the design and development phase of the project. Discovery documents should include the assessment of FCA’s databases (Appendix 1), processes and metadata necessary for requirements development.

3. Delivering requirements documents including explanation and conclusion of how the proposed warehouse solution meets the Agency’s business, system and user requirements. The documentation will serve to communicate, confirm and crystalize the understanding of the business and technological requirements.

4. Delivering a scoping and data warehouse design plan which identifies the comprehensive data strategy to meet the requirements established through discovery process. The scoping deliverable will serve as a road map for the development of enterprise platform(s) including requirements document(s) with sufficient specificity to support follow-on procurement of services to complete the build. While the development and deployment activity will occur in a subsequent phase, the discovery and scope deliverables must establish the architecture and requirement documents for the follow-on development. This activity will be focused on our critical data sources: the Call Report and Loan database. The activity must include:

· Documentation on data modeling which identifies and establishes the conformed dimensions and facts, a staging process or extraction, translation and load (ETL) process that transforms, combines and prepares the source data for use in the data warehouse, data platform or warehouse, presentation layers (cubes, OLAP, etc.) and ultimate integration with the Agency’s BI and analytic tool set.

· Collaboration with Agency staff to evaluate alternative data approaches and logic handling; such as, distributed or single warehouse, data normalization, granularity of data stored, data aggregations, hierarchies, etc. to ensure the approach is understood and agreed-upon as the best practice.

Vendor Requirements

1. Communication and knowledge transfer are essential attributes for Agency staff. Therefore, the selected vendor must have demonstrated project management skill, with proven performance in collaborative communications and knowledge transfer.

2. The team must have significant experience in data warehousing, data modeling and business intelligence/data analytics with experience in the financial industry a strong preference. .

3. Specific expertise and experience in the following areas is highly desirable:

· Identifying, assessing, evaluating, documenting and translating business requirements into dimensional data models

· Building high-performance and scalable data warehouse and OLAP data models.

· Knowledge of data staging and ETL approaches.

· Creating data warehouses and cubes (SSAS and SSIS) using SQL Server 2008 R2 or later

· Designing end-to-end data analytic solutions from source to user applications, including IBM Cognos, MS Analysis Services and MS office suite.

4. Contractor and Contractor’s employees must undergo a background check as deemed appropriate by the FCA.

Right of Substitution

During the term of the contract, if FCA is not satisfied with the services provided by any particular employee of the contractor, the contractor will provide a substitute employee acceptable to FCA. If an acceptable employee cannot be provided, FCA reserves the right to terminate the contract.

Ordering

The COTR or the designated representative will place orders for the Contractor’s services by issuing task assignments to the Contractor. Upon receipt of a task assignment, the Contractor will be responsible for independently carrying out the required work in full compliance with all requirements of this statement of work and all Farm Credit Administration publication specifications.

Timetable

The COTR or designee will establish a delivery date and performance schedule upon the issuance of each task assignment. Based on the needs of the Farm Credit Administration, the COTR or designee will update the performance schedules on a weekly basis during the period of this contract.

Progress Reports Throughout the entire period of performance under this contract, the Contractor shall progress reports to the COTR or designee as determined by FCA. These reports will be may be submitted in writing via email, except as noted below. Each report shall include the following information to the extent to which it is applicable for the reporting period:

· Description of progress made during the reporting period on all open task assignments, including significant accomplishments; problem areas, if any; and recommendations, if any.

· Summary of task assignments finished, and deliverables completed and delivered, during the reporting period.

· Summary of labor hours expended and hourly fees billed, for the reporting period and cumulatively.

In addition to the progress reports, the Contractor shall promptly report to the COTR or designee any noteworthy accomplishments, significant problems, and/or other matters requiring immediate attention. It is particularly important that the Contractor immediately report to the COTR or designee any issues that might adversely affect the performance schedule or delivery date for any task assignment. The COTR or designee may ask the Contractor for informal progress updates at any time.

Contract Type and Period of Performance

This is a fixed-price contract, based on rate for all services bid by the Contractor. Travel costs will not be reimbursed. Progress payments will be made in accordance with services and deliverables completed.

The contract is for a period of performance from September 30, 2015 to January 31, 2016.

Electronic Invoicing and Payment Requirements Internet Payment Platform (IPP) is a secure web-based electronic invoicing and payment information service available to all Federal agencies and their supplier by the U.S. Treasury's Financial Management Service. IPP allows Federal agencies and their suppliers to exchange electronic purchase orders, blanket POs, invoices, and payment information in one easy to access web portal. This service is free of charge to government agencies and their suppliers, including services and support.

The preferred method for invoicing is through IPP. The IPP website address is https://www.ipp.gov. Contractor assistance with enrollment can be obtained by contacting the IPP Production Help desk via email ippgroup@bos.frb.gov or phone (866) 973-3131.

Payment and Invoice Questions

Progress payments will be made in accordance with services and deliverables completed.

Deliverable Number

· Deliver requirement documents including explanation and conclusion of how the proposed warehouse solution meets the Agency’s business, system and user requirements.

30%

For payment and invoice questions, contact the Accounting Services Division at (304) 480-8000 option 7 or via email at AccountsPayable@bpd.treas.gov.

Mandatory Information for Electronic Funds Transfer Payment

METHOD OF PAYMENT

Mandatory Submission of Contractor’s EFT Information

1. The Contractor is required, as a condition to any payment under this contract, to provide the FCA with the information required to make payment by EFT as described in paragraph 3 of this section, unless the FCA Chief Financial Officer (CFO) determines that submission of the information is not required.

2. Prior to submission of the first request for payment (whether for invoice or contract financing payment) under this contract, the Contractor shall provide the information required to make contract payment by EFT, as described in paragraph 3 of this section, directly to the CFO at the address identified in this contract. In the event that the EFT information changes, the Contractor shall be responsible for providing the changed information to the CFO.

3. The FCA may make payment by EFT through either an Automated Clearing House (ACH) subject to the banking laws of the United States or the Federal Reserve Wire Transfer System, at the FCA’s option. The Contractor shall provide the following information for both methods in a form acceptable to the CFO.

NOTICE TO THE FCA OF DELAYS

In the event the Contractor encounters difficulty in meeting performance requirements, or when the Contractor anticipates difficulty in complying with the performance schedule, or whenever the Contractor has knowledge that any actual or potential situation is delaying or threatening to delay the timely performance of this contract, the Contractor shall immediately notify the Contracting Officer and the COTR or designee in writing, giving pertinent details; provided, however, that the data shall be informational only in character and that these provisions shall not be construed as a waiver by the FCA of any delivery schedule or date, or of any rights or remedies provided by law or under this contract.

CONFIDENTIALITY

Contractor must sign a Confidentiality Agreement concerning the disclosure/nondisclosure of “confidential information”, as defined below. Except as required by law or court order, the Contractor shall not disclose or permit the disclosure of any confidential information, including this and any other engagement with the FCA, to anyone without FCA's express written consent. FCA will provide the Contractor with access to electronic documentations which may also be provided through printing of those documents at the request of the Contractor. Access to these documents shall be under the control of FCA personnel. Upon termination of this contract, all information, documents, and work products in the Contractor’s possession relating to any work performed under this contract shall be returned to the Associate Director of AT. FCA will review and may authorize the Contractor to retain copies of any information that the Contractor reasonably considers necessary for its confidential business records. The Contractor shall take all reasonable measures to avoid at any time any unintentional or inadvertent disclosure of any confidential information to any unauthorized person by the Contractor’s employees, agents, or attorneys.

The term “confidential information” means any information acquired by the Contractor in carrying out the duties under the contract, including any information obtained before the date of the contract, and which had not become public information, including information regarding FCS institutions and FCA.

CLAUSES INCORPORATED BY REFERENCE

This contract incorporates one or more clauses by reference, with the same force and effect as if they were given in full text. Upon request, the Contracting Officer will make their full text available. Also, the full text of a clause may be accessed electronically at this address: http://www.arnet.gov/far/

52.222-55 Minimum Wages Under

BACKGROUND CHECK

Contractor must undergo a background check as deemed appropriate by the FCA. For contractors having access to the following, a background investigation can take up to 30 days or more.

· Sensitive information

· PII

· Unescorted access to FCA Offices

· FCA’s network/systems

NO EMPLOYER-EMPLOYEE RELATIONSHIP

This contract does not create an employer-employee relationship between FCA and the Contractor. The Contractor will be responsible for any and all obligations arising from its status as an independent contractor, including but not limited to, tax reporting and payment obligations. Neither the Contractor nor any of its employees will be considered a Federal employee for any purpose, regarded as performing a personal service, or eligible for civil service employee benefits.

CLAIMS FOR RELIEF

The FCA is a non-appropriated fund activity not subject to GAO’s bid protest provisions. Any claim for relief filed by a contractor 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 contractor 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.

AGENCY CONTACT

The Agency Contacts for this contract will be Debbie Kleinwachter, Associate Director, AT, or such other person designated in writing, with notification to the Contractor, by the Contracting Officer.

Debbie Kleinwachter Office of Management Services Farm Credit Administration 1501 Farm Credit Drive McLean, VA 22102 Email: kleinwachter@fca.gov Telephone: (703) 883-4355

ACCEPTANCE

Upon the Agency’s acceptance of the Contractor to perform the work under this contract, Contractor shall promptly proceed with the performance of tasks assigned by the COTR.

CONTRACTING OFFICER TECHNICAL REPRESENTATIVE:

Yun Huang Office of Management Services Farm Credit Administration 1501 Farm Credit Drive McLean, VA 22102 Email: huangy@fca.gov Telephone: (703) 883-4207

INSTRUCTIONS FOR THE PREPARATION OF PROPOSALS

Proposals must include:

A. Fixed Price bid B. Proposed Project Plan with major milestones and proposed dates C. Resume(s) of the business analyst(s)/business intelligence specialist(s) being offered D. Three years past experience references for the business analyst(s)/business intelligence specialist(s) and company E. A list of security clearances, if any, possessed by the business analyst(s) and/or business intelligence specialist(s) being offered.

BASIS FOR SELECTING THE CONTRACTOR TO RECEIVE THE AWARD

Selection will be based on a determination of the best value to the Agency. Best value will be determined based on:

1. Price

2. Relevant past experience of the business analysts and business intelligence specialists being offered as documented on their resumes

3. Communications skills of the business analysts and business intelligence specialists being offered as documented on their resumes

4. References for the business analysts and business intelligence specialists being offered and references for the company

Contractor staff may be interviewed prior to selection.

Appendix 1

Appendix 2

Sample Cognos Report descriptions:

Office of Examination (OE)

1. Association Flash Report – provides a high level snapshot of the condition and performance of System associations as of the current quarter. The Flash Report presents financial measures at an individual association level, a district level, and for all associations combined. Information is typically viewed as of the current quarter, prior quarter and the same quarter as the current for the prior year. Information is presented in tables, crosstabs, and graphically. Text fields are used for high-level comments and additional detail. (primarily data source CRS -Cognos query package)

2. Reviewing Official Dashboard – provides managers with information on the various stages of an institution examination cycle. The dashboard would have 4 tabs with information presented in tables and graphs for examination scope, ongoing oversight, examination activities, and post report of examination issuance. (primary data source is EO Cognos query package)

3. OE Sample Selection Workspace: facilitate OE’s utilization of data stored in FCS2 by providing an easy interface for examiners to explore and identify data outliers and high risk assets. OE currently downloads single institution datasets from the SQL database using an OMS provided utility into an Access workfile which provides basic standard queries. Examiners then export the query results to Excel for further slice/dice and analysis of the data. Cognos offers the ability to provide a one-stop application for data analysis. Conceivably the examiner can select the institution, query data, and perform ad-hoc slice/dice and filtration in a single application, thus eliminating the need to download data to individual laptops. (Primary data source is FCSL2 cube and TBD query package)

Office of Regulatory Policy (ORP)

4. Commodity and Risk Profile Dashboard – provide an interactive overview (ie active reports) of System loan volume and risk characteristics for FCA Board, Senior Management, and other FCA offices. ORP currently produces the high risk industry report that shows the change in risk profile of the FCS Districts by evaluating the weighted average risk rating and commodity concentration. This new dashboard would utilize some of the concepts currently produced in this report while adding other visualizations available in Cognos, as well as location layers available with GIS. The ultimate goal would be to also bring in external economic and agricultural data available through USDA/NASS and the Census. (primary data sources include FCSL2 cube, GIS external sources)

Office of Management Services (OMS)

5. Retirement Eligibility Report – provide FCA Board and Senior Management a report to calculate retirement eligibility dates for business planning purposes. The report would have drill down capability and present the data by office and job series.(Primary data source is HR Cognos query package)

8/11/2015 1 of 15