Attach_10_TO1_PWS.pdf

HTC711-14-R-D004

Attachment 10

Comprehensive Financial Services for United States Transportation Command (USTRANSCOM) Program Analysis and Financial Management Directorate (TCJ8)

TWCF Rates Study and Expansion of TWCF TCJ8 Data Mart Performance Work Statement (PWS)

As of 18 Feb 2014

1.0 DESCRIPTION OF SERVICES

1.1 Background. TCJ8 has been tasked to implement a comprehensive study of Air Mobility Command (AMC) and Military Surface Deployment and Distribution Command (SDDC) Transportation Working Capital Fund (TWCF) billing rates. TCJ8 has also determined the need for additional capabilities within the TCJ8 TWCF Data Mart functionality.

1.2 Objective. This PWS defines the specific tasks to be performed under the TWCF Rates Study and Expansion of TWCF TCJ8 Data Mart task order under the USTRANSCOM Indefinite Delivery, Indefinite Quantity (IDIQ) contract known as Comprehensive Financial Services Support for USTRANSCOM TCJ8. The overall goal of this effort is to ensure the TCJ8 executes the direction for the TWCF Billing Rates Study and expansion of the TCJ8 TWCF Data Mart functionality.

1.3 Scope. This task order shall provide TCJ8 with lead support for program management, cost and financial management analysis, systems analysis, and administration of the Command initiative to study business processes and complete the TWCF Billing Rates Study. Additionally, this task order shall provide TCJ8 with financial and operational information technology systems support of the expansion of the TCJ8 TWCF Data Mart.

1.4 Specific Tasks.

Contract Level and Task Order Execution Management Command Initiatives, Business Processes, and Financial Studies Financial IT and Operational Support

1.4.1 Task Area 1 - Contract Level and Task Order Execution Management - This task area shall encompass overall contract management. This task area requires the Contractor to apply project management best business practices to manage implementation of tasks and schedules and provide contract level and task order execution management and centralized project support that tracks all performance of the work. The Contractor shall be responsible for the performance of the work, and the management of Contractor personnel performing the tasks in this PWS. The Contractor shall provide professional analysis and record keeping support by preparing documents such as briefings, point papers, and meeting minutes related to status of the performance of this task order.

1.4.1.1 – Task Area 1, Subtask 1 - Monthly Status Report - The Contractor shall provide a Monthly Status Report (MSR) monitoring the quality assurance and configuration management.

The MSR should outline the current funding status and a brief synopsis of the efforts and accomplishments completed and deliverables provided by task area during the reporting period.

The report should also describe the objectives to be accomplished during the next reporting period, and an overall evaluation of the task order to date, and any issues, problem areas, and items that require Government action. The MSR is due monthly within the first five (5) business days of each month.

1.4.2 Task Area 2 – TWCF Billing Rates Analysis and Study – USTRANSCOM has increased the emphasis on ensuring TWCF billing rates are based on TWCF operating cost where possible/applicable. TCJ8 has also been tasked with simplifying and streamlining the rate development process. The Contractor shall lead an in-depth study of TWCF billing rates process. Study shall consist of short term rate development process changes (FY16) and long term changes (FY17 and out). The final outcome of the study is for the Contractor to provide a report recommending improvements to the current rate development process along with the associated cost estimates and estimated implementation dates for changes in regulatory guidance and financial and operational systems that may be required. The report shall include recommendations for correcting any discrepancies found. In addition, the report shall document data that clearly shows disconnects between current rates and actual costs. The Contractor shall map and document USTRANSCOM and Components’ operational and financial processes that require changes to improve the rate development process. The study shall be completed and the report shall be provided within one year of the start of the task order.

Currently TCJ8 is responsible for billing rate development for a number of AMC and SDDC TWCF business areas: AMC Channel Cargo, AMC Channel Passenger, SDDC Liner Ocean Transportation, SDDC Port Operations, and SDDC Global Privately Owned Vehicles (POV).

AMC is responsible for development of AMC Special Airlift Assignment Mission (SAAM) rates. The study shall address rate development for all AMC and SDDC TWCF business areas based on a priority established by TCJ8 at the start of the study.

The TWCF billing rate development process is hindered by its dependence on multiple component operational and financial systems for current and historical cost information. There are also many limitations within the related billing systems. Service and regulatory requirements also limit flexibility.

The Contractor shall execute the capture and evaluation of financial data, financial improvement, and operational strategic processes within the Command as part of the study and publish weekly updates, No Later Than (NLT) Tuesday, to TCJ8 and the monthly updates, NLT the 10th business day, to the Rates General Officer Steering Committee (GOSC).

The Contractor shall facilitate weekly related meetings, to include the planning, scheduling, and producing post workshop or conference minutes.

