HRPay ODBC Data Education

1
HR/Pay ODBCData Education
2004 Last updated 10/20/2004
2
Introductions
3
About Todays Session

• Covered Today
• Data Warehouse Overview
• HR Metadata explanation
• HR Data Models
• HR Salary Planner Model
• QA
• Not Covered
• Tool Training
• Business Objects
• SAS, Crystal Reporting, etc.
• How to connect via ODBC connection
• Using SQL tools
• Operational processes and using Banner
• UI-Integrate standard reports
• http//www.ui-integrate.uillinois.edu/docs/hr_repo
  rts_inventory.xls
• HR Standard Reports

4
Goals

• By the end of todays session, you should be able
  to
• Select which tables or views in the database to
  find the data elements that you are looking for.
• Understand the general structure of the EDW.
• Understand why and which conditions you will need
  for most queries.
• This is a technical class. If you are
  interesting in using Infoview, View Direct or the
  Universes, you may want to leave and sign-up for
  the regular data education class.

5
Agenda

• Security Overview
• Metadata Overview
• Requirements/Universe Overview
• HR Data Models
• HR Salary Planner Model
• Examples of Reporting
• QA

6
Security Overview
7
The Security Trade-Off
Performance
Security

• HR data, by its nature, contains a lot of
  sensitive information that needs to be secured.
• The more security we have around the data, the
  worse the performance could be in the DW.

8
Security Schools of Thought
Prove You Need Access
Everybody Has Access
People cant do their jobs
Potential misuse of data and litigation
9
HR\Pay Security Considerations

• Restrict What Data is Visible (Column Level
  Security)
• There are different levels of confidentiality for
  human resources data. Different types users can
  see different columns of information.
• Different parts of Human Resources need to see
  different information about a person.
• Institutional Researchers need to see most
  information about employees.
• Restrict Whose Data is Visible (Row Level
  Security)
• We need hierarchical security (Level 1 - Campus,
  Level 2 - Administrative, Level 3 - College,
  Level 4 - School/Sub-College, Level 5 -
  Department, Level 6 - Department/Sub-Organization,
  Level 7 - Sub-Organization, Level 8 -
  Sub-Organization).
• Units need to see all job related information for
  their employees, not just their Units jobs.
• Units need to see all job related information for
  those employees that they fund.

10
Security Implementation Assumptions

• We are talking about the base
• EDW. We do not have any Data Marts.
• The HR\Pay data will be limited to a subset of
  the Data Warehouse users.
• This subset of users will be authorized users.
• Data Education will remind the users that HR data
  is sensitive and they have promised to use
  University data for University work only.

11
Security Solution

• Minimize the number of columns affected by row
  level security

• PROs
• Units will be able to see all necessary
  information around their employees jobs.
• Units will not be able to see sensitive data
  around jobs that they do not own.
• CONs
• HR/Pay EDW users would have access to cross
  campus/university information.

12
Security Solution
Minimize the number of columns affected by row
level security
13
Security Solution

• DS has worked with UOHR, Payroll and EEO Offices
  to determine which information can be shared and
  which information cannot be shared.
• Information that can be shared will not be
  restricted within the Data Warehouse.
• Information that cannot be shared, will be
  limited to the Unit.

14
Security Solution Functional Roles

• All people accessing the HR\Pay data must be
  placed into a Functional Role
• Units Unit Security Contacts will place
  individuals into proper Functional Role

15
Security Solution Functional Roles
16
Security Solution Functional Roles
17
Security Solution Functional Roles
18
HR Data Warehouse Overview
19
Terms Concepts

• Data Warehouse
• Data Warehouses combine data from operational
  sources to support reporting and analysis. Data
  Warehouses do not create or originate data.
• Data Product
• A generic term for any of the various types of
  data structures that Decision Support provides in
  the Data Warehouse. For example a data mart or
  a Universe is a data product.

20
Terms Concepts - Continued

• EDW Enterprise Data Warehouse
• A specific data product part of the Data
  Warehouse. It refers to
• the purest, largest collection of data
• the bottom layer of data
• the most granular level of data
• is read only
• Data from Banner is currently extracted daily,
  transformed, and loaded into a homogenous
  database.

21
What is the EDW (Enterprise Data Warehouse)
Simplified

• Think of the EDW as
• Hundreds of grids of data (like Excel
  spreadsheets)
• Potentially gathered from a variety of source
  systems (like Banner, HRR, PHD)
• Collected (extracted) at the end of each day
• Arranged (transformed) in a way that will be
  usable for the maximum number of reporting
  purposes
• Copied (loaded) into a central place (warehouse)

22
Pop Quiz!

• The Data Warehouse
• A) is read only
• B) is for auditing Banner
• C) includes everything in Banner

• Decision Support
• A) is a part of AITS
• B) will write my reports for me
• C) supports analytical reporting

23
What is Metadata?

• Metadata means data about data think of these
  as reference materials. Metadata encompasses what
  we have called documentation in the past - and
  more!
• Some examples of Metadata are
• Data dictionary
• Source to Target mappings
• Usage notes
• Logical physical data models

24
Why do I need Metadata?

• Metadata will help you
• Find data you are seeking
• Which universe it is located in
• Name of a field
• Consistently define the data
• Know where the data comes from
• Define the technical structure of the data
• Is it text or numeric
• How long is the field
• Provide Tips Tricks for appropriate data usage

25
Resources for Data Warehouse Users
For additional documentation and information
about universes, objects, tables, loading of EDW
data, training, data models, and metadata search,
please visit the DS website http//www.ds.uillin
ois.edu/web/Home/Metadata.aspx
26
Metadata

• Universe Definitions
• How Universe Objects relate to Physical Tables
  and Columns
• Data Dictionary
• Definitions for all of the tables in the
  Warehouse
• Includes column names and descriptions, table
  keys, etc.
• Useful for finding out more about a specific
  table or column
• Shows source in the EDW for data mart tables and
  columns
• Uses physical table and column names
• Useful for tracking data back to the source

27
Metadata

• Logical Data Model
• High-level business view of the data
• Shows the tables and views with business names
• Useful for determining what data is available
• Physical Data Model
• Representation of physical tables and views in
  database
• Physical table names are used
• Key reference when writing queries directly
  against the database

