Skip Headers
Oracle® Business Intelligence Applications Installation and Configuration Guide for Oracle Data Integrator Users
Version 7.9.5.2
Part Number E13669-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

9 Configuring Oracle Human Resources Analytics

This chapter describes how to configure Oracle Human Resources Analytics for particular sources to meet your business needs, and contains the following topics:

To find out about other possible tasks required to deploy Oracle Business Intelligence Applications, see Section 2.4, "Roadmap To Installing, Configuring, and Customizing Oracle Business Intelligence Applications With ODI".

9.1 Overview of Oracle Human Resources Analytics

Oracle Human Resources contains information for HR operations, employee events, and payroll.

The Oracle HR Analytics application has the following functional areas:

9.2 Configuration Required Before A Full Load for Oracle HR Analytics

This section contains configuration steps that you need to perform on Oracle HR Analytics before you do a full data load, and contains the following topics:

9.2.1 About Domain Values and CSV Worksheet Files for Oracle HR Analytics

You configure Oracle HR Analytics by mapping domain values to columns in the CSV files located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

For more information on domain values, see Oracle Business Analytics Warehouse Data Model Reference.

Note:

Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

Table 9-1 lists the CSV worksheet files and the domain values for Oracle HR Analytics in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

Table 9-1 Domain Values and CSV Worksheet Files for Oracle HR Analytics

Worksheet File Name Domain Value Table - Column Description Package/Interface

domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv

W_EMPLOYEE_D.W_ETHNIC_GRP_CODE

Lists the Ethnic codes and their corresponding domain values of 'Ethnic Group Code' for Oracle EBS.

SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full

domainValues_Employee_Sex_MF_ora<ver>.csv

W_EMPLOYEE_D.W_SEX_MF_CODE

Lists the Sex codes and their corresponding domain values of 'Sex Code' for Oracle EBS.

SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full

domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

W_EMPLOYEE_D.W_VETERAN_STATUS_CODE

Lists the Veteran codes and their corresponding domain values of 'Veteran Status Code' for Oracle EBS.

SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full

domainValues_Employment_Cat_ora<ver>.csv

W_EMPLOYMENT_D.W_EMPLOYEE_CAT_CODE

Lists the User Person Types and their corresponding domain values of 'Employment Category Code' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Exempt_Flg_ora<ver>.csv

W_EMPLOYMENT_D.W_EXEMPT_FLG

Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Full_Time_Flg_ora<ver>.csv

W_EMPLOYMENT_D.W_FULL_TIME_FLG

Lists the Employment Categories and their corresponding domain values of 'Full Time Flag' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Status_ora<ver>.csv

W_EMPLOYMENT_D.W_EMPLOYMENT_STAT_CODE

Lists the Per System Statuses and their corresponding domain values of 'Employment Status' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_EventTypes_ora<ver>.csv

W_EVENT_TYPE_D.W_EVENT_CLASS, W_EVENT_TYPE_D.W_EVENT_GRP_CODE, W_EVENT_TYPE_D.W_EVENT_REASON_CODE, W_EVENT_SUBG_CODE

Lists the Event Types, Event Codes and Meanings and their corresponding domain values of 'Event Group', 'Event Sub-Group' and 'Event Reason' for Oracle EBS.

SDE_ORA_EventTypeDimension_AbsenceAttendance, SDE_ORA_EventTypeDimension_AbsenceAttendance_Full, SDE_ORA_EventTypeDimension_AdditionalEvents_FromFile, SDE_ORA_EventTypeDimension_OtherHREvents, SDE_ORA_EventTypeDimension_OtherHREvents_Full

domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv

W_HR_POSITION_D.W_ACTIVE_POSITION_FLG

Lists the Position Statuses and their corresponding domain values of 'Active Position Flag' for Oracle EBS.

SDE_ORA_HRPositionDimension, SDE_ORA_HRPositionDimension

domainValues_HRPosition_Exempt_Flg_ora<ver>.csv

W_HR_POSITION_D.W_EXEMPT_FLG

Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for Oracle EBS.

SDE_ORA_HRPositionDimension, SDE_ORA_HRPositionDimension

domainValues_Job_Eeo_Cat_Code_ora<ver>.csv

W_JOB_D.W_EEO_JOB_CAT_CODE

Lists the EEO Job Categories and their corresponding domain values of 'EEO Job Category' for Oracle EBS.

SDE_ORA_JobDimension, SDE_ORA_JobDimension_Full

domainValues_Job_Flsa_Stat_Code_ora<ver>.csv

W_JOB_D.W_FLSA_STAT_CODE

Lists the FLSA Statuses and their corresponding domain values of 'FLSA Status Code' for Oracle EBS.

SDE_ORA_JobDimension, SDE_ORA_JobDimension_Full

domainValues_Pay_Type_Grp_Code_ora<ver>.csv

W_PAY_TYPE_D.W_PAY_TYPE_GRP_CODE

Lists the Classification Names, Element Names and their corresponding domain values of 'Pay Type Group Code' for Oracle EBS.

SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full

domainValues_Pay_Type_Flg_ora<ver>.csv

W_PAY_TYPE_D.W_PAY_TYPE_FLG

Lists the Costing Debit or Credit values and their corresponding domain values of 'Pay type Flag' for Oracle EBS.

SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full

domainValues_Employee_Sex_MF_ora<ver>.csv

W_EMPLOYEE_D.W_SEX_MF_CODE

Lists the Sex codes and their corresponding domain values of 'Sex Code' for Oracle EBS.

SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full

domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

W_EMPLOYEE_D.W_VETERAN_STATUS_CODE

Lists the Veteran codes and their corresponding domain values of 'Veteran Status Code' for Oracle EBS.

SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full

domainValues_Employment_Cat_ora<ver>.csv

W_EMPLOYMENT_D.W_EMPLOYEE_CAT_CODE

Lists the User Person Types and their corresponding domain values of 'Employment Category Code' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Exempt_Flg_ora<ver>.csv

W_EMPLOYMENT_D.W_EXEMPT_FLG

Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Full_Time_Flg_ora<ver>.csv

W_EMPLOYMENT_D.W_FULL_TIME_FLG

Lists the Employment Categories and their corresponding domain values of 'Full Time Flag' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_Employment_Status_ora<ver>.csv

W_EMPLOYMENT_D.W_EMPLOYMENT_STAT_CODE

Lists the Per System Statuses and their corresponding domain values of 'Employment Status' for Oracle EBS.

SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full

domainValues_EventTypes_ora<ver>.csv

W_EVENT_TYPE_D.W_EVENT_CLASS, W_EVENT_TYPE_D.W_EVENT_GRP_CODE, W_EVENT_TYPE_D.W_EVENT_REASON_CODE, W_EVENT_SUBG_CODE

Lists the Event Types, Event Codes and Meanings and their corresponding domain values of 'Event Group', 'Event Sub-Group' and 'Event Reason' for Oracle EBS.

SDE_ORA_EventTypeDimension_AbsenceAttendance, SDE_ORA_EventTypeDimension_AbsenceAttendance_Full, SDE_ORA_EventTypeDimension_AdditionalEvents_FromFile, SDE_ORA_EventTypeDimension_OtherHREvents, SDE_ORA_EventTypeDimension_OtherHREvents_Full

domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv

W_HR_POSITION_D.W_ACTIVE_POSITION_FLG

Lists the Position Statuses and their corresponding domain values of 'Active Position Flag' for Oracle EBS.

SDE_ORA_HRPositionDimension, SDE_ORA_HRPositionDimension

domainValues_HRPosition_Exempt_Flg_ora<ver>.csv

W_HR_POSITION_D.W_EXEMPT_FLG

Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for Oracle EBS.

SDE_ORA_HRPositionDimension, SDE_ORA_HRPositionDimension

domainValues_Job_Eeo_Cat_Code_ora<ver>.csv

W_JOB_D.W_EEO_JOB_CAT_CODE

Lists the EEO Job Categories and their corresponding domain values of 'EEO Job Category' for Oracle EBS.

SDE_ORA_JobDimension, SDE_ORA_JobDimension_Full

domainValues_Job_Flsa_Stat_Code_ora<ver>.csv

W_JOB_D.W_FLSA_STAT_CODE

Lists the FLSA Statuses and their corresponding domain values of 'FLSA Status Code' for Oracle EBS.

SDE_ORA_JobDimension, SDE_ORA_JobDimension_Full

domainValues_Pay_Type_Grp_Code_ora<ver>.csv

W_PAY_TYPE_D.W_PAY_TYPE_GRP_CODE

Lists the Classification Names, Element Names and their corresponding domain values of 'Pay Type Group Code' for Oracle EBS.

SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full

domainValues_Pay_Type_Flg_ora<ver>.csv

W_PAY_TYPE_D.W_PAY_TYPE_FLG

Lists the Costing Debit or Credit values and their corresponding domain values of 'Pay type Flag' for Oracle EBS.

SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full

Note:

When editing CSV files, make sure that you:

  • Do no change the case of values in the CSV file.

    For example, do not change 'CONTRACTOR' to 'Contractor'.

  • Do not add new values to the W_ columns, which are not already included in the CSV file.

    In other words, you can add new rows to the spreadsheet, but the W_ values must map to those in the out-of-the-box spreadsheet.

9.2.2 How to Configure the Employee Ethnic Group Codes

This section explains how to configure the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv file.

  1. Identify the Ethnic Group Codes in your Oracle source system by using the following SQL (executed using APPS schema credentials):

    SELECT DISTINCT PER_INFORMATION1 FROM PER_ALL_PEOPLE_F 
WHERE PER_INFORMATION1 in ('1','2','3','4','5','6','7','8','9','10','11','12','BA','BC','BO','C','I','O','P','W')
ORDER BY 1

  2. Open the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the PER_INFORMATION1 to the ETHNIC_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each Ethnic Code to one domain value.

  5. Save and close the file.

9.2.3 How to Configure the Employee Sex Codes

This section explains how to configure Employee Sex Codes using the file domainValues_Employee_Sex_MF_ora<ver>.csv.

  1. Identify the Sex Codes in your Oracle source system by using the following SQL:

    SELECT DISTINCT SEX FROM PER_ALL_PEOPLE_F ORDER BY 1

  2. Open the domainValues_Employee_Sex_MF_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the SEX column to the SEX column in the file. The data must be copied starting from the 6th line.

  4. Map each Sex Code to one domain value.

  5. Save and close the file.

