Skip Headers
Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users
Version 7.9.5.1
Part Number E13766-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

7 Configuring Oracle Human Resources Analytics

This section describes how to configure Oracle Human Resources Analytics. It contains the following topics:

Note:

All references to Oracle 11i also apply to Oracle R12.

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:

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. It contains the following topics:

Configuration Steps for Oracle HR Analytics for All Source Systems

This section contains configuration steps required before you do a full data load that apply to all source systems.

Note:

For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, Oracle Sales Analytics, see Chapter 3, "Configuring Common Areas and Dimensions").

Configuration Steps for Oracle HR Analytics for Oracle EBS

This section contains configuration steps required before you do a full data load that apply to Oracle EBS. It contains the following topics:

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 $pmserver\server\infa_shared\LkpFiles folder. For example, D:\Informatica\PowerCenter8.1.1\server\infa_shared\Lkpfiles.

For more information on configuring domain values with CSV worksheet files, see .

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 7-1 lists the CSV worksheet files and the domain values for Oracle HR Analytics in the $pmserver\server\infa_shared\LkpFiles folder.

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

Worksheet File Name Domain Value Table - Column Description Session

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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 the Oracle 11i Application.

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.

How to Configure the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv

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

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

    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. Using a text editor, open the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employee_Sex_MF_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employee_Sex_MF_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employment_Cat_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employment_Cat_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employment_Exempt_Flg_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employment_Exempt_Flg_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employment_Full_Time_Flg_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employment_Full_Time_Flg_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Employment_Status_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Employment_Status_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_EventTypes_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_EventTypes_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_HRPosition_Exempt_Flg_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_HRPosition_Exempt_Flg_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Job_Eeo_Cat_Code_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Job_Eeo_Cat_Code_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Job_Flsa_Stat_Code_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Job_Flsa_Stat_Code_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Pay_Type_Grp_Code_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Pay_Type_Grp_Code_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

How to Configure the domainValues_Pay_Type_Flg_ora<ver>.csv

This section explains how to configure the 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. Using a text editor, open the domainValues_Pay_Type_Flg_ora<ver>.csv file, located in the $pmserver\lkpfiles folder.

  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.

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 Informatica PowerCenter Designer, open the SDE_ORA1158_Adaptor folder.

  2. In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.

  3. Locate the Expression transformation Exp_SA_Employees_Validate and scroll down the ports to get to the variables 'HOME_ADDRESS_ID_VAR', 'MAIL_ADDRESS_ID_VAR' and 'DEFAULT_ADDRESS_ID_VAR'.

    For example, if you have a specific address type for work addresses, (for example 'W'), you would modify the expression for the variable port DEFAULT_ADDRESS_ID_VAR as follows:

    From: :LKP.Lkp_Address_Id(INP_PERSON_ID, INP_EFFECTIVE_END_DATE, 'Y', 'Default', INP_DATASOURCE_NUM_ID)

    To: :LKP.Lkp_Address_Id(INP_PERSON_ID, INP_EFFECTIVE_END_DATE, 'Y', 'W', INP_DATASOURCE_NUM_ID)

    The output Address ID values are the next three ports 'PERM_ADDRESS_ID', 'MAIL_ADDRESS_ID' and 'WORK_ADDRESS_ID'. If you do not want to assume that the mail address (M) is same as the home address (H) in the event of mail address not being available, then you would modify the logic in the outgoing port 'MAIL_ADDRESS_ID' as follows:

    From: IIF(ISNULL(MAIL_ADDRESS_ID_VAR), HOME_ADDRESS_ID_VAR, MAIL_ADDRESS_ID_VAR)

    To: MAIL_ADDRESS_ID_VAR

  4. Validate and save changes to the repository. If you are using the version controlling for the Informatica Repository, you will have to check in your changes as well.

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 Informatica PowerCenter Designer, open the SDE_ORA1158_Adaptor folder.

  2. In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.

  3. Locate the Expression transformation Exp_SA_Employee and scroll down the ports to get to the output ports 'EXT_FAX_PH_NUM', 'EXT_WORK_PHONE', 'EXT_PAGER_NUM' and 'EXT_MOBILE_NUM'.

    For example, if you have a specific phone type for the primary work phone, 'WP', you would modify the expression for the output port EXT_WORK_PHONE as follows:

    From: :LKP.Lkp_Phone_Number(INP_PERSON_ID, 'W1', INP_EFFECTIVE_END_DATE, INP_DATASOURCE_NUM_ID)

    To: :LKP.Lkp_Phone_Number(INP_PERSON_ID, 'WP', INP_EFFECTIVE_END_DATE, INP_DATASOURCE_NUM_ID)

  4. Validate and save changes to the repository. If you are using the version controlling for the Informatica Repository, you will have to check in your changes as well.

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 session SDE_ORA_ EmployeeDimension uses the Informatica parameter $$QUALIFICATION_CATEGORY_LIST to get the list of categories for the defined Education Degrees in Oracle E-Business Suite. The mapping SDE_ORA_EmployeeDimension uses this parameter to identify and populate Highest Education Degree attribute for the Employee Dimension. Incorrect setup of the parameter could cause the issues with quality of the data for Highest Education Degree attribute in Employee Dimension.

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

  1. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the SDE_ORA_EmployeeDimension task.

  3. Click the Parameters subtab.

  4. Enter an appropriate value for the parameter $$QUALIFICATION_CATEGORY_LIST

    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:

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.

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:

