How To Use BICC Mappings X-Walk
- Jefferson Russell
- Rossy Aviles
The BICC Mappings X-Walk is a mapping of tables and fields from the source GEMS to the equivalent table and fields within Oracle HCM and to the HR Lakehouse. The mapping is provided as a method to find a table or field that users are familiar with from GEMS and lookup the new location of that table and field within Oracle HCM and the HR Lakehouse.
Overview of Layout of Excel
There are several tabs at the bottom of the screen. Each tab represents a logical grouping provided by the Oracle HCM or Analytics team.
Given the above screen shot above, the columns are as follows:
Security Tier - Level of security required to obtain access to the data.
PeopleSoft Recname - GEMS Table
PeopleSoft FieldName - GEMS Field
Oracle HR Business Object - Alternative tab in the X-Walk. Lineage Mapping.
Source Extract - View that might be available in HR Lakehouse. Lineage Mapping.
Source Column Name - Field that is available in the view. Lineage Mapping.
Cloud Database Table - The table that the field is from in Oracle HCM. Not accessible for reporting.
Cloud Database Column - Oracle HCM column in the Cloud Database Table. Not accessible for reporting.
Comments - Shows important information or primary key identifier.
Example Usage
Given the above screen shot of the Worker tab, if the user were looking for PS_JOB.EMPL_STATUS, the equivalent field is GlobalPersonPVOViewAll.AssignmentStatusTypesTLPEOUserStatus
When connected via SQL to the HR Lakehouse the SQL would be:
SELECT [AssignmentStatusTypesTLPEOUserStatus]
FROM [hcm].[GlobalPersonPVOViewAll]
How to find the GEMS Table or Field
In Excel, click CTRL + F simultaneously to use the ‘Find and Replace’ option in Excel (see image below). Enter what you are searching for, then expand the ‘Search options’ highlighted below. For this example, we will search ‘POSITION_NBR’:
Select ‘Workbook’, then click ‘Find all’. This expands your search to the entire workbook instead of the current sheet. You will see a list of locations within the workbook for your find.
Worker Tab
Position Tab
itaEmployeeAssignment Tab
PS_PAY_ERN_DIST Tab
Notice that POSITION_NBR exist on 4 sheets. (Worker, Position, ita.EmployeeAssignment, and PS_PAY_ERN_DIST). However, if you follow through to those pages you will see that the field POSITION_NBR comes from 2 GEMS tables, 1 FAST table, and maps to different destination sources within the HR Lakehouse.
Tab | Source | HR Lakehouse |
|---|---|---|
Worker | PS_JOB.POSTION_NBR | hcm.GlobalPersonPVOViewAll.PositionPEOPositionCode |
Position | PS_POSITION_DATA.POSITION_NBR | hcm.PositionPVOViewAll.PositionPEOPositionCode |
ita.EmployeeAssignment | View that references GlobalPersonPVOViewAll.PositionPEOPositionCode | ita.EmployeeAssignment.PositionCode |
PS_PAY_ERN_DIST | PS_PAY_ERN_DIST.POSTION_NBR | fast.PS_PAY_ERN_DIST.POSTION_NBR |
ita.EmployeeInformation
The ita.EmployeeInformation view is one of a few curated lists of HR Lakehouse objects designed to streamline your data retrieval process, ensuring you access the most relevant and essential information quickly. This collection includes: ita.EmployeeAssignment, ita.PayCheck, ita.PayDeduction, ita.VPAreaCollegeUnit, FAST.PS_HR_ACCTG_LINE, and FAST.PS_PAY_ERN_DIST. These views have a condensed name under the ‘Lakehouse Column Name’ for user consumption and understanding. From the Excel tab ‘ita.EmployeeInformation’ you can see the columns available for use in this view.
The previous GEMS tables of PS_NAMES, PS_ADDRESS, PS_JOB, etc. and the HCM tables of GlobalPersonPVOViewAll, ExternalIdentifierPVO, etc. have now been pre-joined to provide this consolidated view.
A note for the ita.EmployeeAssignment view is to be aware that there are several USF employees that have multiple assignments. This view will not necessarily return one person, but one assignment per person.
ita.EmployeeAssignment
Another view is the ita.EmployeeAssignment view that is also one of a few curated lists of HR Lakehouse objects designed to streamline your data retrieval process, ensuring you access the most relevant and essential information quickly. is a consolidated view created by the Analytics team. A note for the ita.EmployeeAssignment view is to be aware that there are several USF employees that have multiple assignments. This view will not necessarily return one person, but one assignment per person.
The previous GEMS tables of PS_JOB, PS_EG_TENURE_DATA, etc. and the HCM tables of GlobalPersonPVOViewAll, JOBPVOViewAll, etc. have now been pre-joined to provide this consolidated view.