How Ellucian's Banner ODS works
25 May 2021
A regular Oracle User Group meetup attendee who’s a university DBA let me ask questions about ODS (Operational Data Store), a data warehouse maintained by Ellucian to accompany their popular university ERP (enterprise resource planning) database Banner.
The 6 services
ODS is often made up of 6 services. It’s not clear to what extent these services can be installed on separate servers – many are so small that they’re commonly installed on the same server.
- An Oracle database, installed on Linux, containing schemas defined by Ellucian
- A Linux folder containing a “code tree” of stored procedures written in SQL, PL/SQL, Java, C, etc.
- A tool called Oracle Data Integrator, configured to point to another server running a university’s Banner database.
- Its “agent” needs to be running on the server running the ODS database?
- IAADMIN, an Oracle WebLogic (Java-based web server) web application running on Linux that provides DBAs a graphical user interface through which they can schedule the stored procedures to be run by ODI through the Oracle Job Queue, run them manually, run custom in-house stored procedures, etc.
- Pro tip: know a simple job that you can call from IAADMIN to verify that ODI’s agent is up & running & working correctly. (You can also log into the server on which ODI’s agent is running and run a command to see if it thinks it’s running.)
- Oracle REST Data Services, to which IAADMIN connects (definitely easiest to just set up on its own server)
- A web server displaying “metadata” information about ODS’s schemas, so that end users can figure out how to query it to meet their business needs
The 3 R’s: refresh, restage, reload
For every base table (say, STVTERM) in a given Banner database, there’s a corresponding log table of how it’s been changing and whether that change has been acknowledged as having been swept to ODS yet. Let’s call such a table STVTERM_LOG
.
There is also a table in ODS – an m-log table – corresponding to the log table in Banner. Let’s call our example STVTERM_MLOG
.
Refresh
Let’s say BANNERPROD.LOGS.STVTERM_LOG
has 25 more transactions than ODSPROD.MLOGS.STVTERM_MLOG
. There will be something along the lines of a 25-row INSERT performed into ODSPROD.MLOGS.STVTERM_MLOG
. This happens for all such LOG/MLOG pairs.
When a “refresh” from Banner to ODS runs:
ODSPROD.MLOGS.STVTERM_MLOG
gets inspected – records are UPSERTed intoODSPROD.BASE.STVTERM
as necessary.- This happens for all such MLOGs representing Banner base tables, as well as for custom tables a university might have added through IAADMIN (e.g. the tables owned by an “IN_HOUSE_CUSTOM” schema user).
ODSPROD.MLOGS.STVTERM_MLOG
gets updated saying that the data made it into a base tableODSPROD.MLOGS.STVTERM_MLOG
also gets updated saying that “composite tables X, Y, & Z” also need the info.- A “composite table” might be one of the data warehouse quick-reference tables like
ODSPROD.COMPOSITE.VALIDATION
- ODS knows
STVTERM
corresponds to “composite tables” X, Y, & Z because Ellucian programmed the “code tree” to know this kind of thing.
- A “composite table” might be one of the data warehouse quick-reference tables like
ODSPROD.MLOGS.STVTERM_MLOG
gets inspected for “composite tables” that need UPSERTing and does it. Ditto other MLOGs.- Some “materialized views” get populated (I think this is when this happens? notes unclear.)
ODSPROD.MLOGS.STVTERM_MLOG
gets truncated or its rows get marked as “fully done” (DBA couldn’t remember of the top of his head which)BANNERPROD.LOGS.STVTERM_LOG
gets truncated/marked-complete as well (DBA couldn’t remember off the top of his head which)
Restage
It can take quite a while – think hour, not seconds – to do all of ODS for a large university.
- Truncate
BANNERPROD.LOGS.STVTERM_LOG
(ditto other logs) - Drop & recreate
ODSPROD.BASE.STVTERM
based on the table schema found inBANNERPROD.BASE.STVTERM
(ditto other ODS base tables) - I didn’t write it down, but presumably somewhere around here, also drop & recreate “composite tables” & materialized views based on what the ODS “code tree” says to do
- Truncate
ODSPROD.MLOGS.STVTERM_MLOG
(create if not exists) (ditto other base tables)
Reload
This can take a day to do all of ODS for a large university.
- Fill in
ODSPROD.BASE.STVTERM
fromBANNERPROD.BASE.STVTERM
(ditto other base tables); fill in composite tables & materialized views, logging it all inODSPROD.MLOGS.STVTERM_MLOG
&BANNERPROD.LOGS.STVTERM_LOG
(ditto other base tables’ logs).
Notes
A “restage & reload” is required in the following circumstances:
- The schema changes for a Banner table that is tracked in ODS (e.g. with an Ellucian feature enhancement installation)
- You do something that puts bad data into either Banner’s log tables or ODS’s mlog tables
- For example, you might point an ODS at the wrong Banner and let one of the “3 R’s” processes run. This is a common mistake if you have some nonproduction ODS environments running and you try to spin one up by cloning another ODS environment to a different server.
You might also want to delete all “MVIEWS” after a clone from one ODS to another.
Backup and recovery
A typical ODS database doesn’t have a lot of DML CRUD operations going on, except outside of business hours doing scheduled refreshes.
However, it is intended to have a lot of SQL queries going on during normal business hours and just before opening hours.
Fast cold snapshots
One interesting thing about ODS’s activity patterns is that, unlike a highly transactional database such as Banner, even the production version of ODS can be stopped for a few minutes (as long as you choose the right time of day) with little business consequence.
It is not necessarily appropriate to back up a running database by taking a snapshot of the machine on which it is is running. Not if you want to be able to restore it successfully, anyway. Databases need to be stopped before relying upon filesystem-based backup methods.
An old-fashioned cold backup of an Oracle database, which involves copying the files that contain Oracle tables while the database is stopped, can take a good 3 hours to make if the database is large.
While taking a full machine image is not necessary for the backup and restoration of a database from one machine running Oracle to another (the relevant parts of the filesystem pertaining to the database’s contents should suffice), it sounds like AWS might have some ways of backing up entire machine images that take minutes rather than hours. If that were the case, DBA said it wouldn’t necessarily hurt to back up and restore an entire machine image, as long as the backup was taken while the database was stopped.
Consequently, whatever the technology, if there were some sort of backup-and-restore process that could take backups very quickly, it might be possible to run such a process daily (e.g. at 11PM, just before the next “Banner->ODS refresh” is scheduled to start) for disaster recovery purposes.