How the Data Warehouse Works
· Relationship of Warehouse to
· How the Warehouse is Built and Refreshed
· Important Warehouse Facts
Relationship of the Warehouse to Transaction Systems
Transaction systems handle the day-to-day functions of a business--recording,
processing, and storing operational data. Examples of transaction systems
at Penn include the Student Records System, BEN Financials General Ledger,
Online Time Reporting, and Property Management. These systems handle operations
such as enrolling a student in a course, changing a student's major, creating
journal entries, printing a paycheck, or recording the location of equipment.
Transaction systems, however were not designed to provide managers with
the information they need to conduct trend analyses or to undertake longitudinal
studies that require use of historical University data. Data Warehouses,
unlike transaction systems, are specifically designed to handle these
types of management queries and analyses.
How the Warehouse is Built and Refreshed
To build the Warehouse, data from the transaction systems was analyzed
(each piece of data evaluated for inclusion into the Warehouse), "cleaned"
(old codes converted into new codes, existing data redefined), and restructured
(entities broken apart and new entities created). The data was then summarized
and arranged in a format to support analysis and reporting. In some instances,
additional data elements were calculated to support a particular analysis.
The Warehouse is refreshed periodically, using the transaction systems
as its source of data. That is, programs extract data from a transaction
system and translate it into Warehouse format. The formatted files are
than transferred and loaded into the Warehouse. The data is than available
for analyses and reports.
Note that each data collection has its own refresh cycle governing when
its data is periodically updated in the Warehouse. For example, one data
collection may be refreshed daily, while others may be refreshed hourly
or once a term. Thus, data in the Warehouse reflects the most recent refresh
cycles for the data collections. Recipients of reports from the Data Warehouse
need to be aware of this "delay" in Warehouse data.
Refresh schedule information is noted in the documentation for each collection.
Refresh cycles are subject to change; please refer to the Warehouse updates page for schedule changes and announcements.
Important Warehouse Facts
The following are important facts that you should know about the University
- The refresh cycle varies for each data collection in the
Warehouse. Do not execute queries against a collection in the Data Warehouse
when it is being refreshed. Your queries will not be completed because
the refresh process involves dropping index pointers from tables.
- As previously mentioned, there is a "data delay" between the Warehouse
and the transaction system. Keep this time frame in mind when comparing
data between the two systems or when analyzing query results--it may
help you to understand and account for any data discrepancies found.
Always indicate or communicate the date on which your Warehouse results
were obtained and identify the Warehouse, and not the transaction system,
as the source of the information. For example, include the date of the
query in the report header and the source of the information, the University
Data Warehouse, in the report footer.
- Some University data are more dynamic--change more frequently--than
other data or are subject to change at specific times during the fiscal
year. Take this into consideration when writing queries against the
Warehouse. Examples of data that change are course enrollments during
the add/drop period and student grades during the grading period.
- Data in the Warehouse is for queries only. That is, you cannot add,
change, or delete data in the Warehouse. These activities are done only
in the transaction system.