描述：在客户碰到OA中部门分级有如下情况，部门中人员有挂在非末级部门上，如下图：
商务中心是末级部门，**应用事业部为二级部门。由于父子维度要求人员必须挂到末级部门，故使用父子维度无法处理该情况。
处理方式：
此时需要将各级部门都作为末级部门进行考虑处理。
将一级部门、二级部门、三级部门分别取出，并通过合并的方式，按照一级部门、二级部门、三级部门分别形成末级部门的形式，进而实现这种业务。
在做抽取的时候，需考虑将OA中人员单据根据人员ID关联人员和部门表，将部门ID抽取到事实表中，然后将事实表中的部门ID跟部门分级的末级部门ID关联，即实现按照人员的部门进行业务数据统计。
具体参考SQL如下：
1 --第三级部门 2 select 3 a.SORT_ID,a.path as path, 4 c.id as dim_id,cast(c.name as nvarchar(200)) as dim_name, 5 a.id as dim1_id,cast(a.name as nvarchar(200)) as dim1_name, 6 b.id as dim2_id,cast(b.name as nvarchar(200)) as dim2_name, 7 c.id as dim3_id,cast(c.name as nvarchar(200)) as dim3_name 8 from 9 (select u.path,u.id,u.name,u.type ,u.SORT_ID from org_unit u 10 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a 11 left join 12 ( select u.path,u.id,u.name,u.type,u.SORT_ID from org_unit u 13 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=16 and u.path like '00000002%') b 14 on a.path =left(b.path,12) 15 left join 16 ( select u.path,u.id,u.name,u.type,u.SORT_ID from org_unit u 17 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=20 and u.path like '00000002%') c 18 on b.path=left(c.path,16) 19 where c.id is not null 20 21 union all 22 --第二级部门 23 select 24 b.SORT_ID ,b.path as path, 25 b.id as dim_id,cast(b.name as nvarchar(200)) as dim_name , 26 a.id as dim1_id,cast(a.name as nvarchar(200)) as dim1_name, 27 b.id as dim2_id,cast(b.name as nvarchar(200)) as dim2_name, 28 cast('' as int ) as dim3_id,'' as dim3_name 29 from 30 (select u.path,u.id,u.name,u.type ,u.SORT_ID from org_unit u 31 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a 32 left join 33 ( select u.path,u.id,u.name,u.type,u.SORT_ID from org_unit u 34 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=16 and u.path like '00000002%' ) b 35 on a.path =left(b.path,12) 36 where b.id is not null 37 38 union all 39 --第一级部门 40 select 41 a.SORT_ID ,a.path as path, 42 a.id as dim_id,cast(a.name as nvarchar(200)) as dim_name , 43 a.id as dim1_id,cast(a.name as nvarchar(200)) as dim1_name, 44 cast('' as int ) as dim2_id, '' as dim2_name, 45 cast('' as int ) as dim3_id,'' as dim3_name 46 from 47 (select u.path,u.id,u.name,u.type,u.SORT_ID from org_unit u 48 where u.IS_DELETED =0 and u.is_enable= 1 and len(u.path)=12 and u.path like '00000002%') a 49 where a.id is not null