28
Full-Scale Plotter-Printed Data Models Printing
Services offices on each campus are now prepared
to print full-scale data models on request.
1. Contact the Printing Services office on
your campus 2. Provide the exact DS Metadata
website link to the full-scale plotter copy
model(s) you desire, and ask them to print a
plotter sized model. Be sure to only select data
models labeled full-scale plotter copy. 3. If
Printing Services has questions on how to print
the data model, refer them to local plotter
printing instructions. 4. Your department will
be charged for the printing at the regular campus
rate for plotter printing.
UIC Printing Services Contact Information
Phone [phone] Dept Code 2-8560 900 ARTI
MC 291 847 West Jackson Boulevard Chicago, IL
60607
UIS Printing Services Contact Information Phone
217-206-6777 Contact Larz Gaydos Office BSB
149 Web site http//www.uis.edu/CampusServices/E
lectronic20Duplicating.htm
UIUC Printing Services Contact Information
Phone [phone] E. Gregory Champaign,
IL 61820
29
Metadata

• Source-to-Target Mappings
• Show each table and column in EDW and indicate
  the table and column in the source system
• Usage Notes
• Background about the environment and general info
• Refresh rates
• Table joins
• History tracking
• How deleted data is handled
• Use of indexes in the EDW
• Data access summary
• Data groupings

30
Pop Quiz!

• Metadata is
• A) a very large volume of data
• B) only for very technical users
• C) documentation about data

• Source-to-Target Mappings
• A) indicates the location of the data in the
  source system
• B) shows how to find data in the DW
• C) gives the exact Banner form where information
  in the Universe came from

31
Requirements/Universe Overview
32
HR/Pay Top Requirements
Leave Tracking
Leave Tracking Ability to monitor accrued leave
balances, leave taken, non-accruable leave, FMLA
usage, by employee or position, and sabbatical
Position Analysis
Position Analysis Analysis of filled positions,
contract dates, position class codes, salary
group codes, begin and end dates, location,
skill, and work schedule
Union Civil Service Administration
Union Civil Service Administration Analysis of
positions and jobs, related to unions, dates,
position class codes, salary grades, occupation
codes, location, skill, EEO codes, and work
schedules
Whole Employee View
Whole Employee View A summary of the employee
including all jobs, chart of accounts,
appointment percent, salary, rate, tenure
EEO Reporting Analysis
EEO Reporting Analysis Reporting and analysis
to support monitoring and planning for EEO
compliance
33
HR/Pay Top Requirements
Payroll Obligations
Payroll Obligations Analysis of payroll
obligations less payroll expenses by account by
person by pay period
Gross Payroll Analysis
Gross Payroll Analysis Analysis of payroll
dollars per person per account
Hours Worked Analysis
Hours Worked Analysis Analysis of the total
regular and overtime hours worked by an employee
by time period (pay period, month, year, etc.)
Payroll by Grant, Project, Activity
Payroll by Grant, Project, Activity Analysis of
payroll expense for a work activity or funding
source total cost of deductions by type
Labor Distribution
Labor Distribution Reporting of headcount and/or
FTE employees paid from a selected C-FOAPAL, by
organization, department, employee class or
position class
34
HR/Pay Top Requirements
Payroll Post-Calc View
Payroll Post-Calc View Reporting on an employee
from a virtual frozen snapshot after each
payroll run will have the functionality of a
snapshot example use is for adjustment
calculations
Benefit Analysis
Benefit Analysis Count or list of employees with
a specified deduction code total cost of
deductions by type
Pay Composition
Pay Composition Analysis of the elements that
make up payroll expense, for example, salary to
benefits analysis, and dollars paid by employee
class or position class
Notice of Appointment
Notice of Appointment Reporting regarding new
and renewed appointments (some requirements
covered by UI-Integrate and HR Reporting
Transition Team reports)
And many other types of analyses as well!
35
HR/Pay Universes
HR Report Specifications
UI2 Report Specifications
36
HR/Pay Universes
Whole Employee View
Benefit Analysis
EEO Reporting Analysis
Position Analysis
Leave Tracking
Union Civil Service Administration
Notice of Appointment
37
HR/Pay Universes
Payroll Obligations
Labor Distribution
Payroll Post-Calc View
Gross Payroll Analysis
Payroll by Grant, Project, Activity
Hours Worked Analysis
Leave Tracking
Pay Composition
Benefit Analysis
38
HR/Pay Universes
Salary Planner Analysis
39
Universe General Structure

• All eight Universes are designed similarly
• Head Count
• Position
• Job
• Employee
• Universe Specific Information
• The amount of information found under each of
  the above Classes is not same in each Universe.
  Depending on what type of reporting the Universe
  was designed for, will determine how much
  information is in each Class.
• It will sometimes be possible to write the same
  report out of different Universes.

40
Universes General Structure

• All six Universes are designed similarly
• Head Count
• A distinct count of the number of employees
• Position
• An approved budgeted slot for which employees can
  be recruited and hired
• Position Attribute information is found here
• Job
• Most synonymous to previous Appointment Line.
  Jobs occur in Banner when a person is placed in a
  budgeted position
• Job Attribute information is found here
• Employee
• A person who is paid by the University
• Employee Attribute information is found here
• Universe Specific Information

41
Deduction Administration Universe
In all DS HR\Pay Universes
Information only in the Deduction Administration
Universe
42
Data Warehouse Environment
43
Requesting Access

• Requesting Access to Banner
• Users will request access through Unit Security
  Contacts (USCs) access to the Banner
  application
• https//apps.uillinois.edu/access_form.htm
• Requesting Access to the EDW
• Users will request access through Unit Security
  Contacts (USCs)
• Instructions are posted to http//www.ds.uillinois
  .edu/web/Home/GetAccess/RequestProcess.aspx
• Access to Standard Reports
• Instructions posted to http//www.ds.uillinois.edu
  /web/Home/GetAccess/StdReports.aspx
• USCs
• Information on your AITS web site
  https//www.aits.uillinois.edu/

