HU Views and Tables - Definitions

Summary

Definitions of the various HU, HUT, and HUE objects

Body

Overview

As delivered, Banner had some views they called "Object-Access Views".  Two examples were AS_STUDENT_DATA which returned data for students and AS_STUDENT_REGISTRATION_DETAIL which returned data for the classes in which students were enrolled.

In relational databases (RDBMS), a table is a method of storing data that is similar to a spreadsheet with rows and columns.  All of the SQL access commands are optimized for table access which makes them faster.  A "view" in an RDBMS is the result of executing a query on one or more tables which is executed each time the view is accessed.  This gives current aggregated data but comes at the expense of performance. 

The Object-Access Views were no exception.  Some of them could take 30 or more minutes to return data depending on the view.. Also they were designed for the general customer.

So we created "Hardingized" versions of some of the Object-Access views.  To distinguish them, we changed the original two characters to HU, thus creating HU_STUDENT_DATA and HU_STUDENT_REGISTRATION_DETAIL.

But these views, while slightly better, were still slow.

The next step was to create nightly processes that would extract subsets of data from the HU views and save them in tables, thus gaining the performance advantages of the tables with the disadvantage that the data was static as of early in the morning, so any changes made after the table was populated was not reflected until the next morning.

This lead to HUT tables such as HUT_STUDENT_DATA and HUT_STUDENT_REGISTRATION_DETAIL.  But as it took just as long to extract that data from the views, those tables were limited to current term data.  That creates confusion between terms, so the method chosen for the days between terms was to keep the data for the "closest" term whether that be the old or the new term.

The "closest" concept proved unwieldy, so HUT_STUDENT_DATA_3TERM was created   It contains data for students who were enrolled in the previous term, the current term, and the next term thus helping the times between terms.

One other iteration was the "eternity" tables which begin with HUE.  These are designed to store the data for that view for all terms.  When that table is processed each morning, the process is to delete all of the rows from terms starting a year ago and going forward and then populating everything from then forward.  That will not correct changes made to the data more than a year ago, but those are infrequent.

So the iterations were:

  • AS (and others beginning with a) - Object-Access views
  • HU - "Hardingized" Object-Access views
  • HUT - tables created from subsets of the same name HU views
  • HUE - "eternal" tables created from the same name HU views

Several of these tables are recreated or updated within the Appworx job SZPHUVT which is run nightly by the CREATE_HUT_TABLES process flow. 

Details

Details

Article ID: 137464
Created
Tue 4/12/22 5:48 PM
Modified
Fri 7/8/22 5:31 PM