9.2.4 How to Configure Employee Veteran Status Codes

This section explains how to configure Employee Veteran Status codes using the file domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

  1. Identify the Veteran Status Codes in your Oracle source system by using the following SQL:

    SELECT DISTINCT PER_INFORMATION5 FROM PER_ALL_PEOPLE_F
WHERE PER_INFORMATION5 in ('NOTVET', 'OTEDV', 'VET', 'VETDIS', 'VIETVET', 'VIETVETDIS')
ORDER BY 1

  2. Open the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the PER_INFORMATION5 column to the VETERAN_STATUS_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each Veteran Status Code to one domain value.

  5. Save and close the file.

9.2.5 How to Configure the Employment Category Codes

This section explains how to configure Employment Category Codes using the file domainValues_Employment_Cat_ora<ver>.csv.

  1. Identify the User Person Types in your Oracle source system by using the following SQL:

    SELECT DISTINCT SYSTEM_PERSON_TYPE, USER_PERSON_TYPE FROM
PER_PERSON_TYPES
WHERE SYSTEM_PERSON_TYPE IN
('EMP','OTHER','EMP_APL','EX_EMP','EX_EMP_APL','RETIREE','PRTN')
ORDER BY 1,2

  2. Open the domainValues_Employment_Cat_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the User Person Types to the USER_PERSON_TYPE column in the file. The data must be copied starting from the 6th line.

  4. Map each User Person Type (results of the SQL query) to one of the delivered W_EMPLOYEE_CATEGORY_CODE domain values in the delivered example csv file.

    You can map more than one User Person Type to the same W_EMPLOYEE_CATEGORY_CODE by adding in new rows. For example:

    Contractor          CONTRACTOR  CONTRACTOR
Contingent Worker   CONTRACTOR  CONTRACTOR

    System Person Types are also extracted with User Person Type to help you map the domain values. Do not copy the System Person types in the CSV file.

  5. Save and close the file.

9.2.6 How to Configure Employment Exempt Status

This section explains how to configure Employment Exempt Status using the file domainValues_Employment_Exempt_Flg_ora<ver>.csv

  1. Identify the FLSA Statuses in your Oracle source system by using the following SQL:

    SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS  ORDER BY 1

  2. Open the domainValues_Employment_Exempt_Flg_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the JOB_INFORMATION3 to the FLSA_STATUS_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each FLSA_STATUS_CODE to one domain value.

  5. Save and close the file.

9.2.7 How to Configure Employment Full Time Status

This section explains how to configure Employment Full Time Status using the file domainValues_Employment_Full_Time_Flg_ora<ver>.csv.

  1. Identify the Employment Categories in your Oracle source system by using the following SQL:

    SELECT DISTINCT EMPLOYMENT_CATEGORY FROM PER_ALL_ASSIGNMENTS_F ORDER BY 1

  2. Open the domainValues_Employment_Full_Time_Flg_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the Employment Category to the EMPLOYMENT_CATEGORY column in the file. The data must be copied starting from the 6th line.

  4. Map each EMPLOYMENT_CATEGORY to one domain value.

  5. Save and close the file.

9.2.8 How to Configure Employment Status

This section explains how to configure Employment Status using the file domainValues_Employment_Status_ora<ver>.csv

  1. Identify the Per System Statuses in your Oracle source system by using the following SQL:

    SELECT DISTINCT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES
ORDER BY 1

  2. Open the domainValues_Employment_Status_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the PER_SYSTEM_STATUS to the PER_SYSTEM_STATUS column in the file. The data must be copied starting from the 6th line.

  4. Map each PER_SYSTEM_STATUS to one domain value.

  5. Save and close the file.

9.2.9 How to Configure Event Types

This section explains how to configure Event Types using the file domainValues_EventTypes_ora<ver>.csv

  1. Identify the Event Types in your Oracle source system by using the following SQL:

    SELECT DISTINCT LOOKUP_TYPE, LOOKUP_CODE, MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE IN
('EMP_ASSIGN_REASON', 
'LEAV_REAS', 
'PROPOSAL_REASON') 
ORDER BY 1, 2, 3

  2. Open the domainValues_EventTypes_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the Lookup Type, Lookup Code and Meaning to the LOOKUP_TYPE, LOOKUP_CODE, and MEANING columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.

  4. Map each Event Type (LOOKUP_CODE) to one domain value for each of the 3 domain columns — W_EVENT_GRP_CODE, W_EVENT_SUBG_CODE, and W_EVENT_REASON_CODE. Event Category (LOOKUP_TYPE) and Event Description (MEANING) are also extracted with Event Type to help you map the domain values.

  5. Save and close the file.

9.2.10 How to Configure HR Active Position Status

This section explains how to configure HR Active Position Status using the file domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv.

  1. Identify the Position Statuses in your Oracle source system by using the following SQL:

    SELECT DISTINCT STATUS FROM HR_ALL_POSITIONS_F ORDER BY 1

  2. Open the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the STATUS to the STATUS column in the file. The data must be copied starting from the 6th line.

  4. Map each position STATUS to one domain value.

  5. Save and close the file.

9.2.11 How to Configure HR Position Exempt Status

This section explains how to configure HR Position Exempt Status using the file domainValues_HRPosition_Exempt_Flg_ora<ver>.csv.

  1. Identify the FLSA Statuses in your Oracle source system by using the following SQL:

    SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS 
