care and feeding of a data warehouse mary louise powers lehigh university
TRANSCRIPT
SCT Banner® Technical Environment
All SCT Banner modules installed
All ‘Web for’ products installed
AIX/Unix, Oracle 8i
Maintained by Enterprise Systems (ESI)
Project Background
Request for a data warehouse from Institutional Research (IR)Strategic decision supportTrend analysisEfficiency and accuracySingle institutional data source
Project Background
Warehouse definitionTime slice, Pre-defined eventsStrategic, not transactionalNot a Data Mart for operational reportingDirect / indirect customersData, not reports
Project Scope
Design and implementation from Spring 2001 to Fall 2002Graduation back to 1992Enrollment/Registration from Fall 2001Primary reporting source for IR
Design
Focused on enrolled students, courses, graduation Reporting needsBusiness processesTechnical interest in Dimensional Model and Star Schema
Star Schema
Time_Dimension
PK Time_Key
I4 TermAcademic_Year
I1 Activity_Date..
I3 .I4 .
Instructor_Dimension
PK Instructor_Key
I3 IDDepartmentCollege
I3 ..
I4 .I3 .
Demographic_Dimension
PK Demographic_Key
I4 GenderEthnicity
I3 Age....
Registration_Fact
PK,FK4,I1 Demographic_KeyPK,FK2,I1 Instructor_KeyPK,FK3,I1 Time_KeyPK Course_Key
Course_IDCredit_HoursGrade....
Course_Dimension
PK Course_Key
I5 TermSubject
I6 DepartmentI5 .
.I8 .
.
Design
High level technical designTranslated internal processes into schemaAllowed for implementation of flat tables
Stop gap measureSupporting data from Admissions & Financial Aid
Software RequirementsExtraction, translations, cleansing Loading and warehouse table buildsCreation of summary tables and views
SCT Banner Executive-Self Service (WFE)
Data Warehouse Executive Briefing Center Administrative interface
Production Environment
Three databasesSCT Banner Production“Stage” (copy of SCT Banner Production)
Built from Banner exportProvides “snapshot” of Banner at critical point in time – data is frozenLimited access
Data Warehouse
Production OperationsEnrollment / Registration / Graduation
extracts
Enrollment/Registration Events: 10th day census Midterm censusLast day of classClose of term
Production Operations
Pre-export audit reporting – SCT Banner production
Export of SCT Banner production database
Create Stage database from export (can be refreshed at any point in time)
Production Operations
Pre-extract audit reporting – Stage
Clean up of Stage databaseSynchronize of Stage and Banner correctionsArchive corrected copy of StageAudit trail
Run warehouse maintenance process
Customizations / Enhancements
ChallengesData qualityTime involvementGetting accurate specifications
Post Refresh Process
Administering / ManagingProduction Operations
Manage events processingSCT Luminis 3 Portal – Calendar and task list
OperationsWho runs the warehouse Support and troubleshooting
SecurityRequest Tracking System
Technical
Infrastructure maintenanceSource code housekeepingArchiving processesWFE upgradeOracle upgradesOptimize Stage database creation
Work in Progress
You are never doneData retention requirements - TBDData integrity issuesPotential for implementation of other areasChanging business perspectives (New VP’s, etc)
Conclusion
On going challengesComplexity of the system Timing and synchronizationLehigh’s data quality requirements
BenefitsPrimary data source for IREase of use for historical reportingStability of data
References
Adamson, C. & Venerable, M. (1998) Data Warehouse Design Solutions. John Wiley & Sons, Inc.
Dodge, G. & Gorman, T. (1998) Oracle8 Data Warehousing, A Hands on Guide to Designing, Building, and Managing Oracle Data Warehouses. John Wiley & Sons, Inc.
Kimball, R. (1996) The Data Warehouse Toolkit. John Wiley & Sons, Inc.