44
Establishing the ODBC Connection
On your workstation you will need the Oracle 9i
Client and ODBC driver to establish an ODBC
connection to the EDW.   New users must remember
to change their default Oracle password. Link to
instructions on the next slide. NOTE If an
individual is a current Business Objects user,
they should change their Oracle password to match
their Business Objects password. The only
reporting tool that Decision Support supports is
Business Objects. However, on the next slide you
will see links to instructions for various
reporting tools about connecting to the EDW.
If you have difficulty establishing your ODBC
connection, contact the AITS Help Desk. The Help
Desk will generate a Clarify case and route it to
Decision Support. Someone from DS Services will
contact and assist you.
45
Establishing the ODBC Connection
This DS web site link http//www.ds.uillinois.ed
u/SourceData/ODBC.aspx Provides this Oracle and
ODBC informationCreating an Oracle ODBC Data
Source Connection at Connecting to the EDW
Oracle Database with MS Access Connecting to
the Oracle EDW Database with Crystal
Reports Connecting to the Oracle EDW Database
with SAS/PC Version 8 Setting Oracle
Passwords Installing the Oracle 9i Client and
ODBC Driver
46
ODBC Connection Caveats

• Decision Support does now create generic accounts
• University Security Policy states that data usage
  should be tied to individuals
• Generic accounts must be tied to an individual,
  not a department
• If query impacts performance of the Data
  Warehouse, Decision Support will kill the query.
• DS now has an ETL Runtime table for automating
  your ODBC queries against the Data Warehouse.
• Information regarding the table and its use can
  be found at http//www.ds.uillinois.edu/SourceData
  /RuntimeTable.aspx

47
HR Data Model Review
48
General Notes

• The model contains a number of anchor tables
  which just have the key to another table and
  effective date and posted date. These tables
  resolve many to many relationships between
  tables. These are not useful for creating
  queries and should be excluded.
• When Chart of Accounts Code and Organization Code
  appear together without the remaining parts of
  the C-FOAPAL, this indicates the
  campus/college/department that owns the job.
• When Chart of Accounts Code, Organization Code
  and the rest of the CFOAPAL appear together, this
  indicates a normal C-FOAPAL string.
• When Chart of Accounts (COA) Code and
  Organization (ORG) Code appear together without
  the remaining parts of the C-FOAPAL, the campus,
  college and department codes and names are
  denormalized from the organization code table at
  the time it was loaded into the EDW.

49
General Notes (contd)

• The job, job detail and employee tables have
  associated event tables. These tables track the
  column and date information changed in the table
  associated with them.
• The T_PAYR_EVENT_5 and V_PAYR_EVENT_1 tables are
  not change event tables, but rather indicate a
  pay has occurred.
• In the data models provided, a table beginning
  with a V_ is actually a security view, while a
  table beginning with a T_ is truly a table.
• Tables or views ending with a number indicate
  that security is being applied to the data
  contained in the table/view. Numbers range from
  1 to 5 with 1 being the most open and 5 being the
  most restrictive.
• Labor Distribution Tables are the recommended
  C-FOAPAL distributions. Only Payroll Acctg Detl
  contains the actual C-FOAPAL distribution.
• HR/Pay is a history intensive increment.
  Navigating the extensive history will require the
  use of Effective and Expiration Dates or Data
  Status Descriptions and Data Expiration Dates.
• Payroll Event does not indicate the changes to a
  payroll table.
• Expiration Dates default to 12/31/9999 when the
  record is current.

50
General Notes (contd)

• Post(ed) Date are EDW timestamps and as such are
  not reliable for pulling information. If DS
  needs to reload a table, all prior records will
  have the same time stamp.
• The effective dates are the Banner activity dates
  except in instances where Banner has a true
  effective date.
• There are tables in the model that are not
  associated with other tables. The model shows
  primary relationships between tables. Tables not
  associated with other tables can still be used
  together.
• The EDW_PERS_ID is an EDW one-up sequence
  internal to the EDW, similar to the Banner PIDM.
• The models we use for Data Education reflect
  planned changes because the model changes
  continuously. Therefore all fields indicated may
  not be available immediately. Check
  www.ds.uillinois.edu for the most current version
  of the models.
• The models are adjusted to show the views where
  appropriate. Therefore, they will not match the
  models in the metadata section of the decision
  support website.

51
General Notes (contd)

• There are a number of tables associated with HR.
  These tables are fed into Banner from Nessie. As
  such, they are unreliable.
• Skill
• Previous Work History
• Honor Award
• Prior Degree
• Person License Certification
• Endorsement
• Publication

52
Employee Administration

• The job information on the employee view relates
  to the primary job, although there is nothing in
  Banner enforcing that the job and employee tables
  be in sync.
• Faculty are those people entered into the PEAFACT
  form in Banner.
• Active Employee Indicator is an EDW derived field
  useful to all users.
• Original Status Hire Date is an EDW derived field
  that indicates when an employee was first hired
  into an status employee class. For employees
  hired before HR Go-Live, this date will default
  to the First Hire Date.
• Leave of Absence contains all leave except
  Accruable Vacation and Sick leave.
• Employee Promotion Date is currently null. This
  field is derived for the EEO offices based on
  complex business rules which define a promotion.

53
Employee Administration Notes (contd)

• Academic Tenure Track Description can be used
  when the user simply wants to determine if an
  employee is Tenure/Tenure Tracked.
• Accrued Leave contains the leave as it is accrued
  while Accrued Leave Balances contains the balance
  available. The balances table will also contain
  manual adjustments.
• Academic/Faculty Time Reporting requirements vary
  from campus to campus. The Leave Taken Paid
  Monthly table will only reflect data as it is
  reported.
• The Faculty Tenure History table contains a
  calculated Tenure Track Year and Tenure Q Year.

54
Employee Administration tables include
55
Employee Administration tables contd
56
Time Attendance and Payroll Notes

• Pay contains a four part key
• Payroll Year
• Calendar Year
• Payroll ID
• Values include Biweekly and Monthly
• Payroll Number
• Pay of the Year
• Payroll Sequence Number
• A sequence number that increments each time an
  action is taken for the specified Payroll Year,
  Payroll ID and Payroll Number.
• Because a sequence number does not, in itself,
  indicate the type of action taken, payroll tables
  containing the sequence number also contain an
  Event Type Code and Event Type Description which
  will indicate the type of action taken.
• The Payroll Run table is the Payroll Calendar

57
Time Attendance and Payroll Notes (contd)

• Payroll Pay Check Document indicates whether an
  employee is paid by direct deposit or check.
• Payroll Deduction Taken and Payroll Accounting
  Detail contain a deduction type description which
  matches the actual deduction taken from a pay
  check with the deduction set-up tables in the
  Deduction subject area.