The Contractor shall track USTRANSCOM study milestones and develop briefings and presentations on the study.

The Contractor shall draft responses and presentations, related to the study, for use by the TCJ8 at various DPO governance bodies, working groups, and process action teams.

Contractor shall lead after action items through the end of the period of performance as dictated by the USTRANSCOM TWCF Rates General Officer Steering Group (GOSC).

1.4.3 Task Area 3 – Expansion of TWCF TCJ8 Data Mart – Contractor shall provide applicable customers with self-service, easy-to-use access to TWCF related transaction and summary-level financial and revenue/billing workload information required for fact-based analysis and decision making. Access is provided using DLA and USTRANSCOM’s Integrated Data Environment (IDE)/Global Transportation network (GTN) Convergence (IGC) Enterprise Data Warehouse (EDW’s) IBM Cognos v10.2 Business Intelligence (BI) resources. Self-service information access development includes identifying information gaps in the TCJ8-maintained, Teradata-based IGC TWCF data marts, researching the data sources needed to fill these gaps, developing a set of processes to capture, process, and store the additional needed data within the IGC EDW, and then developing and implementing the IBM Cognos v10.2 reporting objects, PowerCubes, metric score cards, and dashboards necessary to make the new information easily accessible by a variety of customers.

Contractor shall perform source data capture, IGC data loading, processing, and storing processes development which requires creating and implementing Structured Query Language (SQL)-based source system data retrieval scripts that can be automatically executed on a set schedule; most of the current source systems accessed are running on an Oracle Database Management System (DBMS). IGC TWCF Data Mart data loading, processing, and storage involves modeling and implementing target data tables, and then creating Teradata FastLoad and MultiLoad utility scripts to efficiently bulk load the source data into the IGC EDW. Data processing is often necessary to cleanse, integrate, and transform data into usable information and this needs to be implemented using Teradata SQL scripts and stored procedures. Data processing/transformation examples include performing AMC Channel shipment revenue allocations down to the mission sortie level, and performing ocean port operations miscellaneous expense allocations down to the Transportation Control Number (TCN)/shipment unit level.

This collection of data capture, loading and processing processes is often referred as “data Extract, Load, and Transform (ELT)”. IBM Cognos v10.2 reporting objects, PowerCubes, metric score cards, and dashboards make up the self-service information business and presentation layers, within IGC Cognos, that customers access. These layers provide integrated views of common information sets, so that users don’t need to know how to perform underlying database table joins. They also incorporate standard business terminology versus using cryptic database object names, and include frequently used calculations to increase reporting consistency and accuracy, and to save users time. The business and presentation layers need to have Cognos “drill-down” and “drill-through” functionality thoroughly incorporated to facilitate quick and efficient data exploration across and within applicable data sets.

An initial IGC Cognos and Teradata-based TWCF self-service analysis and reporting solution has already been developed and is being maintained for the following business areas: AMC Channel, SDDC Ocean Liner, SDDC Port Operations, and SDDC Global POV. Contractor shall enhance and assist in maintaining this existing solution as required.

Contractor shall develop the following new Cognos PowerCubes:

1. AMC Cost Cube by Business Area

2. AMC Net Operating Result (NOR) and Workload Cubes by Business Area

3. AMC Special Assignment Airlift Mission (SAAM) Revenue & Workload Cube

4. AMC Flying Hour (Channel, SAAM, Exercise, & Contingency) Cube

5. AMC Commercial Operations Integrated System (COINS) Contract Airlift Expense and

Flying Hour Cube

6. TWCF Cash Management Cube

Contractor shall provide weekly data loading to update the data mart, maintain and update data mart data transformation and cleansing scripts, support ad hoc query and reporting capabilities, and metadata modeling using for the newly developed Cognos Cubes. Contractor shall provide full documentation of all modified scripts within 90 calendar days after completion.

Contractor shall provide documentation and assistance for users of Cognos PowerPlay and Analysis Studio. The Contractor shall maintain a user guide and documentation containing information on the design of each Cognos PowerCube and the supporting data marts and database views created under this task. This report will include dimension maps, table record layouts, field/dimension/measure definitions, and descriptions of the cube update processes.

Documentation will provide all business rules, assumptions, allocation methods, data sources as well as any other pertinent information needed. Documentation shall be provided within 90 calendar days after completion of building of new or updated Cognos Capability.

Building the above PowerCubes also includes creating and implementing the underlying data infrastructure to support cube regeneration such as source data Extraction, Loading new/updated data into IGC Teradata, and then any necessary Transformations; these are the “ELT” processes.

Contractor is also responsible for developing all Cognos reports needed to facilitate cube generation and user “drill-through” back to the applicable cube source transaction-level records stored within the IGC EDW TWCF Data Marts.

