Ellucian Banner Records When a Major Started. No Column Records When It Stopped.
A replicated Banner lands every column and 99 correct foreign keys on the student record, and still cannot say how many students are in each major. The semantic model chooses the in-force row.
Query Ellucian Banner data with AI against a replicated Oracle schema and the simplest reporting question in the building, how many students are in each major, has no honest one-table answer. Not because a column is missing. Every column lands, the student record carries 99 declared foreign keys that are all correct, and the effective term column that looks like the answer is not the answer.
A provost asks how many students are in each major this term.
Point an AI at the landed SATURN schema and it has no trouble at all. SGBSTDN is the student program record. SGBSTDN_MAJR_CODE_1 is a declared foreign key to STVMAJR, which holds the label. SGBSTDN_TERM_CODE_EFF is a declared foreign key to STVTERM, which holds the terms. Filter to this term, group by major, count.
The query compiles. Every major on the list is a real major. Every number is small enough to be believable.
It is roughly the number of students who changed something this term.
Before you start
- Banner already landed in a warehouse. This post is written for the team that has it. There is no Banner connector: Fivetran's own page says Banner "is built on the Oracle database", that "instead of pulling data from the Ellucian Banner API, we pull directly from the source database", and that to sync it you should use its Oracle connector. Under its "Schema information" heading, the entire content is a pointer to Ellucian's technical reference manual for your version.
- There is no free instance, and not close. Banner is licensed to institutions under multi-year agreements. There is no trial, no developer edition and no public sandbox, and Ethos access runs through the Ellucian Customer Center on institutional approval. Everything below runs against your own replicated schema, and a reader who is not at a Banner institution cannot follow along at all.
- This is Banner, not Colleague. Ellucian sells two student information systems. Colleague is a different product on a different database with different table names, and nothing here has been checked against it.
- Casing varies by extract tool. Oracle stores unquoted identifiers upper case, Snowflake normalises up, BigQuery and Redshift down. This post writes SQL lower case and table documentation upper. Adjust to whatever your destination did.
- The grain may already have been fixed for you. If your warehouse came through Banner ODS or Banner EDW rather than a direct replication of Baseline, the problem below was solved upstream and this post does not apply to your estate. Check which one you have before you start.
The question
"How many students are in each major?"
In higher education this is not a reporting curiosity. It is the enrollment report, it is what goes to the board, it is what drives faculty lines and program review, and it is the number a registrar has been producing correctly for twenty years. It is asked constantly, it is asked of the warehouse the moment there is one, and the institution already knows the right answer.
What breaks
Here is the query, with both join keys declared foreign keys:
select m.stvmajr_desc as major,
count(*) as students
from sgbstdn s
join stvmajr m on m.stvmajr_code = s.sgbstdn_majr_code_1
where s.sgbstdn_term_code_eff = '202610'
group by 1
order by 2 desc;Nothing about that is careless. sgbstdn_majr_code_1 really does point at stvmajr_code. sgbstdn_term_code_eff really does point at stvterm_code. An introspection pass over this schema finds both and gets the cardinality right.
It returns the students whose program record changed effective this term. A continuing student who declared their major two years ago and has not touched it since has no row for this term, so they are not in the answer. The result is small, plausible, and a fraction of the institution.
So you widen it, which is the reflex fix and the more dangerous one:
select m.stvmajr_desc as major,
count(distinct s.sgbstdn_pidm) as students
from sgbstdn s
join stvmajr m on m.stvmajr_code = s.sgbstdn_majr_code_1
where s.sgbstdn_term_code_eff <= '202610'
group by 1
order by 2 desc;Now every student who ever switched majors is counted once in the major they left and once in the major they joined. The count(distinct ...) looks like it is protecting you, and it is: within each major. Across majors it protects nothing. Every per-major figure is inflated, the column sums to more students than the institution has, and no single row looks wrong.
A worked example of one student's program history, laid out against the key structure the Banner ERD documents. Banner writes a row when something changes and never closes the row it supersedes. Major codes are institution-defined, so yours will differ.
Why it breaks
Start with the primary key, because the whole problem is visible there.
PK_SGBSTDN is unique on exactly two columns:
SGBSTDN_PIDM SGBSTDN_TERM_CODE_EFFOne row per student per effective term. A student who enrolls, changes nothing, and graduates has one row. A student who switches major twice and takes a leave has four. The row is not a snapshot of a term. It is the start of an interval, and Banner writes it only when something changes.
Now count what is on the table, from the published Banner ERD. SGBSTDN lands with 109 columns, and not one of them closes that interval. There is no end term, no expiry, no superseded flag, no valid-to. The only column on the whole record whose name mentions an end of anything is SGBSTDN_LEAV_TO_DATE, and that closes a leave of absence rather than the record's validity. Banner models a closed interval when it means one. Here it deliberately did not.
That leaves the schema unable to say the one thing the question needs: which row was in force.
And the schema is otherwise immaculate. The ERD's foreign-key block for SGBSTDN holds 99 declared foreign keys, covering 98 of the 109 columns, pointing at 39 distinct tables, 32 of which are STV* validation tables. Every code on the record has a labelled home and a declared path to it. An agent pointed at a replicated Banner finds all of them, correctly, with no help whatsoever.
This is worth saying plainly, because it is the opposite of what the rest of this series describes. When ServiceNow puts a join where no foreign key exists, or Business Central computes six of eight dimension columns on read so they never land at all, something is missing from the warehouse. Here nothing is missing. A raw Oracle replication is lossless in structure: every table, every column, every constraint, in Banner's own names. And the number is still wrong.
It is also a different failure from ServiceTitan's, where every foreign key is likewise correct. That one is a fan-out: the join produces too many rows and they have to be weighted. This one produces exactly the rows it should and selects the wrong ones. There is nothing to weight and nothing to deduplicate. The question is which rows were true on a given date, and no join expresses that.
Ten term columns, and only one of them selects rows
The trap has a second half that makes it much easier to fall into.
SGBSTDN carries ten columns named SGBSTDN_TERM_CODE_*, and all ten are declared foreign keys to STVTERM:
SGBSTDN_TERM_CODE_EFF SGBSTDN_TERM_CODE_MATRIC
SGBSTDN_TERM_CODE_ADMIT SGBSTDN_TERM_CODE_ADMIT_2
SGBSTDN_TERM_CODE_ASTD SGBSTDN_TERM_CODE_GRAD
SGBSTDN_TERM_CODE_CTLG_1 SGBSTDN_TERM_CODE_CTLG_2
SGBSTDN_TERM_CODE_PREV SGBSTDN_TERM_CODE_CASTNine of them are facts about the student: the term they matriculated, the term they were admitted, the term they are expected to graduate, the catalog year their degree requirements come from. Exactly one, _EFF, is a fact about the row.
Nothing in the schema distinguishes them. They have the same datatype, the same target table, the same kind of name, and all ten are correct foreign keys. An agent choosing among them by name alone is guessing, and _EFF is not the most obvious guess when the question says "this term".
What Banner did for you
Here is the part worth sitting with. This is not an oversight anywhere in the chain.
Banner resolves the effective term on every screen a registrar has ever looked at. The SGASTDN form navigates by term: you give it a term, and it shows you the record in force, deriving the boundary for display rather than reading it from a column. Twenty years of correct, point-in-time program records, and nobody ever wrote a subquery.
Banner also ships code whose entire job is this. The ERD's list of objects that call SGBSTDN includes a C function named select_current_major, in SHREDIY.PC. Replication copies the rows. It does not copy the function.
And Ellucian sells a product for it. Banner ODS and Banner EDW read Banner and republish it in a reporting shape, and removing this exact logic is part of what they are for. A practitioner writing about Banner reporting puts it in a parenthesis, as something everyone in the field already knows:
"Effective dating … is one of the most common topics of discussion when reporting against Banner (Baseline, especially: the ODS and EDW go a long way to removing effective date and term code logic requirements)."
A direct Oracle replication goes around all three. It copies SATURN faithfully, which means it copies Baseline: the form's resolution is not in it, the C function is not in it, and the ODS is not in the path. What lands is precisely the shape Ellucian built a separate product to get away from.
This is the same shape as Salesforce's "Opportunities with Products" report type holding the line-item grain, and the same shape as ServiceNow's table inheritance: the thing that made the application's number correct was never a column, so replication had nothing to copy.
The fix
None of this is a query problem. Rewriting the SQL fixes one question for one person on one afternoon, and the next person to ask about enrollment starts from the same schema and reaches the same wrong number by the same correct route.
It belongs in the semantic model, where the resolution is declared once and every question inherits it.
The joins go in first, and the notable thing about them is how ordinary they are. Every one is a foreign key Banner already declares:
# subject_areas/student/sgbstdn.yaml
relationships:
- name: sgbstdn_to_spriden
from: { table: sgbstdn, columns: [sgbstdn_pidm] }
to: { table: spriden, columns: [spriden_pidm] }
cardinality: many_to_one
citation: >
Banner ERD v7, SGBSTDN. PIDM is Banner's internal person surrogate and is
the only correct join key from a student record to a person. Do NOT join
on spriden_id, the visible identifier, which is not unique across spriden
rows. spriden also holds many rows per person, so this join needs
spriden_change_ind IS NULL on the spriden side or it duplicates every
person who has ever changed their name.
- name: sgbstdn_to_stvterm_eff
from: { table: sgbstdn, columns: [sgbstdn_term_code_eff] }
to: { table: stvterm, columns: [stvterm_code] }
cardinality: many_to_one
citation: >
Banner ERD v7, SGBSTDN foreign-key block. This is the term the row TOOK
EFFECT, not the term the row describes. Nine other sgbstdn columns are
also declared foreign keys to stvterm and are attributes of the student
rather than of the row: _MATRIC, _ADMIT, _ADMIT_2, _ASTD, _GRAD, _CTLG_1,
_CTLG_2, _PREV, _CAST. None of them selects rows.
- name: sgbstdn_to_stvmajr
from: { table: sgbstdn, columns: [sgbstdn_majr_code_1] }
to: { table: stvmajr, columns: [stvmajr_code] }
cardinality: many_to_one
citation: >
Banner ERD v7. The primary major of the primary curriculum. Codes are
institution-defined and only stvmajr carries the label.Then the part that carries the weight. The schema cannot say which row is in force, so the model has to:
entities:
- name: banner_student_program_record_as_of_term
description: >
The sgbstdn row in force for a student as of a given term. Banner writes a
row only when something changes and the row stays in force until a later
row for the same PIDM supersedes it. sgbstdn has 109 columns and none of
them records when a row stopped being true; the primary key is
(sgbstdn_pidm, sgbstdn_term_code_eff) and term_code_eff is the START of an
interval. The only column naming an end of anything is
sgbstdn_leav_to_date, which closes a leave of absence and not the record.
So no filter on sgbstdn alone selects the right rows.
resolves_to:
table: sgbstdn
grain: one row per student per effective term, written on change only
selector: >
sgbstdn_term_code_eff = (SELECT MAX(x.sgbstdn_term_code_eff)
FROM sgbstdn x
WHERE x.sgbstdn_pidm = sgbstdn.sgbstdn_pidm
AND x.sgbstdn_term_code_eff <= :as_of_term)
forbidden_selectors:
- >
sgbstdn_term_code_eff = :as_of_term
(returns only students who CHANGED something that term; a severe
undercount that looks like a plausible small number)
- >
sgbstdn_term_code_eff <= :as_of_term without the MAX subquery
(returns every program the student ever held; overcounts every student
who switched, once per major)
caveats:
- >
Term codes sort lexically in Banner's YYYYMM convention, which is why
MAX() and <= work on a VARCHAR2(6). That is a property of the
institution's coding convention and is not enforced by anything. Confirm
it against stvterm's start dates before relying on it.
- >
This is a Banner fact. Ellucian Colleague is a different product on a
different database and nothing here has been checked against it.
- >
If the warehouse came through Banner ODS or EDW, the grain is already
term-by-term and this entity is wrong for that estate.And the metric binds to the entity rather than to the table:
metrics:
- name: headcount_by_major
calculation: >
Students in each primary major as of a given term, counted from the
sgbstdn row in force for each student at that term, with the major code
decoded through stvmajr.
requires_entity: banner_student_program_record_as_of_term
source_tables: [sgbstdn, stvmajr]
primary_table: sgbstdn
other_names: [headcount by major, students by major, enrollment by major]
citation: >
Banner ERD v7, SGBSTDN. The entity supplies the row selection, which is
the part the schema cannot express.requires_entity is the load-bearing line. It says that reaching sgbstdn for a headcount without the in-force selector is not a stylistic choice, it is a number the model has already declared invalid. An agent that filters on the bare effective term gets refused rather than answered.
Four decisions in here are not inferable from the schema, and a person makes them once.
Which student statuses count as enrolled. STVSTST codes are institution-defined. Whether a student on approved leave is in the headcount is a policy your institution has already written down somewhere, and no introspection recovers it.
Whether term codes sort. The MAX() selector assumes your STVTERM codes sort in the order the terms occur. That is the near-universal convention and it is not a guarantee. One person checks it once against STVTERM's start dates.
Which curriculum is "the" major. SGBSTDN carries a primary and a secondary curriculum, dual-degree columns, and concentrations. Whether a double-major student counts once, twice, or one half in each is an institutional reporting rule, and it changes the total.
Census date versus live. Institutions report enrollment as of a frozen census date. The :as_of_term parameter gets the term right and says nothing about the freeze. A person supplies that date.
Reproduce it yourself
This runs against your own warehouse, which is the only route available for Banner.
- Run the shape query. It tells you how exposed you are before you change anything.
- Run the wrong query from the top of this post. Keep the output.
- Run the gap query. It puts a number on what step 2 has been quietly adding.
- Declare the relationships, the entity and the metric, then ask the question in plain English in the assistant your team already opens, and compare against the registrar's census for the same term.
The shape query. Run this first:
select rows_per_student,
count(*) as students
from (
select sgbstdn_pidm,
count(*) as rows_per_student
from sgbstdn
where sgbstdn_term_code_eff <= '202610'
group by sgbstdn_pidm
) t
group by 1
order by 1;Every student on a row above 1 has a program history that the naive filters cannot read. If everyone is on 1, your institution locks cohorts and you can stop here. Most do not.
The gap query. This is the one worth keeping:
with in_force as (
select s.sgbstdn_pidm,
s.sgbstdn_majr_code_1
from sgbstdn s
where s.sgbstdn_term_code_eff = (
select max(x.sgbstdn_term_code_eff)
from sgbstdn x
where x.sgbstdn_pidm = s.sgbstdn_pidm
and x.sgbstdn_term_code_eff <= '202610'
)
),
ever_held as (
select distinct sgbstdn_pidm, sgbstdn_majr_code_1
from sgbstdn
where sgbstdn_term_code_eff <= '202610'
)
select (select count(*) from ever_held) as naive_major_rows,
(select count(*) from in_force) as actual_students,
(select count(*) from ever_held) - (select count(*) from in_force) as phantom_students
from dual;phantom_students is your own number, computed on your own estate. Every one of them is a student being counted in a major they left. Where cohorts are locked it is near zero. At an open-enrollment college where students shop programs it is a large fraction of the headcount.
Then break it out by college or by division. Programs that students transfer into will carry most of the error, and seeing which departments the inflation concentrates in is far more persuasive than the total.
The corrected query. The correlated subquery is the entire fix:
select m.stvmajr_desc as major,
count(*) as students
from sgbstdn s
join stvmajr m on m.stvmajr_code = s.sgbstdn_majr_code_1
join stvstst t on t.stvstst_code = s.sgbstdn_stst_code
where s.sgbstdn_term_code_eff = (
select max(x.sgbstdn_term_code_eff)
from sgbstdn x
where x.sgbstdn_pidm = s.sgbstdn_pidm
and x.sgbstdn_term_code_eff <= '202610'
)
group by 1
order by 2 desc;It reads "the row that was in force for this student as of this term", which is the sentence the schema cannot say and the semantic model has to.
Frequently asked questions
Why does my Banner headcount by major not match the registrar's census?
Almost certainly because the query is filtering SGBSTDN_TERM_CODE_EFF directly. SGBSTDN holds one row per student per effective term and Banner writes a row only when something changes, so filtering on a term returns only the students who changed something that term, and filtering on or before it returns every major each student has ever held. The row in force is the one with the greatest effective term not later than the term you are asking about, and selecting it takes a correlated subquery.
What is SGBSTDN_TERM_CODE_EFF?
The term the student's program record took effect. It is the second half of the table's primary key, alongside SGBSTDN_PIDM. It is the start of an interval that nothing closes: among the 109 columns on SGBSTDN there is no end term, no expiry and no superseded flag, and the only column naming an end of anything is SGBSTDN_LEAV_TO_DATE, which closes a leave of absence.
Which of the ten term columns on SGBSTDN should I filter on?
SGBSTDN_TERM_CODE_EFF, and only inside a subquery that picks its maximum. The other nine are _MATRIC, _ADMIT, _ADMIT_2, _ASTD, _GRAD, _CTLG_1, _CTLG_2, _PREV and _CAST, and they are attributes of the student rather than of the row: when they matriculated, when they were admitted, which catalog year their requirements come from. All ten are declared foreign keys to STVTERM and nothing in the schema distinguishes them.
Can I just take the latest row per student?
Only if the question is about today. The latest row overall is not the row in force at a past term, so any term-over-term trend, any census-date report and any year-over-year comparison needs the effective term bounded by the term being asked about. That is why the selector is MAX(...) WHERE term_code_eff <= :as_of_term rather than a plain MAX().
Does this apply if my warehouse came through Banner ODS?
No. Banner ODS and Banner EDW republish Banner in a reporting shape and remove much of the effective-dating logic on the way, which is part of what they are for. Institutions that build their own warehouse frequently do the same thing by hand, exploding the interval into one row per term on the way in. This post is about a direct Oracle replication of Baseline, which is the route Fivetran documents.
References
- Banner ERD v7,
SGBSTDN, Victoria College. Every structural claim in this post: the 109 columns,PK_SGBSTDNunique onSGBSTDN_PIDMandSGBSTDN_TERM_CODE_EFF, the 99 declared foreign keys covering 98 columns against 39 tables of which 32 areSTV*, the tenSTVTERMcolumns, the absence of any validity-end column, andselect_current_majorinSHREDIY.PC. An institution's published copy of a version-specific schema rather than a vendor publication, and readable in full. - Ellucian Banner connector, Fivetran. That there is no Banner connector, that the route is the Oracle connector, and that Fivetran publishes no Banner schema.
- Oracle connector, Fivetran. The actual setup path.
- Effective Dating and Sub Queries, Banner Reporting. The practitioner statement of the hazard, the correlated-subquery fix, and the ODS and EDW parenthesis quoted above.
- agami-core on GitHub
Make your Banner data answerable
Agami is the semantic layer between your AI assistant and your warehouse. It declares what each column means and which row is actually in force, so an agent returns a governed number or says why it cannot.