How to Configure the Key Flexfields for the Job Dimension

This section is about configuring Job Code, Job Name and the Job Family Code columns.

To configure the Key Flexfields for the Job Dimension

  1. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the SDE_ORA_JobDimension task.

  3. Specify or create the following parameters with an appropriate value.

    • $$JOBCODE_FLXFLD_SEGMENT_COL = the Column Name of Job Code.

    • $$JOBFAMILYCODE_FLXFLD_SEGMENT_COL = the Column name of Job Group Code.

  4. In the Tasks tab, and select the SDE_ORA_CodeDimension_Job task.

  5. Specify or create the following parameters with an appropriate value.

    • $$JOBCODE_FLXFLD_SEGMENT_COL = the Column Name of Job Code.

    • $$JOBNAME_FLXFLD_SEGMENT_COL = the Column Name of the Job Name.

  6. 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 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.

How to Configure the Key Flexfields for the HR Position Dimension

This section is about configuring the Position Number column.

To configure the Key Flexfields for the HR Position Dimension

  1. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the SDE_ORA_HRPositionDimension task.

  3. Specify or create the following parameter with an appropriate value.

    • $$POSITION_NUM_FLXFLD_SEGMENT_COL = the Column Name of Position Num.

  4. In the Tasks tab, and select the SDE_ORA_HRPositionDimension_Full task.

  5. Specify or create the following parameter with an appropriate value.

    • $$POSITION_NUM_FLXFLD_SEGMENT_COL = the Column Name of Position Num.

  6. 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.

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. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the SDE_ORA_CodeDimension_Pay_Level task.

  3. Specify or create the following parameter with an appropriate value.

    • $$PAYLVLNAME_FLXFLD_SEGMENT_COL = the Column Name for the Pay level Name.

  4. 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 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.

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 BI Applications is installed out-of-the-box, the Informatica parameter for the task '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 Informatica parameter value as:

$$JOBNAME_FLXFLD_SEGMENT_COL=SEGMENT3 || SEGMENT6

Or, if you want a period (that is '.') in between, set the Informatica 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 Informatica PowerCenter Designer Source Qualifier object) before setting the parameter values. For example, before configuring the values for the Informatica parameter $$JOBNAME_FLXFLD_SEGMENT_COL (as in the above example), you should know which table the data will be pulled in. If necessary, use the following steps to obtain OLTP table names.

