The STUDENT FINANCIAL AID Data Mart is made up of current and historical tables that contain student financial aid award information.
The Current tables are updated nightly. The first work day in February, the oldest award year is rolled off of the Current tables into the Historical tables and frozen. The table structures and relationships are the same for both the current and historical data.

The STUDENT INFORMATION Data Mart is made up of Current and Historical tables that contain student demographics and academics. Course and Section information tables are also contained within this Data Mart. Current student tables are updated nightly.
Data from the prior Summer/Fall semester(s) in the Current tables are rolled into Historical tables when registration begins for the current Summer/Fall semester(s). Data from the prior Spring semester in the Current tables are rolled into Historical tables when registration begins for the current Spring semester. The table structures are the same for both current and historical tables.

Future SCT

Employee Information Data Mart

Future SCT

Financial Information Data Mart

Future SCT

Prospective Student Data Mart

Welcome to The UNM Data Mall

Navigating the Data Marts

Tables, Relationships, and Column Definitions

Attribute Definitions for the Student Information Data Mart

Algorithms and Definition

Common File Definitions

Student Information Files

Financial Aid Files

Brio Query Training

Computer Based Training

EOD Registration Page. Basic Brio Query Building. Select: Professional Development - Basic Brio

Table Relationships and Column Definitions

The University of New Mexico’s Data Marts are comprised of current and historical tables that contain information about persons and their association with the University of New Mexico. A Data Mart represents a business area. For example Student admission, registration, and course information is contained within the Student Information Data Mart. The student's financial aid information is contained within the Student Financial Aid Data Mart. The links listed below will display a diagram of the "Current" tables within each Data Mart and the relationships between the tables. You may click on a table image within the diagram to view the columns in the table and the column's description.

The Data Marts are:

Student Information Data Mart

The Student Information Data Mart is made up of tables that contain current student demographics and courses as well as tables that contain historical student demographics and courses. The table relationships shown in the diagram are the same for both the current and historical data.

Student Financial Aid Data Mart

The Student Financial Aid Data Mart is made up of tables that contain current student financial aid information as well as tables that contain historical student financial aid information. The table relationships shown in the diagram are the same for both the current and historical data.

Prospective Student Data Mart

The Prospective Student Data Mart is made up of tables that contain information about prospective students, their informational interests, the type of contact that they made with the UNM and UNM's responses to their interests.

Future Data Marts

The purpose of the University of New Mexico's (UNM) Data Warehouse is to provide a common database containing pertinent student, financial, and employee data for administrative reporting.

Release Dates

The purpose of the University of New Mexico's (UNM) Data Warehouse is to provide a common database containing pertinent student, financial, and employee data for administrative reporting. Therefore, any additions or modifications made to the Data Warehouse will be moved into production on scheduled release dates. We at CIRT hope to provide a reliable, stable environment for the University of New Mexico's reporting needs.

About the University of New Mexico's In-house Developed Data Warehouse

The purpose of the University of New Mexico's (UNM) Data Warehouse is to provide a common database containing pertinent student, financial, and employee data for operational and administrative reporting, as well as executive decision support.

The data warehouse :

Is comprised of several different "Data Marts" (business areas that store related operational data i.e. Admissions information, Student information, Financial Aid information)
Streamlines data access for reporting purposes by reducing the number of versions of databases and platforms traversed for data extraction and reporting
Reducec reporting discrepancies caused by timing issues in departmental data extraction, data update, and report generation
Standardizes business rules, data definitions, and data usage between departments through single database storage of data and data definitions
Well eventually contain tables of data frozen at periodic intervals for statistical and tactical reporting
Will store approximately 10 years worth of data.

The Current Data Warehouse is now used by 80 people. The anticipated user group will grow from 80 to 1000 or more people. The demand for data access is high.

Banner Financial, Student, and Financial Aid Applications, and their corresponding Data Marts, were purchased June 2003 as a result of the recomendations of the Student Systems Re-engineering Project. By July 2004 the Banner Financial data marts will be implemented. The existing UNM Data Warehouse subject areas will be replaced by their corresponding data marts as each subject area is implemented.

About the Data Mall

The purpose of the University of New Mexico's (UNM) Data Mall is to provide a common Web-accessible area containing documentation. The documentation seeks to provide:

Information about the University of New Mexico's Data Warehouse
- The Data Marts
  - the underlying tables and relationships between the tables
  - the columns within the tables
- Query tools
- Requirements for accessing the Data Warehouse
Access to various reports
Links to the Online Transaction System screen documentation
Access to common file definitions for use with reporting tools

* Send questions, comments, recommendations to Student Information Systems (SIS).

The Data Warehouse Staff

Title

No. Resources

Full/Part Time

Responsibilities

Manager 1 Part-time

Oversee the Data Warehouse project

Coordinate priorities with the Steering Committee

Senior Programmer Analyst 1 Full-time

Design, Develop, Test, and Implement the Data Warehouse

Design, Develop, Test and Implement Training and Documentation for the users

Develop Documention for the usage of the Database Design tools

Train, Coordinate and Provide assistance to programmers from the various business areas through the Data Mart Design and tool usage

Programmer Analyst 1 Part-time

Create canned and sample queries

Provide assistance to users who have query questions

Install query tools on the Personal Computers (PCs) of the user group as needed

Provide brief introduction to the query tools after installation on the users PC

Query Tools

Data Warehouse access using Microsoft Access/ODBC Drivers:

General Setup and Download Instructions
- Net8/ODBC/Database Download Overview
"How To" documentation for Establishing a System Data Source (System DSN) using Microsoft ODBC for Oracle
- ODBC driver for Windows 95/NT
- ODBC driver for Windows 98/ME/2000
"How To" documentation for Refreshing Database Links
- Refresh Database Link
Download Microsoft Access Databases
- Student Information
- Operational Student Services Information (IE. Parking Services)
- Student Financial Aid Information

Oracle Tools:

Discoverer licenses and access will be terminated with the upgrade to Oracle Database v. 9i December 2003.

Brio Intelligence Tools:

Authorized UNM staff and faculty may contact CIRT for availablity of Brio tools.

BRIO's Home Page.

Data Mart Refresh Cycles

Student Information Data Mart
Current Student Information	Student information for the current and previous terms are updated nightly. The student's records will reflect the student data as of last night's update. The data is not frozen.
Historical Student Information	The prior Academic Summer and Fall data roll into History once registration begins in April for Summer and Fall semesters of the incoming Academic Year. The prior Academic Spring data rolls into history once registration for the incoming Spring semester begins in November. The completed academic semesters are moved from the Current Student Information tables into the Historical Student Information tables and frozen.
Census Student Information	Not in place.
Financial Aid Data Mart
Current Financial Aid Information	Financial Aid information for the incoming, current, and prior award year is refreshed . The student's records will reflect the student data as of last night's update. The data is not frozen.
Historical Financial Aid Information	The first work day in February the oldest award year is rolled off of the Current Financial Aid Information tables into the Historical Financial Aid Information tables and frozen.
Census Financial Aid Information	Not in place.

Gaining Access to Query Tools and the Data Warehouse

A user may gain access to the Data Warehouse if, the user has met the following requirements.

User Requirements:

