LMS_LOCATION Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home

LMS_LOCATION Table

DWLMS Schema

Explanation
A reference table with current data on the places where instructor-led course iterations are taught. The LMS_LOCATION table has one record for each room where instructor-led course iterations are taught, and includes information about the training room (such as the address, a description of the room, and its seating capacity), the contact person for the location, and the location's status.

A course is a formal educational unit that deals with a particular subject. A course provides training to members of the Penn Community in support of their academic activities and job responsibilities at the University and/or at the University of Pennsylvania Health System. A course may have more than one version. For example, if the course covers Software Package X, version 1 of the course might cover Software Package X release 2.0, and version 2 of the course might cover Software Package X release 3.5. A course iteration is an offering of a particular version of the course. A course version may have more than one course iteration. For example, version 2 of the instructor-led course on Software Package X will have two course iterations if it is offered in two different places on the same date and at the same time.

Common Uses

• Displaying training room information, rather than the code for the location that appears in a related table. ("Don't show the code in the report. Show the facility and room instead.")
• Retrieving information on training rooms based on the building where they are located, the classroom type, or the seating capacity.
• Getting a list of active locations, to assist people who are scheduling instructor-led course iterations.
• Identifying the contact person for a classroom.
  
  

LOCATION_ID

LOCATION_ID
LOCATION_OWNER

COURSE_ITERATION
KNODE
KNODE_CRS_JOIN
KNODE_USER_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_DEFINITION
UPHS_RAW_DATA

Cautions

• The LMS_LOCATION table stores only the current information for a training location. Historical information for a location is not available. For example, if a training room was located in the Nursing Education Building, and had a seating capacity of 100, but the building's name has been changed to Claire Fagin Hall, and (after renovations) the room now seats 75, there is no way to tell from the LMS_LOCATION table that the building was ever called the Nursing Education Building, or that the room ever seated 100.
• The LOCATION_ID uniquely identifies each place where instructor-led course iterations are taught. LOCATION_ID usually refers to a room (ROOM_NAME) in a building (FACILITY_NAME) where a course may be presented to a group of trainees. When an instructor-led course is held on the Web, ROOM_NAME is 'At Your Desk', FACILITY_NAME is 'Virtual via Live Meeting', and CITY is 'Webcast'.
• The LOCATION_STATUS indicates whether the location is active ('A') or inactive ('I') in the Knowledge Link system. When current and future instructor-led course iterations are scheduled, their scheduled locations ought to be active.
• The name of the building where the training room is located is available for every location, and is stored in mixed case as the FACILITY_NAME (for example, 'Institute For Contemporary Art (ICA)'). However, information about training rooms varies when it comes to other details about where the training room can be found.
  • Almost every location has a ROOM_NAME (for example, '240', 'Gittis Classroom 1', or '7 Penn Tower'). Although a few locations also have a ROOM_NUMBER (for example, 'L-FH AUD' or '709'), for most locations, the ROOM_NUMBER is null. Often, it may be sufficient to retrieve only the ROOM_NAME, but, in some cases, you might want to retrieve both the ROOM_NAME and the ROOM_NUMBER. Note:
    • despite its name, the ROOM_NUMBER stores a character string, not a number.
    • the values for both the ROOM_NAME and the ROOM_NUMBER are stored in mixed case.
  • As of this writing, about 1 location in 5 has information for ADDRESS_1 (the street address; for example, '118 South 36th Street'). Even fewer locations have information for ADDRESS_2 (the floor number or suite number; for example '1st Floor'). Note that the values for both ADDRESS_1 and ADDRESS_2 are stored in mixed case. The values may be inconsistent. For example, ADDRESS_1 for one training room has '3930 Chestnut Street', but for another training room at the same address, it is '3930 Chestnut'.
  • Almost every location has information for the CITY and COUNTRY. There are a number of different CITY values, but when the value for COUNTRY is not null, the value means "United States of America." Note that the values for both CITY and COUNTRY:
    • are stored in mixed case
    • are stored inconsistently. For example,
      • instead of 'Philadelphia', some records have 'philadelphia', 'Phialdelphia', or some other value
      • instead of 'USA', some records have 'UAS', 'United States', or some other value
  • Almost every location has 'NJ' or 'PA' as the STATE, but one (with the FACILITY_NAME 'Centra Server') has '-' as its STATE.
  • As of this writing, about 1 location in 5 has information for ZIP (the postal ZIP code).
  • The values for ZIP can be inconsistent. For example, instead of '19104', some records have ' 19104' or '198104'.
  • The use of the ZIP + 4 (for example, '19104-6228') is recommended for University mail, because the last 4 digits is the same as the University's intramural mail code for the address. However, LMS_LOCATION records whose ZIP value is a valid ZIP code store a 5-digit number.
