This is Penn's experience implementing the Grouper organization hierarchy. Note, we used to put the fully qualified org name in the group name, but now we have the group name as the org extension without the ancestor path.
This is the size of our org implementation:
Performance:
Here are the steps to creating a loader SQL_GROUP_LIST job:
CREATE OR REPLACE PACKAGE AUTHZADM.authzadm_pkg AS FUNCTION remove_special_chars (the_input varchar2) RETURN varchar2; END authzadm_pkg; / CREATE OR REPLACE PACKAGE BODY AUTHZADM.authzadm_pkg AS FUNCTION remove_special_chars (the_input varchar2) RETURN varchar2 AS the_string varchar2(4000); BEGIN --take out anything not alphanumeric, space, underscore, or dash the_string := REGEXP_REPLACE(the_input, '[Penn organizational hierarchy - part 1^a-zA-Z0-9 _\-]', ''); the_string := trim(the_string); return the_string; END; END authzadm_pkg; / |
CREATE OR REPLACE VIEW ORG_LIST_V (ORG_NAME, ORG_DISPLAY_NAME, ORG_DESCRIPTION, PARENT_ID, PAYROLL_FLAG, CENTER_CODE, CENTER_NAME, center_code_assign) AS select trim(organization_code) org_name, decode(organization_code, 'UNIV', 'Penn', 'NOTU', 'Not Acad', 'TOPU', 'Top', authzadm_pkg.remove_special_chars(nvl(oc.description, oc.ORG_SHORT_NAME))) org_display_name, authzadm_pkg.remove_special_chars(nvl(oc.description, oc.ORG_SHORT_NAME)) org_description, trim(parent_org_code) parent_id, oc.PAYROLL_FLAG, authzadm_pkg.remove_special_chars(oc.CENTER_CODE) center_code, authzadm_pkg.remove_special_chars(oc.CENTER_NAME) center_name, /* if the parent is in the same center, dont list it, only list it if the parent is in a different center */ (select authzadm_pkg.remove_special_chars(oc.CENTER_CODE) from diradmin.dir_org_codes oc2 where oc.PARENT_ORG_CODE = oc2.ORGANIZATION_CODE and oc.CENTER_CODE != oc2.CENTER_CODE ) as center_code_assign from diradmin.dir_org_codes oc where oc.ENABLED = 'Y' and oc.organization_code not in ( 'DEAD', 'BUD5', 'RADA', 'T', 'CAOP') and oc.PARENT_ORG_CODE not in ( 'DEAD', 'BUD5', 'RADA', 'T', 'CAOP') and oc.description is not null |
ORG_NAME ORG_DISPLAY_NAME ORG_DESCRIPTION PARENT_ID PAYROLL_FLAG CENTER_CODE CENTER_NAME 78YY ACP Other Parent ACP Other Parent 78XX N 78 Audit Compliance and Privacy 9985 AG-Center for School Study Councils AG-Center for School Study Councils AG32 N 99 External Organizations (Agency Funds) 4602 AG-Institute on Aging AG-Institute on Aging IAGE Y 40 School of Medicine IAGE AG-Institute on Aging Parent AG-Institute on Aging Parent SOMI N 40 School of Medicine |
CREATE OR REPLACE VIEW ORG_ASSIGN_V (ORG_CODE, PENN_ID) AS Select distinct p.job_org_code org_code, a.v_penn_id penn_id from comadmin.ssn4_affiliation_view a, comadmin.pennpay_appointment_v p Where a.v_penn_id = p.penn_id And a.v_source = 'PENNPAY' and a.V_ACTIVE_CODE = 'A' and p.JOB_ACTIVE_CODE = 'A' |
ORG_CODE PENN_ID 0007 12345678 8105 12345679 |
CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_META_V ( GROUP_NAME, GROUP_DISPLAY_NAME, READERS, VIEWERS, ORG_ID ) AS SELECT DISTINCT 'penn:community:employee:org:' || OLV.ORG_NAME || ':' || OLV.ORG_NAME || '_personorg' AS group_name, 'penn:community:employee:org:' || OLV.ORG_NAME || ' - ' || olv.ORG_DISPLAY_NAME || ':' || OLV.ORG_NAME || ' - ' || olv.ORG_DISPLAY_NAME AS group_display_name, 'penn:community:employee:orgSecurity:orgReaders' AS readers, 'penn:community:employee:orgSecurity:orgViewers' AS viewers, OLV.ORG_NAME AS org_id FROM org_list_v olv WHERE olv.PAYROLL_FLAG = 'Y' ORDER BY 2; |
penn:community:employee:org:0001:0001_personorg penn:community:employee:org:0001 - General University:0001 - General University penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0001 penn:community:employee:org:0007:0007_personorg penn:community:employee:org:0007 - General University EB Pool:0007 - General University EB Pool penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0007 penn:community:employee:org:0030:0030_personorg penn:community:employee:org:0030 - Learning Alliance for Higher Education LLC:0030 - Learning Alliance for Higher Education LLC penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0030 penn:community:employee:org:0101:0101_personorg penn:community:employee:org:0101 - Anthropology:0101 - Anthropology penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0101 penn:community:employee:org:0102:0102_personorg penn:community:employee:org:0102 - Asian and Middle Eastern Studies:0102 - Asian and Middle Eastern Studies penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0102 |
/* Formatted on 3/10/2013 12:47:28 PM (QP5 v5.163.1008.3004) */ CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_V ( GROUP_NAME, SUBJECT_ID ) AS SELECT DISTINCT olpmv.GROUP_NAME, oav.PENN_ID AS subject_id FROM ORG_LOADER_PERSON_META_V olpmv, org_assign_v oav WHERE olpmv.org_id = oav.ORG_CODE; COMMENT ON TABLE ORG_LOADER_PERSON_V IS 'view which the loader uses to load people to payroll orgs'; |
[appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% GSH_DEBUG=true true gsh 1% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 9702ff7015a84c019ae58ce6ae950115,'GrouperSystem','application' gsh 2% stem = StemFinder.findByName(grouperSession, "penn:community:employee"); stem: name='penn:community:employee' displayName='penn:community:employee' uuid='3cb63130-03e1-4b60-8f01-1454ee3c9588' gsh 4% group = addGroup("penn:community:employee", "orgConfig", "orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 5% groupAddType("penn:community:employee:orgConfig", "grouperLoader"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderDbName", "grouper"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuartzCron", "0 46 6 * * ? "); true gsh 8% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v where 1=0"); true gsh 9% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderScheduleType", "CRON"); true gsh 10% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true |
[appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 6e1432f7de314aeca2f927f939f1a5be,'GrouperSystem','application' gsh 1% group = GroupFinder.findByName(grouperSession, "penn:community:employee:orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 2% loaderRunOneJob(group); loader ran successfully, inserted 0 memberships, deleted 0 memberships, total membership count: 0 |
GROUP_NAME SUBJECT_ID penn:community:employee:org:TOPU:UNIV:USCH:51XX:DPDN:5188:5188_personorg 12345678 penn:community:employee:org:TOPU:UNIV:USTU:85XX:CRSC:ASPP:8508:8508_personorg 12345679 penn:community:employee:org:TOPU:UNIV:USCH:36XX:APPC:3604:3604_personorg 12345680 penn:community:employee:org:TOPU:UNIV:USCH:02XX:GRAD:GRAO:0315:0315_personorg 12345681 |
gsh 5% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupQuery", "select olpmv.GROUP_NAME as group_name, olpmv.GROUP_DISPLAY_NAME as group_display_name, olpmv.READERS, olpmv.VIEWERS, olpmv.ORG_ID from ORG_LOADER_PERSON_META_V olpmv"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupsLike", "penn:community:employee:org:%_personorg"); true gsh 8% loaderRunOneJob(group); |
Penn organizational hierarchy - part 2