To obtain the OLTP table names:

  1. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the SDE_ORA_JobDimension and SDE_ORA_JobDimension tasks (using the example job name flexfield example).

  3. Note the value of the following parameter (.

    • $$JOBNAME_FLXFLD_SEGMENT_COL

    In this case, the mapplet name that you need to derive is mplt_BC_ORA_Codes_Job.

  4. Launch Informatica PowerCenter Designer and log on to the repository and navigate to the appropriate folder.

  5. Open the mapplet found in Step#2, open the Source Qualifier object, and see the SQL override. Get the physical OLTP table name from here. In this case, the table name is 'PER_JOB_DEFINITIONS'.

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.

How to Configure Flags for the Pay Type Dimension

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 DataWarehouse.

To Configure Flags for the Pay Type Dimension

  1. Open the file_comp_pension_taxable_flg_ora<ver>.csv from Informatica Server\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 Informatica mapping values should be acceptable.

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 7-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 BI 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. In DAC, go to the Design view, and select your custom container from the drop-down list.

  2. Click the Tasks tab, and select the task SDE_ORA_PayrollFact_Agg_Items.

  3. Click the Parameters subtab, and specify the following parameters with an appropriate value.

    • $$CLASSIFICATION_NAMES_FOR_GROSS_PAY='PTO Accruals','Earnings','Imputed Earnings'

    • $$CLASSIFICATION_NAMES_FOR_TOTAL_DEDUCTION='Involuntary Deductions','Pre-Tax Deductions','Voluntary Deductions','Tax Deductions'

    • $$CLASSIFICATION_NAMES_FOR_TOTAL_TAXES='Tax Deductions'

    • $$CLASSIFICATION_NAMES_FOR_NET_PAY_EARNINGS='PTO Accruals','Earnings'

    • $$CLASSIFICATION_NAMES_FOR_NET_PAY_DEDUCTIONS='Involuntary Deductions','Pre-Tax Deductions','Voluntary Deductions','Tax Deductions'

  4. 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 ()

Configuration Steps for Oracle HR Analytics for PeopleSoft

This section contains configuration steps required before you do a full data load that apply to PeopleSoft. It contains the following topics:

Domain Values and CSV Worksheet Files for HR Analytics for PeopleSoft HCM

The table below lists the CSV worksheet files and the domain values for PeopleSoft HR Analytics in $pmserver\LkpFiles folder located on Informatica Server box.

Table 7-3 Domain Values and CSV Worksheet Files for HR Analytics for PeopleSoft HCM

Domain Value Table Column Description Session

domainValues_Employee_Ethnic_Group_Code_psft.csv

W_EMPLOYEE_D.W_ETHNIC_GRP_CODE

Lists the Ethnic codes and corresponding domain values of 'Ethnic Group Code' for PeopleSoft.

SDE_PSFT_EmployeeDimension

domainValues_Employee_Sex_MF_psft.csv

W_EMPLOYEE_D.W_SEX_MF_CODE

Lists the Sex codes and corresponding domain values of 'Sex Code' for PeopleSoft.

SDE_PSFT_EmployeeDimension

domainValues_Employee_Veteran_Status_Code_psft.csv

W_EMPLOYEE_D.W_VETERAN_STATUS_CODE

Lists the Veteran codes and corresponding domain values of 'Veteran Status Code'.

SDE_PSFT_EmployeeDimension

domainValues_Employment_Category_psft.csv

W_EMPLOYMENT_D.W_EMPLOYEE_CAT_CODE

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

SDE_PSFT_EmploymentDimension

domainValues_Employment_Exempt_Flg_psft.csv

W_EMPLOYMENT_D.W_EXEMPT_FLG

Lists the FLSA Statuses and corresponding domain values of 'Exempt Flag'.

SDE_PSFT_EmploymentDimension

domainValues_Employment_Full_Time_Flg_psft.csv

W_EMPLOYMENT_D.W_FULL_TIME_FLG

Lists the Employment Categories and corresponding domain values of 'Full Time Flag'.

SDE_PSFT_EmploymentDimension

domainValues_Employment_Status_psft.csv

W_EMPLOYMENT_D.W_EMPLOYMENT_STAT_CODE

Lists the Per System Statuses and corresponding domain values of 'Employment Status'.

SDE_PSFT_EmploymentDimension

domainValues_Emp_EventType_Grp_Code_psft.csv

W_EVENT_TYPE_D.W_EVENT_CLASS,W_EVENT_TYPE_D.W_EVENT_GRP_CODE,W_EVENT_TYPE_D.W_EVENT_REASON_CODE

Lists the Event Types, Event Codes and Meanings and corresponding domain values of 'Event Group' and Event Reason'.

SDE_PSFT_EventTypeDimension_Stage1SDE_PSFT_EventTypeDimension_Stage2

domainValues_Emp_EventType_SubG_Code_psft.csv

W_EVENT_SUBG_CODE

Lists the Event Types, Event Codes and Meanings and corresponding domain values of 'Event Sub-Group'

SDE_PSFT_EventTypeDimension_Stage1 SDE_PSFT_EventTypeDimension_Stage2

domainValues_HRPosition_Active_Pos_Flg_psft.csv

W_HR_POSITION_D.W_ACTIVE_POSITION_FLG

Lists the Position Statuses and corresponding domain values of 'Active Position Flag'

SDE_PSFT_HRPositionDimension

domainValues_HRPosition_Exempt_Flg_psft.csv

W_HR_POSITION_D.W_EXEMPT_FLG

Lists the FLSA Statuses and corresponding domain values of 'Exempt Flag'.

SDE_PSFT_HRPositionDimension

domainValues_Job_Eeo_Cat_Code_psft.csv

W_JOB_D.W_EEO_JOB_CAT_CODE

Lists the EEO Job Categories and corresponding domain values of 'EEO Job Category'.

SDE_PSFT_JobDimension

domainValues_Job_Flsa_Stat_Code_psft.csv

W_JOB_D.W_FLSA_STAT_CODE

Lists the FLSA Statuses and Corresponding domain values of the 'FLSA Status Code'

SDE_PSFT_JobDimension

domainValues_Pay_Type_Grp_code_psft.csv

W_PAY_TYPE_D.W_PAY_TYPE_GRP_CODE

Lists the Classification Names, Element Names and corresponding domain values of 'Pay Type Group Code'

SDE_PSFT_PayTypeDimension_Total, SDE_PSFT_PayTypeDimension_Earnings, SDE_PSFT_PayTypeDimension_FederalTaxes, SDE_PSFT_PayTypeDimension_Deductions, SDE_PSFT_PayTypeDimension_LocalTaxes1, SDE_PSFT_PayTypeDimension_LocalTaxes2, SDE_PSFT_PayTypeDimension_StateTaxes1, SDE_PSFT_PayTypeDimension_StateTaxes2

domainValues_Pay_Type_Flg_psft.csv

W_PAY_TYPE_D.W_PAY_TYPE_FLG

Lists the Costing Debit/Credit values and corresponding domain values of 'Pay type Flag'.

SDE_PSFT_PayTypeDimension_Earnings, SDE_PSFT_PayTypeDimension_FederalTaxes, SDE_PSFT_PayTypeDimension_Deductions, SDE_PSFT_PayTypeDimension_Total, SDE_PSFT_PayTypeDimension_LocalTaxes1, SDE_PSFT_PayTypeDimension_LocalTaxes2, SDE_PSFT_PayTypeDimension_StateTaxes1, SDE_PSFT_PayTypeDimension_StateTaxes2

domainValues_Emp_EventType_EventClass_Code_psft.csv

NA

Lists the event type class codes.

SDE_PSFT_EventTypeDimension_Stage2

domainValues_Emp_EventType_EventReason_Code_psft.csv

NA

Lists the event type reason codes.

SDE_PSFT_EventTypeDimension_Stage2

How to Configure the domainValues_Employee_Ethnic_Group_Code_psft.csv

  1. Identify the Ethnic Group Codes in your PeopleSoft source system by using the following SQL:

    SELECT DISTINCT REG_REGION, ETHNIC_GRP_CD

    FROM PS_DIVERS_ETHNIC

    WHERE ETHNIC_GRP_CD NOT LIKE

    ORDER BY 1

  2. Open the domainValues_Employee_Ethnic_Group_Code_psft.csv file text editor in the $pmserver\lkpfiles folder.

  3. Copy the FIELDVALUE 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.

How to Configure the domainValues_Employee_Sex_MF_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'SEX'

    ORDER BY 1

  2. Open the domainValues_Employee_Sex_MF_psft.csv file located in $pmserver\lkpfiles folder on Informatica Server host using text editor.

  3. Copy the FIELDVALUE 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.

How to Configure the domainValues_Employee_Veteran_Status_Code_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'MILITARY_STATUS'

    ORDER BY 1

  2. Open the domainValues_Employee_Veteran_Status_Code_psft.csv file located in $pmserver\lkpfiles folder on Informatica Server host using text editor.

  3. Copy the FIELDVALUE 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.

How to Configure the domainValues_Employment_Category_psft.csv

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

    SELECT DISTINCT LTRIM (RTRIM (EMPL_CLASS)) EMPL_CLASS

    FROM PS_JOB

    ORDER BY 1

  2. Open the domainValues_Employment_Category_psft.csv file located in $pmserver\lkpfiles directory placed on Informatica Server host using text editor.

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

  4. Map each User Person Type to one domain value.

  5. Save and close the file.

How to Configure the domainValues_Employment_Exempt_Flg_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'FLSA_STATUS'

    ORDER BY 1

  2. Open the domainValues_Employment_Exempt_Flg_psft.csv file located in $pmserver\lkpfiles directory placed on Informatica Server host using text editor.

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

  4. Map each FLSA_STATUS to one domain value.

  5. Save and close the file.

How to Configure the domainValues_Employment_Full_Time_Flg_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'FULL_PART_TIME'

    ORDER BY 1

  2. Open the domainValues_Employment_Full_Time_Flg_psft.csv file located in $pmserver\lkpfiles directory placed on Informatica Server host using text editor.

  3. Copy the FIELDVALUE to the FULL_PART_TIME 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.

How to Configure the domainValues_Employment_Status_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE) AND A.FIELDNAME = 'EMPL_STATUS'

    ORDER BY 1

  2. Open the domainValues_Employment_Full_Time_Flg_psft.csv file located in $pmserver\lkpfiles directory placed on Informatica Server host using text editor.

  3. Copy the FIELDVALUE to the FULL_PART_TIME 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.

