DW Project Scope Statement

Document Sample
DW Project Scope Statement Powered By Docstoc
					North Carolina Community College System

Data Warehousing Project
CCSDW Version 1.0

Scope Statement

Department/Agency Issue Date Project Name Project Sponsor Project Office Contacts

NC Community Colleges System Office October 29, 1999 Data Warehousing Project Dr. Brenda Rogers, Vice President for Administrative Services Steve Ijames, Director of Information Services or Arthur Hohnsbehn, Associate Director of IS

Project Office Telephone Project Office Email Deadline for Vendor Questions Voluntary Vendor's Conference Answers to Vendor Questions Posted on Due Date for Vendor Statement of Work Submit Statement of Work to

919 733-7051 Ijamess@ncccs.cc.nc.us 5:00 PM, November 11 1999 See page 17 November 18, 1999 5:00 PM, November 30, 1999 Ijamess@ncccs.cc.nc.us

Scope Statement Purpose The purpose of the this scope statement is to solicit Statements of Work from eligible vendors to provide a project team that will be responsible for designing, developing, testing, and implementing, in concert with the NC Community Colleges System Office, a data warehouse system to meet the needs as defined in this document.

Table of Contents

1.

INTRODUCTION ........................................................................................................ 1 1.1 THE WORKING MISSION ............................................................................................1 1.2 OVERVIEW OF THE NCCCS........................................................................................1 1.3 NCCCS P ROGRAMS ..................................................................................................1 1.5 DEFINING THE P ROBLEM ............................................................................................2 SCOPE AND OBJECTIVES......................................................................................... 3 2.1 SOLUTION OVERVIEW ................................................................................................3 2.2 P ROJECT MANAGEMENT STRUCTURE..........................................................................6 2.3 DATA WAREHOUSE ARCHITECTURE ...........................................................................8 2.4 SCOPE.......................................................................................................................8 2.5 DELIVERABLES ........................................................................................................10 2.6 DEVELOPING A STATEMENT OF WORK COST .............................................................11 2.6.1 Cost Model.......................................................................................................11 2.6.2 Software and Other Requirements......................................................................11 2.6.3 Hardware.........................................................................................................12 2.6.4 Personnel Resources.........................................................................................13 2.6.5 Training...........................................................................................................13 2.6.6 Cost Summary..................................................................................................14 2.7 DATA SECURITY......................................................................................................15 2.8 EVALUATIVE CRITERIA ............................................................................................16

2.

3.TERMS AND CONDITIONS......................................................................................... 16 3.1 OWNERSHIP AND CONFIDENTIALITY.........................................................................16 3.2 ESTIMATE: TIME AND COST .....................................................................................16 3.3 STATEMENT OF WORK CHECKLIST ...........................................................................17 3.4 VOLUNTARY VENDOR CONFERENCE.........................................................................17 3.5 SIGNATURES : .........................................................................................................17 4. GLOSSARY................................................................................................................... 19

Printed on: 12/19/00

CCSDW-V1.0

ii

1. Introduction
1.1 The Working Mission

The mission of the North Carolina Community College System is to open the door to opportunity for individuals seeking to improve their lives and well being by providing: • • • Education, training and retraining for the workforce, including basic skills and literacy education, occupational and pre-baccalaureate programs. Support for economic development through services to business and industry. Services to communities and individuals that improve the quality of life.

1.2

Overview of the NCCCS

The North Carolina Community College System (NCCCS) is fifty-nine institutions in communities throughout North Carolina and administrative and support Offices at 200 West Jones Street in Raleigh NC called the Community Colleges System Office (CCSO). The CCSO provides support services such as program leadership, financial oversight, budgeting, legislative support, accountability measurement, research, and analysis to further the goals of the colleges and support the staffs who work there.

1.3

NCCCS Programs

The North Carolina Community College System offers a comprehensive range of educational programs to meet the needs of local communities for workforce preparedness and higher academic education, basic educational skills, job retraining, personal growth and development, and community and economic development. These programs are organized under several broad categories. Curriculum programs are made up of credit courses leading to certificates, diplomas, or associate degrees, which range in length from one semester to two years. Most of the more than 1,800 programs offered within the Community College System are designed to prepare individuals for entry level technical positions in business and industry with an associate of applied science degree. Each college also offers credit courses in the arts and sciences leading to an associate degree designed for transfer at the junior level into a senior college or university. Developmental education courses are available for students who need to improve their skills so that they can perform at the level required for college transfer, certificate, diploma and associate degree programs. Developmental education programs consist of courses and support services which include, but are not limited to, diagnostic assessment and placement, tutoring, advising, and writing assistance. These courses do not earn credit toward a degree, diploma or certificate but provide the student with courses for academic readiness. Another category of programs is continuing education. These non-credit courses may be occupational, academic, or avocational in nature. Some are offered as a categorically funded community service. Others are designed to upgrade occupational skills and are funded through enrollment driven formulas (see Finance). Each of the colleges also offers instruction in basic academic skills which include Adult Basic Education (K–8 basic literacy skills), Adult High School and GED programs (9–12 academic preparation), Compensatory Education, and English as a Second Language (ESL).

