Attachment_L-7_Sample_Task_Channel_3_-_Data_Maintenance_15_Feb_18.pdf

ATTACHMENT L-7 SAMPLE TASK CHANNEL 3 – Data Maintenance

SAMPLE TASK CHANNEL 3

SECTION C - STATEMENT OF WORK

Channel 3 - Data Maintenance

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 analyzing 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;

• Analyzing 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 objectives of this Task Order is to provide maintenance for datasets that support several DERA and ORDS analytical products. The support includes maintenance, enhancement and support services for new and existing datasets which are sourced by the SEC using multiple technologies including, but not limited to, Web Services, Web Scraping and data downloads from FTP/SFTP/HTML sites. These are stored in production within multiple data stores including, but not limited to, Oracle, Netezza, Hadoop Distributed File Systems (HDFS), Linux/Unix file systems and potentially cloud-based platforms. Maintenance tasks under this Task Order include new and existing extract transform and load (ETL/ELT) processes/jobs developed by the SEC using multiple tools/languages, which may require enhancements to the transformation process from multiple formats to a structured/reusable format. This Task Order pertains to the maintenance of datasets that have or will be moved to the production environment and require ongoing maintenance and support services. The contractor shall be required to make necessary enhancements to multiple ETL processes, including post/post ETL code and validation scripts to account for periodic adjustments to datasets such as the addition or changes in file formats or data points. In some cases new ETL process will need to be developed. DERA SMEs will provide input or guidance when necessary on how the datasets should accommodate changes.

This Task Order requires the contractor to monitor and maintain processes and components of the ETL system jobs and processes for loading dataset into the ORDS DataMart. All ETL jobs including IBM Datastage ETL jobs that are developed or revised should follow the guidelines and best practices defined by the SEC. In addition, all revisions, if possible, should be done in accordance with any ORDS guidelines and/or procedures for source code version control as they are released.

As technology develops and the SEC transitions to the cloud environment or updates its existing infrastructure, it will be essential to update skillsets throughout the life of the requirement. This may include additional critical skillsets and personnel not listed in this Statement of Work. The government will collaborate with the contractor and provide all the necessary specifications and recommendations for any desired skillsets.

C.3 Project Scope

The Contractor shall provide technical expertise to assist with accomplishing data updates (Tables/Views/Stored Procedures/Packages/Triggers) and related updates to programs and scripts not limited to maintenance and enhancements of specified datasets. Furthermore, the contractor shall troubleshoot, review failed jobs and performance issues, and monitor and ensure data integrity. The Contractor shall provide routine financial industry data maintenance including but not limited to:

• Automate, maintain, monitor and enhance ETL (extract, transform, and load) data processes using approved SEC information technology and tool.

• Update ETL processes efficiently to support dataset format changes by SEC data vendors. Collaborate with SEC and/or data vendors to support format changes.

• Promote or facilitate promoting ETL scripts/code/artifacts to various environments including development, staging/test and production.

• Provide technical recommendations to improve efficiency of SEC ORDS Data Management /ETL process.

• Create proactive alerts/notifications to identify when data load anomalies occur.

• Support the Data Management branch with prescribed data access requests.

• Perform database and datasets refreshes across systems and environments.

• Perform database tuning including creating/reviewing OS, stored procedures, indexes, partitioning tables etc.

• Create/support updates to materialized views to support users’ requests and inquiries.

• Maintain data ingests into Hadoop Distributed files systems (HDFS).

• Support on-loading/off-loading data to/from data stores such as Oracle, Netezza &

Hadoop environment and other file systems as necessary.

• Create/maintain database objects such as tables, views, indexes, constraints, and SQL

(Structured Query Language) code, and stored procedures and shell scripts.

• Create/maintain data archival processes where applicable.

• Ensure data integrity within each data set and support linkage to other appropriate datasets in a manner consistent with approved SEC information technology standards and procedures. This includes testing and validating any new database design against previous data structures.

• Collaborate with and support OIT during system maintenance activities such as patching and upgrades.

• Test databases/patches to ensure no adverse impact from system patches.

