Penn Computing

University of Pennsylvania
Penn Computing << go backback
LMS_REGISTRATION Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home

LMS_REGISTRATION Table

DWLMS Schema

Explanation
Stores information on the course iterations for which trainees have registered. There is one record for each request to enroll a particular trainee in a particular course iteration. REG_REC_ID stores the number that uniquely identifies the record. An LMS_REGISTRATION record stores the trainee’s name, Penn ID, and USER_REC_ID; data pertaining to the course iteration for which the trainee has registered; information on the registration status and course completion status for the registration transaction; and various flags, indicating such things as whether the registration transaction is the latest one for the trainee and course iteration.

A trainee registers for a course iteration (ITERATION_ID), which is an offering of a particular version (CO_REC_ID) of a course (CO_MAS_ID). A trainee may have multiple LMS_REGISTRATION records for the same course iteration. For example, say that Jane Smith (USER_REC_ID 12345) registers for an iteration of Workplace Hazard Assessment - EHRS to be held on the first of next month at 8:30 a.m. in the Left Bank Building's EHRS Conference Room. (ITERATION_ID 98765). A record is created for this registration request with REG_REC_ID 3000003. Its registration status is initially Wait-Listed. When a seat in the course iteration becomes available, the record's registration status changes to Enrolled. Then Jane is asked to attend a meeting that will be held at the same time as the course iteration. She drops the course (changing the record's registration status to Dropped). However, the meeting is cancelled, so Jane registers again for ITERATION_ID 98765. Another record is created for this new registration request for USER_REC_ID 12345 and ITERATION_ID 98765, with REG_REC_ID 3000045.

The LMS_REGISTRATION table is an enhanced version of the REGISTRATION table, and was developed to support both ad hoc reporting and some of the Web-based, parameter-driven reports available in BEN Reports.

Common Uses

  • Generating class rosters for specified course iterations.
  • Counting trainees by their registration status or completion status for specified course iterations.
  • Selecting registration records to be included in a report based on whether they reflect the trainee’s latest registration with regard to the course iteration or the course, or whether they store information on the trainee’s latest completion of the course.
  • Reporting on a trainee's registration history for a course iteration, course version, or course.
Primary Key Indexed Data Elements Related Tables 
REG_REC_ID

COURSE_COMPLETED
COURSE_ITERATION_TITLE
COURSE_MASTER_ID
COURSE_OWNER_BU_ID
COURSE_OWNER_BU_NAME
COURSE_OWNER_BU_NUMBER
CO_MAS_ID
CO_MAS_ID_AS_IS
CO_REC_ID
CO_REC_ID_AS_IS
CURRENTLY_REQUIRED
CURRENT_COURSE_REG_FLAG
CURRENT_CRS_ITERATION_REG_FLAG
DATE_COURSE_COMPLETED
DATE_REGISTRATION_CREATED
FIRST_NAME
ITERATION_ID
LAST_COURSE_COMPLETION_FLAG
LAST_CRS_COMPL_OR_REG_FLAG
LAST_NAME
PENN_ID
REGISTRATION_BU_ID
REGISTRATION_BU_NAME
REGISTRATION_BU_NUMBER
REGISTRATION_STATUS_CODE
REGISTRATION_STATUS_DESC
REG_REC_ID
USER_REC_ID

COURSE_ITERATION
COURSE_MASTER
COURSE_OBJECT
CUSTOM_DATA
GEN21_USER
GRADEBOOK
KNODE

KNODE_CRS_JOIN
KNODE_USER_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_COMPLIANCE
LMS_COURSE_ITER_INSTRUCTOR
LMS_COURSE_MASTER
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_CURRENT_TRAINEE_CRS_STATUS
LMS_DEFINITION
LMS_PERSON
LMS_REGISTRATION_STATUS
LMS_STAFF_ASSIGN
LMS_STAFF_QUAL
LMS_TRAINEE_BU_COURSE_ASSIGN
LMS_TRAINEE_COURSE_ASSIGN
LMS_TRAINEE_COURSE_COMPLIANCE
PKG_CRS_JOIN
PROFILER_ASSIGNMENTS
PROFILER_USER_ANSWER
PROFILER_USER_QUESTIONNAIRE
REGISTRATION
TEST
TRACK_TEST
UPHS_CI_PATHLORE
UPHS_COST_CENTER
UPHS_RAW_DATA
UPHS_USER_ENTITY
USER_COURSES


Cautions

  • The LMS_REGISTRATION table stores a record for every Knowledge Link registration request ever logged. However, it stores only current information for a given registration request. For example, if the REGISTRATION_STATUS_REASON for a particular REG_REC_ID was once 'data correction', but is now 'Dropped for new version', there is no way to know that it was ever 'data correction'.
  • The LMS_REGISTRATION table stores registration requests both for currently required courses and optional courses. A course is currently required for a trainee if: (1) it is actively assigned as a mandatory course to a business unit that currently includes the trainee, and (2) the trainee is not grandfathered for the course. A record may have CURRENTLY_REQUIRED set to 'Y', but that does not mean that the course has always been required for the trainee.
  • The LMS_REGISTRATION table stores one record per registration request (REG_REC_ID), which specifies a trainee (USER_REC_ID) and a course iteration (ITERATION_ID).
    • Whether trainees register themselves for course iterations, or a Knowledge Link Administrator sets up the registrations for them, there may be multiple records for a given trainee and course. For example,
      • a trainee may have registered for more than one iteration of the course.
      • the trainee may have asked to enroll in the same course iteration more than once. This is commonly the case for Web-based courses that trainees are required to re-take periodically.
    • The LMS_REGISTRATION table includes Yes/No flags to facilitate selecting the record you want from a set of records for a given trainee and course iteration, or from a set of records for a given trainee and course.
      • CURRENT_CRS_ITERATION_REG_FLAG is 'Y' if the record is the one that reflects the trainee’s latest registration status with regard to the course iteration (ITERATION_ID).
      • CURRENT_COURSE_REG_FLAG is 'Y' if the record is the one that reflects the trainee’s latest Enrolled or Wait-Listed registration for the course (CO_MAS_ID). Note: if the trainee does not have an Enrolled or Wait-Listed registration for the course, there is no record for the trainee and course that has CURRENT_COURSE_REG_FLAG = 'Y'.
      • LAST_COURSE_COMPLETION_FLAG is 'Y' if the record is the one with information on the trainee’s latest completion of the course. Note: if the trainee has never completed an iteration of the course, there is no record for the trainee and course that has LAST_COURSE_COMPLETION_FLAG = 'Y'.
      • LAST_CRS_COMPL_OR_REG_FLAG is 'Y' if the record is the one that is reflected in the LMS_CURRENT_TRAINEE_CRS_STATUS record for the trainee and course. Note: if the trainee does not have an Enrolled or Wait-Listed registration for the course, and has never completed an iteration of the course, there is no record for the trainee and course that has LAST_CRS_COMPL_OR_REG_FLAG = 'Y'.
  • There are several data elements that provide information about the trainee and course for which the trainee has registered:
    • The USER_REC_ID and the PENN_ID each uniquely identify the trainee.
      • The USER_REC_ID is the number used within the Knowledge Link system as the unique identifier for the trainee. Some tables in the Data Warehouse's Learning Management data collection store data about Knowledge Link users but do not store the users' Penn IDs. USER_REC_ID enables the LMS_REGISTRATION table to be joined to those tables.
      • The PENN_ID is an 8-digit identification number assigned to the trainee by the Penn Community system. No two persons have the same Penn ID. Most tables in the Data Warehouse use PENN_ID as the unique identifier for a person. This facilitates joining tables with data that pertains to people, regardless of whether those tables are in the same data collection.
    • The LMS_REGISTRATION table stores the trainee's FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME. Further information about the trainee is available in the LMS_PERSON table. The LMS_REGISTRATION table may be joined to the LMS_PERSON table by USER_REC_ID or by PENN_ID.
    • The REGISTRATION_BU_NUMBER, REGISTRATION_BU_ID, and REGISTRATION_BU_NAME provide information on the business unit that, at the time of the registration request, included the trainee (USER_REC_ID) and had the course version (CO_REC_ID) assigned to it. A trainee cannot register for an iteration of a course version if that course version does not have an active assignment to a business unit that includes the trainee. A business unit (also known as a BU, role, Knowledge Node, or KNode) is a set of one or more Knowledge Link users. Business unit X includes a trainee if that trainee is actively assigned to X (see the KNODE_USER_JOIN table) or to a business unit that that falls below X in the business unit hierarchy. (For more information on the business unit hierarchy, see the documentation on PARENT_ID and TYPE in the KNODE table.)
    • The ITERATION_ID is the number used within the Knowledge Link system as the unique identifier for the course iteration for which the trainee has registered. The COURSE_ITERATION_TITLE is name of the course iteration. It is common for more than one course iteration to have the same value for COURSE_ITERATION_TITLE. To retrieve the other attributes of the course iteration, the LMS_REGISTRATION table may be joined to the COURSE_ITERATION table by ITERATION_ID.
    • The CO_REC_ID is the number used within the Knowledge Link system as the unique identifier for the course version presented in the course iteration. To retrieve the attributes of the course version, the LMS_REGISTRATION table may be joined to the COURSE_OBJECT table by CO_REC_ID.
      • The COURSE_OWNER_BU_NUMBER, COURSE_OWNER_BU_ID, and COURSE_OWNER_BU_NAME provide information on the business unit that owns the course version.
        • The owner of the course version is one of the criteria used to determine what course versions (if any) are included on the list of course versions that a given user can assign. For more information, see the data element documentation for COURSE_OBJECT.KNODE_ID.
        • Knowledge Communities (KC) and Knowledge Microcommunities (KMCs) are the types of business units that are course version owners. The KCs that own course versions correspond to University orgs. or to University of Pennsylvania Health System (UPHS) entities (process levels) or departments (cost centers or accounting units). A KMC identifies a group of trainees from unrelated UPHS departments. (For further information on business units, see the documentation for the KNODE table. The business unit hierarchy is described in the documentation for PARENT_ID in the KNODE table.)
      • Note: CO_REC_ID_AS_IS stores the REGISTRATION.CO_REC_ID for this record (REG_REC_ID). CO_REC_ID_AS_IS might not accurately reflect the course version offered in the course iteration (ITERATION_ID).
    • The COURSE_MASTER_ID and the CO_MAS_ID each uniquely identify the course that includes the course iteration (ITERATION_ID) for which the trainee (USER_REC_ID) has registered. Most Knowledge Link users are familiar with the COURSE_MASTER_ID (such as UNIV_ALL_ALL_PROFILER). CO_MAS_ID is the number (such as 12437) that identifies the course within the Knowledge Link system, and is used to join tables with course information. To retrieve the other attributes of the course, the LMS_REGISTRATION table may be joined to the LMS_COURSE_MASTER table by COURSE_MASTER_ID or by CO_MAS_ID.
      • Note: CO_MAS_ID_AS_IS stores the REGISTRATION.CO_MAS_ID for this record (REG_REC_ID). CO_MAS_ID_AS_IS might not accurately reflect the course to which the course iteration (ITERATION_ID) belongs.
  • The status of the trainee's registration request for the course iteration is stored in REGISTRATION_STATUS_DESC and REGISTRATION_STATUS_CODE. REGISTRATION_STATUS_DESC (such as 'Enrolled') is useful for displaying in reports. If you know the REGISTRATION_STATUS_CODE values (for example, 39 means 'Enrolled'), you might prefer to use it in your record selection condition statements.
    • REGISTRATION_STATUS_REASON stores comments about the registration request.
      • The value is usually null, but when comments are stored, they may describe any characteristic of the registration request (such as how the record for the request was created); they do not necessarily have to do with the registration status.
      • Note: the value for REGISTRATION_STATUS_REASON might not describe the registration request in its current state. For example, some records have a value for REGISTRATION_STATUS_REASON that begins with 'Completed' but have COURSE_COMPLETED = 'N'.
    • When the status of the trainee's registration request for the course iteration is Wait-Listed, the number stored in WAIT_LIST_PRIORITY indicates what priority the trainee has for becoming enrolled in the course iteration if another trainee drops the course iteration. If WAIT_LIST_PRIORITY = 0, the trainee will become enrolled the next time someone drops the course iteration. Note:
      • Wait lists are kept only for instructor-led courses. (The code identifying the distribution mode is the COURSE_OBJECT.DIST_MODE where LMS_REGISTRATION.CO_REC_ID = COURSE_OBJECT.CO_REC_ID.)
      • In some records, WAIT_LIST_PRIORITY is null even though the registration status is Wait-Listed.
      • In some records, WAIT_LIST_PRIORITY is not null even though the registration status is not Wait-Listed.
  • The LMS_REGISTRATION table stores COURSE_COMPLETED, which indicates whether the trainee has ('Y') or has not ('N') completed the course iteration (ITERATION_ID) for which the trainee has registered per this registration request (REG_REC_ID).
    • When a trainee has enrolled in a curriculum, and has completed all of the courses in the curriculum, Knowledge Link automatically updates the record for the trainee and curriculum, setting the COURSE_COMPLETED to 'Y'.
    • A trainee may legitimately be on record as having completed the course iteration even if the registration status is not Enrolled.
    • If COURSE_COMPLETED is set to 'Y', DATE_COURSE_COMPLETED indicates when the trainee completed the course iteration for which the trainee has registered per this registration request.
      • Note: some records have a non-null value for DATE_COMPLETED, even though COURSE_COMPLETED = 'N'. If COURSE_COMPLETED = 'N', DATE_COMPLETED should be ignored.
    • TIME_COURSE_COMPLETED is a string indicating how much time the trainee has spent on taking the course iteration per this registration request.
      • For instructor-led courses, TIME_COURSE_COMPLETED indicates how much time it took the trainee to complete the course iteration. For Web-based courses, it indicates how much time the trainee has spent on taking the course iteration so far, regardless of whether or not the trainee has completed the course iteration. (The code identifying the distribution mode is the COURSE_OBJECT.DIST_MODE where LMS_REGISTRATION.CO_REC_ID = COURSE_OBJECT.CO_REC_ID.)
      • TIME_COURSE_COMPLETED is null for many records. When it is not null, the value may represent the amount of time in hours (when the value includes a decimal point) or the time in hours, minutes, and seconds (when the value is in the format HH:MM:SS).
      • Note:
        • TIME_COURSE_COMPLETED values may be formatted inconsistently. For example, some records for a particular ITERATION_ID have '0.25' for TIME_COURSE_COMPLETED, while other records for that same course iteration have '.25'.
        • In some cases, TIME_COURSE_COMPLETED is null, even though COURSE_COMPLETED is 'Y'.
    • If the course offered in the course iteration is currently required for the trainee, and the trainee has completed the course iteration, and is not overdue to re-take the course, the trainee is compliant with regard to the course. For compliance information regarding the trainee and course, see the LMS_CURRENT_TRAINEE_CRS_STATUS table.
      • Note: NEXT_COURSE_DATE currently is not used, even when its value is not null. See LMS_TRAINEE_COURSE_ASSIGN.DUE_DATE or LMS_CURRENT_TRAINEE_CRS_STATUS.DUE_BY_DATE.
  • The LMS_REGISTRATION table stores DATE_REGISTRATION_CREATED, DATE_REGISTRATION_MODIFIED and DATE_REGISTRATION_DROPPED for each registration request (REG_REC_ID). Use these dates with caution, if at all.
    • The values for these dates might or might not include the time. Business Objects users that wish to select records based on one of these dates--say, records that were created on 7/21/2008--should include a query filter stipulating that DATE_REGISTRATION_CREATED is greater than or equal to 7/21/2008 and DATE_REGISTRATION_CREATED is less than 7/22/2008. (The system will supply the time--12:00:00 AM--for both dates.)
    • DATE_REGISTRATION_DROPPED may have a non-null value even if the registration status is not Dropped. This might be because the status changed after it became Dropped. When a trainee (USER_REC_ID) decides to cancel his or her registration for a course iteration (ITERTATION_ID), he or she drops the course in Knowledge Link. The record for the current registration request for the trainee and course iteration (CURRENT_CRS_ITERATION_REG_FLAG = ‘Y’) has its registration status changed to Dropped. However, an instructor may later override the registration status (often to change it to Enrolled).
    • The values for these dates might not be reliable. Some records
      • have a value for DATE_REGISTRATION_CREATED that is later than the value for DATE_REGISTRATION_MODIFIED.
      • have a value for DATE_REGISTRATION_CREATED that is later than the value for DATE_REGISTRATION_DROPPED.
      • have a value for DATE_REGISTRATION_DROPPED that is later than the value for DATE_REGISTRATION_MODIFIED.
      • have a null value for DATE_REGISTRATION_DROPPED even though their registration status is Dropped.
  • The list of related tables shown above includes some that may be joined to the LMS_REGISTRATION table only with caution, if at all. For example,
    • PKG_CRS_JOIN may be joined to LMS_REGISTRATION based on CO_REC_ID or CO_MAS_ID, but the fact that an LMS_REGISTRATION record has a corresponding PKG_CRS_JOIN record does not mean that the course was assigned to the trainee as part of a package.
    • PROFILER_ASSIGNMENTS may be joined to LMS_REGISTRATION where the PENN_IDs match and the COURSE_ID matches the COURSE_MASTER_ID, but the fact that Profiler assigned the course to the trainee does not necessarily mean that the trainee registered for the course because of that assignment. (You might want to compare DATE_FED_TO_LMS to DATE_REGISTRATION_CREATED.) Also, if the only conditions are on the trainee and the course, PROFILER_ASSIGNMENTS and LMS_REGISTRATION have a many-to-many relationship; a query on the joined tables would get a Cartesian Product warning.

Source

Knowledge Link, the learning management system used by the University and by the University of Pennsylvania Health System (UPHS) since March, 2005.

Knowledge Link enables authorized administrators to schedule training courses, assign resources to courses, assign groups of Knowledge Link users to courses, and to create online courses. It enables trainees to find out what courses have been assigned to them, to register for those courses (whether those courses are on-line or instructor-led), to take on-line courses, and to provide feedback via course evaluations. Knowledge Link was first used to track information on training required by regulation, but it now tracks information on a variety of training courses.

LMS_REGISTRATION Table - Data Element Index    Tables and Data Elements   Learning Management 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