This blog is part of a series of articles on setting up custom security in Fusion HCM Analytics using Area of Responsibility (AOR) data from Oracle Fusion Cloud HCM.  It describes setting up custom data security in Oracle Fusion HCM Analytics using Organization Hierarchy AOR data extracted from Cloud HCM. 

The Oracle Fusion HCM Analytics data pipeline can extract Area of Responsibility (AOR) data from Oracle Fusion Cloud HCM and load it into the associated Oracle Autonomous Data Warehouse (ADW). Fusion Analytics security administrators can then utilize AOR data to set up custom security following the steps below.

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

  1. Data Configuration
    1. Create the Data Augmentation Jobs
  2. Security Extensions 
    1. Create a Custom Data Application Role in Fusion Analytics
    2. Create a Custom Group
    3. Add the User to the Custom Group
    4. Add the User to the Licensed Group
    5. View the User Group Memberships
  3. Semantic Model Extensions
    1. Create a Branch
    2. Add Session Variables
    3. Merge the Branch with the Main Branch
    4. Configure the Data Security Step
    5.  Publish the Model
  4. Validations
    1. Validate Data Augmentations
    2.  Validate Organization Hierarchy AOR Data Security Setup

 

  1. Data Configuration
  1. Create the Data Augmentation Jobs

Navigation: Fusion Analytics Console -> Data Configuration -> Data Augmentation.

  1. Create two augmentation jobs for extracting hierarchy tree versions and row-flattened organization hierarchy data.  
    Note: with an upcoming Fusion Analytics Warehouse release, both View Objects (VOs) mentioned below will be available by enabling the Fusion HCM Security Configuration functional area, after which you can refer to the corresponding tables in the steps to create session variables and skip additional augmentation.
  1. Data Augmentation for Extracting Hierarchy Tree Versions

VO used: CrmAnalyticsAM.PartiesAnalyticsAM.FndTreeVersionVO 

  1. Select the Create Dimension Augmentation Type.
  2. Select the Supplemental Data Source Dataset Type.
  3. Select the Human Capital Management Pillar.
  4. Select the Customer Provided Source Table Type.
  5. Enter the Source Table, i.e., CrmAnalyticsAM.PartiesAnalyticsAM.FndTreeVersionVO.
  6. Click Next.
  7. Search and select the following attributes:
    1. Status
    2. EffectiveStartDate
    3. EffectiveEndDate
  8. Accept the default for Incremental Key.
  9. Click Next.
    DA3
  10. Accept the default for the Extract date, then click Next. 
  11. Enter a Name, e.g., Hierarchy Tree Versions.
  12. Enter a Description.
  13. Enter a Table Suffix, e.g., HIER_TREE_VERSION.
  14. Leave the Subject Areas blank.
  15. Select Run Now for the Schedule.
  16. Click Finish.
  1. Data Augmentation for Extracting Row-Flattened Organization Hierarchy Data

VO used: HcmTopModelAnalyticsGlobalAM.OrganizationAM.OrgTreeBIRVOForFscm

  1. Select the Create Dimension Augmentation Type.
  2. Select the Supplemental Data Source Dataset Type.
  3. Select the Human Capital Management Pillar.
  4. Select the Customer Provided Source Table Type.
  5. Enter the Source Table, i.e., HcmTopModelAnalyticsGlobalAM.OrganizationAM.OrgTreeBIRVOForFscm.
  6. Click Next.
  7. Search and select the following attributes:
  • AncestorPk1Value
  • Pk1Value
  • Distance
  • FndTreeVersionEffDateEOLastUpdateDate
  • FndTreeVersionEffDateEOLastValidationDate
  1. Accept the default for Incremental Key.
  2. Ensure FndTreeVersionEffDateEOLastUpdateDate and FndTreeVersionEffDateEOLastValidationDate columns are selected for the Extract date.
    DA10
  3. Click Next.
  4. Enter a Name, e.g., RF Org Hierarchy Data.
  5. Enter a Description.
  6. Enter a Table Suffix, e.g., RF_ORG_HIER_DATA.
  7. Leave the Subject Areas blank.
  8. Select Run Now for the Schedule.
  9. Click Finish.

​

  1. Security Extensions 
    1. Create a Custom Data Application Role in Fusion Analytics

Navigation: Fusion Analytics Console -> Security -> Application Roles.

  1. Click New Application Role.
  2. Enter an Application Role Name, i.e., Custom AOR Org Hierarchy Data Security.
  3. Enter a Description, e.g., Setting up AOR Org Hierarchy Data Security.
  4. Select the Manage row-level data access (Data Role) Role Type.
  5. Click Save.
    CS1
    1. Create a Custom Group

Navigation: Fusion Analytics Console -> Security -> Groups.

  1. Click New Group.
  2. Enter a Group Name, e.g., Custom AOR Organization Hierarchy Data Security Group.
  3. Enter a Description
  4. Click Save.
     
    1. Assign the Custom Data Application Role to a Group
  6. Search using Custom AOR and select the Custom AOR Organization Hierarchy Data Security Group.
  7. Click Application Roles.
  8. Click Add Mapping.
  9. Search for and select the Custom AOR Org Hierarchy Data Security application role.
  10. Search for and select the required duty role, e.g., Workforce Core Analysis Duty application role.
  11. Click Save.
    CS2

​

Note: you can add the Custom AOR Org Hierarchy Data Security application role to other prebuilt and custom groups based on your requirements.

    1. Add the User to the Custom Group
  2. Click Users.
  3. Click Assign Users.
  4. Search the Users and select Suzanne.Martha.
  5. Click Assign.

    1. Add the User to the License Group
  2. Search the Groups and select FAW Licensed HCM Authors. (Only if you want users to be able to create their own content, otherwise you can also assign users to FAW Licensed HCM Consumers too.)
  3. Click Users.
  4. Search the Users for Suzanne.Martha.
  5. If the user is not found, Click Assign Users.
  6. Search the Users and select Suzanne.Martha. Click Assign.

 

    1. View the User Group Memberships

Navigation: Fusion Analytics Console -> Security -> Users.

  1. Search the Users and select Suzanne.Martha.
  2. View the Groups.
    CS4

3. Semantic Model Extensions

 a. Create a Branch

Navigation: Fusion Analytics Console -> Semantic Model Extensions -> User Extensions

  1. Click Create Branch.
  2. Enter a Name, e.g., HCM Org Hierarchy Data Security Session Variable.
  3. Enter a Description, e.g., Setup for AOR org hierarchy data security.
  4. Click Done.​
  1. Add Session Variables
  1. Click Add Session Variables in the Add Step popup window.
  2. Enter a Step Name, e.g., HCM AOR Org Hierarchy Session Variables.
  3. Enter a Description. 
  4. Click Next. 
  5. Enter an Initialization Block Name, e.g., HCM_AOR_ORG_HIERARCHY.
  6. Enter a Description.
  7. Enter the SQL Query

Note: Modify the values for TREECODE and RESPONSIBILITY_TYPE in the SQL query as needed.

SELECT
    'AORDEPTLIST',
    dd.department_id
FROM
    dw_fa_x_hier_tree_version ftv,
    dw_fa_x_rf_org_hier_data  dwaor,
    dw_asg_responsibility_d   dwar,
    dw_user_person_d          dwup,
    dw_department_d           dd