ORDER BY 1

  2. Open the domainValues_HRPosition_Exempt_Flg_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the JOB_INFORMATION3 to the FLSA_STATUS_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each FLSA_STATUS_CODE to one domain value.

  5. Save and close the file.

9.2.12 How to Configure Job EEO Category Codes

This section explains how to configure Job EEO Category Codes using the file domainValues_Job_Eeo_Cat_Code_ora<ver>.csv

  1. Identify the EEO Job Categories in your Oracle source system by using the following SQL:

    SELECT DISTINCT JOB_INFORMATION1 FROM PER_JOBS 
ORDER BY 1

  2. Open the domainValues_Job_Eeo_Cat_Code_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the JOB_INFORMATION1 to the EEO_JOB_CAT_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each EEO_JOB_CAT_CODE to one domain value.

  5. Save and close the file.

9.2.13 How to Configure Job FLSA Status Codes

This section explains how to configure Job FLSA Status Codes using the file domainValues_Job_Flsa_Stat_Code_ora<ver>.csv.

  1. Identify the FLSA Statuses in your Oracle source system by using the following SQL:

    SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS 
ORDER BY 1

  2. Open the domainValues_Job_Flsa_Stat_Code_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the JOB_INFORMATION3 to the FLSA_STAT_CODE column in the file. The data must be copied starting from the 6th line.

  4. Map each FLSA_STAT_CODE to one domain value.

  5. Save and close the file.

9.2.14 How to Configure Pay Type Group Codes

This section explains how to configure Pay Type Group Codes using the file domainValues_Pay_Type_Grp_Code_ora<ver>.csv.

  1. Identify the Pay Elements in your Oracle source system by using the following SQL:

    SELECT DISTINCT CLASSIFICATION_NAME, ELEMENT_NAME 
FROM 
PAY_ELEMENT_TYPES_F, 
PAY_ELEMENT_CLASSIFICATIONS 
WHERE 
PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID AND 
CLASSIFICATION_NAME NOT LIKE '%Information%' AND 
CLASSIFICATION_NAME NOT LIKE '%Employer%' AND 
CLASSIFICATION_NAME NOT LIKE '%Balance%' 
ORDER BY 1, 2

  2. Open the domainValues_Pay_Type_Grp_Code_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the Classification Name and Element Name to the CLASSIFICATION_NAME and ELEMENT_NAME columns in the file respectively. The data must be copied starting from the 6th line. Use commas to separate the entries.

    Note:

    Do not change lines one to four in the domainValues_Pay_Type_Grp_Code_ora<ver>.csv file.

  4. Map each Element Name to one domain value. Classification Names are also extracted with Element Names to help you map the domain values. If the element is not related to Payroll Pay Check, you can map the element to 'OTHER'.

  5. Save and close the file.

9.2.15 How to Configure the Pay Type Flag

This section explains how to configure Pay Type Flags using the file domainValues_Pay_Type_Flg_ora<ver>.csv.

  1. Identify the Costing (Debit or Credit) in your Oracle source system by using the following SQL:

    SELECT DISTINCT COSTING_DEBIT_OR_CREDIT FROM PAY_ELEMENT_CLASSIFICATIONS
ORDER BY 1

  2. Open the domainValues_Pay_Type_Flg_ora<ver>.csv file in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  3. Copy the COSTING_DEBIT_OR_CREDIT to the COSTING_DEBIT_OR_CREDIT column in the file. The data must be copied starting from the 6th line.

  4. Map each _DEBIT_OR_CREDIT to one domain value.

  5. Save and close the file.

9.2.16 How to Configure Address Types for HR Profile

This section explains how to configure Address Types for HR Profile. There are three address fields in the Employee dimension table:

  • Permanent address

  • Mail address

  • Work address

For each of these, we use only the primary ones. In addition, the following logic is used to determine the various types of addresses:

  • Permanent: Address Type = 'H' (This is also the home address, in other words)

  • Mail: Address Type = 'M'. If this is not available, use Permanent address (which can be the best alternate for mailing address).

  • Work: Address Type = 'Default'. If this is not available, use Permanent address (which can be the best alternate for mailing address).

You can modify this logic if required. For example, if your system tracks work address with a special address type 'W', then you should be able to modify the existing logic. Or, if you do not want to assume that the mail address (M) is same as the home address (H), you may want to remove the null evaluation check there.