• Provide assistance in resolving ETL and database software bugs where applicable using approved communication process.

• Prepare/draft correspondence for the government to dataset vendors/OIT regarding data pull (/feeds) issues using approved communication process.

• Create/update/maintain data models, data dictionaries, technical diagrams and other documentation to depict environment and processes.

• Support data download from external sites including web services, web Scrapping, FTP/SFTP and HTML sites

• Perform database and ETL enhancements. New ETL development may be performed

• Create/enhance/maintain technical and ETL metadata repositories.

• Create/enhance/maintain technical metadata repository objects and artifacts to include tables, stored procedures, views and code.

• Maintain new and existing datasets in production as they become available.

• Participate in a handover process for new datasets and performing Independent verification/Validation as necessary, if/when work products were developed by others staff/teams.

• Monitor and report data and ETL metrics such as data sizing, load frequency, update frequency, data loaded, data usage, storage usage, free/available space, etc. in databases and data stores.

• Enforce/implement SEC and OIT approved data retention policies/processes while maintaining data integrity.

• Handle remediation, clean up, rollbacks, and re-processing/re-runs upon anomalies/workflow failures.

• Where applicable, maintain jobs or scripts that pull data from vendor sites.

• Provide service and support to users such as by answering technical questions, assisting with queries etc.

C.3.1. To ensure the objectives are achieved, the following Performance Indicators must be met:

• Data will be loaded efficiently into production within 2 business days of the data becoming available in SEC Feeds or available to the SEC from an external site.

• Identify issues with production ETL loads within 2 business day of execution of the

ETL.

• Report production issues within 1 business day of identification of the issue.

• Initiate resolution to production issues within 1 business day of identification of the issue.

• ETL enhancements will be completed in development within prescribed timelines.

• Ensure Technical metadata repository describing the dataset are updated within 5 business days of dataset being deployed into production

• Ensure the ETL metadata repositories for data loads are updated in real time.

• When applicable, database access will be granted /facilitated with 2 days of the request.

• Data models, data dictionaries, technical diagrams and other technical documentation to depict environment will be created/ updated no later than 7 days of the production release to ensure documentation reflect current state.

C.3.2. Datasets

Datasets under this Task Order may include, but are not limited to the list that follows in this section. Details are provided for select datasets to illustrate the type and scope of work covered under this Task Order.

1. FINRA Datamart (BD and ADV) Currently, DERA receives on a weekly basis, a complete extract of the FINRA Datamart which includes BD and IA firm registration information along with their branches and individuals associated with the firms, their employment and disclosure history. The extract is provided as an Oracle DB export file. The current size of the Oracle export file transmitted to SEC on a weekly basis is about two GB or more compressed and about 20 GB or more uncompressed. Upon completion of the new environment, we expect this file to be transmitted to the SEC on a daily basis. The daily process will require additional maintenance and monitoring. These files contain over 150 database tables.

2. FINRA - FOCUS Filings Currently, DERA receives FOCUS data on a daily basis. The ETL system jobs and processes load ORDS FINRA (FOCUS) dataset into ORDS’s Datamart. FOCUS data consists of three sets of files which require three separate loads. FINRA is one source of the FOCUS data. CHX and CBOE are the other sources of FOCUS data. All components of FOCUS data are combined into the FOCUS data base. Each source requires a separate data load. The legacy process generated about 300 MB of text data on a quarterly basis. These files contain the latest filing information for that period. The daily validation process will generate daily XML files that may be processed using the IBM InfoSphere Data Stage tools for loading into an Oracle database. The daily files will result in a larger number of records than the quarterly files previously provided.

3. FINRA –TRACE, TRF, KITTEN

Currently, DERA receives Terabytes (uncompressed) of current and historical data. on daily and monthly basis. These datasets are loaded on a weekly and monthly basis.

4. Mutual Fund Holdings (MF Holdings) The Contractor shall process the MF Holding files on a monthly basis. The MF Holdings data is similar in size and complexity as the N-MFP data. Adjustments by Morningstar to the data point formats within MF Holding datasets can occur. Some of the existing universes include but not limited to Open End, Closed End, Exchange Traded Funds, Money Market and Unit Investment Trust.