How to Configure the domainValues_Emp_EventType_Grp_Code_psft.csv

  1. Identify the Event Types Group Codes in your PeopleSoft source system by using the following SQL:

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'ACTION'

    ORDER BY 1

  2. Open the domainValues_EMP_EventType_Grp_Code_psft.csv file editor in $pmserver\lkpfiles folder using text.

  3. Copy the FIELDVALUE to the LOOKUP_TYPE_CODE. 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 domain column.

  5. Save and close the file.

How to Configure the domainValues_Emp_EventType_SubG_Code_psft.csv

  1. Identify the Event Types Subgroup Codes in your PeopleSoft source system by using the following SQL:

    MSSQL Server Syntax:

    SELECT ACTION + ':'+ ACTION_REASON SUBG_CODE, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1

    Oracle Syntax:

    SELECT ACTION ||':'|| ACTION_REASON SUBG_CODE, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1

  2. Open the domainValues_EMP_EventType_SubG_Code_psft.csv file editor in $pmserver\lkpfiles folder using text.

  3. Copy the SUBG_CODE to the EVENT_REASON. The data must be copied starting from the 6th line. Use commas to separate the entries.

  4. Map each Event Type (EVENT_REASON) to one domain value for each domain column.

  5. Save and close the file.

How to Configure the domainValues_HRPosition_Active_Pos_Flg_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'EFF_STATUS'

    ORDER BY 1

  2. Open the domainValues_HRPosition_Active_Pos_Flg_psft.csv file using text editor in $pmserver\lkpfiles directory.

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

  4. Map each position EFF_STATUS to one domain value.

  5. Save and close the file.

How to Configure the domainValues_HRPosition_Exempt_Flg_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'FLSA_STATUS'

    ORDER BY 1

  2. Open the domainValues_HRPosition_Exempt_Flg_psft.csv file using text editor in $pmserver\lkpfiles directory.

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

  4. Map each FLSA_STATUS to one domain value.

  5. Save and close the file.

