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:

General approach

Here are the steps to creating a loader SQL_GROUP_LIST job:

Person orgs (leaf nodes)

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;
/

* Implement the view of orgs.  Note, in the view you can easily use unions in the sql to end the hierarchy at a different node.  At first we were going to do this, then we decided against it.  However, you will see that we do filter out certain branches and nodes.  Also note we shorten the names of some top level nodes.

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

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;

* This data looks like this

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

* Penn has two types of orgs: orgs that hold people, and orgs that dont (they hold other orgs).  So I will create them separately, here is a view of orgs that hold people, that the loader will use.  Note: these will not be include/exclude since we only need that on the higher level groups (rollups).  Note, each group here should end in _personorg so we can know which groups are managed by this loader process.

/* 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';

Penn organizational hierarchy - part 2