University of Minnesota Enterprise-Wide SPACE Management Project Summary
Navigating and Managing SPACE Data Enterprise-Wide Access through IDEA Data Warehouse
IntroductionThe University of Minnesota is home to over 80,000 student, faculty, and staff on a daily basis. Facility assets (valued in excess of $3 billion) are spread across 24 million square feet in hundreds of buildings on 5 major campuses and more than 20 research sites and experimental stations. Spatial information, identified as one of four key entities in a campus wide Data Modeling Study, is the common denominator in many of the University's asset and personnel management systems.Recently, the University deployed its new SPACE management system to provide accurate information about facilities. The project involved redesign of a legacy space management database, conversion of data for 60,000 rooms, incorporation of 1,300 drawings and campus maps, and development of a highly intuitive graphical navigation model for query and reporting. SPACE was designed as an enterprise-wide resource to allow all academic, administrative and operations groups to gain easy access to data about space. SPACE information is principally used for strategic planning but is also available to departments throughout the University to support a variety of other business planning efforts. This document provides a description of the SPACE system that has been developed for the University of Minnesota by the Jordani Consulting Group.
Project PhasesFull deployment of the new SPACE database system for the University of Minnesota is the final stage in a multi-phase project designed to make accurate up-to-date information about space available to a broad spectrum of potential users. Phases prior to deployment included the following activities:
Phase 1: Needs Analysis & Requirements DefinitionA comprehensive analysis of spatial data requirements and management procedures, and related business procedures was conducted. Input was obtained from academic, administrative and operations groups across the University. Existing programs and data were evaluated. Through a series of focus groups and facilitated discussion groups, both functional and technical requirements were developed for the design of the new SPACE system. Requirements were documented and presented to the University as the basis for soliciting proposals for a new space management system.
Phase 2: Systems SelectionSystem requirements were incorporated into a Request for Information (RFI) for a new SPACE Management system. A selection team comprised of Facilities Management personnel and other University staff was assembled. Four vendors were selected to present products from the group of vendors that responded to the University's RFI. Day-long presentations, using University data (database and drawings) formed the basis for selecting the new system. The following major software components were selected.
Phase 3: Detailed Design & Pilot ImplementationA pilot project was implemented to test the design strategy that had been developed. A detailed evaluation of the implementation tools was also performed. Strategies for a number of process issues including the conversion of 60,000 existing database records were finalized. Standards to guide the creation of approximately 1,300 floor plan drawings and campus maps were developed and tested. The initial version of both the Oracle Forms and ArcView navigation tools were implemented and useability testing was performed.A fully normalized relational data model was developed to convert existing data and to incorporate the drawings and graphical navigation requirements of the system. The Oracle data model employs database triggers and procedures to insure data quality compliant with University business rules. The data model incorporates NCES HEGIS coding schemes for classification of space. New requirements indicated the need to allow simple integration of data from other systems. The concept of user-defined "parochial" data supports the full capabilities of SPACE to be used in concert with other University systems such as Classroom Scheduling, Inventory Services, Human Resources, et al. The pilot project concluded with formal presentations to University staff and faculty and a determination to proceed as planned with full implementation.
Phase 4: Implementation, Training and DeploymentImplementation of the full production version included significant data conversion and creation. Procedures were developed to automatically convert space management data from the existing mainframe system into the new fully relational model. Production versions of navigation, maintenance and reporting software were written and tested.All software and data components were integrated. Extensive functional and performance tests were performed on all components of the system. Quality control was performed on all data, including the more than 1,300 drawings that had been developed. Data reconciliation and audits for accuracy were conducted for twenty-four million square feet of space system-wide. A number of processes were reengineered to facilitate compatibility with other University business activities. Job descriptions were developed or modified and organizational and reporting relationships were redefined. Full documentation was developed for every aspect of the system. Training was conducted for both system users as well as those who would be responsible for maintenance of the system.
Navigating and Managing SPACE DataThe SPACE system consists of Operational System components designed as an OLTP (on-line transaction processing) environment to support data maintenance and a Data Warehouse implementation to support broad-based access from the University at large.
Operational System Description Figure 1: SPACE Operational System Components The Operational System (see Figure 1) consists of several components maintained on equipment in the University's Facilities Management Department. The primary purpose of the operational system is to support accurate and timely maintenance of data about University facilities. It also supports access by users in the Facilities Management department and selected users in other departments. The major components of the Operational System include: Data: Spatial information maintained by FM or accessed from other University systems. Data includes tabular data about the buildings, floors and rooms along with campus maps and floor plan drawings. Data Maintenance: Systems and procedures that are designed to maintain tabular and graphic data. Navigation, Query and Reporting: Systems and procedures for graphical navigation and reporting on both tabular and graphic data. Information from the Operational System is uploaded to the University's IDEA Data Warehouse weekly where it is accessible for navigation, querying and reporting throughout the University.
Operational System: Data Component DescriptionThe data component of the operational system consists of tabular data and graphic data.Tabular data in SPACE is primarily used for strategic planning and programmatic projections, ad hoc reporting, and to support operational and administrative activities. The data is organized in a spatial hierarchical order by: (1) Campus, (2) Zone (for Operations and Maintenance) and / or Precinct per strategic and academic planning, (3) Building, (4) Floor, (5) Room, and (6) Room Detail. Most of the information about space is attached to the room and room detail records, but it is possible to capture information about an element at any level of the hierarchy. The room record stores all of the information that is valid for the entire room, i.e. square footage, capacity, ADA accessibility. The room detail record stores information on the shared activities of a space including multiple occupants (departments), NCES/HEGIS (National Council of Educational Statistics, Higher Education General Information Survey) Use and Function Codes, and grants. Information in the spatial hierarchy will identify the user(s) of a space by department, college and provost (Chancellor) / vice president (Vice Chancellor). Organizational information is linked to information in the spatial hierarchy at the room detail record. The monthly download from the University's financial system (CUFS) provides the record of organizational reporting relationships. Procedures have been implemented to automatically incorporate changes to the University's organizational hierarchy, including a process to resolve any conflicts that may exist as a result of those changes. These procedures are documented in the SPACE System Process Manual.
Drawing data has been developed for over 1,300 floor plans and campus maps for the Minneapolis, St. Paul, Duluth, Morris and Crookston campuses. The new University Center Rochester (UCR) campus map will be added as that information becomes available. Campus Maps illustrate major geographic and facility features. Roads, sidewalks and building outlines have been incorporated into the first release of the data. Plans for future releases include the incorporation of utility and other infrastructure data. Floor plans illustrate exterior and interior walls, window locations and door openings. Major structural, circulation elements (stairs, elevators) and mechanical shafts are also noted.
User-Defined and Enterprise-Wide DataSPACE also allows users to connect enterprise-wide data that is maintained in the Operational System with other user-defined data (also referred to as parochial data) that is maintained by others. Enterprise-wide data (information about space use and characteristics that is of value to a broad spectrum of the University) is the core of the system.User-defined data (information that departments maintain relative to their unique business requirements such as instructional activities, research projects, controlled substances, hazardous waste, etc.) that is of primary interest to individual departments for planning purposes can be analyzed together with enterprise-wide data, floor plan drawings and maps. Responsibility for collecting and updating enterprise-wide data resides with FM. User-defined data is, however, the sole responsibility of the group using this information. In order to connect user-defined and enterprise-wide data for analysis, the data sources must have a common key, such as a spatial reference (e.g., building or unique room number) that matches those used in SPACE. See the section, Operational System: Navigation, Query and Reporting Components Description, for a description of the benefits of using the SPACE System to analyze user-defined data.
Operational System: Data Maintenance Components DescriptionMaintenance of tabular data is accomplished through a set of Oracle Developer 2000 Edit Forms. The forms are designed for easy and intuitive access to the data. Many of the database rules have been embedded in the forms to insure data quality and consistency. Validation lists enforce quality control and are available as drop-down selection lists for data entry. Procedures have been implemented as database rules to prevent erroneous data modification. The use of the Edit Forms is documented in the SPACE System Edit Forms User Manual.Edit Forms for room detail information have been Web enabled to facilitate remote data collection. Departmental and collegiate space coordinators can use a Web form to identify space occupancy and use changes. A limited number of users are authorized to have changes recorded directly in the database. For others, change requests are captured in a separate table for review by FM Planning and Programming who validate the requests. Processes define the responsibility of each group, including the method for obtaining access to the data, the data that can be accessed by an individual, the security controls that have been implemented, and validation. Drawing data (floor plans and campus maps) is maintained through the use of AutoCAD. Standards that fully define the information structure of floor plan and campus map data have been developed to guide drawing creation and maintenance. Software (CAD QC) has been developed to check the compliance of drawing data with defined standards. Procedures identify the mechanisms for determining when and how drawings should be modified. Changes to floor plan drawings may cause updates to tabular data as a result of the CAD Import procedures that have been developed. Additions and/or deletions of rooms, as well as changes in room sizes (square footage) are detected by the CAD Import process, which automatically initiates related changes in the Oracle database.
With the SPACE Navigator, drawings are used as a method to access data. Users can "browse" from a state map, to a campus map, to detailed floor plans. Once open, the drawings can be queried for information from the associated portion of the database. For example, a campus map provides information about buildings and a floor plan provides information about rooms. Drawings can be color-coded to reflect various values in the database and used as part of reports. Drawings can also be annotated with any values from the database. When combined with user-defined data, the Navigator becomes a powerful tool with which administrators can analyze business data that has a spatial reference. For example, information in the FM Building Cost Report (BCR) can be joined with Twin Cites Campus space data for analysis purposes. A campus map can be color coded to reflect the costs of different utilities. Once connected to enterprise-wide information, BCR data can be queried to identify buildings and then the departments and functions in those buildings that have high energy costs. Information can be connected at any level. Equipment or telecommunications data can be linked to room level information to identify classroom and research spaces that include these requirements. Floor plan drawings can be used as a tool for navigating and reporting this data. All of the analysis capabilities of the system are available for user-defined data and are fully explained in the SPACE System Navigator User Manual. The Navigator includes a Local Project Scenario feature that lets users conduct "what if" scenarios for planning and design on their desktop without modifying the enterprise-wide data. If a user elects to save a project data set to their local hard drive, the data for all of the buildings that are active in the project (including all tabular data along with the relevant Campus Maps and Floor plans) are copied down to their system. Users can manipulate the data without modifying the enterprise wide data. All of the navigation and reporting capabilities are available for use with the local data set. The Navigator software includes a reporting component (referred to as Layouts in the user documentation) that produces color drawings, charts and reports, at precise scales including architectural/engineering size plots. Selections of rooms from Floor Plans and Tables can also be summarized in two standard Crystal Reports (Seagate) forms from within the Navigator.
Crystal Reports can also be used stand-alone, for ad hoc queries and standard reports. A suite of more than twenty standard reports have been developed and are documented in the SPACE System Standard Reports User Manual. Both simple and complex reports can be created with Crystal, incorporating graphics if desired.
Development, Test and Production EnvironmentsThe Operational System consists of separate environments (Oracle Table Spaces) designed to support three stages of activity in the evolution and life cycle of the SPACE system. The development environment is designed to allow programmer's to add new features and fix bugs in an environment that will not affect either the production software or data. When programmers believe they have fixed a bug, or that a new feature is stable, they move the application software to the testing environment for more comprehensive testing. The testing environment is designed to allow robust testing in an environment that is identical to production, without affecting the production software or data. When testing is complete, the application is moved to the production environment. A full discussion of the Software Testing and Release procedures are available in the SPACE System Process Manual.
Enterprise-Wide Access through IDEA Data Warehouse Figure 5: SPACE System Data Warehouse Components The data at the Data Warehouse is a "snapshot" of information from the SPACE system that is maintained by FM. The "snapshot" represents data from the Operational System from the end of the preceding week. Data is uploaded from the Operational System to the Data Warehouse each Sunday evening. Each time the data is refreshed, some of the data may be replaced or new data added. The data then remains static until the next refresh cycle. Data in the Data Warehouse is "read-only"-no maintenance is performed against this data.
IDEA Data Warehouse System DescriptionThe purpose of the Data Warehouse is to support enterprise-wide access to SPACE and other data. The data warehouse system consists of several components maintained on equipment in the University's IDEA Data Warehouse and a server that runs under their Novell File System.The major components of the Data Warehouse implementation of the SPACE System include: Data: The SPACE system information that is copied up to IDEA each week includes tabular data and drawings. Data is automatically moved from the Oracle environment in FM into a Sybase database at the Data Warehouse and a Novell file system that houses the drawings. Navigation, Query and Reporting: This component consists of the systems and procedures for graphical navigation and reporting on both tabular and graphic data.
Data Warehouse System: Data Component DescriptionThe data component of the operational system consists of tabular data and graphic data.Tabular SPACE data at the Data Warehouse consists of eleven tables containing space-related information. Four of the eleven tables contain data about Campus, Building, Floor and Room. Six of the eleven tables provide information to the SPACE Navigator about description of look up values, images and their location. Navigator uses these six tables along with the other four tables to provide graphical navigation of the SPACE system. The eleventh table is provided to assist in transition from the earlier space data legacy system, SPAM to the new SPACE database. It contains mapping of SPAM room numbers to SPACE room numbers. Tabular data is documented in the SPACE System Idea User Documentation, which is available at the IDEA Web site. The data warehouse is also the repository for other University information including financial, human resources and course offerings. This data, along with other user-defined data which may be stored at the Data Warehouse or on another user-defined server, can be connected to the SPACE database for analysis and reporting.
Data Warehouse System: Navigation, Query and Reporting Components DescriptionThe Navigator, a customized implementation of ArcView (ESRI, Redlands, CA), provides the graphical navigation and query interface that links the floor plan and campus map drawings with information in the Oracle database. The same SPACE Navigator software that operates against the Oracle data in the Operational System also works against the Sybase data at the Data Warehouse. Navigation and reporting functions using the Navigator are identical, as are the incorporation and analysis of user-defined data and local project scenarios.SPACE data at the Data Warehouse is available in a tabular form via the Web. Nine standard reports (filtered by Campus, Building or Class) are available for display, printing or download via the IDEA web site. Detail data can also be downloaded into several formats using custom query tools developed by the University Office of Information Technology (OIT). Information at the Data Warehouse is also accessible using standard query and connectivity tools.
|
Jordani Consulting Group 12 South 6th Street, Suite 914 Minneapolis, MN 55402 Phone 612 333-9222 Fax 612 333-9210 info@jordani.com Copyright © 2010 Jordani Consulting Group |