12/19/00

CCSDW-V1

1

NCCCS Data Warehousing Project
Because of the unique character of community colleges, student services programs play an especially important role in the life of the colleges. Students receive academic, personal and career counseling services, special assessment and placement assistance, help in transition to work and job development, and a variety of other services which are essential to the success of the instructional programs. Finally, there is a broad effort in specialized programming, often targeting the economic development of the community. The New and Expanding Industry Program, the Focused Industrial Training Centers, and the Small Business Center Network all provide direct consulting and custom training to business and industry to promote their success. The Human Resources Development and Job Training Partnership Act Programs provide services and training specifically targeted to the unemployed and disadvantaged. A variety of other programs connects the colleges uniquely to the needs and aspirations of their communities.

1.4

State-Level Reporting

To support the accountability, research, analysis and general decision support responsibilities of the CCSO, the CCSO captures information about students, classes, faculty and staff. The student information captured includes enrollment records with demographic data in addition to program, course and outcome data. Records are collected at the end of each semester (fall, winter, and summer) along with annual summary records. Law and policy with respect to privacy issues protect all data collected. Approximately 750,000 individual student records are processed annually. The data are complex and extensive. Data pertain to course registrations, grade and outcome reports. In addition, students may be classified as degree, diploma or certificate students known collectively as curriculum students or as Continuing Education Students who enroll in learning and enrichment programs. There may be some mixed occurrences of both reflected in the same data reports. Additionally, the system annually manages approximately 11,000 full time staff members and 15,000 part times staff members who also yield data sets. The data are captured at individual colleges and transmitted to the Information Services Section of the CCSO for processing and analysis purposes. This professional group provides annual report summaries as well as ad hoc analysis service for the member colleges, CCSO staff, the General Assembly, and the public. A major product of the CCSO Information Services Section is the Annual Statistical Report containing such information as Student Enrollment by College, Geographic Origin of Curriculum Students Enrolled, Annualized FTE and the like. A copy of the Annual Statistical Report can be seen at www.ncccs.cc.nc.us under the "publications" section. In addition, each College retains data for it’s on analysis but also relies heavily on the research support of the CCSO for in depth analysis and comparative studies using the system wide data sets. The individual colleges have no access to the system-wide data sets for their own trend and outcome analysis.

1.5

Defining the Problem

The administrations of the colleges, their respective offices of research and planning, faculty and staff, the professional information technology staff at the CCSO, and the State Legislators have a critical need for comprehensive data and state-of-the-art tools to better collect, aggregate and analyze community college data. Individual colleges need to forecast trends and discover student Printed on: 12/19/00 CCSDW-V1.0 2

NCCCS Data Warehousing Project
outcomes both locally and comparatively with other colleges to best serve their communities and the people of the state. CCSO decision makers at all levels need access to information on which they can perform analyses to improve their decision making ability. The research analysts in the General Assembly need access to NCCCS information to perform their functions. The present system has inherent limitations that impair the ability to scale information to data sets that are more comprehensive, prohibits access by non-programmer level staff, and requires professional staff intervention to process and analyze data. To become more effective in the use of statewide community college data for decision support, planning, comparative analyses, and reporting, the statewide data must be re-engineered from a mainframe 'flatfile' technology. The current technology requires professional programmers to access data and perform analyses. The need is to migrate to a system with user-friendly data access, analysis, and reporting tools. It is also important to provide an interface to the World Wide Web so that students and the public have access to appropriate subsets of the data.

2. Scope and Objectives
2.1 Solution Overview

The solution is to manage the data in a Data Warehouse system. Such a solution provides immediate, reliable and usable results that are available to the various user constituents. Colleges are better able to plan and execute services for their communities, the CCSO staff is able to generate more useful and timely reports for administrators, college presidents, the legislators, media, prospective students, and parents. Community College Data Warehouse Input Data Flow

Colleges Source data

System Office Data Collection Systems ETT

Data Transformation

Source data

CCSDW-V1.0 Phase 1 Data Warehouse