WHERE
        1 = 1
    AND TO_NUMBER(dwaor.pk1value) = dd.department_id
    AND ftv.treestructurecode = 'PER_ORG_TREE_STRUCTURE'
    AND dwaor.treecode = 'PRJORGHRY'
    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'
    AND dwar.organization_tree_code = ftv.treecode
    AND dwar.person_id = dwup.person_id
    AND upper(dwup.username) = upper(':USER')
    AND sysdate BETWEEN dwar.start_date AND nvl(dwar.end_date, sysdate)
    AND TO_NUMBER(dwaor.ancestorpk1value) = nvl(dwar.top_organization_id,
                                                TO_NUMBER(dwaor.ancestorpk1value))
    AND dwaor.distance > decode(dwar.include_top_hier_node, 'N', 0,(dwaor.distance - 1))
    AND dwaor.distance < nvl(decode(dwar.hierarchy_levels, '-99999', '', dwar.hierarchy_levels),
                             dwaor.distance + 1)
UNION ALL
SELECT
    'AORBULIST',
    dbu.business_unit_id
FROM
    dw_fa_x_hier_tree_version ftv,
    dw_fa_x_rf_org_hier_data  dwaor,
    dw_asg_responsibility_d   dwar,
    dw_user_person_d          dwup,
    dw_business_unit_d        dbu
WHERE
        1 = 1
    AND TO_NUMBER(dwaor.pk1value) = dbu.business_unit_id
    AND ftv.treestructurecode = 'PER_ORG_TREE_STRUCTURE'
    AND dwaor.treecode = 'PRJORGHRY'
    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'
    AND dwar.organization_tree_code = ftv.treecode
    AND dwar.person_id = dwup.person_id
    AND upper(dwup.username) = upper(':USER')
    AND sysdate BETWEEN dwar.start_date AND nvl(dwar.end_date, sysdate)
    AND TO_NUMBER(dwaor.ancestorpk1value) = nvl(dwar.top_organization_id,
                                                TO_NUMBER(dwaor.ancestorpk1value))
    AND dwaor.distance > decode(dwar.include_top_hier_node, 'N', 0,(dwaor.distance - 1))
    AND dwaor.distance < nvl(decode(dwar.hierarchy_levels, '-99999', '', dwar.hierarchy_levels),
                             dwaor.distance + 1)
UNION ALL
SELECT
    'AORLELIST',
    led.legal_employer_id
FROM
    dw_fa_x_hier_tree_version ftv,
    dw_fa_x_rf_org_hier_data  dwaor,
    dw_asg_responsibility_d   dwar,
    dw_user_person_d          dwup,
    dw_legal_employer_d       led
WHERE
        1 = 1
    AND TO_NUMBER(dwaor.pk1value) = led.legal_employer_id
    AND ftv.treestructurecode = 'PER_ORG_TREE_STRUCTURE'
    AND dwaor.treecode = 'PRJORGHRY'
    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'
    AND dwar.organization_tree_code = ftv.treecode
    AND dwar.person_id = dwup.person_id
    AND upper(dwup.username) = upper(':USER')
    AND sysdate BETWEEN dwar.start_date AND nvl(dwar.end_date, sysdate)
    AND TO_NUMBER(dwaor.ancestorpk1value) = nvl(dwar.top_organization_id,
                                                TO_NUMBER(dwaor.ancestorpk1value))
    AND dwaor.distance > decode(dwar.include_top_hier_node, 'N', 0,(dwaor.distance - 1))
    AND dwaor.distance < nvl(decode(dwar.hierarchy_levels, '-99999', '', dwar.hierarchy_levels),
                             dwaor.distance + 1)
  1. Click Next
    cs11

  1. Enter three Variables:
    1. AORDEPTLIST
    2. AORBULIST
    3. AORLELIST
  2. Check Row-wise Initialization.
  3. Enter Descriptions.
  4. Enter Default Values, e.g., -1.
    CS8
  5. Click Finish.

 

Note: Ensure session variable names are in upper case.

  1. Merge the Branch with the Main Branch

Merge the HCM Org Hierarchy Data Security Session Variable branch with the main branch. Refer to Merge the Customization Branches with the Main Branch for guidance.

  1. Configure the Data Security Step

