The Pennsylvania State University - Administrative Information Services, a unit of Information Technology Services

Student DB--New semester_converted Columns

Posted: Thursday, September 18, 2008

Data Warehouse Student Database Users,

As of Wednesday, September 17th, a change has been made to the uf_tcm and uf_ucm user friendly tables on the production data warehouse. New fields were added that will take any summer semester that is formatted as 'S1' and convert it to 'SU'.

Affected Database: student
Affected Tables: uf_tcm & uf_ucm
Affected Columns: uf_tcm - semester_converted
                               uf_ucm - effective_semester_converted
                                           - end_semester_converted
                                           - last_semester_offered_converted
                                           - prior_effective_semester_converted
                                           - prior_end_semester_converted

For all ‘semester’ fields in both tables, an accompanying 'semester_converted' field has been created. These converted fields will contain the semester code in a more standard form, allowing the conversion of summer semester noted as 'S1' through 'S9' to be read as 'SU'. (All non-summer semester values in this field will remain the same as the non-converted field. For example, 'semester' = '200809FA' will generate 'semester_converted' = '200809FA'.)

The uf_tcm table only contains 1 semester field, and therefore only contains 1 converted field. The new converted field is:
semester_converted -> derived from semester

The uf_ucm table contains 5 semester fields, and therefore 5 converted fields. They are:
effective_semester_converted -> derived from effective_semester
end_semester_converted -> derived from end_semester
prior_effective_semester_converted -> derived from prior_effective_semester
prior_end_semester_converted -> derived from prior_end_semester
last_semester_offered_converted -> derived from last_semester_offered

(All of these new fields are located directly after their derived fields.)

Documentation for these new fields is available online from the uf_TCM and uf_UCM column description pages. As always, in order to utilize these new fields, you’ll need to re-link these tables in your ODBC connection. Your current queries will not be affected. Furthermore, the original semester fields on both tables remain unchanged.

Please let me know if you have any questions or concerns about these new fields.

Thanks!

Suzi Cotter

Content Questions: Data Warehouse