Data Warehouse



 What is a Data Warehouse?
 Online Systems vs Data Warehouse
 Syracuse University Data Warehouse Characteristics
 Data Warehouse and MyReports subject areas

What is a Data Warehouse?

The data warehouse concept evolved from the growing competitive need to quickly analyze business information. The existing institutional systems were not set up to optimize data retrieval and the process was difficult, tedious, and subject to a variety of errors. Existing operational systems cannot meet this need because:

  • Lack of historical data in some operational systems.
  • Data required for analysis resides in different operational systems
  • Complexity of querying effective dated rows in PeopleSoft operational systems.

A data warehouse eliminates these problems by storing the current and historical data from disparate operational systems that business decision makers need in a single, consolidated system. This makes data readily accessible to the people who need it without interrupting on-line operational workloads.

Online Systems vs Data Warehouse

The difference between information in the University's Online systems and the Data Warehouse can be described as the difference between OPERATIONAL and REPORTING processes.


  • Student Records
  • Budget
  • Payroll
  • Financial Aid
  • Financials

These systems capture and manage the University's business transactions, but are not set up to optimize data retrieval.


Information in the Data Warehouse has been taken from the University's Operational Systems and moved into a REPORTING structure that allows clients to use desktop tools to:

  • Select Populations
  • Format Reports
  • Produce Labels
  • Perform Database Import and Export functions

Before the Data Warehouse was developed, these reporting functions were generated through a variety of processes such as:

  • Online selection and Report Generators
  • DataQuery and the Self-Submit Process
  • Standard Reports and Downloads

Unlike the Operational Systems which are being updated throughout the day, the Data Warehouse is a separate system that stores information by subject category for the sole purpose of efficient data retrieval.

Syracuse Data Warehouse Characteristics


There are no screens or processes which allow direct update of the data.


  • Student and Employee information is refreshed daily.
  • Employee information also has Calendar and Fiscal Year-End Tables.
  • Course catalog information is refreshed Weekly.
  • Admissions and Financial Aid will have information by reporting year.


In the Warehouse, data is presented in multiple formats, each of which provides the appropriate data for answering specific question.

  • The max effective dated row. These views of the data can be used to answer the question, "who is registered for next semester?".
  • The most current (sum) dated row. These views provide the max effective dated record less than or equal to the run date of the refresh process, and shows the current status.
  • The full view shows all past, current, and future rows for a record and can be used to look at the entire record, including history.

In the Warehouse, these three areas have been combined into three categories, combining information from these three major systems into a Student Profile. These categories are:

  • Students Biographic Information
  • Student Academic Information
  • Student Course Information

This format allows you to look at a student by a specific year/term or to view his/her entire student record, including all courses taken, majors, grades, etc.


  • Common items such as SU-ID (Syracuse University ID number) is available in all areas containing person information, making it easier to link together information from different subject areas.

  • Consistent code values and descriptions - In the Data Warehouse, the academic program (acad prog) for Arts & Sciences is "AS", the short description is always "A&S", and the long description is always "Arts & Sciences".

  • Clarified -- There is no definition of a full-time student available in the Online Operational systems. For the Data Warehouse, the Registrar's Office has defined all of the components that go into the designation full-time, half-time, less than half-time student. This field enrollment status is available for selection and reporting.

  • Expanded Code Definitions--Data is available in code form or short or long description form. Selection can be made on a code value - "give me all students with acad prog of "AS". The Report can then reflect the full description - "Arts & Sciences"

Subject Areas supported in MyReports and the Data Warehouse

We have a large variety of data in the data warehouse as well as queries/reports in MyReports reading various data warehouse subject areas and various production systems. Below is a table of the current combined list of Subject Areas supported in MyReports and the Data Warehouse.

Athletics - HRSA Student Records related
Card Access
Donor Relations
DSP - HRSA Human Resources related
Education Assessment System - NCATE administration
FAB Financials, Acct Payables
FAB Financials, Budget
FAB Financials, GL
FAB Financials, Grants
FAB Financials, Purchasing
FAB Financials, Treasury/Endowments
HRSA Admissions, GRAD
HRSA Admissions, LAW
HRSA Admissions, UC
HRSA Admissions, UGRD
HRSA Campus Community
HRSA Course/Class
HRSA Financial Aid
HRSA Graduate Awards
HRSA Human Resources
HRSA International / Sevis
HRSA Payroll
HRSA Recruit, GRAD
HRSA Recruit, LAW
HRSA Recruit, UC
HRSA Recruit, UGRD
HRSA Student Financials
HRSA Student Records
IT Project Request and Tracking System
Judicial Affairs - HRSA Student Records related
News and Publications - HRSA Human Resources and Student Records related
OIRA Reporting
RCM - Responsibility Center Management
Security, DW
Security, FAST
Security, Hyperion
Security, Identity Management
Security, PS FIN
Security, PS HRSA
Service Desk
Telecom / Pinnacle

| Data Warehouse Home | Enterprise Systems Home | ITS Home |