Data WareHouse
The Data Warehouse consists of Oracle tables created to facilitate reporting. There are three types of tables in the Data Warehouse:
- Aleph Oracle tables, which currently make up the majority of data in the Data Warehouse. The Aleph Oracle tables are not normalized for reporting often making it difficult to create complex reports. ETL (extract/transform/load) scripts are run nightly to pull selected data elements from the Aleph Oracle tables, normalize them for reporting, and load them into a separate Oracle database (the Data Warehouse). Another issue with using the Aleph Oracle tables for reporting is that the Aleph code is sitll dependant on an old version of the Oracle optimizer called the Rule Based Optimizer (RBO). The newer Oracle optimizer is called the Cost Based Optimizer (CBO) and it is usually more effective for ad-hoc queries. The Data Warehouse Oracle instance is configured with the CBO.
- SFX search records taken from both the current SFX tables and archived files providing the ability to create more comprehensive reports.
- Tabled versions of NOTIS records used for reporting prior to the conversion to Aleph.
Data Warehouse Table Information
- AL_ACQ_MATERIAL - Object Code and Material Type codes and descriptions
- AL_AC_DOC_LINK - Links Between Headings and Bibliographic records
- AL_AUTH_LOAD_KEY - Bib Document Numbers for records added and updates by Authority batch loads
- AL_AUTH_LOAD_POST - Post LCA10 loads of new and updated Headings
- AL_AUTH_LOAD_PRE - Fields updated by the LCA10 load as the existed prior to the load
- AL_BIB_FMT - Two character format code (BK, SE, etc.)
- AL_BUDGET - Budget records
- AL_BUDGET_EXPENDITURES - Budget records with encumbrance and payment totals
- AL_CALL_NUMBER - Call Numbers from Holdings and Items
- AL_CASH - Circulation Cash Transactions
- AL_CIRC_EVENTS - Circulation Events by Type
- AL_CIRC_LOG - Circulation Logger
- AL_CIRC_POLICY_MATRIX - Circulation Policy Configuration values
- AL_COURSES - Administrative information about Course Reserve courses (course, instructor, etc.)
- AL_HEADINGS - Authority Indexes and Headings
- AL_HOL_LOCN - 852 Fields From Holdings [Does not include call numbers]
- AL_INV_HEAD - Fields from Invoice Header records
- AL_INV_LINE - Fields from Invoice Line Items records
- AL_ISBN - ISBN Numbers
- AL_ISSN - ISSN Numbers
- AL_ITEM_STAT - Item Status Codes and Display
- AL_ITEMS - Fields from Item records
- AL_KEY_LINKS - Bibliographic record keys with corresponding HOL and ADM record keys
- AL_LEADER - Bibliographic Leader positions that may be relevant for reporting.
- AL_LOAN_HISTORY - Items Previously On Loan
- AL_LOANS - Items Currently On Loan
- AL_LOG_DESCRIPT - Acquisitions and Circulation Log Entry Descriptions
- AL_OCLC - OCLC Numbers
- AL_ORDERS - Fields from Order records
- AL_OWN - Field from Holdings Record
- AL_PATRON_STAT - Patron Status and Patron Type codes and descriptions
- AL_PROCESS_STAT - Item Processing Status (IPS) values
- AL_PUB - Bibliographic Publication Information
- AL_REQUEST - Requests for Loaned Items or Delivery of Available Items
- AL_REQUEST_HISTORY - Closed Requests for Loaned Items or Delivery of Available Items
- AL_STA - STA fields from Bibliographic and Holdings records.
- AL_SUBLIB_OU_COLL - Sublibrary, Collection and Ordering Unit codes and display names.
- AL_TAB_EXP_OWN - Valid OWN codes and descriptions
- AL_TITLES - 245 Field from Bibs
- AL_TRANSACTIONS - Budget, Encumbrance & Payment
- AL_UB_BIB - UBorrow Locate (Bib) Records
- AL_UB_LEND_REQUEST - UBorrow Lender Information
- AL_UB_LENDER_BARCODE - Barcodes for Items Sent Via UBorrow
- AL_UB_LOG - UBorrow Log Records
- AL_UB_PATRON REQUEST - UBorrow Requests
- AL_UB_REQUEST_KEYS - Cross Reference between UBorrow Borrower Request Numbers and Lender Request Numbers
- AL_VENDORS - Fields from vendor records
- DATE/TIME DIMENTION TABLE - Various date and time fields to be used in reports
- SFX_STAT_OBJECT_OFFLINE
- SFX_STAT_TARGET_OFFLINE