Penn Computing

University of Pennsylvania
Penn Computing << go backback
ST_ADDRESS column definitions . Pennant Student Records Home . Data Warehouse Home

ST_ADDRESS table

(dwngss_ps.st_address)

Explanation

Contains postal mailing addresses of the student. Each address is assigned a type. Students can have multiple rows in this table that are the same type. ST_ADDRESS has one row per student per address type per sequence number within type.

Source

The warehouse ST_ADDRESS table is refreshed in batch mode from the Operational Data Store (ODS), which gets its data from the Banner database. The ODS source tables are UPENN_ODS.SATURN_SPRIDEN and UPENN_ODS.SATURN_SPRADDR. In the online Pennant system, the data about students' addresses can be viewed on the SPAIDEN form.

Common Uses

  • Identify students' current addresses to prepare for a targeted mailing.
  • Find students with a specific type of address.

 

Primary Key Indexed Data Elements Related Tables
ST_ADDRESS_PK
PIDM, PENN_ID, ADDRESS_TYPE

STUDENT
V_ADDRESS_TYPE

Cautions and notes

  • There are now more address types than were available in the legacy Student data collection. For example, in Pennant Student Records, there is a "Learning From" address, and a "Mailing" address -- both provided by the students. Valid values for address type can be found in DWNGSS.V_ADDRESS_TYPE table.
  • There is no longer a warehouse address type of 'C' for current. Instead, please see the column CURRENT_IND, which will have a 'Y' on the row for the address that is current for the student, based on the following rules:
    If the Learning From (LF) address is not null and today's date falls within the start and end dates of the Learning From address, then the Learning From address gets CURRENT_IND = 'Y', else
    If the Temporary (TE) address is not null and today's date falls within the start and end dates of the Temporary address, then the Temporary address gets CURRENT_IND = 'Y'
    else
    If the Local Penn Institution (L1) address is not null and today's date falls within the start and end dates of the Local Penn Institution address, then the Local Penn Institution address gets CURRENT_IND = 'Y',
    else
    If the Local (LO) address is not null and today's date falls within the start and end dates of the Local address, then the Local address gets CURRENT_IND = 'Y',
    else
    If the Permanent (PR) address is not null and today's date falls within the start and end dates of the Permanent address, then the Permanent address gets CURRENT_IND = 'Y',
    else
    If the Business (BU) address is not null and today's date falls within the start and end of the Business address, then the Business address gets the CURRENT_IND = 'Y'.
    Note that because we are looking at the effective dates on the address records, it is possible for a student to have no address that is considered "current", and thus they will have no row with CURRENT_IND = 'Y'
  • The address with the CURRENT_IND = 'Y' indicates where the student is most likely to be or where they can be reached. It does not necessarily indicate where the student's regular physical mail should be sent, via USPS or any other mail service. For regular mail, consider using the address with address type Mailing (MA).
  • It is possible for a student to have more than one address of the same address type. To get the most recent one, use the row of the desired address type with the max seqno. Or, you can use the ADDR_START and ADDR_END dates to find the adddress of the address type that you want. To find the more recent one using the start/end dates, you can look for the address where today's date is greater than the ADDR_START and less than the ADDR_END (or the ADDR_END is null).
ST_ADDRESS column definitions . Pennant Student Records Home . Data Warehouse Home

 



Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania