Overview of On-Line Analytical Processing with UW-Madison’s Retention Data Views


Modern production data management systems (such as customer, order, and product records in a commercial industry, or student records in academia) do not lend themselves readily to computation involving large subsets of records for the purpose of statistical and trend analysis.  Rather, these production systems were designed for the consistent entry/updating of individual records.


To address this limitation of production systems, information managers have constructed “data warehouses.”  A data warehouse extracts vast amounts of related data from a production system (such as the implementation of the PeopleSoft Student Record system on the UW-Madison campus known as ISIS) and organizes it into sets of logically related files for end-user analysis.  Using desktop tools found in most common office software suites (such as Microsoft Office), policy makers/analysts can access and analyze the vast amounts of current and historical information found in these warehouse repositories. This type of data retrieval/analysis system is often referred to as an On-Line Analytical Processing (OLAP) system.


During the last several years, a campus-wide UW-Madison Student Data Needs Committee, led by Dr. Kathy Luker, constructed a set of data views in the UW-Madison data warehouse referred to as the "Retention Data Views" (RDV).  The RDV, designed to provide users with longitudinal student records, contains the vast majority of student record data for every student on the Madison campus in every semester since 1985, including every major, program, award, course, grade, placement and standardized test.  Dr. Steve Kosciuk, of the LEAD Center, has been and remains a key participant in the design and creation of the RDV resource.


Although the creation of the Retention Data Views has been a campus-wide, multi-year, expensive, and painstaking process, the effort needed to develop the tools and expertise that users need to link their desktop computers to the RDV (or any other data view, for that matter) is much simpler and relatively inexpensive. Indeed, most users already have the necessary software tools in the form of Microsoft Office. What they need is expertise with and knowledge of the software tools and the student record system. 


Vice-Chancellor Barrows engaged Steve Kosciuk, of the LEAD Center, to help meet this need by:

·        helping create and test the UW-Madison’s new “Retention Data Views” to the point that this new resource is ready for general use on campus;

·        piloting On-Line Analytical Processing (OLAP) interfaces with the Retention views (using Microsoft Office 2000) that

Ø synthesize longitudinal student records from the Data Views into “source queries” customized for the analytical needs of a given end-user; and

Ø employ OLAP  “hyper-cubes,” via the “Pivot Table” utility in Excel, that streamline users’ analysis of the source data; and

·        training a small group of staff from various campus offices/programs/units, who have a range of student records needs and prior experience with Office 2000, in the use of this new resource.


Currently, as pilot projects, Steve is developing OLAP interfaces for six different groups on campus including: the Academic Advancement Council (AAC), the Graduate School, Letters and Sciences Student Academic Affairs (L&S SAA), the Learning/Tutorial Center directors, as well as the Departments of Math and Chemistry.  The number of end-users of these cubes could total more than thirty.  So far, fully functioning cubes have been populated for the AAC, the Graduate School and the L&S SAA.  The Learning Center group is currently using the L&S SAA cube for exploratory purposes.


Two key features that have emerged from this pilot process are the paired phenomena of  “re-usable logic,” coupled with  “quick refresh.”  Namely, once a given cube is built the logic behind it can, to a great extent, be copied and/or easily modified to quickly add desired customizations/corrections for additional cubes or the original cube.  Click here to see a brief slideshow about OLAP interfaces with the Retention Data Views.


Below is a list of users that are currently pursuing OLAP interfaces as a tool to help assess student retention/success as it pertains to their school, course, or program.


Current OLAP User Groups

User Group

Primary User Name(s)

Business  Learning Center

Judy Cary

Chemistry Learning Center

Cathy Middlecamp

Physics Learning Center

Susan Nossal


Kirk Malnor

Chemistry Department

Gery Essenmacher

Math Tutorial

David Camacho

Math Department

Melania Adem


Linda McCarrol, Gene Kim


Ann Groves-Lloyd


Jeff Shokler


Christopher Lee, Mike Pflieger

L&S--Southeast Asian Student Services

Pao Thao


Dick Barrows

Grad School

Terry Millar, Gesele Durham

Residential Learning Communities

Ann Hasse-Kehl

L&S—Summer Collegiate Experience

Svetlana Karpe



Original Content Copyright ©2003 Board of Regents of the University of Wisconsin System. All rights reserved. Last updated on Thursday, January 30, 2003 4:01 PM CST.