How to set up data security in Oracle Fusion HCM Analytics using the Area of Responsibility Department Hierarchy attribute

April 6, 2023 | 11 minute read
Nupur Joshi
Senior Principal Product Manager, Analytics Apps for HCM
Romesh Lokuge
Principal Member of Technical Staff, Analytics Customer Excellence
Master Principal Sales Consultant
Text Size 100%:

With inputs from Niraj Kumar, Sr.Director, Software Development and Sreebhushan Shivapuram, Consulting Memeber Technical Staff, Software Development.

This blog is part of a series about setting up custom security in Fusion HCM Analytics using Oracle Fusion Cloud HCM Areas of Responsibility (AOR) data.  Please see the overview, How to set up Fusion HCM Analytics custom security using Areas of Responsibility (AOR), for key concepts and background on this capability.

This blog describes how to set up custom data security in Oracle Fusion HCM Analytics using AOR Department Hierarchy data extracted from Oracle Fusion Cloud  HCM. 

Fusion HCM data setup for users with access to a department hierarchy in AOR

  1. The user ‘Marcia.Brooks’ has been assigned Area of Responsibility for Department Hierarchy:



Steps for Data Augmentation

AOR attribute assignment in Fusion HCM is available in DW_ASG_RESPONSIBILITY_D table in your Fusion HCM Analytics instance of Oracle Autonomous Data Warehouse.

For the purpose of this use case, there are two additional row-flattened View Objects (VO) required to set up security based on Department Hierarchy, which mean you need to use augmentation to get that data.

  • CrmAnalyticsAM.PartiesAnalyticsAM.FndTreeVersionVO
  • HcmTopModelAnalyticsGlobalAM.HCMExtractAM.OrganizationBiccExtractAM.DeptTreeNodeRFExtractPVO 

Note - In future releases, both VOs mentioned above will be available by enabling the Fusion HCM Security Configuration work area, which will allow you to refer to corresponding tables when you create session variables and skip additional augmentation steps.

  1. Select all required fields and provide the View Object (VO) name in the “Source Table” field:


  1. Select all attributes:


  1. Select the required Extract Date & Incremental Date:


  1. Under the Entity options tab, no subject area needs to be selected as additional data brought in via augmentation is not to be exposed in any of the subject area.


  1. Repeat the same steps (1- 4 in the steps for Data Augmentation section) for  HcmTopModelAnalyticsGlobalAM.HCMExtractAM.OrganizationBiccExtractAM.DeptTreeNodeRFExtractPVO.
  2. Once the pipeline status of both Augmentations is “ACTIVATION COMPLETE”, you can set up custom data security.Dept9

Steps for Setting up Custom Data Security

  1. Create a custom data role
    1. Navigate from the Fusion HCM Analytics login > Console > Security > Application Roles > New Application Role


  1. Assign the data role created above to any predefined Fusion HCM Analytics job group, or you can create custom job groups based on your business requirements and assign the data role to them. In the example below, there's a custom job group – FAWCustomHCMDeptSec - with all required data/duty roles assigned to it.


  1. Create a session variable
    1. Navigate from the Fusion HCM Analytics login > Console > Semantic Model Extensions > User Extensions > Create Branch > Add Step > Create Session Variable


    1. Use the below SQL to create your session variable. Edit the SQL as required to match the business requirement based on the AOR fields selected in Fusion HCM.

SELECT dwaor.treeversionid,dwaor.treecode, dwup.username, dwaor.pk1value, dh.department_name, dwup.username, dwaor.*


   dw_fa_x_crm_tree_version ftv,




    dw_department_d_tl  dh

WHERE 1=1 AND ftv.treestructurecode = 'PER_DEPT_TREE_STRUCTURE'  AND ftv.status = 'ACTIVE'

    AND trunc(sysdate) BETWEEN ftv.effectivestartdate AND ftv.effectiveenddate AND ftv.treeversionid = dwaor.treeversionid

    AND ftv.treecode = dwaor.treecode AND DWAR.responsibility_type = 'HR_REP' /* This needs to be changed based on Your Responsibility Type usage in Cloud HCM */

    AND DWAR.department_tree_code =  ftv.treecode  AND DWAR.person_id = dwup.person_id AND DWUP.USERNAME=’VALUEOF(NQ_SESSION.USER)’

AND trunc(sysdate) BETWEEN DWAR.start_date AND nvl(DWAR.end_date, sysdate)     /* To  pick only the latest responsibility */

    AND dh.department_id = dwaor.pk1value

