ATTACHMENT_L-7_-_Sample_Task_Order_Channel_3_2-1-18.docx

STATEMENT OF WORK

Channel 3 – Data Development Support

C. STATEMENT OF WORK

C.1 Background

The Division of Economic and Risk Analysis (DERA) provides a wide range of data-driven economic analyses and analytics to the Commission and staff across the Securities and Exchange Commission (SEC). DERA has five primary mission functions:

(1) To support agency rulemaking and policy development;

(2) To develop cutting-edge, customized risk assessment tools and models to assist SEC divisions and offices to proactively detect market risks indicative of possible violations of the Federal securities laws;

(3) To serve as the central hub for the Commission’s approach to intake, processing, and use of data – including analytical methods and the production of market statistics;

(4) To support the Division of Enforcement through the provision of sophisticated economic and statistical analysis to assist in investigations and litigated cases; and

(5) To inform the public about relevant securities issues through the publication of white papers, working papers, and other research publications.

DERA’s sophisticated analytic and data services are mission focused, comprehensive, and include:

· Providing detailed, high-quality economic and statistical analyses, and specific subject-matter expertise to the SEC;

· Identifying, evaluating and recommending ways the SEC staff can use financial data, including interactive data, as well as providing expertise in data analysis;

· Identifying and analysing issues, trends, and innovations in the marketplace;

· Developing analytical tools, approaches, methods, and models in order to identify trends, risks, or potential securities law violations in the capital markets;

· Working with outside experts in academia and industry to strengthen the SEC’s foundation of market knowledge;

· Analysing and maintaining public and private financial and market data to support relevant initiatives and analytic projects;

· Creating knowledge through high-quality research and publication in peer-reviewed journals; and

· Participating in, and contributing to, academic and industry conferences.

Within DERA, the Office of Research and Data Services (ORDS) leads initiatives to develop and support cutting-edge data analytics and Data Management services to support requirements for DERA’s four policy groups, as well as the metrics, models, and other tools developed by DERA’s and other offices and divisions across the Commission.

ORDS maintains multiple financial industry datasets that are used in analytical models that support rulemaking, examinations, and enforcement functions. These datasets are of various formats including structured, unstructured and semi-structured data. They are sourced from multiple providers including but not limited to commercial vendors, Self-Regulatory Organizations and SEC’s EDGAR system. In support of DERA’s analytical functions, ORDS assumed responsibilities to create and maintain an analytical development environment and manage database functions supporting DERA’s analysis projects.

C.2 Objectives

The objective of this Task Order is to support the ORDS Data Management Branch and the SEC with Extraction, Transformation, and Loading (ETL) development and software programming support activities for new and ongoing operations. This includes, but is not limited to, data ingesting and provisioning from multiple sources using various technologies including Application Program Interface (API), DBMS, RDBMS, MMP Appliances, i.e. Oracle, Netezza, Hadoop Distributed File Systems (HDFS) and other file systems. It also includes developing and enhancing new and existing ETL processes required by the SEC, scheduling and scripting jobs, and enhancing the transformation process of unstructured data to structured data.

C.3 Project Scope

The Contractor shall provide technical analytical database management expertise to assist in designing and/or maintaining data sets. This requirement ensures data acquisition, conversion, and interfaces are managed so that the DERA analytical databases are efficiently designed, developed and maintained for use by SEC economists, researchers and other users. Under this Task Order, the contractor shall assist Data Management Branch staff and subject matter experts (SMEs) with provisioning data within the existing infrastructure. This may include planning, designing, developing and enhancing ETL and software development processes. The contractor will need to provide development support for Tables/Views/Stored Procedures/Packages/Triggers/workflows, HDFS objects, etc., using related programs, scripts and tools, not limited to the maintenance and enhancements of data sets and ETL processes. Furthermore, the contractor shall troubleshoot, review, recommend and implement solutions to failed jobs and performance issues, to ensure report data integrity. Supporting production jobs and processes is within the scope of this contract.

C.4. Task 1 - Project Management

It is expected that the Contractor shall provide a project manager who will serve as a conduit for all tasks associated with the efforts of this task order. In addition, it is expected that the project manager will be the conduit by which the required contract deliverables are provided to the Contracting Officer’s Representative (COR) and Project Manager (PM), allowing appropriate time for review and acceptance as outlined in the Project Plan.

The contractor shall provide an integrated project plan to manage all work identified by this task order. At a minimum, the plan shall include the Project Plan with basic efforts associated with developing a project schedule to include: staffing plan, quality assurance plan, estimate task duration, Sprint plans, define priorities, document assumptions, identify risks and review results. The contractor shall reassess the schedule following the completion of each task and may adjust the dates through coordination with the Project Team and with the PM’s and COR’s approval. (For Deliverable, see D.1)

C.5 Task 2 – Administrative

C.5.1 Sprints

An Agile project management methodology may be employed using Sprint planning sessions to collaborate on efforts; each sprint should include assigned tasks with durations. Sprint planning may be conducted jointly with the Project Team or separately as needed. The contractor shall ensure proper Sprint planning and deliverables are documented within the JIRA or SEC-defined application for review and by government staff. (For Deliverables, see D.2)

