1. Reference for Fusion HCM Analytics
  2. Tables

2 Tables

Overview

This section provides information about tables within Oracle Fusion HCM Analytics warehouse and their columns, primary keys denoted as key columns, data types, and the referred table and column names.

Note:

Tables ending with the suffix _EN and those listed in Tables Without WHO Columns are for Oracle Internal use only. In addition, tables storing descriptive flexfiled data (DFF tables) aren't included in the documentation

Download this ZIP file that contains available tables that apply to the current version of Oracle Fusion HCM Analytics. Once the file downloads, extract the file, open the folder, and then open the release-specific HTML file. For example, click 23.R1_Fusion_HCM_Analytics_Tables.html.

Table Types

The suffixes in table names designate the table type.

  • CA, A: Aggregate
  • CF, F: Fact
  • CF_DN: Denormalized Fact
  • CG, G: General
  • D: Dimension
  • DH: Dimension Hierarchy
  • EF: Extended Fact
  • EN: Oracle internal use only
  • H: Helper
  • P: Prediction
  • TL: Translation

Tables Without WHO Columns

All tables have two system date columns which can be used to get the last refresh dates.

These system date columns are:
  • W$_INSERT_DT - the timestamp when that particular record was inserted into that table.
  • W$_UPDATE_DT - the timestamp when that particular row was updated in that table.
Objects that don't have the WHO columns are the views based on the data warehouse tables. Views based on the data warehouse tables won't have any WHO columns because they aren't tables; instead they are views joining two or more underlying data warehouse tables.
The following views based on the data warehouse tables don't have the WHO columns:
  • DW_BUSINESS_UNIT_D
  • DW_DEPARTMENT_D
  • DW_DISABILITY_ORG_D
  • DW_INV_ORGANIZATION_D
  • DW_LEGAL_EMPLOYER_D
  • DW_REPORTING_EST_D
  • DW_BUSINESS_UNIT_D_TL
  • DW_DEPARTMENT_D_TL
  • DW_DISABILITY_ORG_D_TL
  • DW_INV_ORGANIZATION_D_TL
  • DW_LEGAL_EMPLOYER_D_TL
  • DW_REPORTING_EST_D_TL
  • DW_PRFL_JOB_CMPTNCY_CRNT_F
  • DW_PRFL_JOB_DEGREES_CRNT_F
  • DW_PRFL_JOB_HNR_AWARD_CRNT_F
  • DW_PRFL_JOB_LANGUAGES_CRNT_F
  • DW_PRFL_JOB_LIC_CERT_CRNT_F
  • DW_PRFL_JOB_MEMBRSHIP_CRNT_F
  • DW_PRFL_JOB_WRK_REQ_CRNT_F
  • DW_PRFL_POSN_CMPTNCY_CRNT_F
  • DW_PRFL_POSN_DEGREES_CRNT_F
  • DW_PRFL_POSN_HNR_AWARD_CRNT_F
  • DW_PRFL_POSN_LANGUAGES_CRNT_F
  • DW_PRFL_POSN_LIC_CERT_CRNT_F
  • DW_PRFL_POSN_MEMBRSHIP_CRNT_F
  • DW_PRFL_POSN_WRK_REQ_CRNT_F
  • DW_PRFL_PRSN_ADV_RDYNS_CRNT_F
  • DW_PRFL_PRSN_CAR_PREF_CRNT_F
  • DW_PRFL_PRSN_CMPTNCY_CRNT_F
  • DW_PRFL_PRSN_DEGREES_CRNT_F
  • DW_PRFL_PRSN_EDU_LEVEL_CRNT_F
  • DW_PRFL_PRSN_HNR_AWARD_CRNT_F
  • DW_PRFL_PRSN_IMPC_LOSS_CRNT_F
  • DW_PRFL_PRSN_LANGUAGES_CRNT_F
  • DW_PRFL_PRSN_LIC_CERT_CRNT_F
  • DW_PRFL_PRSN_MEMBRSHIP_CRNT_F
  • DW_PRFL_PRSN_PERF_RTNG_CRNT_F
  • DW_PRFL_PRSN_POT_RTNG_CRNT_F
  • DW_PRFL_PRSN_PRV_EMPMT_CRNT_F
  • DW_PRFL_PRSN_RSK_LOSS_CRNT_F
  • DW_PRFL_PRSN_SKILL_CRNT_F
  • DW_PRFL_PRSN_SPCL_PROJ_CRNT_F
  • DW_PRFL_PRSN_TLNT_SCOR_CRNT_F
  • DW_PRFL_PRSN_WRK_REQ_CRNT_F
  • DW_GRADE_RATE_CURRENT_D
  • DW_GRADE_RATE_VALUE_CURRENT_D
  • DW_JOB_CURRENT_D
  • DW_JOB_FAMILY_CURRENT_D
  • DW_MANAGER_CF_TD_CURRENT_DH
  • DW_MANAGER_DN_CURRENT_DH
  • DW_MANAGER_REPORTEES_CF_DN_CURRENT_DH
  • DW_MANAGER_REPORTEES_CF_DN_NSR_DH
  • DW_PAY_GRADE_CURRENT_D
  • DW_PERSON_LEGISLATION_ALL_CURRENT_D
  • DW_PERSON_NAME_CURRENT_D
  • DW_POSITION_CURRENT_D
  • DW_JOB_D_CURRENT_TL
  • DW_JOB_FAMILY_D_CURRENT_TL
  • DW_PAY_GRADE_D_CURRENT_TL
  • DW_POSITION_D_CURRENT_TL

Initial Extract Date Parameter Usage

Oracle Fusion Analytics Warehouse uses the Initial Extract Date pipeline parameter when you extract data for a full load.

Use this spreadsheet to know how the Initial Extract Date pipeline parameter value impacts the tables in Oracle Fusion HCM Analytics during data load.

Tables With Data Rejection Details

This section provides information about tables that contain the data rejection details. The source Oracle Fusion Cloud Human Capital Managementtables might contain bad data and might impact the reporting metrics in Oracle Fusion HCM Analytics. Therefore, bad source data won't be loaded into Oracle Fusion HCM Analytics, and instead are captured in DW_ERR_RECORDS and DW_ERR_CODE_TL tables.

Error Code Translation Table Spreadsheet

Download this spreadsheet that contains the Error Codes and their description details.