1. Reference for Fusion HCM Analytics
  2. Introduction

1 Introduction

Overview

Oracle Fusion HCM Analytics is a prebuilt cloud native warehouse-based analytics solution built on top of the HCM Cloud application.

It provides human resource professionals with prebuilt workforce insights to improve their decisions related to workforce diversity, employee attrition and retention, talent acquisition, compensation, and more. HR teams can enrich their analytics beyond what Oracle Cloud HCM provides with additional data sources by extending the single, cross-functional data model and pipelines, and end user metrics without coding. See Oracle Fusion HCM Analytics.

This guide contains information about data warehouse tables and entity relationship details for Oracle Fusion HCM Analytics. The guide covers these details for subject areas:
  • Job-specific Groups
  • Duty Roles
  • Associated Business Questions
  • Metric Details
  • Descriptive Flexfields

The guide also contains information on prebuilt analyses, prebuilt business metrics, and prebuilt dashboards.

Features Available for Preview

Preview features enable functional administrators to switch functionality on and off. This way, your organization can evaluate and learn how to use new features before they roll out by default.

To enable the preview features, see Make Preview Features Available.

Functional Areas
Feature Description
Diversity Analysis

This feature enables the Diversity Analysis offering.

Note:

You must first enable the Diversity Analysis Prerequisites functional area and only then enable the Diversity Analysis functional area. The Diversity Analysis Prerequisite functional area gets the information needed for further processing and calculations that happens in the Diversity Analysis functional area.
Health and Safety This feature provides information about the workforce health and safety incidents. This includes the details related to incidents, events, and actions.
Learning Management This feature enables the Learning Management offering.
Seniority Dates This feature enables reporting on V3 seniority dates. It provides information of worker's seniority across the enterprise, various work relationship, and assignments. Worker's seniority across all rules are available for reporting using this. You can also report on the seniority rule setup.
Pipeline Features
Feature Description
Disable HCM Data Consistencies and Logging This feature provides the ability to enable and disable HCM Data inconsistency logging and record rejection.

Features Generally Available

Oracle Fusion Data Intelligence offers certain functionality as generally available that you must enable using the Console. Generally available features are publicly available features that you can use for production workloads. These features enable functional administrators to switch functionality on and off and are disabled by default.

To enable the generally available features, see Enable Generally Available Features.

Functional Areas
Feature Description
Payroll Balances This feature enables the Payroll Balances functional area. It provides insights into payroll balances at person level and cumulative balance values across dimensions.

Note:

Before enabling the Payroll Balances functional area, you must enable the Workforce Core functional area and ensure that the required Cloud HCM balance group, Analytics Warehouse Balance Group, is set up. Payroll has been successfully implemented for customers with 90,000 employees. We recommend all customers to implement it as we continue to make it work for larger organizations.
Payroll Costing This feature enables the Run Costing and Costing of Payment information in the Payroll Costing functional area. It provides insights into payroll run costing and costing of payment at person level and cumulative values across dimensions.

Note:

Before enabling the Payroll Costing functional area, you must enable the Workforce Core functional area and ensure that the Cloud HCM Cost Allocation Key Flex Field Label is set up and assigned to individual segments. Payroll has been successfully implemented for customers with 90,000 employees. We recommend all customers to implement it as we continue to make it work for larger organizations.
Salary Basis This feature enables the Salary Basis functional area. Salary Basis has been successfully implemented by 40 organizations across the globe with maximum data volume of around 1 million records. Recommendation is to implement it for all organizations and specifically for larger organizations with higher data volumes.
Time and Labor This feature enables the Time and Labor functional area. Time and Labour has been successfully implemented by 40 organizations across the globe with maximum data volume of around 1 million records. Recommendation is to implement it for all organizations and specifically for larger organizations with higher data volumes.

Prerequisites for Oracle Fusion HCM Analytics