5. Hedge Fund – 7 Sources The datasets relay information about hedge funds including, their names, investment strategies, key management personnel, assets under management and various measures of investment performance. The vendors including but not limited to, Eureka Hedge, Barclay Hedge, Hedge Fund Intelligence (HFI), Lipper Trading Advisor Selection System (TASS), Hedge Fund Research (HFR) and MorningStar Hedge transmit their products to the SEC on a regular basis, with frequency ranging from daily to monthly. The data are transmitted in a variety of formats, including CSV (Comma-Separated Value) text files and XML (Extensible Markup Language) files. The Contractor shall process SunGard Securities Lending data on a frequency to be determined by ORDS.

6. Audit Analytics Data The Audit Analytic financial reinstatement datasets include data from more than 20,000 privately held companies and 1,500 accounting firms. The Contractor shall process Audit Analytics data on a frequency to be determined by ORDS.

7. Volker Data – 20 Sources SEC receives Volker data from financial industry sources and provides SEC with quantitative data. Volker data is reported from each banking entities for each of its trading desks engaged in covered trading activities. Over 20 financial instructions currently submit Volker Data to the commission. That number may grow. Volker technical specifications may under changes. The Contractor shall continue processing Volker data on a frequency to be determined by ORDS.

8. SunGard Securities Lending Data DERA receives SunGard Securities Lending Data files from SunGard’s Astec Analytics, which provide securities lending data to lenders, borrowers and investment managers. These files provide global coverage of fixed income and equities located in 110 countries. These files include loan data on over 44,500 securities, including historical data on inactive securities. The SunGard Securities Lending data contains position data back to 01/01/2005, Rate Dispersion data back to 01/01/2011 and utilization data back to 01/01/2007. Currently the SunGard Securities Lending data is loaded in Oracle and is 200G in size. The Contractor shall process SunGard Securities Lending data on a frequency to be determined by ORDS.

9. MSRB – CD, PM, SHORT and RTRS DERA receives Municipal Securities Rulemaking Board data (MSRB) from financial industry sources. MSRB data provides SEC with access disclosure documents associated with municipal bond issues.

10. Municipal Market Data Municipal Market Data "MMD" from the MSRB includes information on maturity dates and yields. The raw data is delivered through a feed and stored in /feeds/MSRB/MMD. This data is currently in the Oracle Test environment.

11. Municipal Bond Information Services (MBIS ) DERA originally obtained a one-time dump of historical (6/1/2012 to 1/19/2015) bid/offer price information from the Market Data Feed product offered by Municipal Bond Information Services “MBIS”. The MBIS data provides quotes on municipal bonds that it gathers from alternative trading systems. Access to the data is provided through an FTP site. The SEC has recently contracted with MBIS to update the data going forward, and ORDS is working on an ETL process to upload the data into Oracle.

12. DTCC - Credit Default Swap (CDS) - 2 feeds

13. S&P Capital IQ CDS Pricing

14. Thompson Reuters Data Feeds – 4 Sources

15. Form N-PORT Filings

16. Form N-CEN Filings

17. Tick Size Pilot – 7 Sources

18. Trade and Quote (TAQ)

19. NRSRO – 10 Sources

20. Shredded - FORM C

21. Shredded REG A

22. Shredded FORM D

23. Shredded Transfer Agent

24. Shredded Form N-SAR

25. Shredded Funding Portal

26. BlackBox (RMBS data)

27. Equifax (Loan data)

28. Commercial Mortgage Back Securities (CMBS data)

29. CoreLogic (mortgage origination data)

30. Egan Jones (credit ratings)

31. Fannie Mae Loan level data

32. Freddie Mac Loan level data

33. Home Mortgage Disclosure Act data

34. Ginnie Mae MBS Loan Data

35. Options Price Reporting Authority (OPRA)

36. ICE CDS Reference Data

37. Moody’s Analytics

38. DataScope Fixed Income (credit ratings)

39. Canadian Stock Exchange Tick Data

The Contractor shall support other datasets as they become available.

