With inputs from:
Nupur Joshi, Senior Principal Product Manager.
This article describes how to restrict access to data in a Human Capital Management (HCM) manager hierarchy using the security extensions available in Oracle Fusion Analytics Warehouse (Fusion Analytics).
A user or group of users must be restricted from accessing the data of a specific manager and anyone in the manager's hierarchy.
The examples in this article use 2022 as the year and a manager with a headcount of 2,450 who is assigned PERSON_NUMBER 279 in the Oracle Human Capital Management (HCM) application.
Unrestricted users see a total headcount of 3,142.
Restricted users must see a total headcount of 692. This count reflects the total minus the manager's headcount.
The following sections describe how to restrict the access.
Sign in to the Fusion Analytics autonomous data warehouse. The example uses the SQL*Developer utility.
Refer here and here for guidance.
Create a custom view named EXTEND_WORKER_DIM in the OAX_USER schema. The statement below creates the view:
CREATE VIEW OAX_USER.EXTENDED_WORKER_DIM AS
(
SELECT DISTINCT
A.PERSON_ID AS EXT_PERSON_ID
FROM
DW_PERSON_LEGISLATION_ALL_D A
WHERE
PERSON_ID NOT IN (
SELECT
EXT_PERSON_ID
FROM
(
SELECT DISTINCT
MDD.PERSON_ID AS EXT_PERSON_ID
FROM
DW_PERSON_LEGISLATION_ALL_D PLA, DW_MANAGER_DN_DH MDD
WHERE
SYSDATE BETWEEN MDD.EFFECTIVE_START_DATE AND MDD.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLA.DATE_FROM AND PLA.DATE_TO
AND PLA.PERSON_NUMBER = '279'
AND ( MDD.PERSON_ID = PLA.PERSON_ID
OR MDD.MANAGER_ID = PLA.PERSON_ID)
)
)
AND SYSDATE BETWEEN A.DATE_FROM AND A.DATE_TO
AND A.PERSON_NUMBER <> '279'
);
Grant the SELECT privilege to the OAX$OAC schema. The statement below grants the privilege:
GRANT SELECT ON OAX_USER.EXTENDED_WORKER_DIM TO OAX$OAC;
Log into the Fusion Analytics Application.
Create a new application role, group, and test user. Map the application role to the group and assign the group to the user.
Navigate via the home page menu to Console.
Click Security.
Refer here for guidance.
Specify an Application Role Name, e.g., Custom FAW HCM Exclude Manager Data Role V1.
Refer here for guidance.
Specify a Group Name, e.g., Custom FAW HCM Exclude Top Manager v1.
Refer here for guidance.
Specify a name for the user, e.g., HCM User 4.
Refer here for guidance.
Refer here for guidance.
Create a Branch
Click the back arrow on the Security page to return to the Console.
Click Semantic Model Extensions.
Click User Extensions.
Click Create Branch.
Enter a Name, e.g., HCM Exclusion Branch.
Click Done.
Create a Branch Step
Click Add Step.
Click Extend a Dimension.
Enter Extend Worker Dimension as the Step Name.
Select HCM - Workforce Core as the Target Subject Area.
Select Basic Information as the Folder.
Select Dim - Worker as the Logical Table.
Click Next.
Select the OAX_USER user as the Schema.
Select the EXTENDED_WORKER_DIM custom view as the Object.
Click Add Attributes.
Select EXT_PERSON_ID as the Source Column.
Check the Add Attributes and Use for Key boxes.
Click Next.
Select Person Identifier (NUMERIC) as the Dimension Key.
Select Left Outer as the Select Join Type.
Click Next.
Click Yes to acknowledge the different data types and continue.
Click Next.
Click Finish.
Merge the Branch
Wait for the branch state to show Applied.
Click the ellipse (...) on the top right of the window and click Merge to Main Branch. Note: do not use the ellipse on the new step row.
Click Activity.
Wait for the Merge to Main activity State to show Done.
Create a Branch Tag
Click Semantic Model Components.
Click User Extensions.
Click the Main branch.
Enter Extend Worker Dimension into Search Steps.
Hover on the far right side of the row and click the ellipse (...) and Tag.
Enter a Name, e.g., HCM_UseCase_V2.
Click Done.
Publish the Model
Click the back arrow and return to the Semantic Model Extensions screen.
Click Publish Model.
Select the branch having the new tag from the User Extensions list.
Accept the remaining defaults and click Publish.
Click Activity on the Semantic Model Extensions screen.
Wait for the Publish Customizations activity state to show Done.
Create a Data Security Step
Click Semantic Model Components.
Click Security Configurations.
Click Data Security Step.
Enter the Custom Application Role name into the Customization Step Name, e.g., Custom HCM exclude Manager Use Case.
Select the Custom Application Role from the Application Role dropdown.
Click Next.
Select Presentation Objects from the Available Objects dropdown.
Drag Dim-Manager, Dim-Worker, and Dim-Manager Hierarchy to Objects to be Secured.
For the Dim-Worker object:
Click the expression editor (fx).
Expand the Dim-Worker folder.
Double-Click the Person Identifier column to place it in the expression.
Expand the Operators under Functions and double-click the equal (=) operator.
Double-Click the Ext Person ID column to place it in the expression.
Click Validate.
Click Save.
Copy the Dim-Worker Filter Argument into your clipboard.
For the remaining objects:
Click the expression editor (fx).
Paste the Dim-Worker Filter Argument into the expression.
Click Save.
Click Finish and OK.
Click Activity.
Wait until the Add Data Security Step state shows Done.
Publish the Model
Click Semantic Model Components.
Click Publish Model.
Select the branch having the new tag from the User Extensions list.
Accept the remaining defaults and click Publish.
Click Activity.
Wait until the Publish Customizations state shows Done.
From the home page, navigate to Projects. This opens Oracle Analytics Cloud in a new window or tab.
Click the User icon and then click Sign Out. Sign in as the test user created above.
Create a workbook.
Refer here for guidance.
Select the HCM - Workforce Core subject area, then search for and add the Headcount measure.
Confirm the restricted headcount, e.g., 692.
This article described how to restrict access to data in a Human Content Management (HCM) manager hierarchy using the security extensions available in Fusion Analytics.
For more information, see Administering Oracle Fusion Analytics Warehouse.
To learn more about Fusion Analytics Warehouse, visit oracle.com/analytics, follow us on twitter@OracleAnalytics, and connect with us on LinkedIn.
Previous Post
Next Post