C.5.2 Monthly Vendor Status Meeting and Reports

The Contractor shall prepare a presentation that includes a monthly report describing at a minimum the current status and progress, specific work accomplished during the reporting period, recommendations, burn rate, issues and risks as well as corrective action plans. The minutes of the meeting should be posted to the designated SharePoint site NLT two (2) business days following the meeting. (For Deliverables, see D.3)

C.5.3 Meeting Minutes

The Contractor shall prepare and deliver minutes from all substantive meetings, i.e. sprints, status meetings, related to this task order. The contractor shall post the meeting minutes to the designated SharePoint site NLT two (2) business days following the meeting. The Contractor shall work with the Project Team to resolve issues and shall resubmit the revised meeting minutes NLT one (1) business day after receiving final feedback on the meeting minutes. (For Deliverables, see D.4)

C.6 Task 3 Development Support Tasks

C.6.1 Recommendations to improve efficiency: The contractor shall work with the Project Team to quickly develop a thorough understanding of each data set, task, technical application and requirements. Upon review the contractor shall provide technical recommendations (initial/interim/final) on ways to develop or enhance ETL or work products. These recommendations shall include but are not limited to design, development, and implementation of programs, database objects and ETL processes. Recommendations shall also include the determination of an optimal database structure that integrates existing programming languages or application integrates with Oracle, Netezza, Hadoop or other applicable technology. The recommendation should accommodate potential any data format changes. (For Deliverables, see D.5)

C.6.2 ETL/ELT Design/Development:

· Create requirements clarification documents

· Develop Conceptual and Logical models to ensure Business data requirements are completely captured and clearly expressed in the model.

· Design, develop and implement data ingestion, data flows and data processing within Hadoop.

· Design and implement solutions to handle ad-hoc and structured queries within Hadoop.

· Conduct Physical database design review to ensure database design standards, including naming and abbreviations have been followed; availability, recovery, archiving and purging procedures are defined; properly update meta-data repository

· Create and maintain relevant database objects — including, but not limited to, tables, views, user defined functions, indexes, constraints SQL (Structured Query Language) code, and stored procedures;

· Partition and bucketing tables within each data set, as necessary;

· Ensure data integrity within each data set and allow linkage to other appropriate data sets in a manner consistent with approved SEC information technology standards and procedures; including testing any new database design against previous data structures;

· Maintain performance reporting.

· Develop and implement data archival and purge procedures.

· Conduct performance assessment and tuning activities within and for each appropriate set — including, but not limited to, data compression, encryption, data (base) set partition;

· Recommend organization and integration of data to enhance operating efficiency and performance;

· Assist with automating the various periodic (daily, weekly, monthly, quarterly, etc.) data set processing jobs that are currently running manually;

· Standardize the several periodic/regularly occurring (daily, weekly, monthly, quarterly, etc.) ETL (extract, transform, and load) data processes using approved SEC information technology tools;

· Propose and create alerts and technical metadata when data load anomalies occur;

· Assist the SMEs and Data Management Branch in validating database designs and in implementation of data integration procedures;

· Where appropriate, use reporting tools, query languages or views to generate regular reports;

· Assist DBA’s during deployment into stage and production environments; Provide a process for loading relevant data sets in accordance with current DERA architecture and framework requirements and standards

· Develop or use existing processes to capture real time ETL metadata about each load. (For Deliverables, see D.6)

C.7 Task 4 – Procedural Documentation The Contractor shall document the data download procedure and the extraction, transformation and load process. Additionally, the Contractor will create a detailed and comprehensive data dictionary, with detailed explanations of all variables received, as well as documentation related to any variables that are created within ORDS or the SEC. The documentation shall include industry standard documentation such as database dictionaries, data libraries, and ER diagrams, procedural documentation, deployment instructions, Standard operation Document (SOP), test plan/test results, System design specifications and data flow diagrams were appropriate.

The contractor shall provide documentation, and where appropriate, standards, processes, and procedures to include:

Produce or maintain industry standard s?

· A graphical model of the design, done in ER format for a relational design. ORDS will provide access to the Data Modelling tool supported by OIT.

· Provide use cases or sample data, showing what the actual data will look like.

· Provide short descriptions, as needed, to explain: The database architecture and technology chosen, and why they were chosen.

· Constraints that affected the selection of the DBMS, including performance constraints, reliability or scalability constraints, security constraints, expected data volumes, etc.

· The database design process, including the method and tools used.

· The differences between the physical database design and logical data models, and the reasons for these differences.

· Security requirements for implementation.

· User and/or application requirements for the database and their implementation. (For Deliverables, see D.7)

C.7.1 Manage Data Model Versioning and Integration

· Note each change to a data model to preserve the lineage of changes over time. Each change should note:

· Why the change is required.

· What and how the object(s) changed, including which tables had columns added, modified, or removed, etc.

· When the change was approved and when the change was made to the model.

· Who made the change?

· Where the change was made. (For Deliverables, see D.8)

C.7.2 Meta-Data Management

