Выгрузка данных по измерениям Hyperion EPMA из Oracle RDBMS

1. Подключиться к схеме данных приложения EPMA.
2. Выполнить запрос:
   

    SELECT TYPE_NAME,lpad(' ',2*(level-1))|| member_code AS member_code,  
        alias_default,  
        operation,  
        generation,  
        data_storage,  
        uda_text AS uda,  
        formula,  
        smart_list_name,  
        smart_list_label  
    FROM  
     (SELECT ob_type.TYPE_NAME ,  
      obj.object_name member_code,  
      al.object_name alias_default,  
      obj.generation,  
      obj.parent_id,  
      obj_par.object_name parent_member,  
      (CASE  
       WHEN m.consol_op1=0 THEN '+'  
       WHEN m.consol_op1=1 THEN '-'  
       WHEN m.consol_op1=2 THEN '*'  
       WHEN m.consol_op1=3 THEN '/'  
       WHEN m.consol_op1=4 THEN '%'  
       WHEN m.consol_op1=5 THEN '~'  
       WHEN m.consol_op1=6 THEN '^'  
       ELSE 'Undefined'  
      END) AS operation,  
      (CASE  
       WHEN m.data_storage=0 THEN 'Store data'  
       WHEN m.data_storage=1 THEN 'Never share'  
       WHEN m.data_storage=2 THEN 'Label Only'  
       WHEN m.data_storage=3 THEN 'Shared member'  
       WHEN m.data_storage=4 THEN 'DynamicCalc and Store'  
       WHEN m.data_storage=5 THEN 'Dynamic'  
       ELSE 'Undefined'  
      END) data_storage,  
      uda.uda_text,  
      en.name AS smart_list_name,  
      en.label AS smart_list_label,  
      mf.formula  
     FROM hsp_object obj,    
       hsp_object obj_par,   
       hsp_object_type ob_type,  
       (SELECT obj_al.object_name,al.member_id  
        FROM hsp_alias al, hsp_object obj_def,
             hsp_object obj_al, hsp_object_type ob_al_type  
        WHERE al.aliastbl_id = obj_def.object_id(+) 
         AND al.alias_id = obj_al.object_id(+) 
         AND obj_def.object_name = 'Default' 
         AND obj_al.object_type = ob_al_type.object_type 
         AND ob_al_type.type_name = 'Alias'  
       ) al,  
       hsp_member m,  
       hsp_member_formula mf,  
       hsp_enumeration en,  
       (SELECT uda.member_id,sys_xmlagg(xmlelement(col, ud.uda_value||' ')).extract('/ROWSET/COL/text()').getclobval() AS uda_text  
       FROM hsp_member_to_uda uda, hsp_uda ud  
       WHERE uda.uda_id=ud.uda_id  
       GROUP BY uda.member_id  
       ) uda  
     WHERE obj.parent_id = obj_par.object_id(+)   
      AND obj.object_type = obj_par.object_type(+)   
      AND obj.object_type = ob_type.object_type   
      AND ob_type.TYPE_NAME IN ('Scenario','Year','Time Period','Version','Entity','Account','User Defined Dimension Member' )   
      AND al.member_id(+) = obj.object_id   
      AND m.member_id=obj.object_id   
      AND mf.member_id(+)=obj.object_id   
      AND uda.member_id(+)=obj.object_id   
      AND m.enumeration_id=en.enumeration_id(+)  
     ORDER BY ob_type.TYPE_NAME,obj.generation)  
     START WITH parent_member  IS NULL  
     CONNECT BY prior member_code = parent_member;  
   

3. По результату данного запроса сделать подзапрос по колонке TYPE_NAME (соответствует типу измерения). Варианты:
   
   • Account
   • Entity
   • Version
   • Time Period
   • Year
   • Scenario
   • User Defined Dimension Member