EHS Training Requirements and Records Component ----------------------------------------------- (Jim Repa - written in October, 2003. Last modified 10/4/2005) - - - A. Summary: What does the Training Requirements and Records component do? In a nutshell, this component collects data related to people's EHS training requirements, determines people's training requirements from a set of rules, and reports on completed and uncompleted training. Below is a summary of features and functionality of this component. 1. Training Needs Assessment (TNA) Provides an interface for members of the MIT community to fill out a Training Needs Assessment, and to view a real-time summary of their completed and uncompleted training requirements, with web links to take them to (1) web-based training or (2) registration for classroom courses. The TNA provides an interface for people to specify the following: a. Their DLC(s) - picked from a list b. Their PI(s) or supervisor(s) - picked from a list c. Their EHS Activities - picked from a list 2. Maintenance of training rules Provides an interface for EHS Administrators to maintain: a. EHS Activities (i.e., the list of checkable activities seen by users in the Training Needs Assessment) b. Groups of training requirements known as "Certification Types" c. Courses and other requirements (both generic course equivalents and their specific options [web/classroom/other, initial/refresher]) d. The rules for who needs to complete a given Certification Type e. The rules for what courses or other requirements must be completed for a given Certification Type f. A list of PIs or supervisor not found in the PI Space Registration Database There is a separate "staging area" that allows administrators to maintain a set of changes (for items a-e), and put the set into production all at once. 3. Importing of supporting data from other systems Collects supporting data from other sources (mostly via the Warehouse), including a. Directory information for each active employee, student, or other person with a Kerberos username at MIT - ENHANCEMENT: Implement a mechanism for smoothly handling people whose Kerberos username has been added or changed b. A list of job titles (used for training rules) c. A list of DLCs d. A list of PIs or Supervisors from the PI Space Registration Database (used to provide the default pick-list of PIs for the TNA) e. A longer list of possible PIs from financial data (used to provide a longer pick-list of PIs for the TNA) f. ENHANCEMENT: A list of academic course subjects g. ENHANCEMENT: A list of currently enrolled students in specific academic subjects with a laboratory component h. List of people's completed web courses from Traincasters i. List of people's completed classroom courses from the SAP TEM module on the MIT campus j. List of people's completed classroom courses from the SAP TEM module at Lincoln Lab k. List of PIs associated with Room Sets within the PI Space Registration system; these PIs are included in the "short list" of PIs pickable from the TNA (along with the non-registered PIs maintained by the interface described in item 3.f). 4. Calculation of users' training requirements Determines training requirements for each user, in a nightly batch job for all users, and in real time for each user who completes or modifies his/her Training Needs Assessment. (Real-time recalculation is also done when an administrator runs an individual training report for a person, which is Training Report 2 in the current system.) Determination of training requirements for each user is done via a multi-step process: a. Collect the "triggers" for training requirements for the user, including the user's DLCs, Job Title, and self-reported EHS Activities. ENHANCEMENT: Add triggers for PIs and academic courses. b. Based on the user's triggers and the "who needs training" rules, identify certification types that must be completed by the user c. Based on the rules for fulfilling requirements for a certification type, identify courses and other requirements that must be completed d. Record information on the user's triggers, completed and uncompleted certification types, and completed and uncompleted courses 5. Exporting data to Traincasters Sends data to the web course delivery system (Traincasters), some of it nightly in batch files, and individual user data in real time (via hidden web form variables) to provide a smooth interface with this system. The data include completed or required-but-uncompleted web courses, and directory-type data (e.g., person's name, Email address, DLC affilition). 6. Training reports a. Web-based training reports Provides some basic web-based reports on completed and uncompleted courses i. For all trainees associated with a given DLC (Association with a DLC is usually self-reported. In the case of users who have not done the TNA but whose training is based on their job title, the association with the DLC is based on information from HR.) ii. For an individual trainee iii. For all trainees associated with a given PI iv. ENHANCEMENT: Allow web-based report to be organized by a "protocol" or "authorized project" v. Allow reporting on archived training data for people who are no longer active at MIT. b. Facilitating reporting via MIT's Data Warehouse Makes data available to MIT's Data Warehouse (via specially-constructed views) so that the Warehouse can upload training data and support more versatile reporting 7. Uploading of completed courses Provides a web-based interface for administrators to upload data on completed courses or other requirements. (This functionality is currently supported by EHSWEB; it would logical to support it from the SAP TEM module instead.) 7.5. Administrators' ability to set a DLC, PI, and/or EHS Activities on behalf of another user Provides a way for specially authorized people (EHS Coordinators or others specifically authorized in the Roles DB for a given DLC) to set or remove another's DLC or PI affilation. Also, for a smaller set of specifically authorized people, provides the ability to set or change EHS Activities settings for others within the DLC; this can be limited the ability to make these settings only on behalf of PIs associated with the DLC. Keeps an audit trail of who has set DLCs, PIs, and EHS Activities so that the history of changes made to these settings can be easily viewed for a given person. 8. Roles DB control of access to the system a. Controls reporting access to training records via the Web and the Warehouse; also facilitates automatic reporting authorization for PIs and EHS Representatives based on the PI that trainees have self-reported b. Controls who can update courses, self-reported functions, certification types, training rules, and the list of PIs c. Controls who can upload data on users' completed courses via the web interface 9. Email notification for uncompleted courses The system sends Email notification to trainees for uncompleted or expired courses, on the following dates a. 30 days after the system has determined that a new course is required (usually, 30 days after the user takes the TNA or specifies an additional self-reported EHS function) b. 30 days before a previously-taken course is due for renewal c. 7 days before a previously-taken course is due for renewal d. 7 days after a previously-taken course is past due for renewal 10. ENHANCEMENT: "Authorized projects" or "Protocols" Support centrally maintained list of "authorized projects", along with their PIs, hazardous materials by room, and list of researchers. Allow a person's participation in one of these "Authorized projects" or "Protocols" to be a trigger for training rules. 11. Archival of old data Saves daily snapshots of training data in a set of archive tables. Moves people no longer at MIT out of the active tables after they no longer have an active Kerberos username and are no longer marked as current employees or students. Provides a lookup mechanism for identifying users left MIT. Provides the ability to run an individual training report (similar to the EHSWEB Training Report 2) for a given archived user as of a given date in the past. 12. Special Email notification for Lincoln Lab (or any other list of designated DLCs) Identifies any person affiliated with Lincoln Lab who has had changes in completed or required training over the last 24 hours, and sends an Email notification to a specified mailing list itemizing these people. B. Triggers, certification types, and training requirements In this section, we will discuss in more detail the various data types that are involved in determining training requirements. B.1. Certification Types Training requirements are organized around Certification Types. A Certification Type represents a group of training requirements that must be completed by a person in order to be qualified to do a certain kind of work or to work with or around certain materials, equipment, or hazardous conditions. Each certification type has one or more "triggers", i.e., things that "trigger" a person's need to complete the certification type. Currently, triggers can be self-reported EHS functions, job titles, or affiliation with a given DLC. If at least one trigger for a given certification type applies to a person, then the person must fulfill the requirements of the certification type. Triggers are discussed in more detail in section B.2. One certification type may take priority over other certification types. Certification type priority rules are illustrated by the following example. Suppose we have Certification Type A and Certificate Type B, which have overlapping requirements, but A has more stringent requirements than B. We would define Certification Type A as taking priority over Certification Type B. Then, in cases where a person's triggers indicate that they must fulfill both Certification Types A and B, then A will take precedence over B, and only the requirements for Certification A will apply to the person. Each certification type is associated with one or more "course equivalents". The difference between a "course equivalent" and a "course option" is explained in section B.3 -- for now let us just think of a course equivalent as some sort of requirement that must be completed. To fulfill a certification type, you might need to complete (i) A single course equivalent or (ii) Two or more course equivalents, i.e., you need to complete course equivalent "a" and course equivalent "b". or (iii) Multiple sets of optional sets of course equivalents, e.g., you need to complete course equivalents "a" and "b" and "c" or course equivalents "a" and "d" or course equivalents "d" and "e". In most cases, the requirements are simple (as in (i) or (ii)); in a few cases, there are optional requirements. (Note also that course options, described in section B.3, permits the definition of different course options associated with the same course equivalent.) The data recorded for each certification type includes the following: a. The certification ID number (this uniquely identifies the certification type) b. The name of the certification type c. The "short name" of the certification type (for reports where you want to see an abbreviated name) d. If this certification type takes priority over other certification types, the list of those lower-priority certification ID numbers d. A list of triggers that indicate that this certification type must be fulfilled (see section B.2). Each non-DLC trigger (e.g., self-reported EHS functions or job title) can apply to (i) all DLCs, (ii) only one or more specified DLCs, or (iii) all DLCs except for one or more specified DLCs. Each trigger can optionally have a start date and end date specified, which allows administrators to set triggers that only will apply until a given date, or triggers that will not apply until some date in the future. f. A list of course equivalents that must be completed to fulfill this certification type, or in more complex cases, a set of course equivalents in the form (a) and (b) and ... or (c) and (d) and ... or ... For each course equivalent connected to a certification type, the administrator can indicate dates for which it is applicable, e.g., (i) it will not be applicable until a given date, (ii) it will only be applicable for people who took it before a given date [a sort of "grandfather clause"], or (iii) it will not be applicable until some future date. g. An optional start date and end date for the whole certification type. This allows administrators to indicate that a certification type will only apply until a given date, or it will not apply until some date in the future. B.2. Triggers for certification types As described above, each certification type has one or more triggers, which "trigger" the need for a user to fulfill the certification type. If one or more of the triggers for a certification type apply to a person, then that person must complete the requirements of the certification type. (See also the effects of certification type priorities, section B.4.) Each trigger for a certification type includes a. A trigger type (currently could be "DLC", "Self-reported job function", or "job title") b. A specific item of the type indicated by the trigger type (e.g., a specific DLC code, self-reported EHS function, or job title) c. A code indicating whether the trigger applies to (i) All DLCs (ii) One or more listed DLCs (iii) All DLCs except one or more DLCs d. If the trigger does not apply to all DLCs, then a list of DLC codes referenced by item (c). e. An optional start date and end date. These can be used by the administrator to indicate that the particular trigger will cease to apply, or to postpone putting the trigger into effect until some future date. DLC codes are picked from a list of DLC codes already in the system, which are extracted nightly from a table in the Roles Database that is a precursor to the upcoming Master Department Hierarchy. Job titles are picked from a list of job titles extracted nightly from HR data in the Warehouse. (There is more than one job title stored in HR records per person; the system uses the job title for each person that appears in MIT's online directory.) Self-reported job functions are maintained by EHS Training administrators. See section B.5. Future enhancements may add the following trigger types: a. Academic subjects The subject number for certain lab-related academic courses will be used as triggers for EHS-specific training. Students enrolled in these lab-related academic courses will need to complete a certification type whose requirements include training to help them work safely in the laboratory. b. PIs An identifier of certain PIs (probably the MIT ID number) will be used as a trigger for certification types. Anyone who has self-reported that their PI is one of the specified PIs will be need to complete a given certification type. c. Protocols or authorized projects A "protocol" (biosafety) or RPP authorization is a project under a given PI with specific rooms, materials, people, and other data about the nature of the use of those materials. Protocols and RPP authorizations are currently tracked in MS Access databases in the EHS Office. If these Protocols and RPP Authorizations are tracked in a system linked with the Training System either directly or through nightly data feeds, then a person's participation in a given Protocol can serve as a trigger for a specific certification type tied to the Protocol through training rules. When a trigger refers to a list of DLCs, the trigger is applied as follows: If a person fits the trigger (i.e., job title, self-reported EHS function, etc.) and if the person is affiliated with one of the DLCs, then the trigger is applied. The person is considered to be affiliated with one of the DLCs if (a) he/she has taken the TNA and has self-reported the DLC or (b) he/she has not taken the TNA but is an employee whose directory entry indicates the DLC. When a trigger refers to all DLCs except one or more listed DLCs, the trigger is applied as follows: If a person fits the trigger (i.e., job title, self-reported EHS function, etc.) and if the person is affiliated with at least one DLCs that is not in the list, then the trigger is applied. The person is considered to be affiliated with one of the DLCs if (a) he/she has taken the TNA and has self-reported the DLC or (b) he/she has not taken the TNA but is an employee whose directory entry indicates the DLC. Some triggers apply only to people who have done the TNA and others apply to everyone, even if they have not done the TNA. a. Self-reported EHS functions only exist if the person has done the TNA, so they cannot apply to anyone who has not done the TNA. If someone has taken the TNA on behalf of a person and has set EHS Activities for the person, then this works the same way as it would if the person took the TNA himself. b. DLC triggers only apply to people who have done the TNA. (The system was implemented this way so that administrative employees who do not do the TNA will not be affected by the DLC-based triggers. This may create a problem in the future if we ask all MIT employees, including administrative staff, to do the TNA.) c. Triggers based on job titles apply to all matching employees, whether or not they have done the TNA. For a person who has not taken the TNA, the DLC is set from the person's directory information. The DLC is a master department code, e.g., D_BIOLOGY or D_CCR. There is a mapping maintained in the Roles Database and which will soon be maintained in a new Master Department Hierarchy system which links Student Information Systems course numbers, and HR 8-digit org unit numbers to their corresponding master department code. Thus, the DLC code associated with a student within the department Biology and for an employee within the department of Biology will both be D_BIOLOGY, even though student records show a course number of 7 and HR employee records show an org unit number of 10000429. For non-students and non-employees, and for some students and some employees, no departmental affiliation can be derived from directory information. Once a person takes the TNA, or an administrator takes the TNA on the person's behalf, the information about DLC affiliation from the TNA is considered more reliable and overrides any directory information for the person. B.3. Course equivalents and course options Certification types are associated with one or more requirements. Each requirement may be a course, a medical exam, a signed paper, or something else that needs to be obtained or completed. In the system, a requirement is known as a "course equivalent", even though some of them are not really courses. Each course equivalent must have one or more "course options". Course options are used to define different options for completing a course or other requirement, such as web-based or classroom-based, and they are also used to differentiate between initial and refresher versions of the same basic course. The two-level definition of requirements makes it easier to set up the training rules, i.e., to define the connection between a certification type and a requirement. The rules for completing a certification type are defined as connections between the certification type ID and the course equivalent code, not directly to the course options. Suppose, for example, you have the following course equivalent: 990: Safe handling of dry ice Further, suppose that course equivalent code 990 has the following course options: 990c: Initial classroom dry ice training 990w: Initial web dry ice training 991w: Dry ice refresher training (web) Finally, suppose you want to define the following certification type having dry ice training as a requirement: Cert ID 7777: Handling really cold materials Then you would define course equivalent code 990 as a requirement for certification type 7777. You would not directly connect the course options (990c, 990w, and 991w) to certification type 7777. However, the system would recognize that people required to complete cert ID 7777 would need to start with course option 990c or 990w, and would periodically need to refresh their training with course option 991w. If at some date in the future another course option were added to course equivalent code 990 (i.e., 991c: Dry ice refresher - classroom) then there would be no need to change the rules for cert ID 7777; the system would recognize 991c as a new option for fulfilling cert ID 7777 when course equivalent 990 needs to be refreshed. All course options associated with a course equivalent are considered to be equally applicable to any certification types that have the course equivalent as a requirement. The system does not allow you to say that one course option under a course equivalent is OK for a cert type but not another option under the same course equivalent. If you wanted to do this, then the two course options would need to be attached to two different course equivalents. In other words, if two course options are not really "equivalent" to each other in terms of fulfilling certification requirements, then they should not be considered course options under the same course equivalent. A course option must be attached to one and only one course equivalent. As we have seen, a course equivalent can have one or more course options. Each course equivalent is defined with the following attributes: - a course equivalent code - A course equivalent name - A course equivalent short name (or abbreviated name) - A description - Optionally, the "expire days" number, i.e., a number of days after course completion that the course must be refreshed (If blank, the course never needs to be refreshed.) - A status code (allowing administrators to mark an old course equivalent as being inactive, i.e., kept in the system for historical record-keeping but no longer available as a current course) - Optionally, a list of other course equivalents that are a prerequisite for this course equivalent Under each course equivalent, there are one or more course options. Each course option has the following attributes: - a course option code - a type (currently Web, Classroom, or Other) - A course option name - A course option short name (or abbreviated name) - An optional external course code, which maps the course to the course code number in an external system such as Traincasters - The associated course equivalent code - Optionally, the "expire days" number, i.e., a number of days after course completion that the course must be refreshed (If blank, then the "expire days" number from the related course equivalent applies to this course option.) - A flag indicating whether or not this course option is an initial course - A flag indicating whether or not this course option is a refresher course - A status code (allowing administrators to mark an old course equivalent as being inactive, i.e., kept in the system for historical record-keeping but no longer available as a current course) It is possible for a course option to be both the initial and the refresher course, and it is possible to make the initial course option different from the refresher course option. Also, as stated above, some course options do not need to be refreshed. B.4. Certification type priorities Sometimes, there are two certification types that have similar requirements, but one represents more complete or more stringent requirements than the other. For example, someone who uses chemicals within the Department of Chemistry must complete certification type 10, and someone who uses chemicals within a department other than Chemistry must complete certification type 11. Certification type 10 and 11 have similar requirements except for one course -- there is a more comprehensive course (101) for chemical hygiene required for cert type 10 than the chemical hygiene course (100) required for cert type 11. That means if a person uses chemicals both in Dept of Chemistry labs and in the labs of a different DLC, it should not be necessary to complete both Cert Types 10 and 11: since Cert type 11 has more stringent requirements, Cert Type 11 will suffice for using chemicals in both DLCs. When training administrators recognize this situation, they record that one Cert Type takes precedence over another. In this example, Cert Type 11 would take precedence over Cert Type 10. For a user whose triggers indicate that he/she must complete both Cert Types 10 and 11, the ramifications of the precedence rule are as follows: a. If the user has completed Cert Type 11, then training reports will only show data concerning Cert Type 11 and its requirements, and nothing concerning Cert Type 10. b. If the user has not completed either Cert Type 10 or 11, then training reports will only show data concerning Cert Type 11 and its requirements, and nothing concerning Cert Type 10. c. If the user has completed Cert Type 10 and not Cert Type 11, then training reports will show data on both Cert Types 10 and 11 for the user. In this case, even though Cert Type 11 takes precedence over Cert Type 10, it is useful to show that the person has at least completed the lesser requirements for Cert Type 10, which might be of interest to a DLC other than the Dept of Chemistry. B.5. Self-reported job functions (aka EHS Activities) Self-reported job functions are known as EHS Activities on the TNA. These are the activities that appear next to checkboxes on the TNA; people self-report the activities that apply to them by checking them. EHS Activities are then used as triggers for certification types. EHS Activities are organized into "function groups". The groups are used to help organize and present the list of EHS functions in the TNA. Each EHS Activity belongs to one and only one function group; each function group can have many EHS functions. Each function group has the following attributes: - function group name - description (a long text description of the function group, that does not contain HTML constructs) - web description (a long HTML-format description of the function group, which appears in headers on the TNA - if blank, the description field is used instead) - sort order number (for ordering function groups in the TNA) Each EHS Activity has the following attributes: - function name - description (a long text description of the function, that does not contain HTML constructs) - web description (a long HTML-format description of the function, which appears next to the checkboxes on the TNA - if blank, the description field is used instead) - sort order number (for ordering EHS functions within a function group in the TNA) - function group (the group to which this EHS function belongs) - skip-to-next-group option (if set to "skip", then the TNA indicates that the user should skip to the next function group if he/she has not checked this particular EHS function) - status code (allows the administrator to flag an EHS Activity as "inactive", in order to postpone or phase out its use. An EHS function cannot be deleted if it is referenced as a trigger in a certification type) B.6. Administrators' interface for updating training rules and related items There is an EHS training administrator's web-based interface for maintaining certification types, function groups, EHS functions, course equivalents, course options, and the rules that tie them all together. These objects, described in sections B.1 - B.5, are maintained in two different sets of tables within the system. There is a "staging area", containing one set of tables and a "production area" containing the other. Training administrators can insert, update, or delete objects and rules in the staging area without immediately affecting the TNA or reports running in production. The staging area is designed to allow administrators to work on a batch of changes, taking the time to run some test reports and carefully check for errors. When they are satisfied that the changes are ready for production, they can notify one of the few system administrators authorized to move changes from the staging area to the production area. There is a small number of EHS training administrators authorized to make changes to the training related objects and rules in the staging area. Anyone who has an authorization in the Roles Database to MAINTAIN TRAINING RULES is allowed to insert, update, or delete any certification type, course equivalent, course option, function group, EHS function, or training related rule. There is no partitioning or subdividing of the training rules that limits an administrator's authority to a subset of the rules. Any person with a MAINTAIN TRAINING RULES authorization can change anything. Therefore, it is important that people with this authority be familiar with the workings of the system, and take care whenever they make changes. The only division of labor in training maintenance is between the EHS training administrators who are authorized to MAINTAIN TRAINING RULES and those very few individuals authorized to MOVE TRAINING RULES TO PRODUCTION. Although the latter individuals can do a cursory check of some of the changes made to the staging area, the responsibility of putting in appropriate changes rests with the larger group of training administrators. After changes have been made to certification types, EHS Activities, courses, etc. in the staging area, it is possible for training administrators to run some reports to check their changes. There is a report to simulate how the staging area EHS Activities will appear on the screen for people taking the TNA. There is a version of the TNA summary page that can be displayed for any user in the system based on the new rules in the staging area; this report will show what the person's requirements would be based on production area information about the person (including the person's selections in the production area TNA), but based on the new staging area rules. (If the rule changes involve job titles, the staging area report may not reflect the new rules until the next day; for EHS Activities based rules, changes are reflected immediately in this report.) There is also a version of the DLC-based report for training that can be run to show the training requirements of a whole DLC based on the rules in the staging area. (This report has not been actively maintained and may be out-of-sync with the production report.) When it is time to move the changes to production, the specially authorized system administrator will begin the stage-to-production move by clicking a button on a special web page. The system will identify all differences between the staging and production area, and then apply these changes to the production area. Generally, the application of these new rules to training requirements will not occur until the next regularly-scheduled batch training needs recalculation. Currently, this recalculation occurs once in the evening and again in the early morning hours. (The twice-a-day recalculation occurs because of the complexity of various feeds into the system and related timing constraints.) The recalculation of training needs for all users can take up to 5 hours. There are some restrictions on deleting certain objects from the staging area. There are connections between EHS Activities, course equivalents, and certification types that may prevent an administrator from deleting an object referenced by another. In this case, it is usually possible for the administrator to mark an object as inactive rather than delete it, having the same effect. In some cases, objects must be deleted in the right order -- a course must be deleted before the cert type that references it. The system will prevent the administrator from deleting a course that has been previously taken by a trainee -- in this case, the course must be marked inactive and cannot be deleted. B.6.a. Interface for updating "extra" PIs and supervisors There is also a web interface for maintaining a list of PIs and supervisors associated with DLCs. This interface goes directly against production tables; there is no parallel staging area for PIs and supervisors. Users taking the TNA must chose PIs or supervisors from a list, and although most PIs and supervisors are extracted during nightly feeds from PI Space Registration, some of them are not known to the other sources, and must be entered by hand by training administrators. (There is also a "long list" of PIs that includes a somewhat unreliable list of possible supervisors and PIs derived from financial data -- TNA users can click a "show more PIs" button to get the "long list".) C. Details on the web-based Training Needs Assessment The web-based Training Needs Assessment (TNA) is an interface for users to record data related to their DLC(s), PI(s) or supervisor(s), and EHS activities (a.k.a. self-reported job functions). It then calculates the user's training requirements, and displays a summary of uncompleted and completed courses. There are two modes of operation for the TNA: wizard mode and summary mode. Wizard mode is the default for people taking the TNA for the first time. Summary mode is the default for everyone else. In wizard mode, the user is led through four screens in succession: (1) Verify your directory information (2) Set your Department, Lab, or Center (DLC) (3) Set your Principal Investigator or Supervisor (4) Check your activities that have training implications After the 4th screen, the user finally is brought to a summary screen that shows a summary of information from steps 1 - 4 and then a list of uncompleted and completed courses. In summary mode, the user starts with the summary screen. There are clearly labelled buttons that allow a user to go to (2) the screen to set or modify DLCs (3) the screen to set or modify PIs or supervisors (4) the screen to set or modify EHS Activities On each of these screens, there is a Continue button, which returns the user to the summary screen. Users of the TNA must have an MIT-issued personal x.409 certificate. The system uses information from the certificate to determine the identity of the user. The various screens are described in more detail below: Step 1: Verify your directory information (wizard mode only) The user's name, Kerberos ID, MIT ID number, Email address, and type (employee/student/other) are displayed. (The system looks up the data based on the user's identity in his/her certificate.) The user is instructed to examine the information. Links are provided to more information on how to correct this information in the source systems (e.g., HR or Student Systems). It is known that some users at MIT share computers and may inadvertantly use Netscape or IE after someone else's certificate has been loaded onto the machine. The "step 1" screen also makes it easy for the user to see whose certificate is being used to identify him/her -- if this is based on someone else's certificate, the user will notice this. There is also a link to instructions on what to do if the user sees someone else's name displayed on this page. Step 2: Set your DLC This screen does the following a. Instructs the user that he must self-report all applicable DLCs b. Displays the known DLC or DLCs that the user is affiliated with (either from the user's directory information or from previous self-reporting via the TNA) c. Provides a list of all DLCs at MIT with checkboxes, for the user to add or delete them from his/her personal list of DLCs d. Provides a way for the user to designate his/her "primary" DLC, if there is more than one DLC in the his/her list Previously, a default DLC was set for employees and for students who had declared a major, but on the request of EHS Coordinators, there is no longer a default setting. In wizard mode, if there is no set DLC, the user must specify at least one DLC before continuing to the next step. The "primary" DLC is important for administrators doing reports out of Traincasters, since Traincasters can only record one DLC per person. For training reports are done via EHSWEB or the Warehouse, it is not important to record a primary DLC for each user. Step 3: Set your PI or supervisor This screen does the following: a. Instructs the user that he must self-report PIs and supervisors in charge of laboratories or other facilities where the user works or studies. b. If the user had specified one or more PIs or supervisors while doing the TNA, these are displayed c. Provides a list of PIs or supervisor (see details below) with checkboxes. The user can check or uncheck PIs or supervisors that apply. d. Provides a way for the user to designate his/her "primary" PI or supervisor. When a user first sees this screen, the list of PIs and supervisors is a "short list". The short list is based on the user's self-reported DLCs from the previous step. The list contains a. all supervisors for Room Sets (in the PI Space Registration component) under the user's designated DLCs plus b. all supervisos from the "extra" list of PIs/supervisors for the user's designated DLCs (see section B.6.a). If the user does not see all of his PIs or supervisors on this "short list", he/she can click a button "View more PI/supervisors". After clicking this button, the user will see a list of all of the PIs and supervisors from the short list for all DLCs plus a list of all PIs and supervisors gleaned from financial data. This longer list has many "false positives" and obsolete names -- financial data does not provide a perfect source for people who are PIs or supervisors. However, the longer list does give users a better chance of finding their PI or supervisor if they don't the person on the short list. Step 4: Check your activities that have training implications This screen does the following: a. Gives the user instructions b. Shows a list of EHS Activities, organized by function group, with checkboxes. The EHS Activity description comes from the "web description" field defined above under the attributes for EHS Activities. EHS administrators can include web links to other explanatory web pages within these descriptions. There is a checkbox next to each EHS Activity, which the user checks or unchecks to indicate whether or not he/she performs this activity. Summary page The summary page does the following: a. Shows the user's name, Kerberos ID, Email address, MIT ID and type (student/employee/other) b. Shows the user's DLC(s) with a button to go to another screen to update them c. Shows the user's PI/supervisor(s) with a button to go to another screen to update them d. Show the user's EHS Activities, with a button to go to another screen to update them e. Provides a button to go to take web classes (a link to Traincasters, currently) f. Provides a button to go and register for classroom courses (a link to the web front-end the SAP TEM). This button is not shown for people affiliated with Lincoln Lab. g. Shows a list of uncompleted courses and other requirements h. Shows a list of previously-completed courses and other requirements i. Provides a clickable link to show a history of changes to the user's DLC(s), PI(s), and EHS Activities along with the date of each change and the username of the person who made the change. C.1. Concerns about self-reported DLCs and PIs As we have indicated above, users taking the TNA are asked to self-report their DLC(s) and PI(s). A users' self-reported DLCs and PIs determine which users appear on training reports by DLC or by PI. Having users self-report their DLCs and PIs has the advantage that it does not require EHS Coordinators or other central administrators to do the work of entering and maintaining the data -- users can enter and maintain their own data. However, it depends on having the users correctly self-report their own DLC and PI data. There are instances where a. A user taking the TNA may not yet know who his/her PI(s) will be, or may be confused about which PIs or DLCs to report b. The EHS Coordinator sees people on their reports they are not interested in, or fail to see people they are interested in, due to the way people have self-reported (or failed to self-report) a DLC or PI. We see that the DLCs and PIs that a user has picked are not consistent with each other. Some of the mistakes can be corrected by EHS Coordinators after the fact. It might be worth considering other ways of associating people with DLCs, with PIs or supervisors, or with PIs' authorized projects that do not involve self-reporting, and therefore do not leave as much room for errors or mis-interpretation. This other mechanism might be used to supplement the current self-reported data, rather than replace it. Both approaches have their advantages. Some DLCs may be happy with self-reported data, while others may prefer to centrally record people's affiliation with a project, PI, or DLC. D. Details on data feeds from other sources The TNA and the determination of training requirements could not function smoothly if the system did not have access to data extracted from other systems. The training component maintains "shadow tables" of data from other sources, loaded by automated nightly feed programs. In most cases, the data are loaded by doing extracts from MIT's Data Warehouse. There are exceptions, which are noted below. In most of the automated feed programs, provisions are built into the system for handling errors or inconsistencies. Nightly feeds are generally written as incremental feeds. In other words, each program compares the data already known in the local system to the most recent extract from the external source, and builds a temporary file of inserts, deletes, and updates. Each nightly feed does a "sanity check" based on the number of records in the temporary file. For each feed program there is a MAXCHANGES number (which differs for each data type) -- if the program detects more than that number of changes in a nightly feed, then the changes are not applied, and a warning message is Emailed to the system administrators. This protects the system in the event that the Warehouse or its source providers of data have encountered an error. In this case, rather than loading bad data from the external source, the training system preserves its data from the previous nightly feed. Other error handling involves referenced data. Data in shadow tables are used by the training system for reports and training determination. If there are training rules based on a DLC, a job title, or other data, but the particular referenced records from the external sources are deleted from the external source, the training system cannot simply delete the record from its shadow tables. Instead, they are flagged as "inactive" and manual attention must be taken to resolve the problem. The resolution might involve changing training rules, e.g., if two DLCs get merged, the rules for those DLCs must be adjusted accordingly. In the case of people who have left MIT, the system archives the data and usually removes the person's records from the "active" tables, except in the case of PIs or other people with special roles who are referenced by other tables in the system. For people with special roles, manual intervention might be required to drop all system links to these people when they leave MIT. Handling errors related to changed or missing data from external sources involves the cooperation of (a) the system administrators of the training system, (b) the Warehouse team, and (c) the owners of the source data, such as HR, Student Systems or the Controller's Office. Fortunately, as the system has matured, the frequency of errors from external data sources has diminished. The feed programs that load data from external sources involve the following data: 1. Directory information for each active employee, student, or other person with a Kerberos username at MIT The Warehouse combines data from HR (for employees), Student Systems (for students), and Athena User Accounts (for non-students/non-employees who have a Kerberos userid) into a single view that is used to extract people and their directory data. The data in this view includes the person's DLC (which is sometimes blank), Kerberos username (where it exists), MIT ID number (where it exists), office and home phone, office address, job title (for employees), etc.. The data does not include the person's supervisor; there is currently no central system of record at MIT for people's supervisors - for this reason, the TNA asks people to self-report their PI(s) or supervisor(s). The EHSWEB system uses a person's Kerberos username as the primary key in various tables referring to people. For employees who do not have a Kerberos username, the system creates a pseudo-username as a placeholder. Pseudo-usernames start with a dollar sign ($). The system has a special nightly process that can change all references to a Kerberos username if either (a) a person who did not previously have a Kerberos username obtains one or (b) a person with one Kerberos username drops it and adopts a different one. In this special nightly process, the system processes records for a person with a given MIT ID number where the MIT ID number was previously associated with one Kerberos username (or a pseudo-username) and is now associated with another one, based on tables from the Warehouse. 2. A list of job titles (used for training rules) A separate feed program extracts the unique list of job titles held by anyone at MIT. This list is used by training administrators to define training rules based on job titles. The data are extracted from the Warehouse. 3. A list of DLCs The official list of DLCs at MIT must apply to not only HR, but also to financial data and student data. For this reason, HR is not the system of record for DLCs. The decision has been made that there will be a new system called the Master Department Hierarchy, managed and maintained by the Data Administration Team in IS. Plans have been made with the HR team and the Warehouse team to move forward with this project; it is moving forward slowly, however, due to resource constraints. Happily, there is a precursor to some of the key data in the Master Department Hierarchy in the Roles Database. A table in the Roles Database defines links between master department codes (in the form D_xxxxxx) and various other codes that represent DLCs, such as 6-digit org unit numbers, 8-digit org unit numbers, academic course numbers, profit centers, funds centers, spending groups, etc.. The data in the Roles Database table are maintained by Jim Repa with assistance from the Peggie McGrath and Chuck King. The DLC data from the Roles Database is loaded nightly into shadow tables in the EHS training database. The data are loaded directly from the Roles DB. In the future, the data will be loaded from the Warehouse. 4. A list of PIs or Supervisors from the PI Space Registration Database A list of PIs from SAP's data on PI Space Registration is extracted from intermediate tables in the Warehouse by EHSWEB and stored in EHSWEB's shadow tables. 5. A longer list of possible PIs from financial data There is a nightly feed program to extract data from the cost_collector table in the Warehouse to find all people who are supervisors of cost collectors, for cost collectors that are used to pay at somebody at MIT. The data on cost collectors and supervisors is not always up to date, and we know in many cases that the supervisor of a cost collector is not really the supervisor of the people who get paid from that cost collector. For this reason, there are "false positives" and obsolete data in the "long list" of PIs and supervisors derived from financial data. However, the long list can be useful to help some people find their PI or supervisor if the person cannot be found in the short list. 6. ENHANCEMENT: A list of academic course subjects In the future, we will load from Student Systems tables in the Warehouse a list of academic course subjects. The list will be used for training administrators to assign rules for training based on enrollment in certain academic lab-related subjects. 7. ENHANCEMENT: A list of currently enrolled students in specific academic subjects with a laboratory component In order to apply the training rules from the previous item, we will need to know which students are enrolled in the applicable academic lab-related subjects. In the future, the system will load from Student Systems tables in the Warehouse a list of students and academic subjects they are enrolled in. We will try to design the load program to only load a subset of student subject enrollment information, since we will only be interested in lab-related subjects. The details for how this will work have not been worked out yet. 8. List of people's completed web courses from Traincasters Each night, the training system loads completed web course information from Traincasters using FTP to read a flat file kept on the Traincasters server in Topsfield. This is an incremental load that adds newly-found training data to the shadow table on EHSWEB. Data on completed courses from Traincasters goes from Traincasters to EHSWEB and then to the Warehouse. Because of the tight connection between EHSWEB and Traincasters, it makes more sense to have the data flow in this direction than to have it flow from Traincasters directly to the Warehouse. 9. List of people's completed classroom courses from SAP's TEM modules on campus and at Lincoln Lab Data on users' complated and registered courses from SAP's TEM module on campus are extracted by first moving an encrypted file from a special directory on one of the SAP machines into a temporary file on EHSWEB, decrypting the file, and then applying any new data to the completed course and registered course tables. A similar process occurs for Lincoln Lab's SAP TEM module, except that in the case of data from Lincoln Lab, we only have access to completed course data and not registered course data. E. Details on the calculation of users' training requirements E.1. When a user's training requirements are recalculated User's training requirements are recalculated at the following times a. All users have their training requirements recalculated during a nightly automated process that starts about 10 PM. It can take 5 hours to recalculate everyone's training requirements. There are adjustments that can be made to speed this up, and if the processing is still slow after we install newer hardware in the future, we make changes to speed up the process. (See section E.2 to see what we mean by "all users".) b. A partial recalculation of training needs is done in the early morning hours (about 7 AM). This recalculation only applies to people who have changed their TNA settings, added completed courses, or have job titles that determine training. This process takes about an hour. If the training rules changed in the last 24 hours, this 7 AM training needs recalculation applies to all users and takes 5 hours instead of 1 hour. c. Whenever a user completes the TNA or returns to the TNA summary page, his/her training requirements are recalculated. d. Whenever someone runs a training report on an individual user (Report 2), the training requirements of that user are recalculated. Note that the above recalculation times will generally guarantee that all users' training requirements are uptodate when administrators run training reports. Most changes that cause changes in a person's training requirements only occur just before normal recalculations of training requirements. Job titles changes and newly-completed web-based courses are recorded in nightly jobs, just before the early morning recalculation of requirements. As soon as a user takes or modifies his/her TNA, his/her requirements are recalculated. If the rules change (i.e., staging area rules are moved into production), then training requirements will be out-of-sync with the rules until the next training requirements recalculation. Even if training rules have not been change, there is one case where requirements are not immediately recalculated and reports may inaccurately report current requirements: If administrators insert data about users' completed courses, then the newly-recorded completed courses do not trigger a recalculation of the users' training requirements. In this case, it is possible that the web-based training report by DLC (Report 1) will not accurately reflect completed certification types until the next scheduled recalculation of requirements. (Note that the web-based report on an individual - Report 2 - does force a recalculation of the user's training requirements.) E.2. Who is included in the training users whose needs are determined Only the following users are included in the calculation of training requirements: a. Users who have done the TNA b. Users who have a job title that determines training needs, even if they have never done the TNA These are also the same users who are shown on the web-based training report by DLC (Report 1). Note that users who have never taken the TNA (and have not had the TNA taken for them by an EHS Coordinator or other administrator) will not have any training requirements, including training requirements based on their DLC, unless they also have a job title that automatically indicates training requirements. E.3. Training requirements in the staging and production areas There are separate sets of tables for training rules in the staging and production areas, and there are also sets of tables for users' certification types and uncompleted courses in the staging and production areas. Thus, it is possible for a user to have different training requirements shown in the staging area and in the production area. Note that the "production" area is the official area where user's requirements are kept and reported. The staging area is used for staging changes and for running a "what-if" report on users' requirements based on those staged changes. For users' completed courses, self-reported DLCs, self-reported PIs, and self-reported EHS activities, there is only one set of tables, shared by the staging and production area. If a user self-reports a given EHS activity, then this activity is used to determine his/her training requirements in both the staging and production areas. When rule changes are made in the staging area, users' training requirements are not automatically recalculated, as they are when the set of changes is moved into production. Users' training requirements are recalculated in the staging area under the following circumstances: a. Each day in a morning job, all users' requirements are recalculated both in the staging and production area. The staging area recalculation is done later, and does not complete until about 1:30 PM. b. When a training administrator runs a staging-area training report on an individual user (staging area Report 2), the training requirements of that user are recalculated in the staging area. Note that if the person has never taken the TNA, and the rule change has to do with a job title that never had a rule assigned to it in the past, then Report 2 will *not* cause the person's training requirements to be recalculated in the staging area. In this case, the requirements will be recalculated the next morning. In order to test the effects of changes made to rules in the staging area, an administrator can either wait for the next nightly recalculation of training requirements in the staging area, or run a staging area report for an individual user (staging area Report 2) to see the results immediately. E.4. The algorithm used to recalculate a user's training requirements For each user, the following steps are involved in recalculating the user's training requirements a. Determine whether or not the user should have any training requirements. If the user has taken the TNA (recorded in the table train_registration) or if the user's job title implies training requirements, then the use needs to have his training requirements calculated. If not, then skip the rest of the steps. (To see if the user's job title implies training, compare the user's job title from directory information with the list of job titles included as triggers for certification types.) b. Build a list of the user's potential "triggers" for certification types Record in a temporary table (table tr_person_all_trigger) a list of all attributes or objects associated with the user that might be a trigger for certification, including the person's self-reported DLC, the person's directory DLC (if there are no self-reported DLCs), the person's job title, and the person's self-reported EHS activities. (Other trigger types will be added in the future.) c. Determine what certification types the person must complete, based on the person's triggers Compare the user's possible triggers from the previous step to actual triggers associated with training rules, and populate another temporary table (tr_person_cert_trigger) to record all triggers tied to a particular certification type. At the end of this step, we know what certification types the person would have to complete if we were to ignore the certification type priorities. d. Look at completed courses for this person and mark any that are expired. Look at all of the courses that the person has completed in the past, and use the completion_date along with the expiration days for the given course option found in course_option and course_equivalent tables to calculate the expiration date. If the expiration date has already passed, then mark the course as expired. Also, in the tables of completed courses for the user, flag the "most recent" instance of completed course option associated with a course equivalent. (This will be useful in reporting.) e. For each certification type for the person from the previous step, determine which cert types are completed. By looking at the person's completed, and unexpired, courses and other requirements, the system finds which certification types have been completed, i.e., cases where the person has completed all of the requirements for a certification type, or where there is more than one option set of requirements for the certification type, the person has completed all of the requirements in at least one of the option sets. f. Mark certification types that are overridden by others, according to certification type priorities, and delete them for the person, where appropriate. Look through a person's certification types (from step d) along with information on certification type priorities (where one certification type takes precedence over another one based on training rules). Suppose person A has certification types X and Y to be completed, according to step D. Further, suppose that certification type Y takes precedence over X according to training rules. Then handle the following cases: Case i. Person A has not fulfilled the requirements for either cert X or Y. Action: Drop cert X for person A. Case ii. Person A has fulfilled the requirements for cert X but not for Y. Action: Do not drop either cert type for person A. Case ii. Person A has fulfilled the requirements for cert Y but not for X. Action: Drop cert X for person A. Case iii. Person A has fulfilled the requirements for cert Y but not for X. Action: Drop cert X for person A. Case iv. Person A has fulfilled the requirements for both Cert X and Cert Y. Action: Drop cert X for person A. The reasoning behind case ii is this: If a person has completed a "low-priority" certification type, but not a higher-priority one, it is useful to record this information an show it on reports. The "low-priority" and "high-priority" certification types probably apply to different DLCs. The user may be qualified to do research in the DLC associated with the low-priority cert type, even if he/she still needs to complete some requirements before doing research in the DLC associated with the high-priority cert type. It is useful for the EHS Coordinator in the first DLC to know this. g. Update the temporary table tr_person_cert_type to reflect the completed and uncompleted cert types for this person from steps d-f. h. Find uncompleted courses Starting with the list of uncompleted, required certification types for the person, write a list of uncompleted courses or other requirements into a temporary table (tr_person_uncompleted_course). Most certification types have only one "option set" of requirements to be completed; in these cases, it is easy to look at a list of requirements and previously-completed courses, match them up and see what is still uncompleted. In the case of certification types that have more than one option set (e.g., take course 100 and 101 or take course 105 and 106), then the system has a more complex task to complete. It looks at uncompleted requirements in each option set, and determines a score for each option set that represents how much effort it would take to complete the remainining requirements. This score is based not only on how many requirements remain, but also considers refresher courses preferable to first-time courses, and considers web courses preferable to classroom courses which are preferable to other requirements. Based on the scores for uncompleted requirements in each option set, it picks a recommended option set and writes uncompleted requirements to the temporary table (tr_person_uncompleted_course) based on this recommended option set. After these calculations have been made, and the temporary tables have been populated with data, the system now has enough data to (i) do reporting on users' triggers, cert types, and courses, and (ii) to print the Summary page of the TNA showing completed and uncompleted courses. F. Details on data exported to Traincasters For nightly feeds to Traincasters, see http://web.mit.edu/repa/www/netcasters_interface.html sections II.A (list of DLCs), II.B (list of PIs/supervisors), and II.C (information on each registered person). II.A and II.B have been implemented and are in production. II.C has been recently implemented, but has not yet been put into production. (We do not expect to implement II.D, II.E, and II.F.) Feed II.A contains data mapping DLC codes to DLC names. Feed II.B contains data mapping PI/supervisor Kerberos IDs to their names. Data from these feeds are used for reporting in Traincasters. Feed II.C contains information on each registered user. including uncompleted courses. The data in II.C are transferred in real time via hidden form HTML variables whenever a user clicks the "Go to web courses" button from the TNA. Feed II.C is intended to refresh the data on a nightly bases for registered users, even if they do not go back to Traincasters via the "Go to web courses" button on the TNA summary page. G. Details on the interface for uploading completed course information The system provides a web interface for uploading data on users' completed courses and other requirements. (Initially, the plan had been that this component would be built into the Classroom Course Registration component (currently implemented in FileMaker). However, due to time delays and other problems with that component, the decision was made to build a simple web interface for this functionality within Training Requirements and Records Component.) There is a special business function within the Roles Database that allows individuals to be specially authorized to use the web interface for uploading completed course data. The authorization permits the specified administrators to upload completed course data for any course and any trainee. Information is uploaded from a tab-delimited flat file with specific fields. The web interface allows the user to cut-and-paste the data into a large field on the screen, or to use a standard web browswer mechanism for uploading a selected file from their hard drive. The fields in the flat file must be in the following format: 1. Kerberos ID of trainee (either Kerberos ID or MIT ID number must be supplied) 2. MIT ID number of trainee 3. Course option code (must match a course option code for a course or other requirement known to the system) 4. Completion date (required. mm/dd/yy or mm/dd/yyyy format) 5. Instructor kerberos ID (optional) 6. Instructor name (option; ignored if instructor's Kerberos ID is given) The system checks the fields (to make sure they are in the appropriate format, and to make sure they match known people or courses in the system). If everything is OK, it uploads the whole file. If not, it prints error messages and does not upload any of the data. After a new batch of completed courses and other requirements have been loaded, reports will not necessarily completed certification types for users until training requirements have been recalculated. This happens in a nightly (early morning) job. To help administrators to review the data on completed courses and other requirements in the system, there are 3 simple reports available on the web. 1. Show recently posted records 2. Show records for a user (trainee) 3. Show records by completed course date (within a range of dates) By looking at the parameters and options for these reports, you can get a better idea of the nature of the reports. The reports show, for each completed course a. Trainee's Kerberos ID and name b. Course option code c. Course option type (web, classroom, or other) d. Course completion date e. Instructor name (if available) f. Source of data (either "batch load" or "TrainCasters") g. Date posted (the date the record was added to EHSWEB data) h. Posted by (the Kerberos ID of the person who posted data, if batch load) You can view a the parameters for running these reports at https://ehsweb.mit.edu/ehs/complete_course_rpt.html You may not authorized to actually run the reports, but you can still see the front-end. H. Details on web-based training reports There are two web-basd reports on training available to authorized administrators. Report 1. Report registered users (plus users whose job title indicates training requirements) within a given DLC. The administrator enters a sort order and picks a DLC. For each trainee, the report shows: a. trainee's name and Kerberos name b. date first registered in TNA c. trainee's DLC(s) d. trainee's PI(s) and supervisor(s) For each certification type required for the trainee, the report shows: a. The trigger(s) that indicate the cert type must be completed b. The certification type name. Uncompleted certification types are flagged in red. c. A list of completed and uncompleted requirements, i.e., the course equivalent code and course equivalent name. Uncompleted courses are flagged in red. d. For completed requirements, the completion date and type (classroom, web, or other) Completed courses that are not associated with a certification type are also shown. Report 2. Show an equivalent of the TNA summary page for a user other than yourself. The administrator specifies the Kerberos ID of the user of interest. I. Details of data made available to the Warehouse All tables and columns in the Warehouse (for EHS and other data) are documented on the web. See http://web.mit.edu/warehouse/metadata/tables/all_tables.html Look for table names that start with EHS. There are two groups: EHS_ (for Room Set data) and EHST_ (for training data). J. Details on the Roles DB and authorizations for the training component [Describe the specifics of how the Roles DB controls the authority to perform maintenance of training rules and other data, as well as the authority to view training data.] K. Details on Email notification for uncompleted training This feature is described at http://ehsweb-test.mit.edu/ehs/notify_specs.html