Data Warehouse (DW)
Overview of Data Warehousing
The
University of Toledo's Data Warehouse is an environment that brings together
strategic data from multiple enterprise systems in order to facilitate ad-hoc
and cross-application reporting. Systems
in the Data Warehouse include the following SCT Plus systems or sub-systems; Finance, Financial Aid, Student
Records, Admissions, Student Accounts, and Housing. Also, as Banner modules are
implemented (like Human Resources), those modules will be added to our Data Warehouse environment.
The data in our database servers are generally copies of the enterprise data
reformatted to facilitate querying of the data. The data is typically
refreshed each night via batch jobs meaning the data is as of the end of the
previous business day. Data that is copied and completely refreshed each
night with no time element is general referred to as an Operation Data Store
(ODS). Data that contains a time element and can be referenced as of a point in
time, and includes transformations to derive common business logic is referred
to as a Data Warehouse.
Currently, in our database environment, all of our systems are Operational Data
Stores except for the University developed Institutional Analysis System (IAS).
The IAS is considered a Data Warehouse for student related information. See
http://institutional_research.utoledo.edu/ias.asp for additional information
about the system.
Operational Data Store (ODS)
A database designed for queries on
transactional data. An ODS is often an interim or staging area for a
data warehouse, bolt-ons, or other 3rd party applications, but differs
in that its contents are updated on a regular basis. An ODS is designed
for performance and numerous queries on small amounts of data such as an
account balance.
Banner ODS Architecture
(Click to View)
ODS versus DW at UT
•Our
only true Data Warehouse system is the in-house developed Institutional
Analysis System (IAS) for student and course related data. IAS is
derived from the SCT Plus Student ODS. All other database
information are Operational Data Stores. For our current legacy
SCT Plus systems, we have developed in-house extracts to MS SQL Server
databases. As Banner modules are implemented, we will install and
configure each module’s pre-delivered ODS in an Oracle database
environment.
Report Libraries
The data can
be accessed university-wide using common workstation tools such as Microsoft
Access or Crystal Reports. The key component of our strategy is what we term "Data Warehouse
Portals". These "portals" are simply centrally managed report
libraries; in which key departmental business analysts store queries and
reports. Any authorized user can run these reports throughout the day (or
night...all at your convenience, except for scheduled down-time)...and the results can be viewed
electronically...or printed on your printer. Nothing more is required than a
double click of your mouse to obtain immediate results.
Getting Access
Any university departmental staff member may request access to the
data in an Operational Data Store (ODS) or Data Warehouse, or access to a report
library with appropriate Director or Dean level approval. For each access
form, acquire all necessary signatures, and then forward the form to the appropriate system
administrator. The system administrator will forward the application
to the appropriate data warehouse personnel and we will notify you by e-mail that your account is
ready. Incomplete applications will be sent back to the user. For Operational
Data Store or Data Warehouse access, once
you receive a confirmation e-mail, you can install the client software yourself
or you may schedule an appointment with your EIT desktop support person
to install it. Instructions to install the software yourself
may be found below.
You must have a UTAD domain account to access the data.
-
MS SQL Database Access Request Form
- Use the form to request access to SQL tables within a database. Database
access is required to query the tables and use the MS Access Report
Libraries.
-
MS SQL Client Installation Instructions
- Instructions for installing the necessary client files on your computer so
you can access and link to SCT Plus Microsoft Server database files.
Also included are instructions for establishing access to the SCT Plus MS
Access report libraries.
-
Banner ODS Access Request Form -
Use this form to request access to a Banner ODS subject area.
-
Oracle Client
Installation Instructions - Instructions for installing the
necessary client files on your computer for accessing and linking to Banner
Oracle database files.
-
Web Report
Library Access - Use the form to request access to Folders in the
Web Report Library. The Web Report Library is located at
http://reports.utoledo.edu. The library will be the primary
repository for Banner related reports.
Additional Information
Contact Information
For questions about data warehousing at the University of Toledo, contact
Dominic D'Emilio,
associate director, at 419.530.3673, or
Tom Derby, database administrator, at
419.530.3559.
|
