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.

OPERATIONAL PROCESS

• 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.

REPORTING PROCESS

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

REFRESHED--NOT UPDATED

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

POINT-IN-TIME

• 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 DIFFERENT FORMATS

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.

TRANSFORMED

• 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.