How to Configure the domainValues_Job_Eeo_Cat_Code_psft.csv

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

    SELECT 'EEO1CODE' CODE_TYPE,

    LTRIM (RTRIM (EEO1CODE))

    FROM PS_JOBCODE_TBL

    UNION

    SELECT 'EEO4CODE' CODE_TYPE,

    LTRIM (RTRIM (EEO4CODE))

    FROM PS_JOBCODE_TBL

    UNION

    SELECT 'EEO5CODE' CODE_TYPE,

    LTRIM (RTRIM (EEO5CODE))

    FROM PS_JOBCODE_TBL

    UNION

    SELECT DISTINCT 'EEO6CODE' CODE_TYPE,

    LTRIM (RTRIM (EEO6CODE))

    FROM PS_JOBCODE_TBL

  2. Open the domainValues_Job_Eeo_Cat_Code_psft.csv file in $pmserver\lkpfiles directory using text editor.

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

  4. Map each pair of EEO_JOB_CAT_CODE_TYPE and EEO_JOB_CAT_CODE_VAL to one domain value.

  5. Save and close the file.

How to Configure the domainValues_Job_Flsa_Stat_Code_psft.csv

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

    SELECT A.FIELDVALUE, A.XLATLONGNAME

    FROM PSXLATITEM A

    WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE)

    AND A.FIELDNAME = 'FLSA_STATUS'

    ORDER BY 1

  2. Open the domainValues_Job_Flsa_Stat_Code_psft.csv file in $pmserver\lkpfiles directory using text editor.

  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.

How to Configure the domainValues_Pay_Type_Grp_Code_psft.csv

The table below shows pay type sources and their associated group codes.

Table 7-4 Pay Elements for ETL

PAYTYPE_SOURCE W_PAY_TYPE_GRP_CODE

DEDUCTIONS

DEDUCTIONS

FEDERAL_TAXES

TAXES

LOCAL_TAXES

TAXES

STATE_TAXES

TAXES

EARNINGS

REGULAR

TOTAL

TOTAL

  1. To configure the domainValues_Pay_Type_Grp_Code_psft.csv

  2. Open the domainValues_Pay_Type_Grp_Code_psft.csv file using text editor in the $pmserver\lkpfiles directory.

  3. Copy the Classification Name and Element Name to the PAYTYPE_SOURCE. The data must be edited starting from the 6th line. Use commas to separate the entries.

  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.

How to Configure the domainValues_Emp_EventType_EventReason_Code_psft.csv

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

    SELECT ACTION + ':'+ ACTION_REASON EVENT_REASON, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1

    Oracle Syntax:

    SELECT ACTION ||':'|| ACTION_REASON EVENT_REASON, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1

  2. Using a text editor, open the domainValues_Emp_EventType_EventReason_Code_psft.csv file, located in the $pmserver\lkpfiles folder.

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

  4. Map each EVENT_REASON to one domain value.

  5. Save and close the file.

How to Configure the domainValues_Emp_EventType_EventClass_Code_psft.csv

  1. The shipped domain value for PeopleSoft ETL source system is set:

    • EVENT_CLASS: W_EVENT_CLASS

    • DEFAULT: HR_EVENTS

  2. Using a text editor, open the domainValues_Emp_EventType_EventClass_Code_psft.csv file, located in the $pmserver\lkpfiles folder.

  3. Modify EVENT_CLASS column in the file. The data must be copied starting from the 6th line.

  4. Map each EVENT_CLASS to one domain value.

  5. Save and close the file.

How to Configure the file_pension_comp_flg.csv

  1. Identify the Earnings Codes in your PeopleSoft source system by using the following SQL:

    SELECT DISTINCT ERNCD, DESCR FROM PS_EARNINGS_TBL ORDER BY 1

  2. Using a text editor, open the file file_pension_comp_flg_psft.csv, located in the $pmserver\lkpfiles folder.

  3. Copy over the ERNCD and DESCR column into the file. The data must be copied starting from the sixth line.

  4. Relate a Pension Compensation Flag and a Compensation Flag based on the nature of the Earnings Code.

  5. Save and close the file.

The SQL can be further tuned if all the Earnings Code of your Organization is of a particular type. For example, if all the Earnings Code that you are using starts with 'B' then the SQL can be modified as:

SELECT DISTINCT ERNCD, DESCR FROM PS_EARNINGS_TBL WHERE ERNCD LIKE 'B%' ORDER BY 1

How to Configure Compensation Flag and Pension Compensation Flag for Pay Type Dimension

The file 'file_pension_comp_flg_psft.csv' is used to populate the Compensation flag and the Pension Compensation flag. The value of the Compensation Flag and the Pension Compensation flag is chosen based on the Input Earnings Code.

  • Compensation Flag (COMP_FLG)

  • Pension Compensation Flag (PENSION_COMP_FLG)

To modify Compensation Flag and Pension Compensation Flag

  1. Edit the file 'file_pension_comp_flg_psft.csv' in the $pmserver\LkpFiles directory using in a text editor.

  2. Enter the COMP_FLG and PENSION_COMP_FLG value against the correct Earnings Code.

  3. Save the file.

How to Configure the Performance Review Type ETL Runtime Parameter

As a part of the ETL configuration runtime parameter, value for Performance Review Type must be specified in DAC. This parameter will be used to extract performance ranking information and related metrics for the Employee Snapshot Fact table.

