Data Warehouse Services to Support Intergraph RMS Implementation

Data Warehouse Services to Support Intergraph RMS Implementation

DCTO-2011-

Statement of Work

Data Warehouse Services to Support Intergraph RMS Implementation

SECTION C

DESCRIPTION/SPECIFICATION/STATEMENT OF WORK

C.1SCOPE/BACKGROUND

C.1.1In Fiscal Year 2008, the Metropolitan Police Department (MPD) embarked on a significant upgrade and integration of its information technology infrastructure through Phase I of the Law Enforcement Information Management System (LEIMS) Program. Phase I involved the building of a Data Warehouse in which to store several of MPD’s Priority I data sources.

C.1.1.1The Data Warehouse was built by Data Vision Group (a subcontractor in the first contract to Dimension Data and the prime in the second contract).

C.1.1.2The Data Warehouse is divided into two components: a replication of the original source data in a Legacy Data Warehouse (LDW) and an Operational Data Warehouse (ODW) upon which to do further analysis and reporting.

C.1.1.3MPD purchased Informatica as the ETL tool to build and support the Warehouse.

C.1.1.4The Operational Data Warehouse is being positioned as the repository of all MPD reportable data.

C.1.2Phase II of the LEIMS Program involved the creation of a comprehensive Records Management System (RMS). This is being executed through multiple phases involving planning, infrastructure development, data improvement, and acquisition and employment of improved replacement systems.

C.1.2.1MPD contracted with Yellow House Associates to builda customized Records Management System (RMS). The first of the RMS modules, Automated Field Reporting,was implemented in April 2009. Additional modules have been added since then, including an Interim Arrest & Booking module and the Investigative Case Management (ICM) module, which replaces the older case management application.

C.1.2.2In the summer of 2010, however, MPD contracted with Intergraph Public Safety to implement their commercial off-the-shelf (COTS) RMS application, which provides a more all-in-one solution for many of MPD’s operational needs. The desktop version of this application is called I/LEADS, and the mobile version is called IFR. As part of the Task Order, Intergraph is also working with MPD and the District Office of Unified Communications (OUC) to upgrade its CAD schema to v9.1 and converting the CAD database from Oracle to SQL Server.

C.1.3The first phase of the Intergraph award to MPD includes a preliminary export of data to the Legacy Data Warehouse as well as a one-time data conversion to the Operational Data Warehouse. MPD is seeking a vendor to provide additional services to update the Warehouse in preparation for, and following, this conversion.

C.1.3.1Add new fields from the Yellow House Field Reporting, Arrest & Booking and ICM Modules within MPD’s RMS, via an Informatica Extract, Transform and Load (ETL) process, to the related tables that are already part of the Legacy and Operational Data Warehouses;

C.1.3.2Supplement the Operational Data Warehouse with a subset of fields from the Legacy Data Warehouse;

C.1.3.3Transfer new I/LEADS fields from the Legacy Data Warehouse to the Operational Data Warehouse via an Informatica ETL process, and train MPD members to do the same;

C.1.3.4Prepare a Feasibility Study to Convert the Data Warehouse from an Oracle 10G platform to SQL Server. If successful, Contractors will perform the conversion;

C.1.3.5Map Computer-Aided Dispatch (CAD) 911/311 data as an additional data source to the Legacy and Operational Warehouses; and

C.1.3.6Provide ongoing support and maintenance of the Data Warehouse to MPD, OUC, the District of Columbia Office of the Chief Technology Officer (OCTO) and Intergraph during the transition to I/LEADS and IFR.

C.1.4The addition of one new data source and several new data fields into the Data Warehouse noted in Section C.1.3 will satisfy the same initial operational objectives defined at the beginning of Phase I of the LEIMS Program. Specifically, the Data Warehouse standardizes data and uses a logical data structure to store data currently contained in multiple systems. It is expected that these additional data will be standardized using the Global Justice XML Data Model (GJXDM), and exchanged through an Informatica Extract, Transform and Load (ETL) process.

C.1.5All contractor tasks will involve at least weekly, but often daily, coordination between the vendor, MPD, OUC and Intergraph staff.

C.2DEFINITIONS

