Penn Computing

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

LMS_DEFINITION Table

DWLMS Schema

Explanation
A reference table storing sets of codes used to specify various attributes of the records stored in the Knowledge Link system. The DEF_ID may be used to link a code in another table to its decoded value in the LMS_DEFINITION table. For example, given a COURSE_ITERATION record that has a STATUS code of 51, one can retrieve the LMS_DEFINITION record where DEF_ID = 51, and find that the decoded value is 'Closed'. There is one record per code (DEF_ID).

Common Uses

  • Displaying the text for an attribute, rather than the code for the attribute that appears in a related table. ("Don't show the codes in the report. Show the English instead.")

Primary Key Indexed Data Elements Related Tables
DEF_ID
DEF_ID
TYPE
COURSE_ITERATION
COURSE_OBJECT
GEN21_USER
KNODE
KNODE_CRS_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_BUSINESS_UNIT_TYPE
LMS_COMPLIANCE
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_CURRENT_TRAINEE_CRS_STATUS
LMS_GOVERNING
LMS_LOCATION
LMS_REGISTRATION
LMS_REGISTRATION_STATUS
LMS_TRAINEE_BU_COURSE_ASSIGN
PKG
REGISTRATION
TEST
TRACK_TEST
UPHS_COST_CENTER
UPHS_RAW_DATA
USER_COURSES


Cautions

  • Not all coded attribute values are included in the LMS_DEFINITION table. Many tables use 0 ('Inactive') and 1 ('Active) as STATUS values, but these pairs of coded/decoded attribute values are not stored in the LMS_DEFINITION table. (The table has no record where DEF_ID=0, and its record where DEF_ID=1 is not applicable as a STATUS value.)
  • The LMS_DEFINITION table stores only the current information for a record attribute code. Historical information for a code is not available. For example, if DEF_ID 201 used to mean 'Open', but now means 'Faculty Only', there is no way to tell from the LMS_DEFINITION table that the code ever meant 'Open'.
  • The DEF_ID stores the numeric code for a record attribute. The NAME stores the decoded value for the attribute. The DESCRIPTION stores a character string that clarifies the decoded value.
    • Every DEF_ID has a non-null NAME; some also have a non-null DESCRIPTION. In some cases, when retrieving the decoded value of a coded attribute in a report, it might be better to retrieve the DESCRIPTION than to retrieve the NAME. For example:
      • given a COURSE_ITERATION record that has a STATUS code of 51, one can retrieve the LMS_DEFINITION record where DEF_ID = 51, and find that its NAME -- the decoded value -- is 'Closed' and its DESCRIPTION is null. In this case, the decoded value to use is the NAME.
      • given a GEN21_USER record that has a STATE code of 75, one can retrieve the LMS_DEFINITION record where DEF_ID = 75, and find that its NAME -- the decoded value -- is 'CO' and its DESCRIPTION is 'Colorado'. In this scenario, if you a delivering a report to someone who is not familiar with the two-character postal abbreviations for the states in the U.S., you might decide to use the DESCRIPTION rather than the NAME.
    • There can be more than one DEF_ID with the same value of NAME. For example, DEF_IDs 50 and 100050 both indicate a course iteration status of 'Active'. (As of this writing, the GEN21_USER table uses 50 (not 100050) as a STATUS value.)
    • There can be more than one DEF_ID with the same value of DESCRIPTION. For example, DEF_IDs 75 and100075 both indicate the state of 'Colorado'. (As of this writing, the COURSE_ITERATION table uses 75 (not 100075) as a STATE value.)
    • If you are unfamiliar with the code values used for a record attribute in a given table, and you want to select records from that table based on a coded attribute, your might prefer to join that table to LMS_DEFINITION and select records based on the decoded value for the attribute. For example, you might select records from COURSE_ITERATION where COURSE_ITERATION.STATUS = LMS_DEFINITION.DEF_ID and LMS_DEFINITION.NAME='Active'.
  • The STATUS indicates whether the DEF_ID is active (1) or inactive (0) in the Knowledge Link system. Not all of the all of the active DEF_ID values are used in the Data Warehouse's Learning Management collection. For example, both 50 and 100050 are active DEF_IDs as of this writing, and 50 is used in the COURSE_ITERATION table, but 100050 is not used in any table in the data collection besides LMS_DEFINITION.
  • The TYPE identifies the set of codes to which the DEF_ID value belongs. For example, DEF_IDs 50 and 53 both have a decoded value (NAME) of 'Active', but 50 is used to indicate the status of a course iteration (TYPE = 'CI_STATUS'), while 53 is used to indicate the status of a profile (TYPE = 'PROFILE_STATUS'). (Knowledge Link profile information currently is not stored in the Data Warehouse.)
    • Because the DEF_ID uniquely identifies an LMS_DEFINITION record, you need not specify the TYPE when using the LMS_DEFINITION table to get the decoded value of a coded attribute. Simply specify WHERE TableWithCode.Code = LMS_DEFINITION.DEF_ID
    • Note: if a given DEF_ID value is an active code value of the desired TYPE, that does not necessarily mean that it is the code value for the records that you want to select. If you are unfamiliar with the code values used for a record attribute in a given table, and you want to select records from that table based on a coded attribute, your might prefer to join that table to LMS_DEFINITION and select records based on the decoded value for the attribute. For example, you might select records from COURSE_ITERATION where COURSE_ITERATION.STATUS = LMS_DEFINITION.DEF_ID and LMS_DEFINITION.NAME='Active'.
  • Some TYPEs have DEF_IDs grouped by sub-type--the CATEGORY. The value of CATEGORY is often null. For example, given a KNODE record that has a TYPE code of 160, one can retrieve the LMS_DEFINITION record where DEF_ID = 160, and find that its NAME -- the decoded value -- is 'Knowledge Position'. In this example, the LMS_DEFINITION table's TYPE for 160 is 'KNODE_TYPE', and its CATEGORY (subtype) is 'KPOS'.
    • Note: if a given DEF_ID value is an active code value of the desired TYPE and CATEGORY, that does not necessarily mean that it is the code value for the records that you want to select. If you are unfamiliar with the code values used for a record attribute in a given table, and you want to select records from that table based on a coded attribute, your might prefer to join that table to LMS_DEFINITION and select records based on the decoded value for the attribute. For example, you might select records from COURSE_ITERATION where COURSE_ITERATION.STATUS = LMS_DEFINITION.DEF_ID and LMS_DEFINITION.NAME='Active'.
  • Although the LMS_DEFINITION table may be joined to all of the related tables listed above, there are some tables that you might not want to join to it, because they already store the decoded values of the coded attribute(s). Those tables are:
    • LMS_BUSINESS_UNIT_GROUP
    • LMS_BUSINESS_UNIT_TYPE
    • LMS_CURRENT_CRS_ITER_REG_INSTR
    • LMS_CURRENT_TRAINEE_CRS_STATUS
    • LMS_LOCATION
    • LMS_REGISTRATION
    • LMS_REGISTRATION_STATUS
  • The LMS_DEFINITION table includes some data elements that are not meant to be used in reports, or are not used at all. Those data elements are:
    • BITMAP
    • SPECIAL_ID

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 trainees 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_DEFINITION 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