The development of the North Carolina Community College System Data Warehouse project will be implemented in phases. The first phase has been determined and is described in this document. The future phases are tentatively set but can change as the Data Warehouse is developed. Phase I – The objective of phase I is to re-engineer the current state level data systems into a Data Warehouse system, inclusive of the needed databases and generation of all current required standard reports. A major milestone of Phase I is the production via the Data Warehouse system of the Annual Statistical Report (The report can be found in the Publications section at web address: http://www.ncccs.cc.nc.us.). Movement of existing data on the ITS Mainframe System to the Data Warehouse will involve moving the processing, editing, and storage of the data

Printed on: 12/19/00

CCSDW-V1.0

3

NCCCS Data Warehousing Project
received from the colleges and ESC. The current level of ad hoc report development must be supported.

State-level information systems that will be implemented in Phase 1 are:
• • • • • • • • Curriculum Registration System Curriculum Institutional Class Reporting System Continuing Education Registration System Continuing Education Institutional Class Reporting System Curriculum Student Progress Information System Staff Information System Common Follow-up Information System Human Resource Development Student Information System

The following table identifies the data collections (files) currently received and processed by the Community Colleges System Office. All annual counts are based on the most recent report year available. Data Collection Curriculum Registration Continuing Education Registration Curriculum Institutional Class Report Continuing Education Institutional Class Report Staff Information Curriculum Student Progress Information System Common Follow-up System Human Resource Development Student Information System Number Elements 40 25 23 30 28 46 33 98 Table 2.1 Record Size 250 80 150 120 160 250 229 350 Annual Record Counts 215,000 565,860 283,180 70,380 16,800 215,000 2,512,400 26,500 Years to be in Data Warehouse 5 5 5 5 5 5 5 5

Future Phases Future phases are not part of this project. Information on these phases is provided to better enable an assessment of the scope of the long-term project. Future phases will add new data elements to Curriculum Student Records. This will involve the collection of additional data on Curriculum Students from the community colleges, the UNC System, and other agencies. Some of these data include student grade reports from the community colleges and first year GPA from the UNC System.

Printed on: 12/19/00

CCSDW-V1.0

4

NCCCS Data Warehousing Project
State-level Information systems affected during this phase are:
• • • • • High School Graduate Report Developmental Education Follow-up Tracking Literacy Education Information System General Education Development Information System Student Financial Aid data

The following data collections will be used in Phase II of the project. Student Financial Aid Data System, Student Grade Information System, and UNC Transfer Data are under development. All numbers are estimates.

Data Collection Literacy Education Information System General Education Development Student Information Student Financial Aid Data System Student Grade Information System UNC Transfer Data

Number Elements 66 15 12 10 10 Table 2.2

Record Size 2000 80 80 80 80

Annual Record Counts 244,765 49,000 65,000 4,300,000 43,000

Years to be in Data Warehouse 5 5 5 5 5

Additional future work will include: Adding new data elements to Continuing Education Records and Course Records. This involves the collection of additional data on Continuing Education Students, the development of standard data definitions for these new data elements, and the collection of new data on the courses offered by the North Carolina Community College System. Adding new data elements to Staff Records. This involves the collection of additional data on the faculty and staff employed by the community colleges. Adding Financial Data to the Data Warehouse. This involves the definition and development of cost data elements. These elements could be used for additional analysis.

Printed on: 12/19/00

CCSDW-V1.0

5

NCCCS Data Warehousing Project
2.2 Project Management Structure

The phase I project will be managed at various levels by appropriate teams as the diagram below shows.
Sponsor: Dr. Brenda Rogers Sponsor: Dr. Brenda Rogers Data Warehouse Project Management Team Information Systems For the Future Steering Committee Keith Brown (Chair), Bob Blackmun, Steve Ijames , Janyth Fredrickson, Scott Ralls, Chuck Barham, Kim Langdon, Audrey Bailey, Clay Hines Provides overall guidance to the project, approves funding, approves contracts, resolves issues, approves deliverable closures, approves project closure Information Needs Committee Develops the Annual Reporting Plan

Quality Assurance Steve Ijames, Arthur Hohnsbehn, Ken Whichard, Gloria Jones, independent QA vendor Evaluates SOWs, sets priorities, manages scope, approves changes, issue escalations, assesses deliverables status, quality assurance Campus/External Users Needs DW Project Coordination Independent Contractor College representatives, General Assembly, UNC-GA, DPI, ESC Input on client needs, user testing

DW Development Team State Level Systems Team Customer Support Team Vendor Team Daily project operations, issue identification, status reporting

IT Infrastructure Hardware vendor Systems Software vendor RDBMS vendor Data Extraction/migration vendor Data Center Data Communications

DW Operations State Level Systems Team Customer Support Team Daily operations, technical support, help desk, training, and documentation

Figure 2.1 Information Systems for the Future Steering Committee: The Steering Committee is an established committee that oversees the overall administrative information systems development, of which, the Data Warehouse project is a part. The Steering Committee's Mission is to: Define and communicate the case for improving the administrative systems for the Community College System; Establish a vision that ties together the system's academic needs and aspirations with a proposed financial, student and business systems architecture; Develop a set of planning principles that will help define the scope and character of the project as well as provide the means to evaluate its outcomes;

Printed on: 12/19/00

CCSDW-V1.0

6

NCCCS Data Warehousing Project
Establish, through an assessment of various tradeoffs, the high-level scope, boundaries and priorities of the project(s) Data Warehouse Project Management Team: The Project Management Team (PMT) is responsible for the general oversight of the project including setting the project scope, approval of funding prior to submission to the agency leadership, contract approval prior to submission to the SBCC, issue resolution, closure on all specified deliverables, and final closure of the project. The other project management teams report to the PMT through the project coordinator. The project coordinator serves as staff to the PMT and reports to the Director of IS. The PMT reports to the Information System For the Future Steering Committee. Information Needs Committee: This is an CCSDW Project independent committee responsible for the development of the CCSO Annual Reporting Plan. The reporting plan defines all data reports required of the colleges to the CCSO and all the required information reports generated by the CCSO. Quality Assurance : This team manages the quality assurance processes involved in the project, sets priorities for project, manages issue resolution, and approves change orders. DW Project Coordinator: This contractor works under the direction of the CCSDW Project Management Team, reports to the CCSO IS Director, and is independent of the Data Warehouse project vendor. This position coordinates the many tasks, committees, vendors, and people associated with the project Campus/External Users Needs : A process, managed by the project coordinator, to assure that the information needs of the external user constituencies are met. DW Development Team: The team responsible for the development and implementation of the CCSDW Project. The team takes its direction from the DW Project Management Team and reports information through the project coordinator to the Quality Assurance Team. The team is composed of members from several CCSO IS teams and the vendor staff. The team leader is the Associate Director of IS for State Level Systems. DW Operations : This team is responsible for the ongoing operation of the Data Warehouse, for training users, developing and maintaining documentation, and providing technical support. The team will become responsible for portions of the project as they complete deliverables closeout processing. This team will be involved in knowledge transfer processes throughout the project.

Printed on: 12/19/00

CCSDW-V1.0

7

NCCCS Data Warehousing Project
2.3 Data Warehouse Architecture

The following drawing depicts the major architectural elements

Figure 2.2
The overall design of the Data Warehouse must meet the state approved technical architecture as described in http://www.state.nc.us/IRM. Special consideration should be given to Chapter 4 (Data Architecture), Chapter 2 (3-tier, n-tier applications), and Chapter 8 (Data Warehouse). It is required that the application be Web-enabled. The Data Warehouse must adhere to the federated data standards established by the IRMC. The federated data system is a part of the IRM technical architecture. An overview can be found at the following URL: http://www.federated.its.state.nc.us.

2.4

Scope
CCSDW-V1.0 Phase I Project
Feasibility Study Requirements Definition Design Development Testing Implementation Project Closure

Completed

Phase I Project Figure 2.3

The scope of Phase I of the Data Warehouse project is to provide NCCCS with the completion of a design, development, implementation, and transfer of knowledge necessary to achieve the following objectives: 1. Implement the NCCCS Data Warehouse project by migrating the existing student, class, and college staff data into an appropriately designed Data Warehouse system; and, producing the existing set of standard reports off the system.

Printed on: 12/19/00

CCSDW-V1.0

8

NCCCS Data Warehousing Project
2. Design and implement the system to enable effective use by three classes of users: Executive, information skilled knowledge workers, and analyst/programmers. 3. Design and implement a Data Warehouse that provides fast, consistent response to end-user requests. A system that effectively supports interactive, ad hoc exploration, comparison and analysis of data, regardless of database size and complexity. 4. Design and implement a Data Warehouse architecture that is extensible and scalable to enable implementation of future phases. The data sets currently generated and transmitted by each college to the CCSO are to be included in the Phase I Data Warehouse, the only phase being proposed at this time. Refer to the data matrix in table 2.1 for a description of the scope of the data involved in Phase I. In this phase, the reports generated off the datasets included in table 2.1 and referenced at http://www.ncccs.cc.nc.us/reports/reppub99.pdf must be produced. The tools for extraction of complex multiple instance records must be proposed to extract and translate the current data, which will then be compiled into the Data Warehouse. Extraction, transformation, and transportation (ETT) tools must be consistent with middleware architectural requirements of the Information Resource Management Commission. The first phase will require the development of a multi-dimensional database (Cube Structure) where data are stored, viewed, and analyzed from multiple perspectives (or dimensions). Such a structure will contain the initial population of data as specified above in Table 2.1. These data are IBM VSAM datasets. Later phases will require that data be extracted from a variety of source files including OLTP systems on a variety of platforms. Thus, the architecture must include a robust ETT system capable of meeting future needs. Phase I must include a user-friendly tool set capable of enabling the three categories of users defined in (2) above to access information in the Data Warehouse. Each user level will require access tools, security administration, and information views specific to their needs and access rights. The technologies must enable on-line analytical processing (OLAP). End users must be able to manipulate and derive data for analysis purposes by applying analytical operations such as ratios, cumulative totals, trends and allocations across dimensions and across hierarchical levels. Transfer of knowledge and training of Community College Staff as well as researchers at the System Office must be concurrent with the development and implementation phase I of the project. Training requirements include both System Office staff and at least two users from each college. Cost per person for training must be included in the Statement of Work. Future phases will include more in-depth student, staff, and class data. It will also include the incorporation of new information datasets, such as financial and physical facilities datasets. Future phases will provide the system with the ability to allow comparative analysis with data sets generated by other systems such as the Employment Security Commission, The University of North Carolina System, and other information sources. It is anticipated that a four-year development cycle will lead to full Data Warehouse implementation.

Printed on: 12/19/00

CCSDW-V1.0

9

NCCCS Data Warehousing Project

The following figure illustrates the scope of Phase 1 of the CCSDW project.

User Query

Web Access

Ad- Hoc Reports

Standard Reports

User Interface Tools and Programming Languages

Vendor implemented or developed

CCSDW V1.0 Data Warehouse

Business Data Model Done

Data Warehouse Business Data Model (data elements, reporting requirements, etc)

ETT - (Transformation Logic Middleware)

Gap Analysis on Business Data Model and transformation logic to be performed by Vendor

Existing Data Sets
Figure 2.4

Vendor to implement ETT system

2.5

Deliverables

The Vendors will present a Statement of Work for achieving the major deliverables, which include but are not limited to: A. Data Warehouse Products and Services • • • • • • • • • • • • Data Warehouse design document Implementation of the Data Warehouse technical infrastructure Implementation of the extraction, transformation, and transportation system Successful testing of the test warehouse Populate production Data Warehouse Produce the set of standard reports Implement users query tool Implement the Executive Information System Implement web enabled reports Development of all documentation Completion of training plan Project closure

Printed on: 12/19/00

CCSDW-V1.0

10

NCCCS Data Warehousing Project
B. An overview of the company to include sufficient information for the CCSO to assess the company's solution performance history, including performance history of the proposed team (also subcontractors), tools, methodologies, etc. for similar projects. C. A description of the vendor's project management approach.

2.6

Developing a Statement of Work Cost

Vendors may submit multiple Statements of Work representing different solution proposals. 2.6.1 Cost Model Only Vendors from the current approved convenience contract number 41-SIPS 028 will be considered. The contract is referenced at http://www.state.nc.us/SIPS/services/contract/contract.htm. The cost model is based on the assumption that, at this point, vendors cannot accurately determine all costs since the final specifications and design elements have not been determined. Vendors must provide firm costing for the portion of the project involving the completion of the warehouse design. Vendors must provide a professional best estimate of the cost for the development, testing, and implementation portion of the project. It is intended that the selected vendor, after completing the Data Warehouse design, will be employed to complete the phase 1 project. The CCSO reserves the right to terminate the contract with the vendor for the completion of the project if the vendor has failed to perform satisfactorily or the vendors' cost for the completion for the project is considered excessive. After the final design has been completed, the vendor will be required to submit a cost for the remaining project. The CCSO reserves the right to submit the final design specifications as a Scope Statement to the vendors on the IRM contract. 2.6.2 Software and Other Requirements 1. Each Statement of Work must include one of the following vendor database management systems: A. B. ORACLE Sybase

2. Each Statement of Work must include one of the following vendors’ ETT products for data extraction, management and loading. A. B. C. A. B. C. Informatica Platinum SAS/Internet BRIO Cognos Business Objects CCSDW-V1.0 11

3. Each Statement of Work must include one of the following vendors for reporting (OLAP).

Printed on: 12/19/00

NCCCS Data Warehousing Project
D. SAS

Include any additional software products deemed needed for a successful implementation. 4. In addition, the successful vendor must have demonstrated successful experience in the selected OLAP. 5. The successful vendor must have an industry standard project management methodology in place and must demonstrate that it is used. 6. The successful vendor must propose a data warehousing implementation methodology consistent with the project scope and requirements. 7. The successful vendor must have a documented knowledge transfer procedure in place. Using a matrix similar to that below, vendors must provide five-year cost projections for all software products specified. Costs should be projected for 150 Seats or 50 concurrent user licenses, whichever is offered by the provider. Costs must include initial licensing and annual maintenance where applicable.

Licensed Software Cost Elements Item 1 Item 2 Etc.

19992000

20002001

20012002

20022003

20032004

Total Cost

2.6.3 Hardware Part of the CCSDW V1.0 project is to select and implement the hardware on which the Data Warehouse will run. Three (3) possible sources have been identified for the hardware. • • • Sun /Solaris to be owned and managed by the System Office IBM RS6000 to be owned and managed by the System Office State Information Processing Services (ITS) E10000 Time Share System (SIPS rates can be found on their web page, http://www.osc.state.nc.us/sips/, in the "billing rates" section.

For each Statement of Work submitted, vendors must propose utilization of at least one of these platforms, to including a version that uses the SIPS timeshare system alternative, detailing the system specifications. For example, if a vendor proposes a CCSO owned IBM RS6000 solution, then they must also provide a version of the solution that utilizes the SIPS timeshare system. This is to enable the CCSO to determine the best hardware solution comparing an in-house data center to an outsourced data center solution. Using a cost matrix similar to that below, vendors must provide a 5-year cost estimate of supporting the proposed solution. This projection should include items such as hardware, operating system, maintenance, and personnel costs. For Statements of Work that will Printed on: 12/19/00 CCSDW-V1.0 12

NCCCS Data Warehousing Project
require the CCSO to purchase and operate the hardware, vendors must project the fulltime equivalent (FTE) staffing needed to adequately support the proposed hardware system. Hardware Cost Elements Hardware(specify) O/S (specify) Personnel (specify) 2.6.4 Personnel Resources The development of the data-warehousing project demands the skills of a variety of professionals to perform the various tasks associated with a project of this size and complexity. Vendors must specify the resources needed to complete Phase I. These resources should be broken out into development and support roles. In addition, these should be broken out into vendor and CCSO positions. The CCSO will provide overall project coordination. Additionally, the CCSO will provide staff to the project in the areas of supervision, database administration, programming, training support, technical writing support. The CCSO staff support will be in a learning mode during the project development. They will be involved in all aspects of the development process and will assume responsibility for continued support and development of future phases. For example, the CCSO technical writing staff will oversee the documentation effort of the project and will contribute to the actual writing as much as possible. The CCSO training staff will oversee the development of the training plans and participate in the actual training a much as CCSO resources permit[. The CCSO database administrator and database specialist will participate in the development process as members of the development team. In addition to those supplied by the CCSO, vendor staff must be supplied to Phase 1 to enable a successful project. It is anticipated that the vendor team will provide personnel resources to fulfill the responsibilities of vendor team leadership, technical leadership, Data Warehouse architectural expertise, application tool technical expertise, database expertise, training expertise, and technical writing expertise. The NCCCS State Level Information Service team will support and maintain the solution after Phase 1 implementation. The current NCCCS IS Data Warehouse support team consists of a dedicated database analyst and a database programmer specialist and nondedicated programming, training, documentation, and supervisory support. Vendors must recommend changes or additions to the staff where necessary to support the system. 2.6.5 Training Training must be addressed at a minimum of three tiers: the system level for the CCSO IS Data Warehouse support staff, statewide for the skilled information knowledge user, and statewide for the executive user. CCSO IS Data Warehouse Support Staff 19992000 20002001 20012002 20022003 20032004 Total Cost

Printed on: 12/19/00

CCSDW-V1.0

13

NCCCS Data Warehousing Project
The Data Warehouse Staff will need training in both System Administration and Data Administration to be able to provide primary support and maintenance of the selected tools. Costs should be based on 10 Office Staff. Skilled Knowledge Worker There needs to be training for the two (2) identified user staff at each college and twenty (20) System Office staff that form the skilled knowledge worker community. These persons must be taught to utilize the Data Warehouse and tools for data retrieval and analysis. Cost projections for the training should include variables such as the number of sessions, number of people, and location of the classes. Due to the number to be trained, all classes should be held at in-state locations. Executive Training for the executive user must be in a form that is deliverable on an as-need, selfdirected, interactive basis, e.g., computer-based instruction, Internet-based instruction.

Training Costs

19992000

20002001

20012002

20022003

20032004

Total Fiveyear Cost

CCSO Support Staff Training Skilled Knowledge Worker Training Executive Training

2.6.6

Cost Summary The total cost of the complete Data Warehouse project is to be broken out between direct costs and vendor cost. Direct costs are those which are paid directly by the state. It is expected, for example, that the state will buy (or lease or purchase on a usage basis) the appropriate hardware and software directly from the respective vendors. Costs must include all costs for Phase 1 as described in this document. This is a turnkey Statement of Work. Statements of Work must use a matrix similar to that below to show an aggregate, fiveyear, total cost projection broken out by analysis and design and by development and implementation. Statements of Work must use a matrix similar to that below to show an aggregate, fiveyear cost projection broken out by development and support.

Printed on: 12/19/00

CCSDW-V1.0

14

NCCCS Data Warehousing Project

Sample Complete a matrix for Direct Costs and one for Vendor Costs Cost Elements 19992000 20002001 20012002 20022003 20032004 Total Cost

I.

Analysis and Design (Firm Cost) A. Staff Position 1 Position 2 Etc. B. Hardware (specify) C. Software (specify) D. Other (specify)

2. Development and Implementation (Professional Estimate of Cost) A. Staff Position 1 Position 2 Etc. B. Hardware (specify) C. Software (specify) D. Other (specify)

2.7

Data Security

The records in the Data Warehouse will contain personal and confidential information related to community college students and staff. The vendor must propose the systems necessary to protect the data in accordance with all applicable federal, state, and local laws, including the Family Education and Privacy Rights Act (FEPRA) which can be found at the Web location: (http://www.edlaw.net/public/ferpalaw.htm). Data security must be enabled at various levels and for various users. Some examples: • Database programmers and CCSO researchers would have access to data at the detail record level.

Printed on: 12/19/00

CCSDW-V1.0

15

NCCCS Data Warehousing Project
• • • A user executing the executive access system would have access to no individual records and might be restricted as to the number of accessible data elements. A college research analyst would have access to the detail records for their college, but would not have access to any detail records of other colleges. The general public accessing via the Internet would have access only to summary data in which there is no possibility for individual information to be identified or inferred.

The vendor must present to the NCCCS signed non-disclosure agreements for all employees and contractors assigned to work on project. It is up to the vendor to select and install the tools and provide the training necessary to ensure that the NCCCS is able to maintain the required level of security.

2.8

Evaluative Criteria
a) Methodology and logic of proposed approach b) Project Management approach and responsibilities c) Vendor performance history d) Vendor's history of successfully implementing Data Warehouse projects e) Appropriateness of Statement of Work to solving the Data Warehouse business requirements of the CCSO f) Cost/benefit analysis of the proposed solution (including total life cycle cost/benefits)