To configure Address Type:

  1. In ODI Designer, display the Projects view, and expand the 'Oracle BI Applications 7.9.5.2' folder.

  2. Expand the Interfaces node.

  3. Double-click the Interface SDE_ORA_EmployeeDimension_Addresses.EMPLOYEE_D_ADDRESSES_TML to display the Interface: <Name> dialog.

  4. Display the Diagram tab.

  5. In the Target Datastore area, select the column ADDRESS_TYPE.

  6. In the Mapping pane below, modify the value in the Implementation box.

    For example, if you have a specific address type for work addresses, (for example 'W'), you would change the expression for the ADDRESS_TYPE column, from:

    COALESCE(PER_ADDRESSES.ADDRESS_TYPE,'Default')

    To:

    COALESCE(PER_ADDRESSES.ADDRESS_TYPE,'W')

  7. Repeat steps 3 to 6 for the Interface SDE_ORA_EmployeeDimension_Addresses.EMPLOYEE_D_ADDRESSES_TML_FULL.

  8. Save the changes.

  9. Double-click the Interlace Open Interface SDE_ORA_Employee_Dimension.EMPLOYEE_DS to display the Interface: <Name> dialog.

  10. Display the Diagram tab.

  11. Locate the source tables SQ_Employees and LKP_ADDRESSES_WORK.

  12. Select the join between these source tables to display the 'Join between <Name>' pane.

  13. In the Implementation box, update the WHERE clause predicate as follows, from:

    LKP_ADDRESSES_WORK.ADDRESS_TYPE='Default'

    To:

    LKP_ADDRESSES_WORK.ADDRESS_TYPE='W'

  14. Save the changes.

If you do not want to assume that the mail address (M) is the same as the home address (H) in the event of mail address not being available, then you would modify the logic in the Target Datastore for the mappings MAIL_ADDR_EFF_DATE, MAIL_CITY, MAIL_COUNTRY_CODE, MAIL_COUNTRY_NAME, MAIL_COUNTRY_REGION, MAIL_STATE_CODE, MAIL_STATE_NAME, MAIL_STATE_REGION, MAIL_ST_ADDRESS, MAIL_ZIPCODE.

For example, for MAIL_CITY, you might change the value from:

IIF(LKP_ADDRESSES_MAIL.ADDRESS_ID IS NOT NULL,
LKP_ADDRESSES_MAIL.TOWN_OR_CITY,
LKP_ADDRESSES_DEFAULT.TOWN_OR_CITY
)

To:

LKP_ADDRESSES_MAIL.TOWN_OR_CITY

9.2.17 How to Configure Phone Types for HR Profile

This section explains how to configure Phone Types for HR Profile. There are four phone related fields in the Employee dimension table, as follows:

  • Fax

  • Work phone

  • Pager

  • Mobile phone

The following logic is used to arrive at the various types of addresses:

  • Fax: Phone Type = 'WF' (Work Fax)

  • Work Phone: Phone Type = 'W1' (First work phone, if there are more than one)

  • Pager: Phone Type = 'P' (Pager)

  • Mobile: Phone Type = 'M' (Mobile)

You can modify this logic if required. For example, if your system tracks the primary work phone with a special phone type 'WP', instead of W1, then you should be able to modify the existing logic. The same applies for other phone types as well.

To configure Phone Type:

  1. In ODI Designer, display the Projects view, and expand the 'Oracle BI Applications 7.9.5.2' folder.

  2. Expand the Interfaces node.

  3. Double-click the Interface SDE_ORA_Employee_Dimension.EMPLOYEE_DS to display the Interface: <Name> dialog.

  4. Display the Diagram tab.

  5. Locate the source tables Sq_employees and LKP_PHONES_WORK.

  6. Select the join between these source tables to display the 'Join between <Name>' pane, and use the Implementation box to modify the behavior.

    For example, if you have a specific phone type for the primary work phone, 'WP', you might modify the predicate in the expression as follows, from:

    LKP_PHONES_WORK.PHONE_TYPE='W1'

    To:

    LKP_PHONES_WORK.PHONE_TYPE='WP'

  7. Save the changes.

9.2.18 How to Configure Education Degree Codes for Employee Dimension

This section explains how to configure the categories that are used to evaluate the highest education degree code for an employee.

The package SDE_ORA_ EmployeeDimension uses the ODI parameter QUALIFICATION_CATEGORY_LIST to get the list of categories for the defined Education Degrees in Oracle E-Business Suite. The package uses this parameter to identify and populate the Highest Education Degree attribute for the Employee Dimension. Incorrect setup of this parameter might cause data quality issues with the Highest Education Degree attribute in the Employee Dimension.

To configure the categories that are used to evaluate the highest education degree code for an employee:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Common tab.

  4. Specify a value for the QUALIFICATION_CATEGORY_LIST parameter.

    For example, QUALIFICATION_CATEGORY_LIST = 'DEGREE', 'DT'.

  5. Save your changes.

To obtain the list of the valid values for the education degree codes please login to Oracle E-Business Suite Instance using SQL*Plus and execute the following SQL (executed using APPS schema credentials):

SELECT lookup_code, meaning FROM hr_lookups WHERE lookup_type = 'PER_CATEGORIES'

Select from the returned lookup_code column values and decide which ones are used to identify Education Degrees.

9.2.19 About Configuring Key Flexfields

This section explains how to configure Key Flexfields in Oracle EBS applications.

In Oracle EBS Application module, a Flexfield is a field that allows a user to customize the Oracle applications. Each Flexfield in Oracle in turn consists of sub-fields called segments. Each segment is assigned a value set. The value set consists of values.

The main purpose of using Flexfields in Oracle EBS Applications is to customize the applications according to the business environment and practices. Oracle EBS Application allows a user to validate values that are entered in these flexfields. A user can change the structure of a Flexfield depending on the data in the application.

Since Flexfield configurations vary from implementation to implementation, in order to accommodate all kinds of flexfield configurations done at the Oracle EBS Applications end, the attributes sourced from Flexfield columns is parameterized.

To configure Oracle HR Analytics to support flexfields, do the following:

9.2.20 How to Configure the Key Flexfields for the Job Dimension

This section explains how to configure the Key Flexfields Job Code, Job Name and Job Family Code.

To configure the Key Flexfields for the Job Dimension

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Human Resources Analytics from the Select BI Application field

  4. Specify a value for the following parameters:

    • JOBCODE_FLXFLD_SEGMENT_COL = the Column Name of Job Code.

    • JOBFAMILYCODE_FLXFLD_SEGMENT_COL = the Column name of JobGroup Code.

  5. Display the Common tab.

  6. Specify a value for the following parameters:

    • JOBCODE_FLXFLD_SEGMENT_COL = the Column Name of Job Code.

    • JOBNAME_FLXFLD_SEGMENT_COL = the Column Name of the JobName.

To find out what columns are defined in the Flexfield, do the following:

  1. Login with Application Developer Responsibility.

  2. Under the Key Flexfield, click on Segments.

  3. Query for the Application Human Resources and locate the Flexfield Title relevant to Job.

  4. Click on the Segments corresponding to the Flexfield that is being used in order to find the columns being used for the Job Code and Job Name.

9.2.21 How to Configure the Key Flexfields for the HR Position Dimension

This section explains how to configure the Position Number column.

To configure the Key Flexfields for the HR Position Dimension

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Parameters link.

  3. Display the Common tab.

  4. Specify a value for the following parameter:

    • POSITION_NUM_FLXFLD_SEGMENT_COL = the Column Name of Position Num

  5. Save your changes.

To find out what columns are defined in the Flexfield, do the following:

  1. Login with Application Developer Responsibility.

  2. Under the Key Flexfield, click on Segments.

  3. Query for the Application Human Resources and locate the Flexfield Title relevant to Position.

  4. Click on the Segments corresponding to the Flexfield that is being used in order to find the columns being used for the Position Num.

9.2.22 How to Configure the Key Flexfields for the Pay Grade Dimension

This section explains how to configure the Pay Level Name column.

To configure the Key Flexfields for the Pay Grade Dimension

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Common tab.

  4. Specify a value for the following parameter:

    • PAYLVLNAME_FLXFLD_SEGMENT_COL = the Column Name for the Paylevel Name

To find out what columns are defined in the Flexfield, do the following:

  1. Login with Application Developer Responsibility.

  2. Under the Key Flexfield, click on Segments.

  3. Query for the Application Human Resources and locate the Flexfield Title relevant to Job.

  4. Click on the Segments corresponding to the Flexfield that is being used in order to find the columns being used for the Job Code and Job Name.

9.2.23 How to Configure multi-segmented Flexfields

This section explains how to configure HR Analytics for multi-segmented Flexfields for any column.

In case your Flexfield configuration for a given business column involves more that one segment column, or in case you want to report a business column as a concatenation of more than one segment column separated by a character (for example, a period '.'), then the corresponding parameter can be used to provide the information correctly. The example of configuring Job Name flexfield is provided below to illustrate the process.

When Oracle Business Intelligence Applications is installed out-of-the-box, the ODI parameter for the package 'SDE_ORA_CodeDimension_Job' is set to the following:

JOBNAME_FLXFLD_SEGMENT_COL=SEGMENT6

If the Job Name in your deployment is configured using SEGMENT3 together with SEGMENT6 (or if you want to report Job Name as a concatenation of these two with 'dot' as the separator), you should set the ODI parameter value as:

JOBNAME_FLXFLD_SEGMENT_COL=SEGMENT3 || SEGMENT6

Alternatively, if you want a period (that is '.') in between, set the ODI parameter value as:

JOBNAME_FLXFLD_SEGMENT_COL=SEGMENT3 || '.' || SEGMENT6

If your OLTP table already stores the concatenated value in some other column (like NAME), you could use it directly as follows:

JOBNAME_FLXFLD_SEGMENT_COL=NAME

Note: You must make sure you take a look at the SQL code (in ODI Designer) before setting the parameter values. For example, before configuring the values for the ODI parameter JOBNAME_FLXFLD_SEGMENT_COL (as in the above example), you should know which table the data will be extracted from. If necessary, use Oracle BI Applications Configuration Manager to lookup the package name (for example, SDE_ORA_CODEDIMENSION_JOB), then use ODI Designer to obtain the OLTP table name for this package (using the Projects\Mappings\SDE_ORA11510_Adaptor folder).

Once you know the OLTP table names, you can work out the column names to use in the parameter file (SEGMENT3 or SEGMENT6 or NAME and so on) that really exist in the table PER_JOB_DEFINITIONS.

9.2.24 How to Configure Flags for the Pay Type Dimension

This section explains how to configure Pay Types. The Pay Type dimension W_PAY_TYPE_D has three Flag columns namely COMP_FLG, TAXABLE_FLG and PENSION_COMP_FLG, apart from others. Each of these three flags can be either 'Y' or 'N'. These flag columns indicate whether the Pay Type specified in the current record is a Taxable Earning type, or Regular Compensation Earning type or a Pension Compensation Earning type. These Flag columns are chosen to contribute towards slowly changing dimension of type 2, and hence should be configured correctly.

Each of these three Flag columns are evaluated based on the Incoming Classification Name using a Flat File lookup. This Flat file needs to be configured to see the accurate values of the Pay Type Flags in the Data Warehouse.