Be sure you review and set up these prerequisite requirements to use Oracle Fusion HCM Analytics and the provided subject areas.

  • Select the Include in daily report breakdown check box that's available in the Action Items tab of the Absence Type Setup page and run the Generate Daily Breakdown of Absence Details process in Oracle Cloud HCM to provide applicable values in the HCM - Workforce Absence Management subject area in Oracle Fusion HCM Analytics . See Generate Daily Breakdown of Absence Details Process.
  • Set up the Analytics Warehouse Balance Group in Oracle Cloud HCM before enabling the pipeline for the Payroll subject area. This setup ensures that only applicable balance data is loaded into Oracle Fusion Data Intelligence when you run the pipeline for the Payroll subject area. See Create Balance Groups and Usages.
  • Set up labels for costing segments in the Cost Allocation key flexfield in Oracle Cloud HCM to associate the correct labels with segments in the flexfield setup. This ensures the payroll costing data is organized and easy to understand.
    1. In Oracle Cloud HCM, navigate to Set up and Maintenance and search for Cost Allocation Key Flexfield.
    2. Click Actions, then click Manage Segment Labels, then select Create Labels and associate BI Object Names.
    3. To associate each label with the corresponding segment, navigate to Manage Structures, search for a specific structure, and then click Edit. Repeat this step to edit each individual segment for each label.
    4. When you're done editing all segments, navigate back to Cost Allocation Key Flexfield and click Deploy Flexfield.
    5. When the process completes, run Import Oracle Fusion Data Extensions for Transactional Business Intelligence.

    Note:

    Repeat these steps each time you edit the flexfield in Oracle Cloud HCM.

    With this setup in Oracle Cloud HCM, you can report on the segregation of expenses and liabilities based on individual segments with appropriate labels associated with them. For example, if segment1 in a GL account code combination is a company, then you can report on a Company as a label instead of Segment1. In the absence of this setup, you can only run reports or analytics at the concatenated segment level.

  • Run HCM Diagnostics in Oracle Cloud HCM to identify data inconsistencies beforehand and corrective diagnostics to fix most of the inconsistencies observed in Oracle Cloud HCM data. If you leave the inconsistencies in Oracle Cloud HCM data unresolved, then the inconsistencies lead to data pipeline rejecting the records (either fully or partially) from loading into the warehouse base tables. You must have the Application Diagnostics Administrator role assigned in Oracle Cloud HCM to view and run the Run Diagnostics Tests link from the Settings and Actions on global header. You should run these diagnostic tests (manually or automatically) before every pipeline execution to avoid any data-related issues in Oracle Cloud HCM:
    • Person Diagnostic Auto Correct: This diagnostic test addresses Person related data corruptions that can be resolved without needing to take any decision-related input from the user. Run this test when the DW_PERSON_LEGISLATION_ALL_D table has rejected records in the Oracle Fusion Data Intelligence pipeline run.
    • Run these tests before running the Refresh Manager Hierarchy scheduled process:
      • Overlapping Line Managers - Reports: This diagnostic test is addresses cases where an assignment has more than one Line Manager present at a given point of time. This is a corruption scenario because an assignment can only have a single Line Manager at a time. This constraint doesn't apply to other types of managers.
      • Manager Hierarchy Loop Detection - Report: This diagnostic test is addresses Manager Hierarchy loop-related corruptions. For example, assume the line manager hierarchy is already present in the system A to B to C. If the manager relationship C to A is also present in the system, then it's called a Manager Hierarchy loop. If there's one or more manager hierarchy loops present in the source data, such corrupted data can cause problems with submitting or completing employment transactions.

      These tests are required in case the Oracle Fusion Data Intelligence pipeline detects rejected records in these data warehouse tables:

      • DW_WRKFRC_SUP_FL_DT_STAGE
      • DW_WRKFRC_GNLS_DRVD_PRV_STAGE
      • DW_WRKFRC_GNLS_DRVD_CUR_STAGE
      • DW_WRKFRC_GAIN_LOSS_ASG_MGRH_F
      • DW_WRKFRC_ASG_EVENT_RSN_F
      • DW_WRKFRC_GAIN_LOSS_ASG_F

      You can retrieve this information from the Common - Warehouse Refresh Statistics subject area.

      Note:

      You must have the Application Diagnostics Administrator role assigned in Oracle Cloud HCM to view and run the Run Diagnostics Tests link from the Settings and Actions on global header. See Run Diagnostic Tests.
  • Run the Refresh Manager Hierarchy process to avoid erroneous data from being imported to Oracle Fusion Data Intelligence or missing data. To optimize performance, the complete manager hierarchy for each person is extracted from live data tables and stored in a separate manager-hierarchy table, known as the denormalized manager hierarchy. For populating the manager hierarchy dimension, Oracle Fusion Data Intelligence refers to the underlying application hierarchy tables being refreshed by this process. If this process doesn't run on Oracle Cloud HCM before the data is loaded into the warehouse (pipeline activation), you may encounter data errors. See The Manager Hierarchy: How It's Maintained.
  • Create a user in Oracle Fusion Cloud Applications to extract data. If you want to use the password-based basic authentication to connect to your source system, then you must provide credentials of a user who has the appropriate privileges to extract data from Oracle Fusion Cloud Applications into Oracle Fusion Data Intelligence. See Create a User in Oracle Fusion Cloud Applications to Extract Data.
  • Verify the Location Based Access Control (LBAC) setting. LBAC is a method which allows users connecting from an approved IP address to access the Oracle Fusion Cloud Human Capital Management application with their full roles and rights. The approved IP addresses are listed in the IP Address Allowlist section of the Security Console. If the Oracle Cloud HCM application URL (podname.dataregion.oraclecloud.com) is accessed from an IP address that isn’t listed in the IP Address Allowlist area of the Security Console, then by default that user will only be given access to public roles. When LBAC is configured, roles can be marked as public from the security console. These public roles will take effect when logging in from an unlisted IP address.

    If the user that you created to extract data from Oracle Fusion Cloud Applications into Oracle Fusion Data Intelligence accesses Oracle Fusion Cloud Human Capital Management from a location that is unlisted (IP is not Allowlisted), then the private roles that the user is associated with get filtered out and won't be assigned. Unless the connection is made from a server whose IP address is allowlisted/approved, this user won't be assigned full roles and rights. This causes user authentication issues during Oracle Fusion Data Intelligence instance creation and pipeline jobs.

    In Oracle Fusion Cloud Applications, navigate to the Security Console, click Administration, and select the Location Based Access tab to verify whether the Enable Location Based Access checkbox is selected. If LBAC is enabled in the Oracle Cloud HCM instance, then perform these steps:

    • Create copies of these prebuilt roles and enable them for access from all IP addresses:
      • Rest API Extract Privilege (role code: BICC_REST_EXTRACT_PRIV); for example, name it CustomCopy Rest API Extract Privilege and provide code as COPY_BICC_REST_EXTRACT_PRIV.
      • Upload and download data from the on-premises system to the cloud system (role code: OBIA_EXTRACTTRANSFORMLOAD_RWD); for example, name it CustomCopy Upload and download data from on-premise system to cloud system and provide code as COPY_OBIA_EXTRACTTRANSFORMLOAD_RWD.
    • On the Role Hierarchy step, add the seeded role membership Rest API Extract Privilege (role code: BICC_REST_EXTRACT_PRIV) to the CustomCopy Rest API Extract Privilege role, and change the Upload and download data from on-premise system to cloud system (role code: OBIA_EXTRACTTRANSFORMLOAD_RWD) role to the CustomCopy Upload and download data from on-premise system to cloud system role.
    • Assign the CustomCopy Rest API Extract Privilege and CustomCopy Upload and download data from on-premise system to cloud system roles to the user that you created to extract data from in Oracle Fusion Cloud Applications into Oracle Fusion Data Intelligence. See Create a User in Oracle Fusion Cloud Applications to Extract Data.
  • In Oracle Fusion Data Intelligence, set up the pipeline parameters for your data model file before running your data pipelines for the functional areas and reporting configurations for Oracle Fusion HCM Analytics to specify how the Human Capital Management-related data is presented on the key metrics, workbooks, visualizations, dashboards, and reports. See Set Up the Pipeline Parameters and Set Up the Reporting Configurations for Human Capital Management.
  • You need to run the Calculate Seniority Dates process in Oracle Cloud HCM to get seniority data for workers. You can use this data for reporting in the HCM - Worker Seniority (Preview) subject area.
  • For HCM Goals and Career Development performance goals, ensure the following:
    • Performance goals are present in Performance goal fact : dw_goal_performance_ef.
    • Organization goals are present in Organization goal fact: dw_goal_organization_ef.
    • The mismatch of goal count is because of organization goals not present in dw_goal_performance_ef.
    • The Oracle Transactional Business Intelligence reports brings in Organization Goals in addition to Performance Goals. Comparing with only Performance Goals from Oracle Transactional Business Intelligence is a work in progress and is in pipeline.

