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.
Navigation: Fusion Analytics Console -> Data Configuration -> Data Augmentation.
VO used: CrmAnalyticsAM.PartiesAnalyticsAM.FndTreeVersionVO
VO used: HcmTopModelAnalyticsGlobalAM.OrganizationAM.OrgTreeBIRVOForFscm
Navigation: Fusion Analytics Console -> Security -> Application Roles.
Navigation: Fusion Analytics Console -> Security -> Groups.
Note: you can add the Custom AOR Org Hierarchy Data Security application role to other prebuilt and custom groups based on your requirements.
Navigation: Fusion Analytics Console -> Security -> Users.
Navigation: Fusion Analytics Console -> Semantic Model Extensions -> User Extensions
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)
Note: Ensure session variable names are in upper case.
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.
Navigation: Fusion Analytics Console -> Semantic Model Extensions -> Security Configurations.
This example shows three objects to be secured. Add additional objects to meet your requirements.
(("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' ))
Navigation: Fusion Analytics Console -> Semantic Model Extensions -> User Extensions.
Refer to Tag the Main Branch's Steps for guidance.
Refer to Publish the Model for guidance.
Navigation: Fusion Analytics Console -> Data Configuration -> Data Augmentation.
The overall headcount is 2,835, and the business unit has 14.
The overall headcount is 14, and the business unit has 14.
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.
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.
Previous Post