To Configure Flags for the Pay Type Dimension

  1. Open the file_comp_pension_taxable_flg_ora<ver>.csv in a text editor.

    This file is located in the $ODI_HOME\biapps_odi\odifiles\odidatafiles\lkpfiles\ directory.

  2. Add all possible Classification Names that are being used in your business.

  3. Corresponding to the Classification Name, add the relevant values for the Pay Type Flags. Make sure to use either 'Y' or 'N'.

In Oracle HR Analytics, these flags apply to Earnings only. Therefore, you should only need to configure Classification Names related to Earnings. For other areas, the default ODI mapping values should be acceptable.

9.2.25 How to Configure Classification Names for Payroll

This section explains how to configure classification names for Payroll.

The Aggregated items are loaded into the Payroll Fact table with a DETAIL_FLG = 'N', apart from the line items. The Aggregated Line items that are loaded are: TOTAL_GROSS, NET_PAY, TOTAL_DEDUCTIONS and TOTAL_TAXES.

The following parameters need to be configured to get accurate results. Each Aggregated Line item is computed by grouping by elements that belong to a certain Classification name set.

Table 9-2 Classification Name Parameters

Parameter Name Description

GROSS_PAY_CLASSIFICATION_NAME

Add all the Classification Names that add up to the Total Gross Pay.

TOTAL_DEDUCTION_CLASSIFICATION_NAME

Add all the Classification Names that correspond to any deduction.

TOTAL_TAXES_CLASSIFICATION_NAME

Add all the Classification Names that correspond to various Taxes.

TOTAL_GROSS_FOR_NETPAY_EARNINGS

Add all the Earnings Classification Names that are realized in Net pay. Note that some Classification Names are considered for Gross pay, but do not feature in the Net Pay (for example, Imputed Income). This is the major difference between this parameter and the GROSS_PAY_CLASSIFICATION_NAME parameter.

TOTAL_GROSS_FOR_NETPAY_DEDUCTIONS

This is same as the parameter TOTAL_DEDUCTION_CLASSIFICATION_NAME. However, in case some additional Classification Names are required to be added or removed for Net Pay, this parameter should be used for that.

The parameters are assigned default values when Oracle Business Intelligence Applications is installed out of the box, but you can modify the values by following the steps below.

To configure the classification name parameters:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Human Resources Analytics from the Select BI Application field

  4. Specify an appropriate value for the following parameters:

    • CLASSIFICATION_NAMES_FOR_GROSS_PAY='PTOAccruals','Earnings','Imputed Earnings'

    • CLASSIFICATION_NAMES_FOR_TOTAL_DEDUCTION='InvoluntaryDeductions','Pre-Tax Deductions','Voluntary Deductions','Tax Deductions'

    • CLASSIFICATION_NAMES_FOR_TOTAL_TAXES='Tax Deductions'

    • CLASSIFICATION_NAMES_FOR_NET_PAY_EARNINGS='PTOAccruals','Earnings'

    • CLASSIFICATION_NAMES_FOR_NET_PAY_DEDUCTIONS='InvoluntaryDeductions','Pre-Tax Deductions','Voluntary Deductions','Tax Deductions'

  5. Save your changes.

To find out the possible Classification Names existing in your system, please run the following SQL against the OLTP Database:

SELECT CLASSIFICATION_NAME FROM PAY_ELEMENT_CLASSIFICATIONS WHERE LEGISLATION_CODE LIKE 'US'

The Classification Name of Elements can be obtained using the following SQL:

Note: Enter the Element Type IDs of the elements for which you need the Classification Name within the quotes. If there are multiple elements, separate them using commas:

SELECT E.ELEMENT_TYPE_ID,E.ELEMENT_NAME,C.CLASSIFICATION_NAME FROM PAY_ELEMENT_TYPES_F E, PAY_ELEMENT_CLASSIFICATIONS C

WHERE E.CLASSIFICATION_ID = C.CLASSIFICATION_ID AND E.ELEMENT_TYPE_ID IN ()

9.2.26 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle HR Analytics.

9.2.26.1 How to Aggregate the Payroll Table for Oracle HR Analytics

You can aggregate the Payroll table to a different time levels, and aggregate levels of Employees, Jobs, and Payment Types dimensions. There are two time grain parameters to configure for this aggregate table and these parameters need to have the same value.

The GRAIN parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:

  • DAY

  • WEEK

  • MONTH

  • QUARTER

  • YEAR

The Payroll aggregate table is fully loaded from the base table in the initial E-LT run by the mapping 'PLP_PayrollAggregate_Load_Full'. The table can grow to millions of records. The Payroll aggregate table is not fully reloaded from the base table after an incremental E-LT run. Oracle HR Analytics minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. Oracle Business Intelligence Applications looks for new records in the base table during the incremental E-LT. This process is done in two steps:

  1. There are new records in the W_PAYROLL_A table, which are inserted after the last E-LT run. These new records are inserted into the W_PAYROLL_A_TMP table. This step is part of the post load-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Extract'.

  2. Oracle HR Analytics aggregates the W_PAYROLL_A_TMP table and joins it with the W_PAYROLL_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Load'.

To load the Payroll aggregate table (W_PAYROLL_A), you need to configure the post-load processing parameters in Oracle Business Intelligence Applications Configuration Manager.

