...
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 FieldName - GEMS Field
BICC BUSINESS OBJECT Oracle HR Business Object - Alternative tab in the X-WalkBICC PVO . Lineage Mapping.
Source Extract - View that might be available in HR LakehouseBICC PVE . 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 TableNotes - Can be important showing any comments, or primary keys. 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.AssginmentStatusTypesTLPEOUserStatusAssignmentStatusTypesTLPEOUserStatus
When connected via sql SQL to the HRLakehouse HR Lakehouse the sql SQL would be - :
SELECT [
...
AssignmentStatusTypesTLPEOUserStatus]
FROM [hcm].[GlobalPersonPVOViewAll]
How to find the GEMS Table or Field
The easiest method to find a specific GEMS field is to use Find and Replace option in Excel. When you first try to find text in Excel it will default to the sheet within Search options. Make sure to open the search options.
And change it to within Workbook before you searchIn 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 in 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 GEMS tables, 1 fast FAST table, and maps to different destination sources within the HRLakehouseHR Lakehouse.
Tab | SourceHRLakehoouse | 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 ITAita.EmployeeIInformation EmployeeInformation view is a consolidated view created by the Analytics team. It is a prebuilt view that joins the several Oracle HCM tables for the end users in order to create a single repository for employee information. This view has friendlier names 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‘ita.EmployeeInformation 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
...
The ITA.EmployeeAssignment view 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. It is a prebuilt view that joins the several Oracle HCM tables for the end users in order to create a single repository for employee assignments. This view has friendlier names for user consumption and understanding. From the Excel tab ita.EmployeeAssignment you can see the columns available for use in this 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.
...
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. Be aware that there are several USF employees that have multiple assignments. So this view will not necessarily return one person, but one assignment per person.