AND to_number(DWAOR.ancestor_pk1_value) = nvl(DWAR.top_department_id, to_number(DWAOR.ancestor_pk1_value)) /* To  join to the parent explicitly if Top department is given in AOR UI */

    AND DWAOR.distance > decode(DWAR.include_top_hier_node, 'N', 0,(DWAOR.distance - 1)) /* To restrict the levels if  defined in AOR UI */

    AND DWAORf.distance < nvl(DWAR.hierarchy_levels, DWAOR.distance + 1) /* To  restrict the levels if  defined in AOR UI */

    1. Enter the SQL above in the screen below after modifying it based on the AOR fields used in Fusion HCM.


    1. Create the Session Variable Name as CUSTOMDEPTSEC, per the value used in the select statement in the previous steps. Make sure it is in CAPITAL letters only with the Row-Wise Initialization check box selected.


    1. Merge this step with the Main Branch and publish the model. 
  1. Apply filters to secure facts and dimensions using custom data roles created in Step 1 of the section “Steps for Setting up Custom Data Security”
    1. Navigate from the Fusion HCM Analytics login > Semantic Model Extension > Security Configuration > Data Security Step
    2. In the Application Role field, select the Data Role created in Step 1:


    1. Select the required logical objects from Available Object list on left panel in the screen below:


    1. Click 'Edit Filter Argument' and add the following filters: "Core"."Dim - Department"."Department Name"=VALUEOF(NQ_SESSION.CUSTOMDEPTSEC).
      1. This refers to the session variable created in Step 3, Create Session Variable.



Steps to verify data security setup in above sections

  1. Assign user ‘Marcia.Brooks’ to the custom job group “FAWCustomHCMDepSec” to grant data access based on the department hierarchy values assigned in Fusion HCM.
  2. Assign users to the Fusion HCM Analytics Licensed HCM Author/Consumer group based on your business needs. For the purpose of this use case, the user 'Marcia.Brooks' has been assigned to the FAW Licensed HCM Authors group to allow logged in users to create their own content. 


  1. Verify the total worker count by ckecking a user with Fusion HCM View All Data Security
    1. With no parameters selected, the Total Worker Count shows 4042, and the department hierarchy breakdown for EMEA shows 180:


    1. From the login of the user with Fusion HCM View All Data Security, when applying filters on a specific organization hierarchy assigned as an AOR to Marcia.Brooks:


    1. This validates that Marcia.Brooks will see ‘180’ as the EMEA worker count after the latest pipeline run date.
  1. Log in as Marcia.Brooks and open the same analysis. 



  1. Check historical data access for user Marcia.Brooks:
    1. Person Number 831 is under the department Consulting EAST in the EMEA hierarchy through 27-FEB-2023.  


    1. ET831 has moved to the APAC department from 28-FEB-2023 onwards.


    1. The below screenshots show that EMEA and APAC are two separate nodes under Corporate HQ:  



    And that the Department Consulting East FR is under the EMEA hierarchy:


      1. Marcia.Brooks only has access to EMEA hierarchy:


    1. Marcia.Brooks can see following data for person number 831 through 27-FEB-2023 (the time the worker was under EMEA hierarchy):


    1. For the user with Fusion HCM View ALL Data security access, for worker 831 in 2023, assignments with “Consulting East FR” & “APAC” are visible:


  1. Validate terminated worker data access for Marcia.Brooks by checking the termination record transaction with INACTIVE ASSIGNMENT for PERSON NUMBER 735:


    1. Marcia.Brooks can see worker 735 under Termination Count from March 2023 onwards, and under the Active Worker Count through Feb 2023.  This validates that her access is correct based on the AOR data.




Currently, security data syncing between Fusion HCM Analytics and Fusion Cloud HCM isn't automated.  Using AOR data means you can set up custom data security and offer your business users a near seamless experience between the two solutions. For large organizations with many departments, extending the department dimension and using it to build custom data security helps ensure good performance and reduce the work required for change management.

If you have any questions, please post them in the Fusion HCM Analytics community forum.



Nupur Joshi

Senior Principal Product Manager, Analytics Apps for HCM

Romesh Lokuge

Principal Member of Technical Staff, Analytics Customer Excellence


Master Principal Sales Consultant

Previous Post

Oracle Analytics Server 2023 is available

Alan Lee | 5 min read

Next Post

Oracle Analytics Best Practices: Understanding Application Roles in Oracle Analytics

Amarpreet Nagra | 6 min read