Navigation: Fusion Analytics Console -> Semantic Model Extensions -> Security Configurations.

  1. Click Data Security Step.
  2. Enter a Customization Step Name, e.g., HCM AOR Org Hierarchy Based Data Security Setup.
  3. Enter a Step Description.
  4. Select Custom AOR Org Hierarchy Data Security from the Application Role drop-down.
    CS9

​This example shows three objects to be secured. Add additional objects to meet your requirements.

  1. Select Logical Objects from the Available Objects drop-down.
  2. Drag and drop the following Available Objects from the left pane to the Object to be secured.
    1. Fact – Assignment
    2. Fact – Assignment Event
    3. Dim – Worker
  3. Use the following for all Filter Argument Expressions and click Save.
(("Core"."Dim - Department"."Department Identifier"=VALUEOF(NQ_SESSION.AORDEPTLIST) and "Core"."Dim - Organization Hierarchy"."Tree Code"='PRJORGHRY')

OR ("Core"."Dim - HCM - HR Business Unit"."Business Unit Identifier"=VALUEOF(NQ_SESSION.AORBULIST) and  "Core"."Dim - Organization Hierarchy"."Tree Code"='PRJORGHRY')

OR ("Core"."Dim - Legal Employer"."Legal Employer Identifier"=VALUEOF(NQ_SESSION.AORLELIST)  and  "Core"."Dim - Organization Hierarchy"."Tree Code"='PRJORGHRY' ))
  1. Click Finish.
    ASV

 

  1. Publish the Model
    Create a Tag 

Navigation: Fusion Analytics Console -> Semantic Model Extensions -> User Extensions.

Refer to Tag the Main Branch’s Steps for guidance.

  1. Click Create Tag.
  2. Enter a Name, e.g., HCMAOR_OrgHier.
  3. Enter a Description.
  4. Select HCM AOR Org Hierarchy Session Variables from the Step drop-down.
  5. Click Done.
  6. Publish 

Refer to Publish the Model for guidance.

  1. Select Main (HCMAOR_OrgHier) from the User Extensions drop-down list.
  2. Accept the default All for Security Configurations.
  3. Click Publish.
    ​
  4. Validations
    1. Validate Data Augmentations

Navigation: Fusion Analytics Console -> Data Configuration -> Data Augmentation.

 

  1. Verify the data augmentation job is successfully activated:
    • Pipeline Status shows Activation Complete.
    • Semantic Model Status shows Skipped.
  1. Verify the warehouse tables DW_FA_X_HIER_TREE_VERSION and DW_FA_X_RF_ORG_HIER_DATA created by the augmentation jobs are populated. 
  2. Connect to the OAX_USER schema of the ADW instance associated with the FAW and execute the following queries.
    SQL2

    SQL1

    1. Organization Hierarchy AOR Data Security Validation
  2. Have a user with a membership in the HCM View All Data Security application role or the Fusion Analytics Service Administrator group log into Fusion Analytics and run a Data Visualization (DV) showing the Overall Headcount and the Headcount Under Specific Node AU Content Business Unit associated with AOR.

The overall headcount is 2,835, and the business unit has 14.
              DV1

            

  1. Have user Suzanne.Martha log in and run the same DV. The AOR data security is applied.

The overall headcount is 14, and the business unit has 14.
DV2

​

When user Suzanne.Martha logs in to FAW and runs the DV, the data filter expression used in the data security configuration step is applied in the generated physical SQL query.
SQL
​

Conclusion

This blog describes setting up custom data security in Fusion HCM Analytics using Organization Hierarchy AOR data extracted from Fusion Cloud HCM. Currently automated data security synchronization between Oracle Cloud HCM and Fusion HCM Analytics is not available. However, with AOR data in Fusion Analytics, custom data security provides business users with a nearly seamless experience.

For more information about Oracle Fusion HCM Analytics, check out the Help Center Documentation.