Analyze Data Anomolies

Use the Oracle Fusion HCM Analytics Rejected Report to analyze, investigate, and resolve data anomolies in Oracle Cloud HCM.

(Optional) <Describe the context.>
(Optional) <Describe the prerequisites.>
  1. Sign in to your Oracle Fusion HCM Analytics service.
  2. In Oracle Fusion Data Intelligence Console, navigate to Projects Catalog, then open Shared Folders, and within the Common folder open the Warehouse Refresh Statistics workbook.
    Review the Reject Record Codes to troubleshoot issues and determine the next appropriate steps.
  3. Use the table shown here to pinpoint the potential causes of each error.
    Error Code Error Message Corresponding SQL Subject Area
    GOAL_PLAN_GOAL_ISSUE_A The combination of Goal, Goal Plan, Goal Plan Set, and Review Period is not unique 
    SELECT goal_id,
                   goal_plan_id,
                   goal_plan_set_id,
                   review_period_id
FROM   hrg_goal_plan_goals
WHERE  goal_plan_goal_id
/*Insert the Rejected Record Primary Key Value within the parenthesis */
    		 HCM- Goals and Career Development
    ACCRUAL_ENTRY_DETAIL_ISSUE_A Accrual Entry is associated with more than one assignment 
    SELECT DISTINCT per_accrual_entry_id,                    