C.4 Task 1 – Master Project Plan

It is the expectation that the Contractor shall provide a project manager who will serve as a conduit for all tasking associated with the efforts of this task order. In addition, it is the expectation that the project manager will be the conduit by which the required contract deliverables are provided to the Contracting Officer’s Representative (COR) allowing appropriate time for review and acceptance. The contractor shall provide an integrated project schedule to manage all work identified by this task order. At a minimum, the plan shall include the Work Breakdown Structure (WBS) with basic efforts associated with developing a project schedule to include but not limited to: define the type of schedule, estimate task duration, 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 in consultation with the assigned DERA Project Manager (PM) and the Contracting Officer Representative’s (COR) approval. (For Deliverable, see D.1)

C.5 Task 2 – Project Plan

The contractor shall provide a project plan to manage all work identified for this task. The Project plan shall include the Project Management plan, Change Management Plan, Communication Plan, Risk Management and Quality Assurance Plan for this project. The Project plan narrative, Project Management Plan, Communication Plan, and Quality Assurance Plan shall be in Microsoft compatible format and include an initial project schedule that includes major milestones and contract deliverable dates. The Contractor shall provide a project plan narrative that describes the project, technical approach, and methodology. The contractor shall submit a detailed work breakdown structure and burn rate to accomplish the objectives of this project.

An agile project management methodology may be employed using Sprint planning sessions to collaborate on efforts; each Sprint should iteam 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 Deliverable, see D.2)

C.6 Task 3 – 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.

The monthly report shall also include the following details per production dataset:

o Monthly load statistics o Gaps if any in production loads, since load inceptions.

o ETL processes which have not been fully automated. (For Deliverables, see D.3)

C.7 Task 4 Meeting Minutes

The Contractor shall prepare and deliver minutes of all meetings i.e. Sprints, status meetings related to this task order. The Contractor shall post eh meeting minutes to the designated SharePoint site NLT two (2) business days after the meeting was conducted. The Contractor shall work with the Project Team and COR to resolve issues and shall resubmit revised minutes NLT one (1) business day after receiving final feedback regarding the meeting mintues. (For Deliverables, see D.4)

C.8 Task 5 Recommendations

The contractor shall work with Project Team to quickly develop a thorough understanding of each dataset, task, technical application or requirements. Upon review the contractor shall provide technical recommendations (initial/interim/final) on ways to develop or enhance ETL or work product. 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 any potential data format changes. (For Deliverables, see D.5)

C.9 Task 6 - Procedural Documentation The contractor shall create, update & maintain technical documentation and database reports to include, but not limited to Design documentation, deployment documentation, Data Models, Data dictionaries and Operations documentation. (For Deliverable, see D.6)

D. DELIVERABLES

The contractor shall prepare deliverables specific to the tasks as shown in the tables below. All deliverables shall be formatted by the Contractor and prepared using Microsoft Office Suite applications as appropriate then posted to the designated SharePoint site for review by the Project Team and approval by the COR.

Task No. Deliverable Description Due Date Recipient

1 Master Project Plan See para. C.4 NLT 10 business days following the Kick-off Meeting

COR/PM

2 Project Plan See para. C.5 As outlined in the Project Plan

COR/PM

3 Monthly Vendor Status Meeting & Reports

See para. C.6 Monthly as outlined in the Project Plan

COR/PM

4 Meeting Minutes See para. C.7 No later than two (2) business days after the meeting

COR/PM

5 Recommendations

See para. C.8 As outlined in the Project Plan

COR/PM

6 Procedural Documentation

See para. C.9 As outlined in the Project Plan

COR/PM

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:

Sr. Big Data Developer Sr. ETL Developer (Netezza) Sr. ETL Developer (Oracle) Sr. Tester

F. PERIOD OF PERFORMANCE

CLIN Period of Performance

0001 Date of Award through 1-year

0002 Date of Award through 1-year (Option Year 1)

0003 Date of Award through 1-year (Option Year 2)

0004 Date of Award through 1-year (Option Year 3)

0005 Date of Award through 1-year (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

H. 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 http://www.gsa.gov/

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)