3. Terms and Conditions
3.1 Ownership and Confidentiality

The Community Colleges System Office of the State of North Carolina will retain ownership of all software and documentation associated with this project. All project materials, all data and all queries are the property of the State of North Carolina and must be kept confidential, or returned to the CCSO, or destroyed.

3.2

Estimate: Time and Cost

The Statement of Work cost portion must be in two parts, one for completion of the Design process and one for the Development and Implementation process. For the Design portion of CCSDW V1.0, it is required that a firm cost will be provided by the vendor. For the Development and Implementation portion of the project, an estimate should be given for planning purposes only. The target date for project start is December 1, 1999. The target delivery date for project closure is December 1, 2000. It has not yet been determined where the vendor work will be performed. It may be performed at a CCSO site or the vendor site or it might be split between the vendors site and the CCSO site. Since the CCSO Information Services staff will be involved in all aspects of the project, close coordination with the CCSO will be required. Vendor Statements of Work should address both a CCSO-site and Vendor-site scenarios and detail any implications, such as cost differences.

Printed on: 12/19/00

CCSDW-V1.0

16

NCCCS Data Warehousing Project

3.3

Statement of Work Checklist

All Statements of Work must include: • • • • • • • • • Name of project (CCSDW V1.0) All information in “Cost Tables” Firm Cost for Design Process Planning Estimate for Development and Implementation Process Resumes of proposed project team Confirmation of Project Start Date Confirmation of Project End Date or proposed Project End Date All tasks and deliverables must be addressed in Statement of Work Clear demonstration of understanding of what is required to construct CCSDW V1.0.

