This paper was presented at CUMREC '99, The College and University Information Services Conference. It is the intellectual property of the author(s). Permission to print out copies of this paper is granted provided that the copies are not made or distributed for commercial advantage and that the title and authors of the paper appear on the copies. To copy or disseminate otherwise, or to republish in any form, print or electronic, requires written permission from the authors.

The Power of Multidimensional Analysis (OLAP) in Higher Education Enterprise Reporting Strategies

Susan M. Grotevant
Director, Information Management Systems
University of Minnesota
Minneapolis, MN

David Foth
Cognos, Corporation
Schaumburg, Illinois

Institutional Information

The University of Minnesota, with its four campuses, is one of the most comprehensive universities in the country and ranks among the top 20 universities in the United States. It is both the state land-grant university, with a strong tradition of education and public service, and a major research institution, with scholars of national and international reputation. The Twin Cities campus, the largest of the four campuses, is located in the heart of a major metropolitan area and is comprised of 19 colleges offering 157 bachelor�s, 202 master�s, 5 professional and 116 doctoral degrees. Other important parts of the University include the Supercomputer Institute in Minneapolis, and Agricultural Experiment Stations at Rosemount, Crookston, Grand Rapids, Morris, Lamberton, and Waseca. Through the Minnesota Extension Service, the University is present in each of Minnesota�s 87 counties.

The university has set a goal of becoming one of the top public institutions in the country. The 1993 Gourman Report ranked the University�s undergraduate program seventh among all U.S. public universities and 22nd among all U.S. public and private universities. Its graduate programs ranked sixth among U.S. public universities and 14th among all U.S. public and private universities. Six undergraduate programs and eight graduate programs ranked number one, and all six of the health sciences professional schools on the Twin Cities campus were ranked in the top ten among U.S. public universities.

Corporate Information

Founded in 1969, Cognos is an international corporation with corporate headquarters in Ottawa, Canada, and U.S. sales headquarters in Burlington, Massachusetts, U.S.A. Cognos first introduced Business Intelligence tools in 1989 and with more than 900,000 users world-wide Cognos is a leading supplier of business intelligence software. Cognos� Business Intelligence tools let managers work in two complementary modes�exploring and reporting summarized data to identify trends that point to the underlying dynamics of the enterprise, and examining detailed transaction data. With Cognos tools, managers can seamlessly move between these two modes to maximize analysis and reporting capabilities. With PowerPlay for multidimensional analysis and reporting; Impromptu for querying transaction level details; Scenario for automated analysis of patterns and relationships in corporate data; and 4Thought for predictive modeling, everyone in the enterprise can use data in the way that best suits their distinct business requirements. The Cognos OLAP solution fully leverages existing environments and offers flexible implementation and maintenance. Cognos data access and analysis tools optimize OLAP technology across both client/server and Web environments to provide full enterprise scalability.

Abstract

This institutional-vendor presentation, based on the implementation of Cognos Business Intelligence tools at the University of Minnesota, will focus on the powerful business advantages available to colleges and universities leveraging investments in new Y2K compliant Enterprise Reporting and Planning Systems, data warehouses, data marts and networks with OLAP reporting tools. The conference presentation is designed to supplement the published paper with 1) an on-line demonstration of actual financial, human resource, and student administration internet reporting and analytic solutions developed at the University of Minnesota; 2) a demonstration of how Cognos has been linked and integrated with existing enterprise reporting applications; and 3) solutions implemented to meet institutional data access and security requirements for Cognos internet reports containing private data. The solutions being implemented at the University of Minnesota using Cognos will be of particular interest to institutions seeking reporting solutions to Peoplesoft Financial, Student and HR applications being implemented as well as institutions with seeking to enhance and extend the functionality of existing legacy systems.

The Power of Multidimensional Analysis (OLAP) in Higher Education Enterprise Reporting Strategies

All enterprises, whether public or private, destined to thrive in the coming millennium will, of necessity, be organizations that are constantly learning and changing, that maximize the use of knowledge and information, and deploy it faster and to better advantage than their competitors. Successful organizations will be characterized by their ability to use information better, learn faster, be dynamic rather than static, and foster innovation while managing risks. All of these outcomes will require a greater quality and quantity of Business Intelligence (BI) delivered via robust reporting systems than is currently available today in most organizations.

Access to critical management information lets administrators and managers make informed decisions that can significantly improve results. As organizations �flatten,� key decisions are being made at increasingly lower levels--which means that almost every employee needs quick, easy access to appropriate information. Organizations are also discovering that making information more easily accessible and easily understood to individuals throughout the enterprise can have profound changes in productivity and outcomes. The widespread growth in data warehousing is clear evidence of the efforts of an expanding number of organizations to leverage their investments in transactional data systems and to obtain both increased operational responsiveness and decision support at the managerial level.