• Regarding the information on the location's features:
  • Almost every location has a ROOM_TYPE (for example, 'Class' or 'PC-Lab'). The ROOM_TYPE_DEF_ID is also stored. If you know the numeric room type codes, you might prefer to select records using the ROOM_TYPE_DEF_ID (for example, 60 or 64) rather than using the ROOM_TYPE.
    • The ROOM_TYPE in the LMS_LOCATION table is the NAME in the LMS_DEFINITION table where LMS_LOCATION.ROOM_TYPE_DEF_ID = LMS_DEFINITION.DEF_ID.
    • The numeric room type codes are the DEF_IDs in the LMS_DEFINITION table where TYPE = 'ROOM_TYPE'.
  • Very few locations have information for ROOM_DESC. However, anyone who assigns locations to course iterations ought to read the ROOM_DESC information for a location before scheduling a class to be held there.
  • Although every location has a value for SEAT_CAPACITY, in some cases, that value is 0 (even if the location is active).
• Information on the contact persons for locations is spotty.
  • About 1 location in 7 has information on its contact person, but the name given is not necessarily the name of an individual. The CONTACT_NAME is stored in mixed case (for example, 'Marcy', 'Pat Whitby', or 'VPUL Room Request website').
  • If the location has information on its contact person, it almost always includes a phone number, fax number, and/or e-mail address. However, in some cases, the only contact information is the CONTACT_NAME.
    • There is a CONTACT_PHONE for almost every CONTACT_NAME, but the format of the CONTACT_PHONE is inconsistent. For example, it could have the format '(215) 898-5000', '215-898-5000', or some other format.
    • About 1 CONTACT_NAME in 4 has a CONTACT_FAX. The format of the CONTACT_FAX is inconsistent. For example, it could have the format '215.898.5000', '215-898-5000', or some other format.
    • Most CONTACT_NAMEs have a CONTACT_EMAIL, but the value is not stored in a consistent format, and, in some cases, is not an e-mail address. For example, it could have a format like 'BFRANKLIN@GSE.UPENN.EDU', 'clara.barton@uphs.upenn.edu', '215-898-5000', or some other format.
• The LOCATION_OWNER is the KNODE_ID of the business unit that owns the location. The business units that are location owners are at or near the top of the business unit hierarchy--for example, the Trustees of the University of Pennsylvania. You can retrieve information about this business unit from the KNODE table, where LMS_LOCATION.LOCATION_OWNER = KNODE.KNODE_ID. Documentation on the KNODE table is available here.
  • If you need to retrieve information on the locations owned by a given business unit, and that business unit has other business units below it in the hierarchy, select records from LMS_LOCATION and LMS_BUSINESS_UNIT_GROUP where the business unit group in the LMS_BUSINESS_UNIT_GROUP table is the one that is of interest, and LMS_LOCATION.LOCATION_OWNER = LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_MEMBER_NUMBER. Documentation on the LMS_BUSINESS_UNIT_GROUP table is available here.
  • Although you could join the LMS_LOCATION table to the KNODE_CRS_JOIN table (where LMS_LOCATION.LOCATION_OWNER = KNODE_CRS_JOIN.KNODE_ID), the need for such a join is unlikely. KNODE_CRS_JOIN tracks which Knowledge Link courses are assigned to which business units. While a given business unit owns a location, a given course need not be assigned to that business unit in order to be offered at that location.
  • Although you could join the LMS_LOCATION table to the KNODE_USER_JOIN table (where LMS_LOCATION.LOCATION_OWNER = KNODE_USER_JOIN.KNODE_ID), the need for such a join is unlikely.
    • KNODE_USER_JOIN tracks which Knowledge Link users are assigned to which business units, but that information will not help you identify the contact person for the location.
    • While a given business unit owns a location, a given trainee need not belong to that business unit in order to attend a course iteration at that location.

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