Contractor shall provide IBM Cognos expertise in support of developing and implementing strategic performance scorecards dashboards within Cognos Metric Studio and Cognos Workspace. This includes designing new Metric Studio applications.

Contractor shall develop and implement a set of processes that automatically monitor the IGC TWCF Data Mart data Extract, Transform, and Load (ELT) processes and send out automated alerts to J8 administrators via e-mail when problems are detected. Automated processes that perform source systems data retrievals, bulk data loading into IGC Teradata (i.e. TWCF Data Mart), and then process and transform the source data generate an extensive amount of audit log files (ASCII text, some containing thousands of lines) that can be used to identify and then troubleshoot a variety of ELT problems. These automated alert mechanisms need to be developed using IGC EDW and USTRANSCOM-approved applications and programming languages such as Oracle SQL*Plus, Microsoft Visual Basic for Applications (VBA), PERL, Teradata tools and utilities, and Cognos Event Studio. The IGC system already contains IBM Cognos Event Studio, which can be used to send out automated alert e-mails.

2.0 Deliverables Schedule - All deliverables shall be submitted directly to the Contracting Officer Representative (COR) in electronic format. Deliverables shall be identified by Delivery Order Number, PWS subtask number, and deliverable title. All deliverables must meet professional standards and comply with the requirements set forth in this PWS. The Contractor shall provide all deliverables electronically in Microsoft Office (Word, Excel, Power Point, Project, etc.) formats pursuant to the following schedule.

All technical data and noncommercial software delivered under this task order are developed exclusively at the Government’s expense, and in accordance with Department of Defense Federal Acquisition Regulation Supplement (DFARS) 252.227-7013 and DFARS 252.227-7015, the Government has unlimited rights in these deliverables. For all other noncommercial data delivered under this task order, the Government has the right to use, modify, reproduce, release, display, or disclose, in whole or in part, in any manner and for any purpose whatsoever, without restrictions of any kind, within and without the Government.

PWS

Task

DELIVERABLE SCHEDULE

1.4.1 Monthly Status Report (MSR) Monthly - No later than the fifth (5th) business day of each month.

1.4.2 Provide final TWCF Rates Study

report

NLT one year after task order start

1.4.2 Provide weekly updates on status

of the study to J8

Weekly NLT Tuesday of the following week

1.4.2 Provide monthly updates of the

status to the Rates General Officer Steering Committee

(GOSC).

Monthly NLT 10th business day of the following month

1.4.3 Data Loading Process Update weekly

1.4.3 Maintain and Update Data Mart

Data Transformation and Cleansing Scripts

Full documentation of all modified scripts within 90 calendar days after completion

1.4.3 Documentation and User Guides Update documentation within 90 calendar days after completion of new or updated Cognos Capability

3.0 SERVICE DELIVERY SUMMARY The Service Delivery Summary (SDS) represents the most important contract objectives that, when met, will ensure contract performance is satisfactory. Although not all PWS requirements are listed in the SDS, the Contractor is fully expected to comply with all requirements in the PWS.

PERFORMANCE

OBJECITVE

PWS PARAGRAPH PERFORMANCE THRESHOLD

Monthly Status Reports 1.4.1 98% of the time reports received by the 5th of each month. Final by end of contract period.

Provide final TWCF Rates Study report

1.4.2 100% of the time NLT one year after task order start

Data Loading Process 1.4.3 98% of the time within 5 work days of previous data load

3.1 Inspection and Acceptance. A deliverable is a specific measurable, tangible, and verifiable activity, outcome, result, item, or product that the Contractor shall provide and that will require formal Government acceptance and approval. The Government will review, assess and provide comment, as required.

Unless otherwise specified within an individual task order, final inspection and acceptance of all work performed, reports, or other deliverables shall be performed at the place of delivery. The COR will notify the Contractor of deliverable acceptance or provide comments in writing.

4.0 GENERAL INFORMATION

4.1 Place of Performance. The primary place of performance for all services will be performed on Scott AFB, IL. The Government shall provide workspace Contractor personnel as required.

4.2 Period of Performance. The period of performance for this contract shall consist of a base period with 3 optional periods through 31 July 2017. Exercise of option periods are at the sole discretion of the Government

Base Period: 1 August 2014 to 30 September 2014 Option Period 1: 1 October 2014 to 30 September 2015 Option Period 2: 1 October 2015 to 30 September 2016 Option Period 3: 1 October 2016 to 31 July 2017

5.0 Contractor Ramp-up. The Contractor shall have at least 50 percent (50%) of all necessary personnel on contract start (1 August 2014). The Contractor shall have 100 percent (100%) of personnel available on 14 August 2014. The contractor shall ensure that personnel start does not impair performance to meet all deliverables.