Must be a current University of New Mexico Staff or Faculty Member
Possess a valid Oracle Logon ID/Account Name and Password
Have been granted Data Warehouse Query Privileges
- Have purchased a Discoverer User License from CIRT and
- Have Discoverer (Oracle's query tool) on their PC
or
- Have prior experience using Microsoft Access
- Have a copy of SQL*Net on their PC and
- Have downloaded Microsoft Access ODBC Driver

Request Oracle Logon ID:

Send the following information to Student Information Systems (SIS) requesting an Oracle Logon ID.

Manager's Full Name
Manager's Phone Number
User's Full Name
Office Phone Number
Employment Category– Faculty, Staff, Student, Other (Description required)
Department Name
Organization Code
Campus Location - Albuquerque, Gallup, Los Alamos, Taos, Valencia
UNM NetID
Microsoft Access Experience (Yes/No)

Security

A user must be a current University of New Mexico staff or faculty member. The user must have requested and been assigned a valid Oracle Logon ID/Account Name, Password, and have been granted query privileges to the Oracle Data Warehouse. Once the user has the required elements, the user may either contact SIS about the departmental cost and availablity of a Discoverer License, or request a copy of SQL*Net for their PC and then download the Microsoft Access ODBC Driver.

The user will be granted query and viewing privileges within designated roles that limit their view of the tables and data within the Data Warehouse. This is to ensure that the data remains confidential and protected.

Data Warehouse Terminology

TERM	DEFINITION
Ad Hoc Reporting	Reporting concerned with a particular end or purpose used for specific or immediate problems or needs.
Algorithm	Step-by-step procedures for solving a problem or accomplishing some end.
Attribute	A characteristic of a table or column such as name, length, or type of data.
Column	A vertical arrangement of items in a table that are limited by the data type (i.e. Last_Name).
Data Mall	Web accessible area where users are able to gather different types of data, definitions, and/or reports.
Data Mart	A collection of data that is focused on a particular subject or department.
Data Warehouse	A collection of data designed to support management decision-making.
Decision Support System (DSS)	Software used to aid management decision-making, typically relying on a decision support database.
Derived Data	Data that has been calculated using one or more data sources.
Download	To transfer data from one computer to another storage device.
Drill-Down	Changing the view of the data to a greater level of detail to see how the information at the various levels is related.
Drill-Up	Changing the view of the data to a higher level of aggregation to see how the information at the various levels is related.
Entity	A person, place, or thing that an organization wishes to capture and store information.
Entity Relationship Diagram (ERD)	A model of an organization's view of it's data and its relationship.
Extract	Data that has been filtered and selected from a larger data source.
Field	An item of information within a record.
File	A collection of related items of information (fields and records) treated by a computer as a unit.
Filter	To reduce the number of rows or records returned by limiting the variables.
Foreign Key	A column in a table that identifies rows in a different table.
Metadata	Data about data. Metadata describes how the data is formatted.
On Line Analytical Processing (OLAP)	A category of software tools that provides analysis of data stored in a database.
On Line Transactional Processing (OLTP)	A type of computer processing in which the computer responds immediately to user requests.
One-to-Many Relationship	An entity from one table may be related to one or more entities on another table (i.e. A student may be registered for one or more classes)
Oracle Account Name	An identification code that is assigned to an individual granting access to designated areas of information within an Oracle Database. Also known as the Oracle Logon ID.
Oracle Logon ID	An identification code that is assigned to an individual granting access to designated areas of information within an Oracle Database. Also known as the Oracle Account Name.
Parameter	The term parameter is synonymous with argument, a value that is passed to a program to limit data retrieval.
Populate	To provide a table or database with data.
Primary Key	A field or column that must hold a unique value for each record in a table. It is used to sort and retrieve data.
Query Tool	Software that enables users to write queries to extract data from a database to create a report.
Record	A collection of related items of information about something or someone (i.e. a record from a file).
Row	A horizontal arrangement of related items in a table organized by their primary key.
Slice and Dice	Refers to the ability to combine and re-combine the dimensions to see different slices of the information.
Table	A systematic arrangement of data usually in rows and columns for ready reference.
Time Stamp	The date and/or time the data extract was created or modified.
UNM NetID	The Identification code assigned to the user by the University of New Mexico for access to the user's Email account.
Upload	To transfer data from one storage device to another computer.
View	A logical table based on a table or another view. A view is like a window through which data from one or more tables can be viewed.

Data Warehouse Information
Introduction to the Data Warehouse
Navigating the Data Marts
Security and Access
Downloads and Setup Information
Frequently Asked Questions
Training
View Metadata
Banner Data Marts
Reports and Queries
Course, Department, and Student Reports
Online Transaction System
IDMS Screen Documentation
Common File Definitions
Back to:	Data Mall

Title	No. Resources	Full/Part Time	Responsibilities
Manager	1	Part-time	Oversee the Data Warehouse project Coordinate priorities with the Steering Committee
Senior Programmer Analyst	1	Full-time	Design, Develop, Test, and Implement the Data Warehouse Design, Develop, Test and Implement Training and Documentation for the users Develop Documention for the usage of the Database Design tools Train, Coordinate and Provide assistance to programmers from the various business areas through the Data Mart Design and tool usage
Programmer Analyst	1	Part-time	Create canned and sample queries Provide assistance to users who have query questions Install query tools on the Personal Computers (PCs) of the user group as needed Provide brief introduction to the query tools after installation on the users PC