· Maintain a library of the Data Definition Language (DDL) specifications for all database objects (tablespaces, tables, indexes, views, sequences, stored procedures and packages.).

· Maintain a library of the Data Manipulation Language (DML) specifications for all database objects that maintain configuration and lookup data (lookup tables, configuration tables).

· Document the technical meta-data, including business definitions, data types, length, domain, source, and usage of each column, and the structure of keys and indexes related to each table, PII.

· Document Data update cycle.

· Document Historical data availability.

· Document valid value constraints.

· Document Encoding / Reference table conversions.

· Identification of source system fields.

· Document Mapping, transformations to target tables.

· Document Performance.

· Document ETL job dependencies and schedule.

· Document ETL job names and descriptions.

· Document filenames and file locations for ETL Jobs.

· Document User report and query access patterns, frequency and execution time.

· Document Recovery and backup rules. (For Deliverables, see D.9)

C.7.3 Data Formats:

The data formats will vary, generally consisting of structured, unstructured and semi structured formats from Self-Regulatory Organizations, commercially available sources, EDGAR and others. (For Deliverables, see D.10)

C.7.4 Extract Transform and Load (ETL) process:

The ETL and development process will be based off SEC available programming languages, tools and/or processes. (For Deliverables, see D.11)

D. Additional Deliverables The contractor shall prepare deliverables specific to the tasks as shown in the tables below. Additionally, based on the types of assignment and subsequent discussions with the COR and Project Manager, there may be common deliverables associated with all datasets, which may include the following:

1. Requirements documents for the identified changes, upgrades, or enhancements.

2. Data Models - this includes conceptual, logical and physical model.

3. Data Management Documentation - should include periodic and scheduled dataload process, data manipulation process inclusive of any function procedures or any other code base. This document should also include metadata and master data as applicable.

4. Code base - code snippets and other code that is required for successful completion of tasks.

5. Design - all design documents and program specifications required for successful completion of task.

6. ETL - detailed ETL design documents including mapping, transformation logics, ETL code.

7. Data cleansing - provide detailed document and code base for data cleansing and data quality management.

8. Test Plan - test scripts aligned with the identified requirements, this includes all unit testing, User Acceptance Testing (UAT) – to be applied at the government’s discretion - and integrated tests.

9. Test Results - provide approved and documented test results.

10. Deployment - provide detailed deployment documents and deployment package.

The following deliverables may also be requested per dataset: deployment documents, mapping documents, Data model, data dictionary, Standard operating procedures and others as needed. All deliverables shall be formatted by the contractor and prepared using Microsoft Office Suite applications as appropriate and posted the designated SharePoint site.

E. Key Personnel

The Contractor shall designate specific senior level professional and managerial personnel as key personnel who are essential to the successful performance of work under this contract. Key personnel shall be available as necessary to efficiently manage the work of the contract and shall be available on the effective date of award. This project will not commence with partial or incremental key personnel staffing without government approval.

Key personnel:

· Project Manager

Non-Key personnel include Sr. Big Data Developer, Sr. Data Modeler, Architect and Sr. Tester. See the descriptions for the non-key/sample personnel labor categories in Attachment L-2-1.

· Sr. Big Data Developer

· Sr. Data Modeler

· Architect

· Sr. Tester

F. PERIOD OF PERFORMANCE

(Option Year 3)

(Option Year 4)

G. PLACE OF PERFORMANCE

The work will primarily be performed at the address below; however, some work may be performed at the contractor’s site:

Securities and Exchange Commission (Headquarters) Division of Economic and Risk Analysis (DERA) 100 F Street, NE Washington, DC 20549

G. TYPE OF ORDER

This is a Time and Materials task order.

I. TRAVEL

It is anticipated that long distance travel will be on a not-to-exceed basis.

Local travel will not be reimbursed. However, in the event travel is required, all travel shall be in accordance with Federal Travel Regulations (FTR) and approved in advance by the COTR. Additionally, costs for transportation, lodging, meals, and incidental expenses must be coordinated and approved in advance by the COR. Submit cost breakdown electronically to the COR. (1) Costs incurred by contractor personnel on official company business are allowable, subject to the limitation contained in FAR Part 31.205-46. Government per diem rates can be found on the website at www.gsa.gov. The ability to perform unscheduled travel is contingent upon Government approval and will only be used to address travel needs as they occur.

I.1 Local Travel

The local travel area for SEC duty assignments in the Washington, DC metropolitan area has been established as follows: The District of Columbia; the cities of Alexandria, Fairfax, Falls Church in Virginia; Arlington and Fairfax Counties in Virginia; and Montgomery, Charles, and Prince George’s Counties in Maryland.

J. Other Direct Costs

It is anticipated that other direct costs may be included for incidental expenses.

K. GOVERNMENT FURNISHED INFORMATION/PROPERTY

The government will provide office space, supplies and/or access to necessary telecommunications and computer equipment for the support services work that is to be performed on-site. The Contractor will not have a due diligence event to evaluate the government furnished property or information prior to award. The government will make meeting rooms available when required.

L. DRESS CODE

Business attire is required when contractor personnel are on-site.

(End of Section I)