Acknowledgments

With inputs from:

Nupur Joshi, Senior Principal Product Manager.

Introduction

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).

Use Case

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 Access Scenario

Unrestricted users see a total headcount of 3,142.

Excluded Access Scenario

Restricted users must see a total headcount of 692. This count reflects the total minus the manager's headcount.

Excluding Access

The following sections describe how to restrict the access.

Autonomous Data Warehouse

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

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

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;

Fusion Analytics Application

Log into the Fusion Analytics Application.

Security Console

           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.

Create an Application Role

Refer here for guidance.
Specify an Application Role Name, e.g.,  Custom FAW HCM Exclude Manager Data Role V1.

Create a Group

Refer here for guidance.
Specify a Group Name, e.g., Custom FAW HCM Exclude Top Manager v1.

Create a Role Mapping for the Group

Refer here for guidance.

Create a Test User

Specify a name for the user, e.g., HCM User 4.
Refer here for guidance.

Assign the User to the Group

Refer here for guidance.

Semantic Model Extensions

Extend a Dimension

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.

Configure Data Security

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.

Confirming Restricted Access

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

Create a workbook.
Refer here for guidance.

Select the HCM - Workforce Core subject area, then search for and add the Headcount measure.

Confirm Restricted Access

Confirm the restricted headcount, e.g., 692.

Summary

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.