Despite the apparent need, in many organizations management information maintained in data warehouses, data marts, enterprise resource planning systems, and a myriad of corporate databases is largely inaccessible for many employees. The advent of OLAP (online analytical processing), with its ability to easily consolidate and present summarized information from a wide variety of sources without the need to build SQL-type queries offers the promise of more effective and meaningful access to critical management data. However, in order to make this promise a reality, information systems must possess the following critical characteristics:

  1. Responsiveness. The system must have fast and predictable response times in order for users to have sufficient faith to make consistent use of it.
  2. Quality. Users must have confidence that the data being reported is correct, whether it is derived from single or multiple operational systems, or is combined with external data�
  3. Flexibility. Many operational reports are used in the same way each day and can be predefined to suit the needs of many users who have similar requirements. Often, however, organizations must also address the need to provide users with the flexibility to look at data in new ways, without relying on IT intervention.
  4. Ease-of-Use. In order to leverage the return on investment in data, end-user tools for accessing the data must be sufficiently easy to use. This way, users can satisfy most of their own information requirements without extensive training or IT assistance.
  5. Functionality. Given the breadth of business processes within complex organizations and the variety of user functions that must be supported, the reporting and decision support environment must offer a wide range of functionality, including:

Components of a successful Business Intelligence/Reporting strategy

Business Intelligence is increasingly recognized as a powerful strategy for leveraging information system investments to deliver the benefits promised. "BI can be described as a strategic approach to information management that delivers key information to decision-makers, in the form and at the time they need it, to take effective action." For the large number of organizations currently replacing legacy systems with one of the widely used ERP (Enterprise Resource and Planning) applications, finding tools that can read the metadata of these applications, rapidly creating specialized databases designed for reporting and analysis, and can integrate data from multiple sources is critical to user acceptance and support and the eventual return on investment expected by senior management, governing boards and the public. To the extent that an organization seeks a return on its investments in technology and business process change it is important to keep in mind that

Key strategic principles to consider in developing an Enterprise Business Intelligence Reporting include the following:

  1. Reports serve to bridge the gap between data and information and as such are a critical part of any systems implementation.
  2. The majority of users will not interface with ERP client-server panels. Rather, they will evaluate the success and value of a new system by the quality, intuitiveness, and accessibility of information required to successfully perform their job responsibilities.
  3. Significant lag time between the deployment of new systems and the availability of critical reports will be increasingly unacceptable.
  4. Given the magnitude and risks associated with implementing new ERP systems, the likelihood is high that reporting objectives and needs will not be assigned a high priority and/or achieved without an institutional advocate whose primary responsibility is management reporting.
  5. Different user roles require a range of flexible reporting products, with different interfaces and different levels of functionality, because they interact with the same data for very different purposes.
  6. Understanding your user base, how users differ, and how to support all classes of users from the most casual consumer of information to the most sophisticated power user is key to a successful Enterprise Reporting Strategy.
  7. The answer to meeting the information needs of both power users and casual users is the ability to provide client server based technology to information producers, linked to servers over high speed networks, and to provide robust, flexible and intuitive reports via the Intranet or Internet for the information consumers.
  8. Enterprise data is relational, multidimensional and unstructured, and it is stored in applications, data warehouses, and data marts. An Enterprise Business Intelligence/Reporting strategy must be able to leverage all of that data effectively.
  9. Knowledge is power but it is collective knowledge that matters most --an environment where data flows easily throughout every level of a company is what provides the competitive edge. A common denominator of organizations with successful Enterprise Reporting Strategies is providing users with easily accessible information not only related to their job responsibilities but from areas that may directly or indirectly impact their decision-making process.
  10. New, powerful reporting tools make it possible to deliver reports currently generated in Legacy system production environments from data warehouses and data marts. Choosing to leverage the power of these tools to deliver management information can result in significant reductions in cost throughout the life of a system.
  11. An Enterprise Reporting Strategy should provide both Web and client/server solutions. This combination not only increases user satisfaction but also has significant cost implications in most organizations.
  12. The degree to which the Internet is the preferred delivery mechanism for both scheduled operational reports and unscheduled ad hoc reports will have a significant effect on costs.
  13. Minimizing the staff resources required to search for data and by pushing information to the user�s desktop and acquiring the ability to publish data and/or permitting users to subscribe to data views will have large paybacks.
  14. Deliver the majority of information for casual users and knowledge workers through robust reporting systems and reserve the data warehouse for power users with high data and technical knowledge.

