Work_Category_3_-final.doc

Sample Work Category 3

SECTION I – STATEMENT OF WORK

Work Category 3 SUPPORT SERVICES FOR OQR DATA MAINTENANCE – Task 1

C. STATEMENT OF WORK

C.1 Background The Division of Economic Research and Analysis (DERA) was created in September 2009 to integrate financial economics, and rigorous data analytics into the core mission of the U.S. Securities and Exchange (SEC). The Division is involved across the entire range of SEC activities, including policy-making, rule-making, enforcement, and examinations. As the agency’s “think tank,” DERA relies on a variety of academic disciplines, quantitative and non-quantitative approaches, and knowledge of market institutions and practices to enable the Commission to approach complex matters using innovative and risk based methods. DERA also assists in the Commission’s efforts to identify, analyze, and respond to risks and trends, including those associated with new financial products and strategies. Through the range and nature of its activities, DERA serves the critical function of promoting collaborative efforts throughout the agency.

Within Risk, Strategy, and Financial Innovation, the Office of Quantitative Research (OQR) is responsible for providing analytical research and development, analytical model development, user support for analytical applications, and data management and administration to support analytical projects. In support of DERA’s analytical functions, OQR has recently assumed responsibilities to create a new analytical development environment and manage database functions supporting DERA’s analysis projects.