3.4 Voluntary Vendor Conference
A conference to answer questions related to the project.

Date Time Location

November 9, 1999 2:00PM 5th Floor Conference Room - Caswell Building NC Community Colleges System Office 200 West Jones St. Raleigh, NC 27603 Steve Ijames, Director of Information Services or Arthur Hohnsbehn, Associate Director of IS 919/733-7051 Ijamess@ncccs.cc.nc.us

Contacts

3.5 Signatures:
Steve Ijames, Director of Information Services_________________________Date:_________ Charles Valrie, Asst. Director for Administrative Svcs.__________________Date :__________

Printed on: 12/19/00

CCSDW-V1.0

17

NCCCS Data Warehousing Project

Printed on: 12/19/00

CCSDW-V1.0

18

NCCCS Data Warehousing Project 4. Glossary
The definitions shown on the following pages describe the terms used in this publication. Reference should be made to the Education Guide Catalog for the North Carolina Community College System for more detailed information regarding curriculum programs (can be found at http://www.ncccs.cc.nc.us in the "publications" section). Ad-Hoc Query Any query that cannot be determined prior to the moment the query is issued. A query that consists of dynamically constructed SQL, which is usually constructed by desktop-resident query tools. Administrative Data In a Data Warehouse, the data that helps a warehouse administrator manage the warehouse. Examples of administrative data are user profiles and order history data. Aggregate Data Data that is the result of applying a process to combine data elements. Data that is taken collectively or in summary form. Associate Degree Programs Planned academic curriculum programs leading to an Associate in Arts (A.A.), Associate in Fine Arts (A.F.A.), Associate in Science (A.S.), Associate in General Education (A.G.), or an Associate in Applied Science (A.A.S) degree. Bi-directional Extracts The ability to extract, cleanse, and transfer data in two directions among different types of databases, including hierarchical, networked, and relational databases. Business Model A view of the business at any given point in time. The view can be from a process, data, event or resource perspective, and can be the past, present or future state of the business. Certificate Programs Curriculum programs designed to provide entry-level employment training. Certificate programs range from 12 to 18 semester-hour credits and can usually be completed within one semester by a full-time student. Classic Data Warehouse Development The process of building an enterprise business model, creating a system data model, defining and designing a Data Warehouse architecture, constructing the physical database, and lastly populating the warehouses database. Client/Server A distributed technology approach where the processing is divided by function. The server performs shared functions -- managing communications, providing database services, etc. The client performs individual user functions -- providing customized interfaces, performing screen to screen navigation, offering help functions, etc. Consumer An individual, group or application that accesses data/information in a Data Warehouse. Cube Structure (Multidimensional Databases) is thought of in row, columns, and depth. It is really data over time by several categories, aggregated into several groups. Cubing Storing data in a multi-dimensional Cube that has one cell for each possible combination of dimensions Curriculum Programs A wide variety of planned educational programs which range in length from one semester to two years. These programs lead to certificates, diplomas or associate degrees, depending on the nature of the curriculum. Curriculum programs include certificate, diploma, Associate in Applied Science, Associate in Arts, Associate in Fine Arts, Associate in Science and Associate in General Education programs.

Printed on: 12/19/00

CCSDW-V1.0

19

NCCCS Data Warehousing Project
Data Administration A task of Data Warehouse management that includes capacity planning, data purging, security maintenance, backup maintenance, and usage tracking. Data Cleansing The process of ensuring data accuracy, validity, and consistency. Data Dictionary A database about data and database structures. A catalog of all data elements, containing their names, structures, and information about their usage, a central location for metadata. Normally, data dictionaries are designed to store a limited set of available metadata, concentrating on the information relating to the data elements, databases, files and programs of implemented systems. Data Extraction The process of selecting and copying data from a data source. Data Management Controlling, protecting, and facilitating data access to provide information consumers with timely access to the data they need. The functions provided by a database management system. Data Mapping The process of assigning a source data element to a target data element. Data Marts A small subset or view of a Data Warehouse. A data mart is a focused slice of a larger Data Warehouse. Data Migration The process of extracting data from a source, interfacing the data with the destination environment, and loading the data to its destination. Data Mining Data mining is the application of artificial intelligence (AI) techniques (neural networks, fuzzy logic, genetic algorithms, etc.) to large quantities of data, to discover hidden trends, patterns, and relationships. Knowledge discovery applies to the process of translating the results of data mining into actionable business plans (marketing effort redirection, customer profit center assessment, etc.). Data Model A logical map that represents the inherent properties of the data independent of software, hardware or machine performance considerations. The model shows data elements grouped into records, as well as the association around those records. Data Selection The process of choosing the data necessary to provide an accurate representation of the problem. Data Transformation Creating “information” from data. This includes decoding production data and merging of records from multiple DBMS formats. It is also known as data scrubbing or data cleansing. Data Warehouse An implementation of an informational database used to store sharable data sourced from an operational database-of-record. Typically, a subject database allows users to tap into a company's vast store of operational data to track, respond to business trends, and facilitate forecasting and planning efforts. Diploma Programs Programs designed to provide entry-level employment training and are offered at all colleges. Diploma programs range from 36 to 48 semester-hour credits and can usually be completed by a full-time student within two semesters and one summer session. Associate degree level courses within a diploma program may also be applied toward an Associate in Applied Science degree. Executive Information Tools programmed to provide canned reports or systems (EIS) briefing books to top-level executives. They offer strong reporting and drill-down capabilities. Currently, these tools enable ad hoc querying against a multidimensional database, and most offer analytical applications along functional lines such as enrollments or financial analysis. Printed on: 12/19/00 CCSDW-V1.0 20

NCCCS Data Warehousing Project

Extension Programs are comprised of non-credit courses such as fire service, law enforcement and emergency medical training, community service, business and industry and adult basic skills education. ETT (Extraction, Transform, and Transportation) Software used to extract data, transform to desired format, and move to output dataset. Faculty includes staff whose primary responsibilities are instructional and involve guiding/directing the learning experiences of pupils in an instructional environment. FERPA Family Education and Privacy Rights Act. Federal law that protects student’s right to privacy and holds institutions accountable for personal and confidential material. Full-time equivalent curriculum annual budget (FTE) is based on class membership for 16 hours of class, shop, or laboratory per week for 32 weeks, the number of weeks in the Fail and Spring semesters, 1997-98. To determine the annual FTE, the total membership hours reported for the year are divided by 512. Full-time equivalent extension annual budget (FTE) is computed by dividing the total membership hours reported by 608. The reporting periods used for computing the extension budget FTE were Spring quarter 1996-97, Summer session 1997-98, and Fall semester 199798. Integrated Tool Set Tools that are used to generate code for all tiers of the Data Warehouse. Multidimensional Databases A powerful database that lets users analyze large database amounts of data. An MDBS captures and presents data as arrays that can be arranged in multiple dimensions. Metadata or Meta Data Metadata are data about data. Examples of metadata include data element descriptions, data type descriptions, attribute/property descriptions, range/domain descriptions, and process/method descriptions. The repository environment encompasses all corporate metadata resources: database catalogs, data dictionaries, and navigation services. Metadata includes things like the name, length, valid values, and description of a data element. Metadata are stored in a data dictionary and repository. It insulates the Data Warehouse from changes in the schema of operational systems. Normalization The process of reducing a complex data structure into its simplest, most stable structure. In general, the process entails the removal of redundant attributes, keys, and relationships from a conceptual data model. OLAP On-Line Analytical Processing. A systematic tool which allows users to extract multidimensional information from large databases which can be used prospectively and or retrospectively in business analysis. OLAP server technology is the key to high performance analytical use of large databases. Its added intelligence about the structure and organization of the data, as compared with flat, detailed relational tables, makes an OLAP server more responsive to end user requests while also eliminating SQL-style queries. An OLAP server may physically stage the processed multi-dimensional information to deliver consistent and rapid response times to end users, or it may populate its data structures in real-time from relational or other databases, or it may offer a choice of both. OLTP On-Line Transaction Processing. A type of computer processing in which the computer responds immediately to user requests. Each request is considered is a transaction.

Printed on: 12/19/00

CCSDW-V1.0

21

NCCCS Data Warehousing Project
Test Data Testing of models using test data is an essential step of the selection process. Test data is used to evaluate the results of various methods in order to select the best performing solution (best descriptive model or optimal predictive capabilities). Scalability The ability to scale to support larger or smaller volumes of data and more or fewer users. The ability to increase or decrease size or capability in cost effective increments with minimal impact on the unit cost of business and the procurement of additional services. VSAM Virtual Sequential Access Method - a data storage system developed by IBM, Corp. Web Enabled A program that can be accessed by users on an internet or intranet

Printed on: 12/19/00

CCSDW-V1.0

22