Acronym / Name / Description
ETL / Extract, Transform, and Load / The functions performed when pulling data out of one database and placing it into another of a different type.
GJXDM / XML data model / Global Justice XML Data Model (GJXDM or Global JXDM) is a data reference model for the exchange of information within the justice and public safety communities
LDW / Legacy Data Warehouse / One half of MPD’s Data Warehouse, which serves as a backup of the original primary source databases (crime, arrest, case management and other databases dating back several years)
LEIMS / Law Enforcement Information Management System / Name of the technical system planned for MPD in FY 2008
MPD / Metropolitan Police Department / Primary law enforcement agency of the District of Columbia
OCTO / Office of the Chief Technology Officer / District of Columbia government agency that coordinates technology projects throughout the city
ODW / Operational Data Warehouse / Second half of MPD’s Data Warehouse: subset of the LDW, which standardizes the data for future analysis and reporting
Oracle / Relational database / A relational database management system (DBMS) from Oracle
SQL Server / Relational database / A relational database management system (DBMS) from Microsoft

C.3REQUIREMENTS

C.3.1Add new fields from the Yellow House Field Reporting, Arrest & Booking and ICM Modules within MPD’s RMS, via an Informatica Extract, Transform and Load (ETL) process, to the related tables that are already part of the Legacy and Operational Data Warehouses;

C.3.1.1Contractors shall work with Yellow House and MPD to identify additional fields added since early CY 2010 to MPD’s RMS as part of the development of the new modules. They will then use an Informatica ETL process to populate the LDW and ODW with these new fields.

C.3.2Supplement the Operational Data Warehouse with a subset of fields from the Legacy Data Warehouse

C.3.2.1Contractors shall identify and incorporate fields defined as crucial to the Intergraph Data Conversion, but missing from the current LDW, to the ODW, again via an Informatica ETL process.

C.3.3Transfer new I/LEADS fields from the Legacy Data Warehouse to the Operational Data Warehouse via an Informatica ETL process, and train MPD members to do the same

C.3.3.1Intergraph will identify the fields for, and perform the mapping of, new I/LEADS fields to the LDW. Intergraph will then perform a database export to the LDW, ensuring that the format of fields in production I/LEADS is identical to the format in the LDW. Assistance will then be needed from the contractors to transferthese new I/LEADS fields from the LDW to the ODW via an Informatica ETL process. These data elements will be used for future reporting.

C.3.3.2As part of this task, contractors will train two MPD IT representatives how to transfer fields added to I/LEADS in the future from the LDW to the ODW, and provide written documentation for this function in the Post Project Report (C.6.5).

C.3.4Prepare a Feasibility Study to Convert the Data Warehouse from an Oracle 10G platform to SQL Server. If successful, Contractors will perform the conversion;

C.3.4.1Contractors shall prepare a Feasibility Studyfor MPD’s review and approval to list the steps required to convert the Data Warehouse from Oracle 10G to SQL Server. This will involve researching known Microsoft and other tools to perform such conversions. The plan will also include regression and performance testing steps, as well as a risk assessment, to ensure boththe LDW and ODW continue to function properly after the conversion.

C.3.4.2If MPD approves the work described in the Feasibility Study, and preliminary testing demonstrates such a conversion will be successful, contractors will also perform the conversion.

C.3.5 Map Computer-Aided Dispatch (CAD) 911/311 dataas an additional data source to the Legacy and Operational Warehouses.

C.3.5.1MPD has archived two years of older (1998-1999) GEAC CAD data (currently stored as a flat file)and up to 10 years of more recent Intergraph CAD data (stored in a replicated Oracle database server called the DCCADMIR and Microsoft Access files). CAD data for much of 2011 is in the process of being converted to SQL. Contractors shall map all available data from these sources to the LDW and the ODW.

C.3.5.2Contractors shall publish the selected priority systems through an Informatica ETL process, in accordance with the standards and schema used to build the original Data Warehouse.

C.3.5.3The Data Warehouse shall not replace the existing source. Data shall continue to be entered into the CAD system and update in the Data Warehouse.

C.3.5.4Contractors shall ensure the Data Warehouse is continuously updated with data from these new systems using the same architecture developed during LEIMS I.

C.3.5.5Contractors shall ensure the completeness and accuracy of the loads using the same testing and MPD approval procedures developed during LEIMS I.

C.3.6Provide ongoing support and maintenance of the Data Warehouse to MPD, OUC, the District of Columbia Office of the Chief Technology Officer (OCTO) and Intergraph during the transition to I/LEADS and IFR.

C.3.6.1As part of the one-time Data Conversion, Intergraph will be writing directly to the ODW and bypassing the ETL functionality. The Contractor shall provide ongoing support of, and maintenance to, the Data Warehouse. Examples of related tasks will include:

