понедельник, 19 декабря 2011 г.

Выгрузка данных по измерениям 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


Комментариев нет:

Отправить комментарий