• Payroll Job Detail, Payroll Job Earns, and
  Payroll Job Earns Labor Distribution are frozen
  pictures of time, earnings and the job for each
  pay.
• Payroll Accounting Detail contains the Payroll
  Acctg Expense Amount which is a signed value
  based on the event type and the sign code. Using
  this field will greatly simplify the roll-up of
  salary amounts.
• Payroll Accounting Detail contains many different
  pieces of information. Most roles can only see
  Gross Pay (Payroll Acctg Detl Category Cd A)
  and Employer Paid Benefits (Payroll Acctg Detl
  Category Cd X).

58
Time Attendance and Payroll Notes (contd)

• The Accounting Detail table has no key. As such
  be careful using it. It contains information for
  a person, job, pay event, earnings code, etc.
• If you wish to include One Time Payments, nothing
  is required. However, to filter these One Time
  Payments out of queries, there is an indicator on
  select Payroll and Job tables which will filter
  out these payments.
• Reported Time contains only the reported time for
  those employees who must report their time
  hourly. Employees who report exception time only
  will not have time in this table.
• Earnings transfers are included in the Payroll
  Accounting Detail.

59
Time Attendance and Payroll Tables
60
Position/Job Hierarchy Notes

• The Job contains a three part key
• EDW Person Id
• A one up sequence number that ties to a person in
  the Bio-Demo subject area.
• Position Number
• The position is set-up independent of the job or
  person.
• Job Suffix
• A job occurs only when a position is associated
  with a job. There is no particular job suffix
  which indicates a primary job. Additionally,
  there is no uniform way that job suffixes are
  assigned.
• Seniority Manual Adjustments are included in the
  Seniority Balances table.
• Position Attribute give further information about
  a position which is tied directly to an employee.
• Position Labor Distribution and Job Labor
  Distribution do not have a key. Be careful when
  using these tables.

61
Position/Job Hierarchy Notes (contd)

• Work Schedules are only maintained for those
  employees who report their time hourly.
• Unions are not tied directly to a job or
  employee, but it is safe to assume that all jobs
  associated with a position are unionized.
• The union contract table contains no data.
• While the job record has a job begin and job end
  date, these dates do not reflect the appointment
  year. Instead, this information will be found
  in the Contract History table.
• The Open Position Indicator on Position History
  is not maintained by the Banner user community.
  Therefore, the data is not reliable.
• If you wish to include One Time Payments, nothing
  is required. However, to filter these One Time
  Payments out of queries, there is an indicator on
  select Payroll and Job tables which will filter
  out these payments.

62
Position/Job Hierarchy Notes (contd)

• The Position Labor Distribution and Job Labor
  Distribution tables do not contain a key, so they
  easily produce cartesian products. The Job Labor
  Distribution table does contain a Data Status
  Description and Data Expiration Date to make
  querying easier.
• The Job History table contains the non-volatile
  information about a job while the Job Detail
  History table contains that data that is subject
  to change frequently.
• The Job Detail Effective Date is used by the
  Payroll process to determine how to pay a person.
  The date a change was effective for a job is the
  Job Detail Personnel Effective Date.
• In Banner, if a regular rate is entered, the
  system will use the Pay Period Salary and Annual
  Pay Count to calculate the Annual Salary.
  Conversely, if an Annual Salary is entered, the
  system uses the Pay Period Salary and Annual Pay
  Count to calculate a regular rate.

63
Position/Job Hierarchy Tables
64
Position/Job Hierarchy Tables (contd)
65
Data Status Description

• A description that categorizes the status of
  records as Current, Expired (no longer
  effective), Future (effective in the future) or
  Deleted for University Business.
• A record, that has a DSD with the value of
  Current, indicates that the record is currently
  active and effective.
• When using Data Status Descriptions in your
  conditions, make sure that all DSDs have the same
  value.

66
Data Expiration Date

• The Data Expiration Date is the implied end date
  of a particular record. The DED creates an
  effective date range for a particular record when
  used with the Effective Date.
• The default DED for Current records when there
  are no future dated records is 12/31/9999.
• If a record was future dated (DSD Future),
  and is deleted prior to the date it would have
  been active, the DED value will be null.

67
DSD/DED Example Original Record
A
B
C
D
1

• The Job Effective Date (A) is 8/12/02. This is
  the date the job will start.
• The Job Activity Date (B) is 8/11/02. This the
  date the record was entered into the source
  system (Banner).
• For original records, the CII (C) will be Y, the
  Expiration Date (C) will be 12/31/9999, the DSD
  (D) will be Current and the DED (D) will be
  12/31/9999.

68
Example Visiting Job
A
B
1 2
The Job is a Visiting Job, so there is a Future
dated record (row 2) that is added that
terminates the original job (row 1) in 1 year
(A). The Terminated record (row 2) has a future
Job Effective Date (A) of 8/12/2003. The DED (B)
on the original row (row 1) now becomes the day
before the Terminated Record (row 2) becomes
effective (A). The DED (B) on the Terminated
record (row 2), becomes 12/31/9999.
69
Example Visiting Job, Renewed
C
A
B
D
1 2 3 4
On July 1, 2003 (A), the visiting position gets
reappointed with a raise (row 3). Along with the
reappointment, a new, future dated termination
record (row 4) is added. The reappointment
record (row 3) will have a CII (B) of Y. It
also has a DED (C), of the day before the new
terminated record (row 4) goes into effect (D).
The Termination Record (row 4) also has a CII of
Y. It has a DED (C) of 12/31/9999. The old
terminated record is deleted (row 2). The
deletion causes the CII (B) to become D and the
DSD to be Deleted. The DED (C) for the deleted
row become null, because it was never in effect.
70
Example Visiting Job, Renewed
C
E
A
B
D
1 2 3 4
With the reappointment record (row 3), the
original Record (row 1) now has a DSD (E) of
Expired. The original record (row 1) DED (E)
changes to the day before the reappointment
records (row 3) effective date (D). With the new
termination record (row 4), the old Termination
record (row 2) is deleted. It now has a CII (B)
of D and a DSD (E) of Deleted. Because it was
never in effect, the DED (C), is null.
71
Example Visiting Job turns permanent
B
C
E
A
D
1 2 3 4 5
On April 15, 2004 (A) the job becomes permanent
and the person is given a raise. Row 4 is the new
job and raise and row 5 is the deletion of the
future dated job termination. The reappointment
record (row 3) now has a DSD (E) of Expired and
a DED of 4/14/04. This is the date before the
new job record (row 4) becomes effective
(D). The Future dated termination record (row
5), will now have a CII (B) of D, a DSD of
Deleted and a DED of null.
72
Example Salary Correction
A
B
C
D
E
1 2 3 4 5 6
On April 15, 2004 (A) the salary is corrected.
Row 4 is changed in the source system and creates
row 5 in the EDW. Row 4 then has a CII (B) of
N, a DSD (E) of Expired and DED of Null.
The corrected record (row 5) has a CII of Y a
DSD of Current and a DED of 12/31/9999.
73
Example Final Comments
1 2 3 4 5 6
There will always be only one row for a job with
a DSD of Current. There will only be a DED
when a record was active or future dated.
74
HR Salary Planner Notes

