This lesson should take about 1 1/2 hours to complete. After completing this lesson, you should be able to describe the content of table and data element help and recall the tables that make up the RSALMGT Universe and identify their content.

Lesson 3. Table and Data Element Documentation

· prerequisites  · using this tutorial  · data training

Activity 1

Read the document "What the Warehouse Contains." After reading the document, use your browser's back button to return to this lesson.

Activity 2

After reading the document, "What theWarehouse Contains," please answer the following questions:

1. Why should you read the Cautions section in the table help documentation?
2. What is a data element?
   
3. What is included in the online help for data element documentation?
   
4. The Data Warehouse contains data at the summary level. Identify the types of summary data that can be available within a data collection.
   
5. What is a data collection?
   
6. What is the purpose of the Related Tables section in the table help documentation?
   

Activity 3

Read through the explanations and common uses sections of the tables that make up the RSALMGT universe. After reading the explanations and common uses for the tables that make up this universe, please answer the following questions.

1. Name the tables that contains secured biographic or demographic data on employees.
2. Why would you use the EMPLOYEE_ENCUMBRANCE_CURRENT table rather than the EMPLOYEE_ENCUMBRANCE table?
3. Which tables contain information about payments to employees? What sort of information can you find out about payments?
4. What are some common uses for the JOB_CLASS and JOB_CLASS_GENERAL tables?
5. Why might you choose to query EMPLOYEE_GENERAL rather than EMPLOYEE?
6. When should you use COMP_PERSON rather than EMPLOYEE?
7. From what tables would you select employee address information? What are some factors to consider when querying these tables?
8. For which tables should you be concerned about the history status of a record? Why might this matter?
9. Which table would you use to display the name of an employee's country of citizenship, rather than the code that appears in the COMP_PERSON or EMPLOYEE tables?
   

Activity 2 Answers

1. The Cautions section provides additional guidance, help, or explanation about a table. It can also include recommendations that must be followed to prevent poor query results.
2. A data element is the smallest unit of data that you can work with which cannot be logically divided any further without losing its meaning or context.
3. Help describes the data element and includes its indexed, format, and not null values. If applicable, it also provides a list of valid values for the data element.
4. Summary data can be made available by week or by month in EMPLOYEE_PAYMENT, or by year in EMPLOYEE_PAYMENT_SUMMARY.
5. A term used to refer to the information in the Warehouse from each source system at Penn.
6. The Related Tables section helps you to identify other tables that may be meaningful to your query. That is, tables that are good candidates for containing information that you may want to include in your results.

Activity 3 Answers

1. The COMP_PERSON and EMPLOYEE tables.
2. If you want to access current encumbrances only (i.e., those for the current Accounting Period) you would use EMPLOYEE_ENCUMBRANCE_CURRENT. EMPLOYEE_ENCUMBRANCE contains both current and past encumbrances.
3. The EMPLOYEE_PAYMENT table contains data about payments to employees, and reallocations. You can find the check date, accounting period, earnings type and account number for each payment. PAR_EARNINGS contains similar data, and also includes hours worked for weekly paid employees. EMPLOYEE_PAYMENT_SUMMARY contains a fiscal year summary of payments to an individual, from a particular 26-digit account number.
4. You can use both tables to find data about a job class, such as the job title, personnel class, job group and faculty class. JOB_CLASS includes additional restricted data about the class, such as job grade and minimum and maximum salaries.
5. If you need only basic biographic or demographic data on employees, or don't have the proper level of access to the EMPLOYEE table, you may opt to query EMPLOYEE_GENERAL instead.
6. COMP_PERSON contains the latest available biographic/demographic data for the employees whose records are managed in PennWorks (since November 1, 2009). The EMPLOYEE table includes employees who had Payroll records on or after July 1, 1996, so for employees with Payroll records prior to October 2009, biographic/demographic data is available in the EMPLOYEE table only.
7. If you are looking for home address, then consult the COMP_PERSON_ADDRESS or EMPLOYEE_ADDRESS tables. Keep in mind that the EMPLOYEE table has only the last known Payroll mailing address, and may include employees who have been purged from Payroll. You may query these tables only if you are authorized to access data for the employee's home school or organization. (An employee's campus mail code and work phone can be found in the COMP_PERSON and EMPLOYEE tables.)
8. EMPLOYEE_DISTRIBUTION and EMPLOYEE_JOB both store history status on each record. Unless you are tracing distributions over time, you should choose a history status of 'C' in EMPLOYEE_DISTRIBUTION to find distributions as they currently exist (or most recently exist, for purged records) in Payroll. Similarly, EMPLOYEE_JOB stores historical job records - only jobs with a history status of 'C' reflect those that currently exist in Payroll (or, for those records which were purged from Payroll, a history status of 'C' indicates the last known job record). You should also consider screening on dates to find truly current records - Distribution Start and Stop dates for EMPLOYEE_DISTRIBUTION, and Appointment Begin and End dates for EMPLOYEE_JOB.
9. The COUNTRY_CODES table.