The Power of OLAP

OLAP systems store and access data as dimensions that represent business factors like time, functional activities, fund sources, organizational unit, and geographical location. This information is stored "multidimensionally"-- like a cube that can be viewed, turned, and shifted from any angle. Because information is presented in a business context rather than a database context, it is more intuitive to the majority of users. OLAP reports that take an analyze-then-query approach allow decision makers to access data the same way they identify and solve problems: by reviewing totals or summary information first, then looking at the underlying details.

The user's first view of the data in an OLAP report is a 'top-level' one that reveals patterns and trends at a glance. If users have identified issues or questions in summary-level information, OLAP reports enable them to interactively explore data from any angle, to any level of detail. There are three ways to explore data in a PowerPlay OLAP report to uncover what lies behind trends and totals:

This capability allows most users to answer their own questions, easily investigating data sets that would otherwise be inaccessible without expert IT or analytical assistance.

There are two stages to implementing Cognos’ OLAP reporting solution. The first step is to create OLAP cubes the multidimensional structures that house summary-level detail of organizational data. Typically, these cubes are created by technical staff and deployed to information analysts and report authors. The cubes are then used to build the browsable OLAP reports that are distributed to decision-makers and other information consumers.

The structure of a cube is defined in terms of dimensions and measures. Dimensions are hierarchical categories of information like time, organizational units, programs, and geography. Measures are the calculations that are used to track organizational activities and outcomes (i.e. revenues, expenses, costs etc.)

OLAP cubes generally contain only the dimensions and measures associated with a specific analysis. For example, financial data and human resources data would be housed in separate cubes. This ensures that cubes remain manageable, not just in terms of their size but also in terms of the clarity of the information they contain. With PowerPlay, cubes can be easily linked together so that users can move effortlessly from one cube to another, accessing information from all parts of the organization.

Determining what data should be contained in each cube and where that data is coming from is a critical exercise. IT or power users, working with information consumers to ensure their requirements are being met, are typically responsible for building the cube. In Cognos OLAP applications the PowerCube build process is automated. Administrators use the product's Transformer module to create a model with data from various data sources. The model specifies the location of the source data, as well as the structure of the data in the cube. Transformer then uses this model to create PowerCubes and populate them with data.

Because PowerCubes can be built on both UNIX and NT servers, Administrators can quickly construct cubes of any size. Populating PowerCube models is not constrained by where the data resides, so IT can harness both relational and non-relational data from multiple sources like databases, data warehouses, data marts, and spreadsheets. PowerPlay automatically creates time dimensions such as week; month, quarter, and year or users can incorporate custom time periods like grant years. PowerPlay can also create relative periods, such as year-to-date, quarter-to-date, prior month, and so on, which allows report authors to easily compare current results against previous time periods. For organizations whose fiscal year does not coincide with the calendar year, PowerPlay can include both periods in the same cube.

With PowerPlay, your organization's business rules can be captured and standardized in a cube and deployed to the entire organization. Complex mathematical expressions and calculations can also be used to create new categories of information from existing data. This capability ensures consistent results by ensuring everyone uses the same values for critical calculations. Because these mathematical expressions are built right into the cube, training time for report authors and consumers is greatly reduced.

PowerPlay also lets Administrators easily define and govern the data that each user is permitted to access or analyze. User access can be restricted by cube, by category, and by measure, or by any combination of these factors. Secure user classes allow one PowerCube to serve the diverse requirements of many users. In addition, because security is embedded in the cube, even mobile users, those that are not LAN- or WAN-connected, can receive cubes and still be restricted to the information they are authorized to see.


PowerCubes scale to manage very large data sets--up to 50 million consolidated rows of data, and as many as 500,000 categories--which is the size of a typical data mart. PowerPlay's unique cube technology enables cubes to be built very quickly yet remain highly compressed, making them easy to distribute and update. Automating cube building and deployment also allows cubes to be built on a daily basis, even for large data volumes. By scheduling data updates to run automatically, processing can be moved to off-peak hours, reducing network traffic and accommodating recurring update schedules. PowerCubes can be deployed across Windows clients, servers, and LANs. Cubes can also be deployed across the Web with PowerPlay Server Web Edition.


Once OLAP cubes are created and deployed, the process for authoring is extremely straightforward for all types of reports: status reports that reveal a snapshot of data; ad hoc reports that answer specific questions; and business performance management reports. By taking advantage of PowerPlay’s powerful report creation and formatting features, they can build and package OLAP reports to meet the needs of a wide variety of user roles.