• Decision Support captures initial and final
  data from the HR Salary Planner module
• The Salary Planner tables will only contain data
  that was in Salary Planner
• When joining Salary Planner tables, you need to
  include both the Extract Code and Scenario
  Description.
• The scenario description (SP_HR_SCENARIO_DESC)
  indicates whether the data is from an INITIAL
  or FINAL scenario.
• You will want to limit on the Fiscal Year Code,
  although currently there is only one fiscal year
  in the EDW Salary Planner tables.
• Salary Planner contains both a base and final
  amount. In the EDW, we refer to these as _BASE
  and _NEW amounts. Weve used this naming
  convention everywhere Banner has provided us with
  base and final values.

75
HR Salary Planner Notes (contd)

• The job change reasons for a particular job are
  only available to the organizations that own the
  job, following the security schema established in
  the HR-Pay increment.
• The EDW provides a C-FOAP string within Salary
  Planner that is concatenated for easier
  comparisons. All the C-FOAPAL elements are still
  available for individual manipulation.
• Each labor distribution table contains the grant
  code where appropriate. The grant code is
  denormalized from the fund code table at the time
  it was loaded into the EDW.
• Each labor distribution table contains a funding
  description. The funding description is derived
  from the first character of the fund code at the
  time it was loaded into the EDW.
• Each labor distribution table contains a
  Distribution Percent. This is not the percentage
  of the person that is funded by the C-FOAPAL, but
  rather the percentage of the C-FOAPAL that is
  funding a particular job.

76
HR Salary Planner Notes (contd)

• There appears to be some data quality issues with
  the Salary Planner Job Change Percent field
  (SP_JOB_CHG_PCT).
• To determine how an extract was created, refer to
  the HR Salary Planner Extract, HR Salary Planner
  Extract Employee Class and HR Salary Planner
  Extract Budget Profile tables.
• Similar to the Position Job Hierarchy labor
  distribution tables, the labor distribution
  tables in Salary Planner also do not contain
  keys. It is easy to create a run-away query.
• While there is an open position indicator, it is
  currently populated with a N. After the data
  has been in use, DS will work with the
  Institutional Researchers to determine a proper
  derivation for this field.

77
HR Salary Planner Tables
78
HR-Pay and Salary Planner Interaction

• The Salary Planner Job table is most like the Job
  Detail History table in HR-Pay.
• The Salary Planner Job Change Reason will include
  detailed information regarding why a salary
  change occurred, while the HR Job Change Reason
  will only indicate that there was a Salary
  Planner change to the Job Detail History record.
• Be careful using the Data Status Description
  (DSD) when interacting with Salary Planner
  tables. If any information on the Job Detail
  History record has changed since the Salary
  Planner upload, you will not join these two table
  accurately using DSD.
• Remember to limit by Fiscal Year when joining the
  Salary Planner tables to the Payroll Accounting
  Detail table.
• Do not join using COA Code and ORG code where
  they are not part of the key. Rather, limit the
  query on all tables to the specific COA and ORG
  combination.

79
Deduction Administration Notes

• Except for the Payroll Deduction Taken table, all
  other deduction tables are a record of the
  deductions to be taken from a payroll.
• In the Payroll Deduction Taken table, the payroll
  deduction type description will indicate which
  deduction set-up table to which it is related.
• Benefit Labor Distribution sets up the C-FOAPAL
  each deduction should be charged to.
• The Benefit Person Employee Indicator is an EDW
  derived field which indicates whether the Benefit
  Person is the employee.

80
Deduction Administration Tables Include
81
Deduction Administration Tables Contd
82
History Tables
83
History Tracking EDW
The EDW tracks history on the following tables
84
History Tracking EDW (continued)
85
History Tracking Banner
Banner tracks history on the following tables
86
Event Tables
87
Change Event Tables Employee
The EDW tracks changes on the employee table for
the following columns
88
Change Event Tables Job
The EDW tracks changes on the job table for the
following columns
89
Change Event Tables Job Detail
The EDW tracks changes on the job detail table
for the following columns
90
Change Event Tables Person
The EDW tracks changes on the person table for
the following columns
91
Sample Reporting
92
Example 1 Department Directory
Pop Quiz!

• What are the primary subject areas for a
  department directory?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo
• F) HR Salary Planner

93
Example 1 Department Directory

• Which tables would you use to create a department
  directory?

• V_EMPEE_PERS_HIST_1
• V_CAMPUS_ADDR_HIST
• V_CAMPUS_TELE_HIST
• V_JOB_DETL_HIST_1

94
Example 1 Department Directory

• Which columns would you use to create a
  department directory?

• PERS_LNAME
• PERS_MNAME
• PERS_FNAME
• ADDR_STREET_LINE_1
• ADDR_STREET_LINE_2
• ADDR_STREET_LINE_3
• ADDR_CITY
• ADDR_STATE_CD

• ADDR_ZIP_CD
• TELE_AREA_CD
• TELE_NBR
• TELE_EXT
• JOB_DETL_DEPT_NAME

95
Example 2 List employees by gender, race
ethnicity, salary, and FTE
Pop Quiz!

• What is the primary subject area to list
  employees by gender, race ethnicity, salary, FTE?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo
• F) HR Salary Planner

96
Example 2 List employees by gender, race
ethnicity, salary, and FTE

• Which tables would you use to list employees by
  gender, race ethnicity, salary, and FTE?

