Using Oracle Fusion HCM Analytics to Help Manage Data Integrity for Oracle Fusion Cloud HCM Records

March 22, 2023 | 10 minute read
Nupur Joshi
Senior Principal Product Manager, Analytics Apps for HCM
Abhishek Bajpai
Consulting Solution Architect, Oracle Analytics
Anirban Majumdar
Director, FAW Engineering
Ranjith Annadi
Principal Member of Technical Staff
Text Size 100%:

 

Oracle Fusion Analytics is an analytics application that helps you answer key business questions and make better-informed decisions. Cultivating data literacy in your organization and maintaining accurate data in your underlying transaction system are two of the most important elements in becoming data-driven. Fusion HCM Analytics not only helps transform data into an easily consumable format, but it also identifies erroneous data, which helps you clean data in the source transaction system via reject records generated every time the data pipeline runs. This article provides guidance on how you can use this metadata to investigate data anomalies and collaborate with Oracle Fusion Cloud HCM support for data cleanup at the source.

Approach

This blog describes how you can

  1. Check the Rejected Report in Fusion HCM Analytics.
  2. Execute investigation queries to analyze the anomalies in Oracle Fusion Cloud HCM.

Check the Rejected Report in Fusion Analytics

Proceed as follows:

  • Log in to Fusion HCM Analytics
  • Navigate Home àProjects.

Navigation

  • Further navigate to data visualization Shared Folders àCommonà Warehouse Refresh Statistics.


WStats

This is a ready-to-use report with various tabs that provide different levels of detail. Alternatively, you can create your report by using the Common – Warehouse Refresh Statistics subject area, as shown in this image:
Reject Report

You can see the reject record codes on the report. You are also provided primary keys (Identifiers or IDs) to identify exactly the records that had issues. The next section describes how you can query these reject codes further.

Reject codes and corresponding investigation queries

 This section lists the most common error codes you might encounter, along with SQL queries you can use for further analysis and investigation.

ERROR_CODE

ERROR_MESSAGE

Corresponding SQL

Impacted 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

SELECT     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**.

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 area

 

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 Fusion Cloud HCM with the following parameters:

Execute investigation queries to analyze the anomalies in Fusion Cloud HCM.

To execute the queries provided above:

  • Log in to Oracle Analaytics Publisher and select the data model:

BIP login

  • On the data model screen, select the SQL Query option:

model

  • Enter the  SQL Query anc change the data source to ApplicationDB_HCM:

BIP Query

  • After your data model is created, select View Data:

Query

  • Check the results.

Results

Further Action

You can file a Service Request with Fusion HCM Cloud to fix any records that are identified via the SQL queries suggested.

Summary

To help ensure data integrity, Fusion HCM Analytics blocks anomalous data from being loaded and logs it as rejected. This blog describes the 12 most common data mismatch scenarios you might encounter. Using this information can help you understand and analyze the nature of the data rejects and collaborate with the Fusion Cloud HCM teams on the best approach for reconciling the data. 

For more details on reject records, please see Tables with Rejection Details.  And to be sure you're set up for success with your Fusion HCM implementation, see  Prerequisites for Implementing Fusion HCM Analytics.

 

 

 

Nupur Joshi

Senior Principal Product Manager, Analytics Apps for HCM

Abhishek Bajpai

Consulting Solution Architect, Oracle Analytics

I have worked in data management and reporting tech stack for 22 years and more. Currently, I am part of the CEAL team for Service Excellence.

Past roles :

Oracle Analytics - Had been a part Oracle North America Sales engineering team and managed several Oracle Analytics projects for Deloitte, Capgemini, and Oracle consulting services.

           Other Roles-   Oracle DBA (8.x. to 10g) , HP Neoview product engineering team, architected solutions/managed deliveries in Azure, and AWS tech stack.

Show more

Anirban Majumdar

Director, FAW Engineering

Ranjith Annadi

Principal Member of Technical Staff


Previous Post

Access Oracle Analytics Cloud logs through integration with Oracle Cloud Infrastructure Logging

Adam Bloom | 7 min read

Next Post


Leverage Parameters for dynamic data manipulations

Avinash Krishnaram | 5 min read
Oracle Chatbot
Disconnected