assignment_id
FROM            anc_per_acrl_entry_dtls
WHERE           per_accrual_entry_id IN              
      (
                SELECT per_accrual_entry_id
                FROM   anc_per_acrl_entry_dtls
                WHERE  per_accrual_entry_id IS NOT NULL
                AND    per_accrual_entry_dtl_id IN()     
/*Insert the Rejected Record Primary Key Value within the parenthesis */);
    		 HCM- Workforce Absence Management
    PROFILE_ITEMS_ISSUE_A The combination of Profile, Content-Type, and Content Item records has overlapping date ranges 
    SELECT a.profile_id, 
     a.content_type_id, 
     a.content_item_id, 
     a.date_from, 
     a.date_to, 
     a.item_number_2, 
     a.rating_model_id1, 
     a.rating_model_id2 
FROM hrt_profile_items a 
inner join hrt_profile_items b 
ON a.profile_id=b.profile_id 
AND a.content_type_id=b.content_type_id 
AND Nvl(a.content_item_id,-99999) = Nvl(b.content_item_id,-99999) 
WHERE b.profile_item_id= 
/* Add PROFILE_ITEM_ID Value here*/ 
order by a.profile_id, 
     a.content_type_id, 
     a.content_item_id, 
     a.date_from;
    • HCM – Talent profile
    • HCM – Workforce Core
    • HCM- Talent Review
    PROFILE_ITEMS_ISSUE_B There is more than one row for the combination of Profile, Content-Type, and Talent Review Meeting
    WRKFRC_ASG_SUP_ISSUE_A A single Manager Assignment Identifier is associated with multiple Manager Person Identifiers 
    SELECT DISTINCT manager_assignment_id,               
     manager_id
FROM       per_assignment_supervisors_f
WHERE      (
                 manager_assignment_id) IN                    
     (
      SELECT manager_assignment_id count
      FROM   per_assignment_supervisors_f
      WHERE  manager_type='LINE_MANAGER'
      AND    primary_flag='Y'
      AND    assignment_supervisor_id =
      /* Add ASSIGNMENT_SUPERVISOR_ID Value here*/
      and    effective_start_date = ''
      /* Add EFFECTIVE_START_DATE Value here*/
      AND    effective_end_date = ''
      /* Add EFFECTIVE_END_DATE Value here*/);
    		 All Subject Areas
    TA_SRC_TRCK_ISSUE_A Multiple Source Tracking Details exist for a single job application/submission 
    SELECT   source_tracking_id,
         submission_id
FROM     irc_source_tracking
WHERE    submission_id IN
         (
                SELECT submission_id
                FROM   irc_source_tracking
                WHERE  source_tracking_id IN ()
   /*Insert the Rejected Record Primary Key Value within the parenthesis */)

ORDER BY submission_id;
    		 HCM – Talent Acquisition
    RATING_CATEGORY_ISSUE_A Rating categories have overlapping upper or lower boundaries 
    LECT     a.category_id,
                       a. business_group_id,
                       a.rating_model_id,
                       a.lower_boundary,
                       a.upper_boundary
FROM       hrt_rating_categories_b a
inner join hrt_rating_categories_b b
ON         a.rating_model_id=b.rating_model_id
AND        a.business_group_id=b.business_group_id
AND        b.category_id IN ()
/*Insert the Rejected Record Primary Key Value within the parenthesis */
    • HCM- Performance Management
    • HCM – Talent Profile
    • HCM – Talent Review
    • HCM – Workforce Core
    MNGR_HRCHY_CFDNDH_ISSUE_A** For a manager type, multiple records are present on the same day for the same manager and reporter. For this case, bad data is not removed from Fusion Analytics 
    SELECT   manager_assignment_id,
         level20_reportee_assignment_id,
         effective_start_date,
         effective_end_date
FROM     per_manager_hrchy_reportees_dn
WHERE    manager_type='LINE_MANAGER'
AND      manager_assignment_id =
         /*Insert the Rejected Record MANAGER_ASSIGNMENT_ID Value here */