• V_EMPEE_PERS_HIST_1
• V_PERS_RACE_ETH_HIST_5
• V_JOB_DETL_HIST_1
• T_RACE_ETH_CD_HIST

97
Example 2 List employees by gender, race
ethnicity, salary, and FTE

• Which columns would you use to a list employees
  by gender, race ethnicity, salary, and FTE?

• PERS_LNAME
• PERS_MNAME
• PERS_FNAME
• SEX_CD
• SEX_DESC

• RACE_ETH_CD
• RACE_ETH_DESC
• JOB_DETL_ANNL_SAL
• JOB_DETL_FTE

98
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary
Pop Quiz!

• What is the primary subject area for a Summary of
  employee by all jobs, campuses, colleges,
  departments, FTE, salary?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo
• F) HR Salary Planner

99
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary

• Which tables would you use to create a Summary of
  employee by all jobs, campuses, colleges,
  departments, FTE, salary?

• V_EMPEE_PERS_HIST_1
• V_JOB_DETL_HIST_1

100
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary

• Which columns would you use to create a Summary
  of employee by all jobs, campuses, colleges,
  departments, FTE, salary?

• PERS_LNAME
• PERS_FNAME
• PERS_MNAME
• POSN_NBR
• PRIMARY_JOB_IND
• JOB_SUFFIX
• JOB_DETL_ANNL_SALARY

• JOB_DETL_EMPEE_CLS_LONG_DESC
• JOB_DETL_TITLE
• JOB_DETL_CAMPUS_NAME
• JOB_DETL_COLL_NAME
• JOB_DETL_DEPT_NAME
• JOB_DETL_FTE

101
Example 4 Analysis of Contract Information by
Job
Pop Quiz!

• What is the primary subject area for analysis of
  contract information by job?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo
• F) HR Salary Planner

102
Example 4 Analysis of Contract Information by
job

• Which tables would you use to analyze contract
  information by job?

• T_JOB_HIST
• V_JOB_DETL_HIST_1
• V_EMPEE_PERS_HIST_1
• T_CNTRCT_HIST
• T_CNTRCT_PARM_HIST

103
Example 4 Analysis of Contract Information by
job

• Which columns would you use to analyze contract
  information by job?

• PERS_LNAME
• PERS_FNAME
• PERS_MNAME
• POSN_NBR
• JOB_TITLE
• JOB_SUFFIX

• JOB_DETL_FTE
• CNTRCT_TYPE_CD
• CNTRCT_SVC_BGN_DT
• CNTRCT_SVC_END_DT
• CNTRCT_ANNIV_DT

104
Example 5 List of employees with Medical
Benefits on Leave of Absence
Pop Quiz!

• What is the primary subject area for a list of
  medical benefits on leave of absence?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo
• F) HR Salary Planner

105
Example 5 List of employees with Medical
Benefits on Leave

• Which tables would you use to list employees with
  medical benefits on leave of absence?

• T_LOA_HIST
• T_MED_DED_HIST
• V_EMPEE_PERS_HIST_1
• V_HOME_ADDR_HIST

106
Example 5 List of employees with Medical
Benefits on Leave

• Which columns would you use to list employees
  with medical benefits on leave of absence?

• PERS_FNAME
• PERS_MNAME
• PERS_LNAME
• ADDR_STREET_LINE_1
• ADDR_STREET_LINE_2
• ADDR_STREET_LINE_3
• ADDR_CITY

• ADDR_STATE_CD
• ADDR_ZIP_CD
• MED_DED_CD
• LOA_CAMPUS_CD
• LOA_FROM_DT
• LOA_TO_DT

107
Example 6 List Retirement Deductions taken for
the UIUC campus by Payroll Run?
Pop Quiz!

• What is the primary subject area for retirement
  deductions taken for UIUC?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo Universe
• F) HR Salary Planner

108
Example 6 List Retirement Deductions taken for
the UIUC campus by Payroll Run?

• Which tables would you use to list retirement
  deductions taken for the UIUC campus by Payroll
  Run?

• V_JOB_DETL_HIST_1
• T_PAYR_DED_TAKEN
• V_EMPEE_PERS_HIST_1

109
Example 6 List Retirement Deductions taken for
the UIUC campus by Payroll Run?

• Which tables would you use to list retirement
  deductions taken for the UIUC campus by Payroll
  Run?

• PERS_FNAME
• PERS_MNAME
• PERS_LNAME
• PAYR_DED_TYPE_DESC
• PAYR_DED_EMPEE_AMT
• PAYR_DED_EMPLR_AMT
• T_JOB_DETL_CAMPUS_CD

• PAYR_ID
• PAYR_YR
• PAYR_NBR
• PAYR_SEQ_NBR

110
Example 7 Compare Actual Payroll Expenses to
those obligated in Salary Planner
Pop Quiz!

• What is the primary subject area for actual
  payroll expenses compared with Salary Planner
  data?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo Universe
• F) HR Salary Planner

111
Example 7 Compare Actual Payroll Expenses to
those obligated in Salary Planner

• Which tables would you use to show actual payroll
  expenses compared with Salary Planner data?

• T_SP_JOB
• T_SP_JOB_LBR_DISTR
• T_PAYR_ACCTG_DETL

112
Example 7 Compare Actual Payroll Expenses to
those obligated in Salary Planner

• Which tables would you use to show actual payroll
  expenses compared with Salary Planner data?

• EDW_PERS_ID 
• POSN_NBR
• JOB_SUFFIX
• SP_JOB_ANNL_SAL_NEW
• SP_JOB_DISTR_CFOAP
• SP_JOB_DISTR_PCT
• SP_JOB_DISTR_AMT

• PAYR_YR
• PAYR_ID
• PAYR_NBR
• PAYR_SEQ_NBR
• PAYR_ACCTG_EARNS_CD,
• PAYR_ACCTG_EXPS_AMT

113
Contacts
114
Contact DS When

• To Report Problems
• I tried to find out xyz but the results dont
  make sense to me
• My query took significantly longer than usual
• Something unexpected happened as my query
  executed
• You werent sure how to build a query, even after
  referring to the metadata and your data education
  materials
• When You Report Problems, Be Sure to Include
• What tables and columns youre using
• As exactly as possible any error messages
• To Suggest Enhancements
• Hearing from you about each of the following will
  help us understand how build a better universe or
  even another data product such as a data mart
• You find yourself always doing something as you
  create each report