With PowerPlay, deploying OLAP reports is extremely efficient, because the report is very small--it is only the combination of dimension filters and formatting. The data itself is stored in the OLAP cube, not the report. This significantly reduces the typical overhead of electronic reporting.

Cognos OLAP reports can be deployed across Windows clients, servers, LANs, and the Web--whichever platform best suits particular user roles. And PowerPlay’s Portfolio module allows report authors to package a collection of reports into an electronic briefing book with other OLE objects like Impromptu reports and graphics. In this way, decision-makers receive a consolidated package of information that is tailored to their particular needs.

Business and technical requirements resulting in the selection of CognoSuite (Impromptu, Powerplay and Scenario)

The minimum business requirements listed below represent both business drivers and potential advantages that fueled the search for new reporting technologies at the University of Minnesota. The goal was to acquire several tools that filled expected reporting functionality niches within the Enterprise Reporting Strategy and integrated with and enhanced existing reporting expertise. Once a "toolbox" of applications could be obtained, the reporting strategy called for matching application strengths with user technical and data knowledge, report functionality and business requirements, and current access to informational and operational systems. Previous experience suggested that achieving close matches in application strengths and business and user needs would yield the lowest cost solutions and highest user satisfaction. Purchase of an OLAP tool which would serve a wide range of reporting needs, integrate with existing plans to develop the new functionality included in SQL7, result in significant cost avoidance, and provide more timely delivery of reporting functionality and enhanced analytical capability, was a key objective of the university’s Business intelligence/Reporting strategy.

MINIMUM BUSINESS REQUIREMENTS:

  1. Eliminate or reduce the risk of significantly cost increases associated with meeting user-reporting needs associated with the Enterprise Project.

  2. Significantly reduce the risk of the university community not obtaining essential data and reports from the Peoplesoft student administration and human resources systems in a timely manner.
  3. Reduce the time and costs, associated with creating management and operational reports both in a production and data warehouse/data mart environment.
  4. Permit functional data experts to create and deploy management and operational reports thus increasing the number of existing staff able to design and deploy reports and eliminating or minimizing the need for the use of developers (hard to obtain, retain, and costly).
  5. Provide technological solutions that improve the productivity of the existing IMS staff thus reducing costs while delivering a broader and wider level of reporting functionality to meet the dramatically increasing user needs.
  6. Provide a range of interfaces and functionality well matched to user data and technological skills and knowledge.
  7. Improve the quality and analytical functionality incorporated in management reports delivered to users at the University of Minnesota.
  8. Achieve a significant cost reduction in the desktop hardware training and support needs of University departments by delivering the widely used information and analysis tools via the Internet.
  9. Enable reports previously, created in a production environment to be deployed in a data warehouse/data mart environment thus achieving a significant reduction in ongoing operating costs.
  10. Obtain a reporting application with the potential to replace some or all of the functionality currently available in the AS mainframe system in order to significantly reduce costs by phasing it out and replacing it with a client-server application.
  11. Provide a reporting tool, which would minimize the costs of upgrading and expanding the functionality of existing financial web reports.
  12. Provide a reporting tool/methodology that could be used by both central and decentralized units and campuses.
  13. Increase the number and quality of management reports available to faculty, staff, departments and colleges in order to drive improved utilization and accountability for resources necessary to provide an acceptable return on investment in the Peoplesoft Human Resource and Student Administration systems.
  14. Provide the level of vendor support necessary to permit pre-purchase tests of functionality and ensure successful deployment of initial reports within 90 days. Strong preference for locally based support sales and technical resources.
  15. Provide training resources for functional experts used to design and deploy reports.

TECHNOLOGICAL REQUIREMENTS:

  1. Able to take full advantage of Peoplesoft metadata including trees, select values, and effective dating.
  2. Able to leverage PS query to extract and deliver application data.
  3. Use Peoplesoft Cube Manager to automatically design and build multi-dimensional models and cubes.
  4. Able to utilize the Process Scheduler to automate the creation and distribution of multi-dimensional information throughout the enterprise.
  5. Ability to run on multiple operating platforms: i.e. Unix and Intel.
  6. OLAP functionality scalable to several million rows of data.
  7. Must provide both client server and web functionality using the same code.
  8. Must have drag and drop, point and click programmatic interfaces for building reports.
  9. Need for code generation by a developer should be minimal or non-existent.
  10. Must be OLE compatible for Microsoft Integration.
  11. The performance associated with the generation of multidimensional cubes should be optimized by the ability to compress data or by other comparable approaches.
  12. Preference given to products demonstrating other performance based features such as disk based vs. RAM based operation that leverage cube scale and optimize performance.
  13. Preference given to products with planned integration strategy with OLAP functionality in SQL 7.