As part of the functions described above, DERA economists and OQR subject matter experts (SMEs) monitor and analyze Credit Default Swap (CDS) data submitted to the Depository Trust and Clearing Corporation’s Trade Information Warehouse (DTCCTIW). Primarily these analyses are used to inform policy through cost-benefit analysis, as well as to determine how market participants react to policy changes. DERA experts run both regular reports and ad-hoc reports on these data. It is essential that data be structured and maintained to accommodate time series analyses. The scope of the uses for CDS data is expected to increase significantly in the future. The CDS data are described in the “Cross-Border Security-Based Swap Activities” Release No. 34-69490” (page 28 and footnote 50; available at http://www.sec.gov/rules/proposed/2013/34-69490.pdf) and in the March 15, 2012 memorandum to the file “Information regarding activities and positions of participants in the single-name credit default swap market,” from the Division of Risk, Strategy and Financial Innovation (available at http://www.sec.gov/comments/s7-39-10/s73910-154.pdf).

DERA utilizes multiple datasets from FINRA that are used in analytical models that support rulemaking, examinations and enforcement functions. These datasets include broker dealer (BD) registration information from BD firms, Investment Advisor (IA) firms, and branches and individuals associated with a broker dealer or investment adviser firm that is under the jurisdiction of the SEC. Specifically, OQR accesses information from the following datasets:

· Form BD which is the Uniform Application for Broker-Dealer Registration. Broker-Dealers must file this form to register with the Securities and Exchange Commission, the self-regulatory organizations, and jurisdictions through the Central Registration Depository (“CRD”) system, operated by FINRA. By law, the applicants must promptly update Form BD information by submitting amendments whenever the information on file becomes inaccurate or incomplete for any reason.

· Investment adviser registration (ADV filings), exempt reporting adviser filing, regulatory review, and the public disclosure information of investment adviser firms from FINRA’s IARD system.

· All broker/dealers who are FINRA member firms have an obligation to report transactions in corporate bonds under an SEC approved set of rules. The Trade Reporting and Compliance Engine (TRACE) is the FINRA developed vehicle that facilitates this mandatory reporting. The data contain over the counter secondary market transactions in eligible fixed income securities reported in the TRACE system.

· BD firms are required to compose and submit Financial and Operational Combined Uniform Single (FOCUS) Reports pursuant to SEC Rule 17a-5. The SEC receives FOCUS datasets from FINRA and in the case of the futures industry and the options trading firms OQR receives from CBOE and CHX separate but similar datasets. OQR provides access to these data to other SEC offices and Divisions. These filings result in three separate data loads to support the OQR analyses.

OQR also uses information from the Form N-MFP which is the public reporting form that provides monthly reports of money market funds as required by section 30(b) of the Investment Company Act and rule 30b1-7 under the Act (17 CFR 270.30b1-7) (available at http://www.sec.gov/rules/final/2010/ic-29132fr.pdf). A money market fund must report information about the fund and its portfolio holdings as of the last business day of the preceding month. The Form N-MFP must be filed with the Commission no later than the fifth business day of each month, but may be filed any time beginning on the first business day of the month. Each money market fund, or series of money market funds, is required to file a separate form. If the money market fund does not have any classes, the fund must provide the information required by Part I.B for the series. A money market fund may file an amendment to a previously filed Form N-MFP at any time, including an amendment to correct a mistake or error in a previously filed form. A fund that files an amendment to a previously filed form must provide information in response to all items of Form N-MFP, regardless of why the amendment is filed. Examples of the types of analyses DERA staff have conducted using N-MFP data are available in the “Response to Questions Posed by Commissioners Aguilar, Paredes and Gallagher,” November 2012 (available at http://www.sec.gov/news/studies/2012/money-market-funds-memo-2012.pdf).

OQR also receives investor advisor (IA) and mutual fund holdings (MF Holdings) information from commercial data sources such as Morningstar on a monthly basis. These data include information describing fund portfolio holdings. These data can be used to understand aggregate mutual fund investments and exposures. The MF Holdings data provides a source of information used that can be combined with investment data or data from other registrants. For example, these data could be useful for monitoring mutual fund exposure to sovereign debt or to monitor fund manager incentives and mutual fund family trading activity.

C.2 Objectives The objectives of this Task Order are to provide maintenance for several of the datasets that support multiple OQR and DERA analyses products. This Task Order will provide maintenance for one year for the datasets described in the tasks in the following section.

1. Credit Default Swap Datasets:

Currently, DERA receives, on a monthly basis files containing weekly credit default swap (CDS) positions and transactions datasets submitted to the Depository Trust and Clearing Corporation’s Trade Information Warehouse (DTCC-TIW). The raw data files, including the historical data, currently total 200 GB while the processed data files result in approximately 20 GB.

The raw data are delivered from DTCC as custom reports, manually accessible through a web portal. The raw data are comprised of approximately 1 GB of data per month, and they are modified and stored as SAS files on a DERA Unix server. In the future, the DTCC-TIW data may be provided more frequently (weekly or daily as opposed to monthly) and may be automatically downloaded via FTP instead of through the web portal.

Multiple offices within DERA utilize the CDS data. On a monthly basis, OQR receives a CDS dataset that includes weekly positions files and monthly transactions files. These DTCC-TIW files are downloaded from DTCC’s web portal as one compressed raw text file. This file contains many other compressed files, which are then extracted, combined, and converted to a usable format (e.g. SAS). There are over 200 variables in each file and more are added periodically (OQR estimates that changes to the data structure occur four to six times a year).

Currently, the data are based only on corporate and sovereign swaps of regulated entities. Specifically, a transaction will be in the dataset if the reference bond is a U.S. company or if either party to the transaction is in the U.S., or a subsidiary of a U.S. firm. In the future, the data may expand to include all CDS contracts written on domestic and foreign equities, which would be a significant increase in data volume resulting in data files potentially as large as 10 GB.

Currently the raw files are approximately 1 GB. They are extracted and processed using Unix shell scripts and SAS on the Unix server. There are four weekly positions files per month and one transaction file per month. The files are downloaded at the same time. The file structures change periodically to accommodate industry inconsistencies in the data. These files have over 200 variables and variables are sometimes added to the data set on an ad-hoc basis. DERA SMEs often reach out to industry (e.g. investment banks) to determine how their back-office operations define the DTCC variables. The DERA SMEs decide how the data sets should accommodate any differences.

2. OQR 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 current size of the Oracle export file transmitted to SEC on a weekly basis is about 2GB compressed and 20GB 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. A set of database procedures is executed to restore the export file onto the local OQR Oracle Datamart. FINRA periodically adds new database objects between four and six times per year. The Contractor shall accommodate these changes with additional database development resource as coordinated by the OQR DBA and at the approval of the COR.

OQR FINRA Datamart (FOCUS filings)

The Contractor shall be required to process FOCUS data on a daily basis. 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.

In addition, the Contractor shall be required to assist in validating the data to complete a transition from the legacy quarterly validation process to the new daily validation process. The Contractor shall be required to assist in validation of data for one quarter before the old process can be decommissioned. The legacy process generated 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 will be processed using the IBM Infosphere Data Stage tools prior to loading into an Oracle database. The daily files will result in a larger number of records than the quarterly files previously provided.

OQR FINRA Datamart

(TRACE)

The SEC will receive single load of approximately 300GB (uncompressed) of historical data and thereafter a daily load of approximately 100MB (compressed) data. These datasets are composed of approximately 20 text files. The Contractor will be required to provide an assessment of the efficiency of the new datasets, a database model, and recommended ETL processes, provide daily maintenance of these files, and monitor the load processes to the OQR Oracle Datamart.

3. N-MFP

The Contractor will process the N-MFP files on a monthly basis. The N-MFP monthly data consists of approximately 600 files plus one to two dozen amendments. The size of these 600 files range from approximately 3.3 MB to as small as 6KB. These files are added to the current datasets stored on the DERA Unix system. Five to ten reports are generated that can be loaded into SAS for further analyses.

4. MF Holdings

The Contractor will process the MF Holding files on a monthly basis. The MF Holdings data will require new edit routines, processing so that it can be analyzed further. The MF Holdings data is similar in size and complexity as the N-MFP data.

C.3 SCOPE

Under this Task Order for Work Category 3 Support, the Contractor shall assist the OQR SMEs and DBAs in accomplishing database updates, database development planning, and database development support (Tables/Views/Stored Procedures/Packages/Triggers) for one year. Furthermore, the contractor shall troubleshoot, review failed jobs and performance issues, and monitor and ensure data integrity. While OQR receives and utilizes a multitude of datasets from a variety of sources, the work under this Task Order shall be focused only on the datasets associated with CDS, N-MFP, and MF Holdings, and constituent datasets within the FINRA Datamart to include BD, ADV, FOCUS, and TRACE.

C.4 Task 1 – Program Management

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, project contract milestones, and contract deliverables. The contractor shall reassess the schedule following the completion of each task and may adjust the dates with COR approval. (For Deliverable, see D.1)

C.5 Task 2 - CDS Data Maintenance C.5.1 Task 2 Project Plan The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task. (For Deliverable, see D.2 and D.3) C.5.2 Task 2 Monthly Reports The Contractor shall prepare a monthly status report describing, at a minimum, the burn rate, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. (For Deliverable, see D.5) C.5.3 Task 2 Interim Reports. The Contractor shall prepare and deliver to the COR a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding analytical methods. (For Deliverable, see D.4) C.5.4 Task 2 Meeting Minutes. The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes. (For Deliverable, see D.6) C.5.5 Task 2 Recommendations to improve efficiency

The contractor shall work with OQR SMEs and the OQR DBAs to quickly develop a thorough understanding of DERA's CDS data sets, and the technical applications and operational requirements relying on these databases. Upon review of DERA’s existing CDS data structure, management system and the DTCC-TIW, the contractor shall provide recommendations on ways to improve storage and processing efficiency. These recommendations shall include but are not limited to, simple Unix shell scripts, migrating the CDS data including all the historical CDS data, and processing on an Oracle server. Recommendations shall also include the determination of an optimal database structure that integrates SAS processing with Oracle and accommodates any data changes, including, but not limited to, variable additions or deletions and new types of CDS transactions. (For Deliverable, see D.7) C.5.6 Task 2 – Regular Maintenance of CDS Data

The contractor shall implement an archival process and develop protocols for archiving source files and deleting work files.

The contractor must implement and maintain the recommended CDS data and database structures that have been approved by the COR. This includes database refresh, tune performance, support updates to materialized views and reports, report generation, and document the dataset/database organization and processes as appropriate. While this data is currently updated on a monthly basis, the contractor must be prepared to adapt to data that is provided on a weekly or daily basis.

The Contractor shall provide routine CDS data maintenance including but not limited to:

· maintain and modify table spaces;

· partition tables;

· creating audit trails;

· ensure data integrity including testing any new database design against previous data structures;

· design, develop and refresh materialized views;

· monitor database performance and activity;

· conduct performance tuning, data compression, encryption;

· recommend organization and integration of data to enhance operating efficiency;

· automate various daily, weekly, and monthly jobs that are running manually;

· monitor daily ETL processes;

· assist the SMEs and OQR DBAs in validating database designs and data integration implementation;

· use reporting tools to generate regular reports;

· support occasional ad hoc reports;

· assist DBA during deployment into a production environment.

(For Deliverable, see D.8 and D.9)

C.5.7 Task 2 – Documentation of CDS Data The Contractor shall document the data download procedure and the extraction and conversion process. Additionally, the Contractor will create a detailed and comprehensive data dictionary, with detailed explanations of all variables received from DTCC, as well as documentation related to any variables that are created within OQR or the SEC. The documentation shall include industry standard documentation such as database dictionaries, data libraries, and ER diagrams where appropriate.

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

· developing processes and procedures to establish official descriptions of the CDS data and maintain these in the DERA data dictionaries;

· documenting audit trails;

· documenting Unix shell scripts to maintain database views and tables, as well as backup and recovery files supporting the CDS data in DERA;

· documenting Oracle databases from conceptual models to Logical Models and Physical Models using data modelling tools such as ER Studio or other similar tools.

· producing industry standard documentation such as database dictionaries, data libraries, and ER diagrams.

(For Deliverable, see D.10, D.11, D.12, and D.13) C.6 Task 3 FINRA (BD, ADV, FOCUS, TRACE) Data Maintenance

C.6.1 Task 3 Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task. (For Deliverable, see D.14 and D.15) C.6.2 Task 3 Monthly Reports

The Contractor shall prepare a monthly status report describing, at a minimum, the burn rate, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. (For Deliverable, see D.17) C.6.3 Task 3 Interim Reports.

The Contractor shall prepare and deliver to the COR a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding analytical methods. (For Deliverable, see D.16) C.6.4 Task 3 Meeting Minutes.

The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes. (For Deliverable, see D.18) C.6.5 Task 3 Recommendations to improve efficiency

The contractor shall work with OQR SMEs and the OQR DBAs to quickly develop a thorough understanding of DERA's FINRA data sets, and the technical applications and operational requirements relying on these databases. Upon review of DERA’s existing FINRA data structure and management system, the contractor shall provide recommendations on ways to improve storage and processing efficiency. These recommendations shall include but are not limited to, simple Unix shell scripts, migrating the FINRA data including all the historical FINRA data, and processing on an Oracle server. Recommendations shall also include the determination of an optimal database structure that integrates SAS processing with Oracle and accommodates any data changes, including, but not limited to, variable additions or deletions and new types data contained within the FINRA Datamart. (For Deliverable, see D.19) C.6.6 Task 3 – Regular Maintenance of FINRA Data

The contractor shall implement an archival process and develop protocols for archiving source files and deleting work files.

The contractor must implement and maintain the recommended FINRA data and database structures that have been approved by the COR. This includes database refresh, tune performance, support updates to materialized views and reports, report generation, and document the dataset/database organization and processes as appropriate. While this data is currently updated on a monthly basis, the contractor must be prepared to adapt to data that is provided on a weekly or daily basis.

The Contractor shall provide routine FINRA data warehouse maintenance including but not limited to:

maintain and modify table spaces;

partition tables;

creating audit trails;

ensure data integrity including testing any new database design against previous data structures;

design, develop and refresh materialized views;

monitor database performance and activity;

conduct performance tuning, data compression, encryption;

recommend organization and integration of data to enhance operating efficiency;

automate various daily, weekly, and monthly jobs that are running manually;

monitor daily ETL processes;

assist the SMEs and OQR DBAs in validating database designs and data integration implementation;

use reporting tools to generate regular reports;

support occasional ad hoc reports;

assist DBA during deployment into a production environment.

(For Deliverable, see D.20 and D.21)

C.6.7 Task 3 – Documentation of FINRA Data

The Contractor shall document the data download procedure and the extraction and conversion process. Additionally, the Contractor will create a detailed and comprehensive data dictionary, with detailed explanations of all variables, as well as documentation related to any variables that are created in house. The documentation shall include industry standard documentation such as database dictionaries, data libraries, and ER diagrams where appropriate.

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

developing processes and procedures to establish official descriptions of the FINRA data and maintain these in the DERA data dictionaries;

documenting audit trails;

documenting Unix shell scripts to maintain database views and tables, as well as backup and recovery files supporting the FINRA data in DERA;

documenting Oracle databases from conceptual models to Logical Models and Physical Models using data modelling tools such as ER Studio or other similar tools.

producing industry standard documentation such as database dictionaries, data libraries, and ER diagrams.

(For Deliverable, see D.22, D.23, D.24, and D.25)

C.7 Task 4 MF Holdings Data Maintenance

C.7.1 Task 4 Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task. (For Deliverable, see D.26 and D.27) C.7.2 Task 4 Monthly Reports

The Contractor shall prepare a monthly status report describing, at a minimum, the burn rate, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. (For Deliverable, see D.29) C.7.3 Task 4 Interim Reports.

The Contractor shall prepare and deliver to the COR a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding analytical methods. (For Deliverable, see D.28) C.7.4 Task 4 Meeting Minutes.

The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes. (For Deliverable, see D.30) C.7.5 Task 4 Recommendations to improve efficiency

The contractor shall work with OQR SMEs and the OQR DBAs to quickly develop a thorough understanding of DERA's MF Holdings data sets, and the technical applications and operational requirements relying on these databases. Upon review of DERA’s existing MF Holdings data structure and management system, the contractor shall provide recommendations on ways to improve storage and processing efficiency. These recommendations shall include but are not limited to, simple Unix shell scripts, migrating the MF Holdings data including all the historical MF Holdings data, and processing on an Oracle server. Recommendations shall also include the determination of an optimal database structure that integrates SAS processing with Oracle and accommodates any data changes, including, but not limited to, variable additions or deletions and new types of MF Holdings transactions. (For Deliverable, see D.31) C.7.6 Task 4 – Regular Maintenance of MF Holdings Data

The contractor shall implement an archival process and develop protocols for archiving source files and deleting work files.

The contractor must implement and maintain the recommended MF Holdings data and database structures that have been approved by the COR. This includes database refresh, tune performance, support updates to materialized views and reports, report generation, and document the dataset/database organization and processes as appropriate. While this data is currently updated on a monthly basis, the contractor must be prepared to adapt to data that is provided on a weekly or daily basis.

The Contractor shall provide routine MF Holdings data maintenance including but not limited to:

maintain and modify table spaces;

partition tables;

creating audit trails;

ensure data integrity including testing any new database design against previous data structures;

design, develop and refresh materialized views;

monitor database performance and activity;

conduct performance tuning, data compression, encryption;

recommend organization and integration of data to enhance operating efficiency;

automate various daily, weekly, and monthly jobs that are running manually;

monitor daily ETL processes;

assist the SMEs and OQR DBAs in validating database designs and data integration implementation;

use reporting tools to generate regular reports;

support occasional ad hoc reports;

assist DBA during deployment into a production environment.

(For Deliverable, see D.32 and D.33 ) C.7.7 Task 4 – Documentation of MF Holdings Data

The Contractor shall document the data download procedure and the extraction and conversion process. Additionally, the Contractor will create a detailed and comprehensive data dictionary, with detailed explanations of all variables, as well as documentation related to any variables that are created in house. The documentation shall include industry standard documentation such as database dictionaries, data libraries, and ER diagrams where appropriate.

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

developing processes and procedures to establish official descriptions of the MF Holdings data and maintain these in the DERA data dictionaries;

documenting audit trails;

documenting Unix shell scripts to maintain database views and tables, as well as backup and recovery files supporting the MF Holdings data in DERA;

documenting Oracle databases from conceptual models to Logical Models and Physical Models using data modelling tools such as ER Studio or other similar tools; and producing industry standard documentation such as database dictionaries, data libraries, and ER diagrams.

(For Deliverable, see D.34, D.35, D.36, and D.37) C.8 Task 5 N-MFP Data Maintenance

C.8.1 Task 5 Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task. (For Deliverable, see D. 38 and D.39) C.8.2 Task 5 Monthly Reports

The Contractor shall prepare a monthly status report describing, at a minimum, the burn rate, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. (For Deliverable, see D.41) C.8.3 Task 5 Interim Reports

The Contractor shall prepare and deliver to the COR a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding analytical methods. (For Deliverable, see D.40) C.8.4 Task 5 Meeting Minutes The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes. (For Deliverable, see D.42) C.8.5 Task 5 Recommendations to improve efficiency

The contractor shall work with OQR SMEs and the OQR DBAs to quickly develop a thorough understanding of DERA's N-MFP data sets, and the technical applications and operational requirements relying on these databases. Upon review of DERA’s existing N-MFP data structure and management system, the contractor shall provide recommendations on ways to improve storage and processing efficiency. These recommendations shall include but are not limited to, simple Unix shell scripts, migrating the N-MFP data including all the historical N-MFP data, and processing on an Oracle server. Recommendations shall also include the determination of an optimal database structure that integrates SAS processing with Oracle and accommodates any data changes, including, but not limited to, variable additions or deletions and new types of N-MFP transactions. (For Deliverable, see D.43) C.8.6 Task 5 – Regular Maintenance of N-MFP Data

The contractor shall implement an archival process and develop protocols for archiving source files and deleting work files.

The contractor must implement and maintain the recommended N-MFP data and database structures that have been approved by the COR. This includes database refresh, tune performance, support updates to materialized views and reports, report generation, and document the dataset/database organization and processes as appropriate. While this data is currently updated on a monthly basis, the contractor must be prepared to adapt to data that is provided on a weekly or daily basis.

The Contractor shall provide routine N-MFP data maintenance including but not limited to:

maintain and modify table spaces;

partition tables;

creating audit trails;

ensure data integrity including testing any new database design against previous data structures;

design, develop and refresh materialized views;

monitor database performance and activity;

conduct performance tuning, data compression, encryption;

recommend organization and integration of data to enhance operating efficiency;

automate various daily, weekly, and monthly jobs that are running manually;

monitor daily ETL processes;

assist the SMEs and OQR DBAs in validating database designs and data integration implementation;

use reporting tools to generate regular reports;

support occasional ad hoc reports;

assist DBA during deployment into a production environment.

(For Deliverable, see D.44 and D.45) C.8.7 Task 5 – Documentation of N-MFP Data

The Contractor shall document the data download procedure and the extraction and conversion process. Additionally, the Contractor will create a detailed and comprehensive data dictionary, with detailed explanations of all variables, as well as documentation related to any variables that are created in house. The documentation shall include industry standard documentation such as database dictionaries, data libraries, and ER diagrams where appropriate.

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

developing processes and procedures to establish official descriptions of the N-MFP data and maintain these in the DERA data dictionaries;

documenting audit trails;

documenting Unix shell scripts to maintain database views and tables, as well as backup and recovery files supporting the N-MFP data in DERA;

documenting Oracle databases from conceptual models to Logical Models and Physical Models using data modelling tools such as ER Studio or other similar tools; and producing industry standard documentation such as database dictionaries, data libraries, and ER diagrams.

(For Deliverable, see D.46, D.47, D.48 and D.49)

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. The following deliverables shall be submitted electronically to the COR, and the Contract Specialist as designated:

D.1 Task 1 Master Project Plan 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, project contract milestones, and contract deliverables. The contractor shall reassess the schedule following the completion of each task and may adjust the dates with COR approval.

D.2 Task 2 Draft Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task.

D.3 Task 2 Final Project Plan

Upon completion of C.5.5 the contractor shall reassess the draft project plan. Dates and timeframes may be adjusted with COR approval. The final plan shall include detailed plans for any significant database structure changes and process changes. Additionally, the final project plan must explicitly state the reasons for any changes including the database platform selection, as well as ease of adaptability to new fields/variables, changing frequency of data delivery, and changing method of delivery.

D.4 Task 2 Interim Report The Contractor shall prepare and deliver to the COR and SMEs a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding procedures or analytical methods.

D.5 Task 2 Monthly Report

A monthly status report to the Contracting Officer’s Representative (COR), designated PM describing, at a minimum, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution.

The monthly report shall include project cost, schedule and risk. It should include a detailed summary of the burn rate, estimated monthly hours, actual hours and planned hours for the next reporting cycle. The report shall be submitted with each monthly invoice to monitor cost and schedule.

D.6 Task 2 Meeting Minutes

The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes.

D.7 Task 2 Recommendation Package

Contractor shall provide written recommendations as required byC.5.5. These recommendations shall be provided in Microsoft Office compatible format. DERA will review and provide approval decisions on recommendations no later than 10 business days after receipt.

D.8 Task 2 Database development support

Contractor shall design and develop new database objects (Tables/Views/Stored Procedures/Packages/Triggers) as required as a result of changes in the source systems and approved by the COR.

D.9 Task 2 Perform timely maintenance of CDS data sets

Contractor will provide timely maintenance of CDS data sets as described in C.5 in accordance with OQR data load standards: daily datasets no later than (NLT) 24 hours after receipt, weekly datasets NLT 24 hours after receipt, monthly datasets NLT 3 business days after receipt, quarterly datasets NLT 3 business days after receipt. Data quality standards will be described by industry standards and the final project plan. The Contractor shall conduct the data maintenance in accordance with best practices. For example, the Contractor shall maintain detailed log information that can be provided along with root cause analysis for data loading problems.

D.10 Task 2 Documentation of CDS data sets used within DERA This documentation shall consist of narrative descriptions of the CDS datasets within DERA. The documentation should describe views, table spaces, partitions, audit trails, as appropriate and according to industry standards.

D.11 Task 2 Procedural documentation for regular processes

Contractors shall provide in a Microsoft Office compatible format, technical manuals complying with industry standards describing steps in each process. The descriptions should include dependencies between processes and describe roles/responsibilities within processes. The narrative should describe the end-to-end process. The regular process descriptions shall include the Contractor’s process for changes to the database objects. As a result of the SEC’s review process, the Contractor may be asked to perform additional procedures, adjust or clarify existing work and revise its draft or final report to reflect comments provided by the SEC.

D.12 Task 2 Updated documentation of CDS data sets within DERA The contractors shall provide updated narrative descriptions of the CDS datasets within DERA as determined by the final work plan.

D.13 Task 2 Updated procedural documentation for regular processes

The contractors shall provide updated technical manuals that include descriptions of dependencies between processes and roles/responsibilities within processes. The technical manuals should be updated as necessary and as determined by the final work plan.

D.14 Task 3 Draft Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task.

D.15 Task 3 Final Project Plan

Upon completion of C.6.5, the contractor shall reassess the draft project plan. Dates and timeframes may be adjusted with COR approval. The final plan shall include detailed plans for any significant database structure changes and process changes. Additionally, the final project plan must explicitly state the reasons for any changes including the database platform selection, as well as ease of adaptability to new fields/variables, changing frequency of data delivery, and changing method of delivery.

D.16 Task 3 Interim Report

The Contractor shall prepare and deliver to the COR and SMEs a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding procedures or analytical methods.

D.17 Task 3 Monthly Report

A monthly status report to the Contracting Officer’s Representative (COR), designated PM describing, at a minimum, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution.

The monthly report shall include project cost, schedule and risk. It should include a detailed summary of the burn rate, estimated monthly hours, actual hours and planned hours for the next reporting cycle. The report shall be submitted with each monthly invoice to monitor cost and schedule.

D.18 Task 3 Meeting Minutes

The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes.

D.19 Task 3 Recommendation Package

Contractor shall provide written recommendations as required by C.6.5. These recommendations shall be provided in Microsoft Office compatible format. DERA will review and provide approval decisions on recommendations no later than 10 business days after receipt.

D.20 Task 3 Database development support

Contractor shall design and develop new database objects (Tables/Views/Stored Procedures/Packages/Triggers) as required as a result of changes in the source systems and approved by the COR.

D.21 Task 3 Perform timely maintenance of FINRA Datamart data sets

Contractor will provide timely maintenance of FINRA Datamart data sets as described in C.6 in accordance with OQR data load standards: daily datasets no later than (NLT) 24 hours after receipt, weekly datasets NLT 24 hours after receipt, monthly datasets NLT 3 business days after receipt, quarterly datasets NLT 3 business days after receipt. Data quality standards will be described by industry standards and the final project plan. The Contractor shall conduct the data maintenance in accordance with best practices. For example, the Contractor shall maintain detailed log information that can be provided along with root cause analysis for data loading problems.

D.22 Task 3 Documentation of FINRA Datamart datasets This documentation shall consist of narrative descriptions of the FINRA Datamart datasets within DERA. The documentation should describe views, table spaces, partitions, audit trails, as appropriate and according to industry standards.

D.23 Task 3 Procedural documentation for regular processes

Contractors shall provide in a Microsoft Office compatible format, technical manuals complying with industry standards describing steps in each process. The descriptions should include dependencies between processes and describe roles/responsibilities within processes. The narrative should describe the end-to-end process. The regular process descriptions shall include the Contractor’s process for changes to the database objects. As a result of the SEC’s review process, the Contractor may be asked to perform additional procedures, adjust or clarify existing work and revise its draft or final report to reflect comments provided by the SEC.

D.24 Task 3 Updated documentation of FINRA Datamart datasets

The contractors shall provide updated narrative descriptions of the FINRA Datamart datasets within DERA as determined by the final work plan.

D.25 Task 3 Updated procedural documentation for regular processes

The contractors shall provide updated technical manuals that include descriptions of dependencies between processes and roles/responsibilities within processes. The technical manuals should be updated as necessary and as determined by the final work plan.

D.26 Task 4 Draft Project Plan

The Contractor shall submit a detailed work breakdown structure for managing the work to accomplish the objectives of this project. The project plan shall include the Project Management plan, Communication plan and Quality Assurance plan for this project. The contractor shall provide a project plan narrative that describes the project, technical approach, milestones, and deliverables. The project plan shall also include a project schedule for all work associated with this task.

D.27 Task 4 Final Project Plan

Upon completion of C.7.5, the contractor shall reassess the draft project plan. Dates and timeframes may be adjusted with COR approval. The final plan shall include detailed plans for any significant database structure changes and process changes. Additionally, the final project plan must explicitly state the reasons for any changes including the database platform selection, as well as ease of adaptability to new fields/variables, changing frequency of data delivery, and changing method of delivery.

D.28 Task 4 Interim Report

The Contractor shall prepare and deliver to the COR and SMEs a status report or a presentation describing, at a minimum the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution. This report may be in the form of a presentation, particularly regarding procedures or analytical methods.

D.29 Task 4 Monthly Report

A monthly status report to the Contracting Officer’s Representative (COR), designated PM describing, at a minimum, the current status and progress, work accomplished during the report period, work to be accomplished during the next report period, and identifying any issues or concerns requiring resolution.

The monthly report shall include project cost, schedule and risk. It should include a detailed summary of the burn rate, estimated monthly hours, actual hours and planned hours for the next reporting cycle. The report shall be submitted with each monthly invoice to monitor cost and schedule.

D.30 Task 4 Meeting Minutes

The Contractor shall prepare and deliver to the COR Meeting Minutes from all meetings with the OQR SME related to this task order. The Contractor shall work with the COR to resolve issues and shall resubmit the revised Meeting Minutes.

D.31 Task 4 Recommendation Package

Contractor shall provide written recommendations as required by C.7.5. These recommendations shall be provided in Microsoft Office compatible format. DERA will review and provide approval decisions on recommendations no later than 10 business days after receipt.

D.32 Task 4 Database development support

Contractor shall design and develop new database objects (Tables/Views/Stored Procedures/Packages/Triggers) as required as a result of changes in the source systems and approved by the COR.

D.33 Task 4 Perform timely maintenance of MF Holdings data sets

Contractor will provide timely maintenance of MF Holdings data sets as described in C.7 in accordance with OQR data load standards: daily datasets no later than (NLT) 24 hours after receipt, weekly datasets NLT 24 hours after receipt, monthly datasets NLT 3 business days after receipt, quarterly datasets NLT 3 business days after receipt. Data quality standards will be described by industry standards and the final project plan.