• You always filter on some value
• You always do a wildcard search
• You always put two objects together
• You need to do something that EDW does not
  support

115
Ways to Contact DS

• Create a Clarify Ticket
• File an on-line ticket at
• http//onlinesupport.uillinois.edu/ds.html
• Phone the AITS Help Desk
• Chicago [phone]
• Urbana [phone]
• Springfield [phone]
• Email the AITS Help Desk
• helpdesk2_at_uillinois.edu (one address for all 3
  campuses to use)
• If you have access to Clarify,
• Send a Clarify Ticket directly to the Decision
  Support Queuewell take it from there the
  appropriate person will respond.

116
Questions?
One last suggestion Keep your Data Ed materials
for future reference Keep notes for your own
ready reference Review the latest Data Ed
material from the DS Web site.
117
Appendix
118
Example Report Cheat Sheet
119
Pop Quiz!

• The Data Warehouse
• A) is read only
• B) is for auditing Banner
• C) includes everything in Banner

• Decision Support
• A) is a part of AITS
• B) will write my reports for me
• C) supports analytical reporting

120
Pop Quiz!

• Metadata is
• A) a very large volume of data
• B) only for very technical users
• C) documentation about data

• Source-to-Target Mappings
• A) indicates the location of the data in the
  source system
• B) shows how to find data in the DW
• C) gives the exact Banner form where information
  in the Universe came from

121
Example 1 Department Directory
Pop Quiz!

• What are the primary subject areas for a
  department directory?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo

122
Example 1 Department Directory

• Which tables would you use to create a department
  directory?

• V_EMPEE_PERS_HIST_1
• V_CAMPUS_ADDR_HIST
• V_CAMPUS_TELE_HIST
• V_JOB_DETL_HIST_1

123
Example 1 Department Directory

• Which columns would you use to create a
  department directory?

• PERS_LNAME
• PERS_MNAME
• PERS_FNAME
• ADDR_STREET_LINE_1
• ADDR_STREET_LINE_2
• ADDR_STREET_LINE_3
• ADDR_CITY
• ADDR_STATE_CD

• ADDR_ZIP_CD
• TELE_AREA_CD
• TELE_NBR
• TELE_EXT
• JOB_DETL_DEPT_NAME

124
Example 1 Department Directory
select distinct pers_lname, pers_fname,
pers_mname, v_job_detl_hist_1.edw_pers_id,
addr_street_line_1, addr_street_line_2,
addr_street_line_3, addr_city, addr_state_cd,
addr_zip_cd, tele_area_cd, tele_nbr, tele_ext,
V_EMPEE_CAMPUS_EMAIL_ADDR.EMAIL_ADDR from
v_job_detl_hist_1, t_job_hist, v_empee_pers_hist_1
, v_empee_campus_addr_hist,
V_EMPEE_CAMPUS_TELE_HIST, V_EMPEE_CAMPUS_EMAIL_ADD
R where v_job_detl_hist_1.edw_pers_idt_job_hist.e
dw_pers_id and v_job_detl_hist_1.posn_nbrt_job_hi
st.posn_nbr and v_job_detl_hist_1.job_suffixt_job
_hist.job_suffix and v_job_detl_hist_1.edw_pers_id
v_empee_pers_hist_1.edw_pers_id and
v_job_detl_hist_1.edw_pers_idv_empee_campus_addr_
hist.edw_pers_id and v_empee_campus_addr_hist.addr
_type_cd'C1' and v_empee_campus_addr_hist.addr_st
atus_desc'Active' and v_job_detl_hist_1.edw_pers_
idv_empee_campus_tele_hist.edw_pers_id and
v_empee_campus_tele_hist.tele_type_cd'C1' and
v_empee_campus_tele_hist.tele_cur_info_ind'Y' and
v_job_detl_hist_1.JOB_DETL_DATA_STATUS_DESC'Curr
ent' and t_job_hist.JOB_DATA_STATUS_DESC'Current'
AND v_job_detl_hist_1.JOB_DETL_STATUS_DESC ltgt
'Terminated' and v_empee_pers_hist_1.PERS_CUR_INFO
_IND'Y' and v_empee_pers_hist_1.edw_pers_idV_EMP
EE_CAMPUS_EMAIL_ADDR.edw_pers_id() and
EMAIL_STATUS_IND'A' and v_job_detl_hist_1.JOB_DET
L_DEPT_NAME'Decision Support Team' order by
pers_lname
125
Example 2 List employees by gender, race
ethnicity, salary, and FTE
Pop Quiz!

• What is the primary subject area to list
  employees by gender, race ethnicity, salary, FTE?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo

126
Example 2 List employees by gender, race
ethnicity, salary, and FTE

• Which tables would you use to list employees by
  gender, race ethnicity, salary, and FTE?

• V_EMPEE_PERS_HIST_1
• V_PERS_RACE_ETH_HIST_5
• V_JOB_DETL_HIST_1
• T_RACE_ETH_CD_HIST

127
Example 2 List employees by gender, race
ethnicity, salary, and FTE

• Which columns would you use to a list employees
  by gender, race ethnicity, salary, and FTE?

• PERS_LNAME
• PERS_MNAME
• PERS_FNAME
• SEX_CD
• SEX_DESC

• RACE_ETH_CD
• RACE_ETH_DESC
• JOB_DETL_ANNL_SAL
• JOB_DETL_FTE

