My team and I are trying to apply AOR security to a very large custom headcount dataset built on Oracle ADW base objects.

What I’ve tried so far:

• Created custom headcount dataset (ADW object)
• Created a custom AOR Dept List dataset. (ADW object)
• Joined headcount to aor on department_id.

Applied the Consumer role with the following expression:

User() = XSA('[my username]'.'Test AOR & BU Permissions_v2#-#FkTBoE0OPJ#-#')."join"."jointable"."USERNAME"

When filtering for a handful of users within the AOR Dept List, the setup works as expected; however, when I remove the user criteria I get the following error on the workbook:

State: HY000. Code: 60008. [nQSError: 60008] The query for user '[my username]' exceeded the maximum query governing rows 2000000 from the database

Questions:

• How can we structure AOR security to handle large datasets without hitting the 2M row query limit?
• Is there a recommended best practice for securing large FDI custom datasets with AOR filtering in Oracle Fusion Analytics Warehouse?

AOR Dept List Code (returns two columns: department user has access to and the username):

SELECT
dd.department_id,
dwup.username

FROM
DW_TREE_VERSION_D ftv,
DW_ORGANIZATION_RF_DH dwaor,
dw_asg_responsibility_d dwar,
dw_user_person_d dwup,
dw_department_d dd
WHERE
1 = 1
AND TO_NUMBER(dwaor.pk1_value) = dd.department_id
AND ftv.tree_structure_code = 'PER_ORG_TREE_STRUCTURE'
AND dwaor.tree_code = '(COMPANY)_ORG_TREE'
AND ftv.status = 'ACTIVE'
AND trunc(sysdate) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
AND ftv.tree_version_id = dwaor.tree_version_id
AND ftv.tree_code = dwaor.tree_code
AND dwar.responsibility_type IN ('HR_REP', 'CH_SRLEAD', 'CH_HRBUS', 'CH_HRBP', 'CH_HRD', 'CH_HRVP')
AND dwar.organization_tree_code = ftv.tree_code
AND dwar.person_id = dwup.person_id
AND sysdate BETWEEN dwar.start_date AND nvl(dwar.end_date, sysdate)
AND TO_NUMBER(dwaor.parent_pk1_value) = nvl(dwar.top_organization_id, TO_NUMBER(dwaor.parent_pk1_value))
AND dwaor.node_distance > decode(dwar.include_top_hier_node, 'N', 0,(dwaor.node_distance - 1))
AND dwaor.node_distance < nvl(decode(dwar.hierarchy_levels, '-99999', '', dwar.hierarchy_levels),
dwaor.node_distance + 1)