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 ready-to-use 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.
Salary Basis	This feature enables the Salary Basis 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.
Time and Labor	This feature enables the Time and Labor offering.

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 Analytics Warehouse 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.

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 Analytics Warehouse 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.
  • Ensure label codes don't have any spaces.
  • After you create the label codes, don't remove any existing labels used with individual segments.
  • Deploy this flexfield and run the Import Oracle Fusion Data Extensions for Transactional Business Intelligence process before enabling the pipeline for the Payroll Costing subject area.
  • See Payroll Cost Allocation Key Flexfield Setup and Enable Descriptive Flexfields for Oracle Business Intelligence.
  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 menu 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 Analytics Warehouse 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 Analytics Warehouse 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 menu on global header. See Run Diagnostic Tests.
• Run the Refresh Manager Hierarchy process to avoid erroneous data from being imported to Oracle Fusion Analytics Warehouse 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 Analytics Warehouse 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 Analytics Warehouse. 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 Analytics Warehouse 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 Analytics Warehouse 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 ready-to-use 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 Analytics Warehouse. See Create a User in Oracle Fusion Cloud Applications to Extract Data.
• In Oracle Fusion Analytics Warehouse, 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.

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 Analytics 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 Analytics Warehouse. 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.