To configure the Performance Review runtime ETL parameter

  1. In DAC, go to the Design view, and select the appropriate custom container.

  2. Click the Tasks tab, and select the task 'SDE_PSFT_EmployeeDailySnapshotFact_Reviews_Accruals'.

  3. Click the Parameters subtab.

  4. Query for the parameter $$PERFORMANCE_REVIEW_TYPE, and enter the appropriate value in the Value field.

You can use the SQL statement below to find out the Performance Review Types for review process in the PeopleSoft source system:

SELECT EP_REVIEW_TYPE, EP_REVW_DESCR FROM PS_EP_REVW_TYP_TBL

How to Configure Event Types using domainValues_EventTypes_psft.csv

To configure Event Types using the domainValues_EventTypes_psft.csv:

  1. Identify the Actions and Action reasons in your PeopleSoft source system using the following SQL:

    SELECT A.ACTION,A.ACTION_REASON 
FROM PS_ACTN_REASON_TBL A, (SELECT ACTION,ACTION_REASON, MAX(EFFDT) EFFDT 
FROM PS_ACTN_REASON_TBL 
GROUP BY ACTION,ACTION_REASON) B 
WHERE A.ACTION=B.ACTION AND 
A.ACTION_REASON = B.ACTION_REASON AND 
A.EFFDT = B.EFFDT

  2. Using a text editor, open the file domainValues_EventTypes_psft.csv, located in $pmserver\lkpfiles.

  3. Copy the Action and Action Reason from the SQL result starting from the sixth line.

    Use commas to separate the entries.

  4. Map each combination of Action and Action reason to one domain value for each of the domain value columns (that is, W_EVENT_GRP_CODE, W_EVENT_SUBG_CODE, W_EVENT_REASON_CODE and E_EVENT_CLASS).

  5. Save and close the file.

How to Configure Department Trees for Oracle HR Analytics

Oracle HR Analytics supports PeopleSoft department-based organization hierarchy. ETL mappings extract and flatten PeopleSoft department trees into a flattened organization hierarchy. ETL parameters also enable you to flatten department trees by SetID and tree name.

Supported Tree Structures

Oracle HR Analytics supports winter tree and summer tree structure types. Winter trees have nodes but do not have detail values. Summer trees have both nodes and detail values. Nodes are grouped into levels, and Oracle HR Analytics supports only strictly enforced tree levels in which all nodes on the same level represent the same type of entity. For detailed information about tree structures, see the PeopleSoft documentation.

How Oracle HR Analytics Handles Department Trees

PeopleSoft departments and the associated department tress are supported in Oracle HR Analytics as the organization dimension (W_INT_ORG_D) and the flattened organizational hierarchy structure (W_INT_ORG_DH).

Oracle HR Analytics flattens trees up to 15 levels deep, level 0 to 14, with level 0 as the bottom node. During the tree flattening ETL process, every tree node is inserted into W_INT_ORG_DH along with the path from the top-most node on the tree. If a node is less than 15 levels deep, the node value will be repeated in all levels below the node level.

Example of How a Department Tree Is Populated

The following diagram and tables give an example of how the department tree is populated into W_INT_ORG_D and W_INT_ORG_DH. This example uses the tree name "NA Sales" and the setID "Share."

Figure 7-1 How the Department Tree Populates Data Warehouse Tables

This graphic is described in the surrounding text.

The department table (PS_DEPT_TBL) populates the Internal Organization dimension table (W_INT_ORG_D ) as follows:

Table 7-5 How PS_DEPT_TBL Populates W_INT_ORG_D

ROW_ID ORG_NUM ORG_NAME HR_ORG_FLAG

1

A

American Sales

Y

2

B

West Region

Y

3

C

New England

Y

4

D

Massachusetts

Y

5

E

California

Y

6

F

Boston

Y

The department tree populates the Internal Organization hierarchy table W_INT_ORG_DH as follows:

Table 7-6 How PS_DEPT_TBL Populates W_INT_ORG_DH

ORG_WID ORG_HIER11_NUM ORG_HIER11_NAME ORG_HIER12_NUM ORG_HIER12_NAME ORG_HIER13_NUM ORG_HIER13_NAME ORG_TOP_NUM ORG_TOP_NAME HIERARCHY_NAME W_HIERARCHY_CLASS FIXED_HIER_LEVEL HR_ORG_FLG

1

A

North American Sales

A

North American Sales

A

North American Sales

A

North American Sales

Share ~NA Sales

HR Org

14

Y

2

B

West Region

B

West Region

B

West Region

A

North American Sales

Share ~NA Sales

HR Org

13

Y

3

C

New England

C

New England

C

New England

A

North American Sales

Share ~NA Sales

HR Org

13

Y

4

D

Massachusetts

D

Massachusetts

C

New England

A

North American Sales

Share ~NA Sales

HR Org

12

Y

5

E

California

E

California

B

West Region

A

North American Sales

Share ~NA Sales

HR Org

11

Y

6

F

Boston

D

Boston

C

New England

A

North American Sales

Share ~NA Sales

HR Org