To load the Payroll aggregate table (W_PAYROLL_A):

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Human Resources Analytics from the Select BI Application field.

  4. Edit the value of GRAIN parameter for the PLP_PayrollAggregate_Load scenario, and specify an appropriate value ('DAY', 'WEEK', 'MONTH', 'QUARTER' or 'YEAR').

  5. Save the Task.

9.2.26.2 About Configuring the Employee Snapshot Fact tables

The Employee Daily Snapshot Fact table stores employee-related metrics for all employees against their primary job function. You can configure the two fact tables that store Employee Snapshot data, namely W_EMPLOYEE_DAILY_SNP_F and W_EMPLOYEE_MONTHLY_SNP_F.

You can configure the Rolling Period and the Granularity for the Employee Snapshot data process.

Rolling Period

The Employee Daily Snapshot Fact table is loaded everyday, the date being identified by the snapshot date column. For example, if your organization has 1000 employees, this table would load 1000 records everyday. If an employee's contract is terminated on a particular day, the following day's snapshot data would not have that record (that is, 999 records would be uploaded). Continuing this example, if you run E-LT for 60 days, you will have 60 snapshot data sets. However, the number of days worth of snapshot data you want to store can be configured. In fact, you can configure to store only for a 'rolling period of' some days, example 30. This is the out of box setting. If you start your E-LT on January 1 and keep running for 40 days, you will have snapshot data sets from Jan 20 until Feb 10. If you configure to store only 7 rolling days, you will have snapshot data sets from Feb 3 until Feb 10, and so on.

The ODI package 'PLP_EmployeeDailySnapshot_Trim' maintains the rolling period choice, and uses two ODI parameters:

  • KEEP_PERIOD

    Indicates your choice of the period type based on which you will be providing the rolling periods. The Valid values for this parameter are 'DAY', 'WEEK', 'MONTH', 'QUARTER', and 'YEAR' (values should include single quotes as indicated).

  • NUM_OF_PERIOD

    Indicates your choice of how many days/weeks/months/years (set by KEEP_PERIOD) data you want to preserve in a rolling fashion. The valid value for this parameter is any integer.

For example, if you choose to preserve a rolling period of 45 Days, you would set KEEP_PERIOD='DAY' and NUM_OF_PERIOD=45.

Granularity

The Employee Monthly Snapshot Fact table stores the end-of-month snapshot data for all employees in your organization. Out of the box, this table stores end-of-month snapshot data, but this is configurable to store end-of-week, end-of-quarter or even end-of-year data. If you configure it to store end-of-month, as it is installed out-of-the-box, then you will have one snapshot data set for a given month, the set being refreshed everyday.

The out-of-box setting for the W_EMPLOYEE_MONTHLY_SNP_F table is to store end-of-month snapshot data set, on set for each calendar month. However, this can be configured. If you like to have this table store end-of-week, or end-of-quarter or even end-of-year data, you need to configure the GRAIN parameter in Oracle Business Intelligence Applications Configuration Manager. The GRAIN parameter indicates the chosen granularity of the snapshot fact table that stores data at a interval higher than the base snapshot fact table. Valid values for this parameter are 'WEEK', 'MONTH', 'QUARTER', 'YEAR' (values should include single quotes as indicated.

Notes:

  • Do not set the value of GRAIN to 'DAY'.

  • In the reporting logical metadata (RPD), the table W_EMPLOYEE_MONTHLY_SNP_F is joined with the Calendar Month dimension table - both in the physical layer as well as the logical layer. If you are change the GRAIN out-of-box value from 'MONTH' to anything else (like 'QUARTER'), you would need to modify the join in the RPD as well to the appropriate date dimension.

  • The physical name of the table can still remain as W_EMPLOYEE_MONTHLY_SNP_F. You can change the logical name (or alias) appropriately. For example, if your GRAIN is 'QUARTER', you might change it to quarterly snapshot.

To configure the Employee Snapshot Fact tables, perform the following tasks:

9.2.26.3 How to control the rolling period for storing Employee Daily Snapshot data

For more information about the rolling period for the Employee Snapshot data, see Section 9.2.26.2, "About Configuring the Employee Snapshot Fact tables".

To configure the rolling period for storing Employee Daily Snapshot data:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Human Resources Analytics from the Select BI Application field

  4. Set the values of the two parameters KEEP_PERIOD and NUM_OF_PERIOD for the PLP_EmployeeDailySnapshot_Trim scenario.

    Note: Although the NUM_OF_PERIOD requires an integer value, do not change the data type from 'text'.

9.2.26.4 How to control the granularity of the Employee Monthly Snapshot data

For more information about granularity in the Employee Snapshot data, see Section 9.2.26.2, "About Configuring the Employee Snapshot Fact tables".

To configure the granularity of the Employee Snapshot data:

  1. Start Oracle BI Applications Configuration Manager (for more information, see Section 4.5.8.9, "How to Login to Oracle BI Applications Configuration Manager Using A Connection").

  2. Select the Administer ELT Parameters link.

  3. Display the Application Specific tab, and select Human Resources Analytics from the Select BI Application field.

  4. Specify an appropriate value for the GRAIN parameter for the PLP_EmployeeMonthlySnapshot scenario.

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us