C.3.6.1.1Conducting preventative maintenance tasks, such as log monitoring and error troubleshooting, to ensure that the Data Warehouse loads flow as scheduled;

C.3.6.1.2Conducting perfective maintenance tasks, such as database tuning and software upgrades, to ensure that the Data Warehouse environment continues to operate at optimal efficiency;

C.3.6.1.3Working with MPD and its partners to establish production support procedures;

C.3.6.1.4Ensuring that all data flows to the Citywide Data Warehouse are uninterrupted;

C.3.6.1.5Serving as technical support/help desk for user-generated questions about the Data Warehouse and the data it contains; and

C.3.6.1.6Working with MPD and its vendors to plan for and implement high availability and disaster recovery solutions, including backups and mirroring.

C.4TECHNICAL STANDARDS

C.4.1The Contractor shall recognize MPD’s architecture as being developed as part ofthe LEIMS project as the target architecture for MPD development. All installation, development, deployment and migration activities performed through this contract shall employ this architecture and these specific components as appropriate.

C.4.2MPD builds technology systems in accordance with a logical information technology architecture design that enables data exchange and application integration across the enterprise. Extensible and adaptable in nature, the architecture model scope includes legacy systems, custom and off-the-shelf applications.

C.5TECHNICAL STANDARDS FOR IMPLEMENTATION

C.5.1MPD has set a number of technical standards for implementation of the Data Warehouse. The Contractor shall implement its work in accordance with the specific configuration, software components and standards of the following model:

Table C-1 – District Product Standards

ETL Tool / Informatica Power Center
Data Quality Tool / Informatica Data Quality Tool
Relational database / Oracle 10g or SQL Server

C.11COOPERATION WITH OTHER CONTRACTORS

C.11.1Contractors shall cooperatively work as a team with each other as well as with any other Contractors assigned to MPD’s customized RMS and Intergraph RMS projects. Contractors shall not commit or permit any act that will interfere with the performance of work by another District Contractor or by any District employee.

C.11.2Contractors may have to sign non-disclosure agreements with other MPD contractors.

C.12BACKGROUND CHECKS

C.12.1All Contractor personnel involved in the project shall submit to a background check including fingerprinting. Contractors and/or the ITSA COTR, OST, shall be responsible to pay the FBI processing fee of $35 per person.

F.3 DELIVERABLES

The Contractor shall submit the following deliverables to the COTR along with those specified in Section C.3.

CLIN / Deliverable / Quantity / Format/
Method of Delivery / Due Date / To Whom
1 / C.6.1Deliverable 1
Project Kickoff Meeting / Meeting / 10 days from contract award / MPD Project Team
2 / C.6.2Deliverable 2 Project Management Plan / 2 / Document (hard and soft copy) / 20 days from contract award / COTR
3 / C.6.3 Deliverable 3
Weekly Progress Reports / 1 / Document (hard and soft copy) / Weekly / COTR
4 / C.1.3.1Add new fields from the Yellow House Field Reporting, Arrest & Booking and ICM Modules within MPD’s RMS, via an Informatica Extract, Transform and Load (ETL) process, to the related tables that are already part of the Legacy and Operational Data Warehouses / N/A / N/A / 75-90 days from contract award / COTR
5 / C.1.3.2 Supplement the Operational Data Warehouse with a subset of fields from the Legacy Data Warehouse / N/A / N/A / 75 days from contract award / COTR
6 / C.1.3.3Transfer new I/LEADS fields from the Legacy Data Warehouse to the Operational Data Warehouse via an Informatica ETL process, andtrain MPD members to do the same / N/A / N/A / 60-75 days from contract award for transfer / COTR
7 / C.1.3.4Prepare a Feasibility Study to Convert the Data Warehouse from an Oracle 10G platform to SQL Server. If successful, Contractors will perform the conversion / 2 / Document (hard and soft copy) / 120 days from contract award / COTR
8 / C.1.3.5 Map Computer-Aided Dispatch (CAD) 911/311 data as an additional data source to the Legacy and Operational Warehouses / N/A / N/A / 160 days from contract award / COTR
9 / C.1.3.6 Provide ongoing support and maintenance of the Data Warehouse to MPD, OUC, the District of Columbia Office of the Chief Technology Officer (OCTO) and Intergraph during the transition to I/LEADS and IFR / N/A / N/A / Ongoing / COTR
10 / C.6.5 Deliverable 4
Post-Project Report / 2 / Document (hard and soft copy) / 10 days from expiration of contract / COTR

1