12

Y

How a Summer Tree Is Flattened

The tree flattening process also supports summer trees. A summer tree is a tree with detail ranges. If a tree has detail ranges specified for the bottom nodes, the extraction process creates a number of nodes in W_INT_ORG_HIER corresponding to departments in the specified node range.

If a tree is a summer tree, the granularity of the data returned from the ETL source Qualifier is one row per specified range. The lowest parent nodes of the tree can be repeated multiple times because multiple ranges can be created. The following diagram shows how a summer tree is flattened.

Figure 7-2 How a Summer Tree Is Flattened

This graphic is described in the surrounding text.

The detail ranges populate the Internal Organization dimension table W_INT_ORG_D as follows:

Table 7-7 How Detail Ranges Populate W_INT_ORG_D

ROW_WID ORG_NUM ORG_NAME HR_ORG_FLG

7

2334

Appliances

Y

8

2340

Home Theater

Y

9

3001

MP3 Players

Y

The summer tree detail range is populated in W_INT_ORG_DH as follows:

Table 7-8 How Detail Ranges Populate W_INT_ORG_DH

ORG_WID ORG_HIER (1-10) ORG_HIER11 ORG_HIER12 ORG_HIER13 ORG_TOP FIXED_HIER_LVL HR_ORG_FLG

7

Boston

Boston

Massachusetts

New England

North American Sales

10

Y

8

Boston

Boston

Massachusetts

New England

North American Sales

10

Y

9

Boston

Boston

Massachusetts

New England

North American Sales

10

Y

How the Flattened Internal Organization Hierarchy Is Presented in Oracle BI Enterprise Edition

The Oracle HR Analytics Presentation Catalog delivers a 15-level employee organization. Employee Organization hierarchy levels are mapped to Internal Organization dimension and hierarchy tables as follows:

RPD Presentation Layer Physical Table Mapping
Employee Organization Number W_INT_ORG_D.ORG_NUM
Employee Organization Name W_INT_ORG_D.ORG_NAME
Employee Organization Hierarchy Name W_INT_ORG_DH.HIERARCHY_NAME
Hierarchy Version W_INT_ORG_DEH.CURRENT_VER_HIER_FLG
Employee Organization Hierarchy 1 W_INT_ORG_DH.HIER1_NUM
Employee Organization Hierarchy 2 W_INT_ORG_DH.HIER2_NUM
Employee Organization Hierarchy 3 W_INT_ORG_DH.HIER3_NUM
Employee Organization Hierarchy 4 W_INT_ORG_DH.HIER4_NUM
Employee Organization Hierarchy 5 W_INT_ORG_DH.HIER5_NUM
Employee Organization Hierarchy 6 W_INT_ORG_DH.HIER6_NUM
Employee Organization Hierarchy 7 W_INT_ORG_DH.HIER7_NUM
Employee Organization Hierarchy 8 W_INT_ORG_DH.HIER8_NUM
Employee Organization Hierarchy 9 W_INT_ORG_DH.HIER9_NUM
Employee Organization Hierarchy 10 W_INT_ORG_DH.HIER10_NUM
Employee Organization Hierarchy 11 W_INT_ORG_DH.HIER11_NUM
Employee Organization Hierarchy 12 W_INT_ORG_DH.HIER12_NUM
Employee Organization Hierarchy 13 W_INT_ORG_DH.HIER13_NUM
Employee Organization Hierarchy 14 W_INT_ORG_DH.HIER14_NUM

The table below describes HR Organization dimension and dimension hierarchy tables:

Table Name Description Source Tables
W_INT_ORG_DS HR Organization dimension staging table PS_DEPT_TBL
W_INT_ORG_D HR Organization dimension table W_INT_ORG_DS
W_INT_ORG_DHS HR Organization dimension hierarchy staging table PSTREESTRCT

PSTREENODE

PSTREELEVEL
W_INT_ORG_DH HR Organization dimension hierarchy table W_INT_ORG_DHW

The following temporary tables are used to handle the extraction and load of the trees:

  • Sequence 1: W_PSFT_INT_ORG_DEPT_DH_TMP

  • Sequence 2: W_PSFT_INT_ORG_TREE_TMP

  • Sequence 3: W_PSFT_INT_ORG_VERT_DH_TMP

  • Sequence 4: W_PSFT_INT_ORG_DTLRGE_DH_TMP

  • Sequence 5: W_PSFT_INT_ORG_FLAT_DH_TMP

Department Tree Flattening Process ETL Parameters

DAC provides the parameter $$TREE_SETID_NAME_LIST to configure the tree flattening ETL process. $$TREE_SETID_NAME_LIST supports two PeopleSoft tree parameters, SETID, and TREE_NAME. No DAC parameter is provided for PeopleSoft tree parameter EFFDT. The tree extract mapping has built-in logic to extract the current effectively-dated tree (excluding future date) for the specified tree name. There is no increment extraction for PeopleSoft trees and a full extraction is always performed during each ETL process.

