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 table 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 table contains 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. From what table would you select employee address information? What are some factors to consider when querying this table?
7. For which tables should you be concerned about the history status of a record? Why might this matter?
8. Which table would you use to display the name of an employee's country of citizenship, rather than the code that appears in the EMPLOYEE table?
   

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 EMPLOYEE table.
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.
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. If you are looking for home address, then consult the EMPLOYEE_ADDRESS table. Keep in mind that this table has only the last known Payroll mailing address, and may include employees who have been purged from Payroll. You may query this table 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 EMPLOYEE table.)
7. 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.
8. The COUNTRY_CODES table.