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:
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.
Questions about this page? Email us at da-staff@isc.upenn.edu
|