To configure tree SetID and Tree Name in DAC

  1. In DAC, go to the Design view.

  2. Click the Tasks tab, and select the SDE_PSFT_Stage_InternalOrganizationDimension_Hierarchy_Extract.

  3. Click the Parameters subtab.

  4. For the parameter $$TREE_SETID_NAME_LIST, enter the appropriate values for SETID and TREE_NAME.

Note: The $$TREE_SETID_NAME_LIST parameter is in the format '<setid>~<tree_name>'.

A single quotation mark is required. If you have multiple trees, separate them using a comma, for example, 'SHARE~DEPT1', 'US~DEPT1'.

Configuration Steps for Oracle HR Analytics for Universal

This section contains configuration steps required before you do a full data load that apply to Universal.

Not applicable to Oracle BI Applications release 7.9.5.1.

Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle HR Analytics.

Configuration Steps for Oracle HR Analytics for All Source Systems

This section contains configuration steps that apply to all source systems. It contains the following topics:

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 ETL 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 ETL run. Oracle HR Analytics minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. Oracle BI Applications looks for new records in the base table during the incremental ETL. This process is done in two steps:

  1. There are new records in the W_PAYROLL_A table, which are inserted after the last ETL 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 the DAC (Data Administration Console).

To load the Payroll aggregate table (W_PAYROLL_A)

  1. In DAC, go to the Design view.

  2. Click the Tasks tab, and select the Task PLP_PayrollAggregate_Load.

  3. Click the Parameters subtab, and add a parameter with the name $$GRAIN.

  4. Depending upon the aggregation requirement, define the value as one of the following: 'DAY', 'WEEK', 'MONTH', 'QUARTER' or 'YEAR'.

  5. Select Static as the parameter type.

  6. Save the task.

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 ETL 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 ETL 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 seven rolling days, you will have snapshot data sets from Feb 3 until Feb 10, and so on.

The ETL mapping responsible for maintaining the rolling period choice you have indicated is 'PLP_EmployeeDailySnapshot_Trim', which uses two Informatica 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, your would need to configure the correct Informatica parameter definition in the DAC. This parameter definition is as follows:

  • $$GRAIN: 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:

How to Configure the Rolling Period for Storing Employee Daily Snapshot Data

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

To configure the rolling period for storing Employee Daily Snapshot data

  1. Log in to the DAC repository, go to the Design view, and select your custom container from the drop-down list.

  2. Click on Tasks in the right pane and query for the task name = PLP_EmployeeDailySnapshot_Trim.

  3. In the pane below, click on the Parameters subtab.

  4. Set the values of the two parameters $$KEEP_PERIOD and $$NUM_OF_PERIOD.

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

How to Configure the Granularity of the Employee Monthly Snapshot Data

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

To configure the granularity of the Employee Snapshot data

  1. In the DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Click on the Tasks tab, and query for the task PLP_EmployeeMonthlySnapshot.

  3. Click the Parameters subtab.

  4. Query for the $$GRAIN parameter, and enter an appropriate value in the Value field.

Configuration Steps for Oracle HR Analytics for Oracle EBS

This section contains configuration steps that apply to Oracle EBS.

Not applicable to Oracle BI Applications release 7.9.5.1.

Configuration Steps for Oracle HR Analytics for PeopleSoft

This section contains configuration steps that apply to PeopleSoft. It contains the following topics:

How to Tune Performance for the Payroll Table for PeopleSoft HCM HR Analytics

The Payroll mappings designed are designed to take care of the Payroll Adjustment and the Payroll Reversal process. These are complicated processes and normally take a reasonable amount of time to complete. Depending on your requirement, there are two ways you can achieve better overall performance in the Payroll Analytics. These techniques are as follows:

  • Inactivate Adjustments and Reversals, if not required, for a given pay period

  • Defer calculating Adjustments and Reversals for some time, until needed.

To inactivate Adjustment and Reversal calculations for Payroll

If you decide that there will be no Payroll Adjustment and Reversal for a Pay period, then you can choose to inactivate those tasks in the DAC.

  1. In the DAC, go to the Design view, and select the appropriate custom container.

  2. Select the Tasks tab.

  3. Deselect the Inactive check the box for the following tasks:

    • SDE_PSFT_PayrollFact_Deductions_Adjustments_Reversals

    • SDE_PSFT_PayrollFact_Earnings_Adjustments_Reversals

    • SDE_PSFT_PayrollFact_EarningsOther_Adjustments_Reversals

    • SDE_PSFT_PayrollFact_Total_Adjustments_Reversals

    • SDE_PSFT_PayrollFact_Taxes_Adjustments_Reversals

  4. Save the tasks.

  5. Assemble the subject area HR- Payroll and build the execution plans that contain this subject area.

To defer Adjustment and Reversal calculations for Payroll

  1. Inactivate Adjustment and Reversal calculations for Payroll and reassemble the subject area Payroll and rebuild the execution plans that contain this subject area.

  2. When you choose to run these again, reactivate the tasks, and reassemble Subject Area (Payroll) and Execution Plan.

  3. Adjust the parameter $$LAST_EXTRACT_DATE so that it goes back to the date (preferably one day earlier) since when you decided not to run these tasks.

Configuration Steps for Oracle HR Analytics for Universal

Not applicable to Oracle BI Applications release 7.9.5.1.

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