and      level20_reportee_assignment_id =
         /*Insert the Rejected Record LEVEL20_REPORTEE_ASSIGNMENT_ID Value here*/
ORDER BY effective_start_date;
This issue is a result of HCM prerequisites not being executed, Check the section below this table for detailed pre emptive steps**.

    This issue is a result of HCM prerequisite not being executed, Check the section below this table for detailed pre emptive steps**.

    		 All Subject Areas
    WRKFRC_ASG_TERMINATION_ISSUE Multiple termination records exist for an Assignment on the same day 
    SELECT assignment_id,
       effective_start_date,
       effective_end_date,
       effective_sequence,
       effective_latest_change,
       assignment_type,
       assignment_status_type
FROM   per_all_assignments_m per_all_assignments_m
WHERE  assignment_id =
       /* Add ASSIGNMENT_SUPERVISOR_ID Value here*/
and    effective_start_date = ''
       /* Add EFFECTIVE_START_DATE Value here*/
       ;
    		 HCM – Workforce Core
    GOAL_ALIGNMENT_ISSUE_A The combination of Goal and Aligned Goal is not unique 
    SELECT goal_id,
       aligned_goal_id
FROM   hrg_goal_alignments
WHERE  (
              goal_id,aligned_goal_id ) IN
       (
              SELECT goal_id,
                     aligned_goal_id
              FROM   hrg_goal_alignments
              WHERE  goal_alignment_id=
                     /* Add GOAL_ALIGNMENT_ID Value here*/ );
    		 HCM- Goals and Career Development
    OVERLAP_RULE_A The Start Date comes after the End Date 
    SELECT DISTINCT person_id,
                legislation_code,
                effective_start_date,
                effective_end_date
FROM            per_people_legislative_f
WHERE           (
                                person_id,legislation_code) IN
                (
                       SELECT person_id,
                              legislation_code
                       FROM   per_people_legislative_f
                       WHERE  person_legislative_id =
                              /* Add PERSON_LEGISLATIVE_ID Value here*/
                       and    effective_start_date = ''
                              /* Add EFFECTIVE_START_DATE Value here*/
                       AND    effective_end_date = ''
                              /* Add EFFECTIVE_END_DATE Value here*/
                );
    		 All Subject Areas
    OVERLAP_RULE_D The start date of the current record is the same or less than one of the end dates of the previous records OR the end date of the current record is the same or greater than one of the start dates of the next records

    **Fusion HCM Analytics Prerequisite Steps for Manager Hierarchy

    Run the Refresh Manager Hierarchy process in Oracle Cloud HCM with the following parameters and then reset Oracle Fusion Data Intelligence. See Reset the Data Warehouse.

    Updated Within the Last N Days Mode Details
    No parameter value (Blank/NULL) Normal - Full Run Populates the denormalied tables PER_MANAGER_HRCHY_DN and PER_MANAGER_HRCHY_CF from the source of PER_ASSIGNMENT_SUPERVISORS_F deletes and insert denormalized data for ALL the assignment supervisor rows
    +ve parameter value Normal - Incremental Run Populates the denormalized tables PER_MANAGER_HRCHY_DN and PER_MANAGER_HRCHY_CF from the source of PER_ASSIGNMENT_SUPERVISORS_F deletes and inserts denormalized data for assignment supervisor rows updated/created in last N days (ie if 5 is provided as parameter value then data updated/created in last 5 days will be processed)
    0 parameter value OTBI - Full Run Populates the denormalized table PER_MANAGER_HRCHY_REPORTEES_DN from the source of PER_ASSIGNMENT_SUPERVISORS_F deletes and insert denormalized data for ALL the assignment supervisor rows during processing would refer to data of other de-normalized tables PER_MANAGER_HRCHY_DN and PER_MANAGER_HRCHY_CF
    -ve parameter value OTBI - Incremental Run Populates the denormalized table PER_MANAGE_HRCHY_REPORTEES_DN from the source of PER_ASSIGNMENT_SUPERVISORS_F deletes and insert denormalized data for assignment supervisor rows updated/create in last N days (ie if -5 is provided as parameter value then data updated/created in last 5 days will be processed)
  4. Log in to Oracle Analytics Publisher and select the data model.
  5. On the Diagram tab select SQL Query.
  6. Enter the SQL query Name, change the Data Source to ApplicationDB_HCM, ensure the Type of SQL is set to Standards, and then enter the SQL query text from the examples above in the field provided. Click OK.
  7. Click View Data and check the results in the Table View.