128
Example 2 List employees by gender, race
ethnicity, salary, and FTE
select distinct pers_lname, pers_fname,
pers_mname, sex_desc, t_race_eth_cd_hist.RACE_ETH_
DESC, job_detl_annl_sal, job_detl_fte from
v_job_detl_hist_1, t_job_hist, v_empee_pers_hist_1
, v_empee_pers_hist_3, V_EMPEE_RACE_ETH_HIST_5,
t_race_eth_cd_hist where v_job_detl_hist_1.edw_pe
rs_idt_job_hist.edw_pers_id and
v_job_detl_hist_1.posn_nbrt_job_hist.posn_nbr
and v_job_detl_hist_1.job_suffixt_job_hist.job_su
ffix and v_job_detl_hist_1.edw_pers_idv_empee_per
s_hist_1.edw_pers_id and v_job_detl_hist_1.JOB_DET
L_DATA_STATUS_DESC'Current' and
t_job_hist.JOB_DATA_STATUS_DESC'Current' AND
v_job_detl_hist_1.JOB_DETL_STATUS_DESC ltgt
'Terminated' and v_empee_pers_hist_1.edw_pers_idv
_empee_pers_hist_3.edw_pers_id and
v_empee_pers_hist_1.PERS_HIST_EFF_DTv_empee_pers_
hist_3.PERS_HIST_EFF_DT and v_empee_pers_hist_1.ED
W_PERS_IDV_EMPEE_RACE_ETH_HIST_5.EDW_PERS_ID
and V_EMPEE_RACE_ETH_HIST_5.PERS_RACE_ETH_CUR_INFO
_IND'Y' and t_race_eth_cd_hist.RACE_ETH_CDv_empe
e_race_eth_hist_5.RACE_ETH_CD and
T_RACE_ETH_CD_HIST.RACE_ETH_CD_CUR_INFO_IND'Y' an
d v_empee_pers_hist_1.PERS_CUR_INFO_IND'Y' and
v_job_detl_hist_1.JOB_DETL_DEPT_NAME'Decision
Support Team' order by pers_lname
129
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary
Pop Quiz!

• What is the primary subject area for a Summary of
  employee by all jobs, campuses, colleges,
  departments, FTE, salary?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo

130
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary

• Which tables would you use to create a Summary of
  employee by all jobs, campuses, colleges,
  departments, FTE, salary?

• V_EMPEE_PERS_HIST_1
• V_JOB_DETL_HIST_1

131
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary

• Which columns would you use to create a Summary
  of employee by all jobs, campuses, colleges,
  departments, FTE, salary?

• PERS_LNAME
• PERS_FNAME
• PERS_MNAME
• POSN_NBR
• PRIMARY_JOB_IND
• JOB_SUFFIX
• JOB_DETL_ANNL_SALARY

• JOB_DETL_EMPEE_CLS_LONG_DESC
• JOB_DETL_TITLE
• JOB_DETL_CAMPUS_NAME
• JOB_DETL_COLL_NAME
• JOB_DETL_DEPT_NAME
• JOB_DETL_FTE

132
Example 3 Summary of employee by all jobs,
campuses, colleges, departments, FTE, salary
select pers_lname, pers_fname, pers_mname,
jdh1.posn_nbr, jdh1.job_suffix,
job_detl_campus_name, job_detl_coll_name,
job_detl_dept_name, job_detl_fte,
job_detl_annl_sal from v_empee_pers_hist_1 eph1,
v_job_detl_hist_1 jdh1 where eph1.edw_pers_idjdh1
.edw_pers_id and eph1.pers_cur_info_ind'Y'
and jdh1.job_detl_data_status_desc'Current'
and jdh1.job_detl_status_desc ltgt 'Terminated' and
exists (select 'X' from v_empee_pers_hist_1
eph2, v_job_detl_hist_1 jdh2 where
eph1.edw_pers_ideph2.edw_pers_id and
eph2.edw_pers_idjdh2.edw_pers_id and
jdh2.JOB_DETL_DATA_STATUS_DESC'Current' and
jdh2.JOB_DETL_STATUS_DESC ltgt 'Terminated' and
jdh2.JOB_DETL_DEPT_NAME'Decision Support
Team') order by pers_lname
133
Example 4 Analysis of Contract Information by
Job
Pop Quiz!

• What is the primary subject area for analysis of
  contract information by job?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo

134
Example 4 Analysis of Contract Information by
job

• Which tables would you use to analyze contract
  information by job?

• T_JOB_HIST
• V_JOB_DETL_HIST_1
• V_EMPEE_PERS_HIST_1
• T_CNTRCT_HIST
• T_CNTRCT_PARM_HIST

135
Example 4 Analysis of Contract Information by
job

• Which columns would you use to analyze contract
  information by job?

• PERS_LNAME
• PERS_FNAME
• PERS_MNAME
• POSN_NBR
• JOB_TITLE
• JOB_SUFFIX

• JOB_DETL_FTE
• CNTRCT_TYPE_CD
• CNTRCT_SVC_BGN_DT
• CNTRCT_SVC_END_DT
• CNTRCT_ANNIV_DT

136
Example 4 Analysis of Contract Information by
job
select pers_lname, pers_fname, pers_mname,
v_job_detl_hist_1.posn_nbr, v_job_detl_hist_1.job_
suffix, cntrct_svc_bgn_dt, cntrct_svc_end_dt,
cntrct_anniv_dt, cntrct_type_cd from
v_empee_pers_hist_1, v_job_detl_hist_1,
t_job_hist, t_cntrct_hist, t_cntrct_parm_hist wher
e v_empee_pers_hist_1.edw_pers_idv_job_detl_hist_
1.edw_pers_id and v_job_detl_hist_1.edw_pers_idt_
job_hist.edw_pers_id and v_job_detl_hist_1.posn
_nbrt_job_hist.posn_nbr and
v_job_detl_hist_1.job_suffixt_job_hist.job_suffix
and t_job_hist.JOB_DATA_STATUS_DESC'Current'
and t_job_hist.edw_pers_idt_cntrct_hist.edw_pers_
id and t_job_hist.posn_nbrt_cntrct_hist.posn_n
br and t_job_hist.job_suffixt_cntrct_hist.job_
suffix and t_cntrct_hist.CNTRCT_DATA_STATUS_DES
C'Current' and t_job_hist.edw_pers_idt_cntrct_pa
rm_hist.edw_pers_id() and t_job_hist.posn_nbr
t_cntrct_parm_hist.posn_nbr() and
t_job_hist.job_suffixt_cntrct_parm_hist.job_suffi
x() and v_empee_pers_hist_1.PERS_CUR_INFO_IND'Y'
and job_detl_data_status_desc'Current' and
v_job_detl_hist_1.JOB_DETL_STATUS_DESCltgt'Terminate
d' and v_job_detl_hist_1.JOB_DETL_DEPT_NAME'Decis
ion Support Team' order by pers_lname
137
Example 5 List of employees with Medical
Benefits on Leave of Absence
Pop Quiz!

• What is the primary subject area for a list of
  medical benefits on leave of absence?
• A) Deduction Administration
• B) Employee Administration
• C) Position Job Hierarchy
• D) Payroll Time Attendance
• E) Bio-Demo

138
Example 5 List of employees with Medical