19 июня 2008

Граф зависимости показателей

Так получилось, что наш куб достаточно сложный и стандартный CALC ALL дает неверные результаты в силу своей двупроходности. Поэтому нам приходится писать бизнес-правила для пересчета либо отдельных частей, либо всего куба. Для этого очень удобно иметь картинку с зависимостями статей.

Когда я писал правило по расчету продаж, то честно прошерстил весь соответствующий кусок outline'а и нарисовал граф зависимостей на бумажке. Это заняло достаточно много времени, туда затесалось несколько ошибок и в результате я захотел скрипт который бы сам рисовал такие графы.

И я написал такой скрипт. Только стоит учесть, что поможет он только тем, кто работает с Hyperion Planning - просто у Planning очень удобное и понятное реляционное хранилище метаданных.

Для работы скрипта понадобится установленный graphviz и SQL*Plus.

account_desc.bat:

set NLS_LANG=RUSSIAN_CIS.AL32UTF8

sqlplus login/password@your_DBSID @account_desc.sql

dot -Tgif account_desc.txt -o account_desc.gif


account_desc.sql:

set linesize 200
set colsep ""
set pagesize 0
set newpage none
set feedback off
set termout off
set recsep off
set sqlprompt ""
set heading off
set echo off
set serveroutput on size 10000

spool account_desc.txt

drop table hsp_account_dependence;

create table hsp_account_dependence (
parent_account varchar2(80 byte),
child_account varchar2(80 byte)
);

declare
begin
/* Dependence by formula*/
for i in (select object_name from hsp_account, hsp_object where account_id = object_id) loop
insert into hsp_account_dependence (parent_account, child_account)
select object_name, i.object_name
from hsp_object, hsp_member_formula
where formula like '%'||i.object_name||'%'
and member_id = object_id;
end loop;
commit;
/* Dependence by outline*/
insert into hsp_account_dependence (parent_account, child_account)
select o2.object_name, o1.object_name
from hsp_object o1, hsp_object o2, hsp_member m1, hsp_member m2
where o1.object_id = m1.member_id
and o2.object_id = o1.parent_id
and m1.consol_op1 in (0, 1, 2, 3, 4)
and o1.parent_id = m2.member_id
and m2.data_storage in (1, 4, 5, 6)
and exists (select 1 from hsp_account where account_id = o1.object_id);
commit;
end;
/

begin
dbms_output.enable;
dbms_output.put_line('digraph G { node [shape=record, fixedsize=false, fontsize=6, regular=true]; nodesep=.5;');
end;
/

select o.object_name||' [label="'||o.object_name||'\n'||o2.object_name||'"];'
from hsp_account a, hsp_object o, hsp_alias al, hsp_object o2, hsp_member m1
where a.account_id = o.object_id
and al.member_id = a.account_id
and al.aliastbl_id = 14
and al.alias_id = o2.object_id
and m1.member_id = o.object_id
and m1.DATA_STORAGE in (1, 4, 5, 6)
and o.object_name like '6103%'
order by o.object_name;

select parent_account||'->'||child_account||';'
from hsp_account_dependence
where parent_account like '6103%'
and child_account like '6103%'
order by parent_account;

begin
dbms_output.enable;
dbms_output.put_line('}');
end;
/

spool off
exit


Сразу замечу, что sql-скрипт можно улучшать: завести переменные для like-ов (для того чтобы отсекать нужные элементы), проверять на наличие таблицы перед тем как ее грохать, не генерить ребра для всех показателей, но это мелочи.

в результате получается вот такой вот граф:


На этом автоматизм заканчивается. Дальше можно ручками редактировать account_desc.txt для того чтобы граф строился так, как вам удобнее.

5 комментариев:

  1. Спасибо за статью.
    Для полноты картины не хватает парсера для BR



    ER.

    ОтветитьУдалить
  2. Отличная вещь!

    ОтветитьУдалить
  3. Двойной расчет можно отключить на свойсвах куба :)

    essbase.ru

    ОтветитьУдалить
  4. Евгений, проблема не в наличии или отсутствия двойного расчета, а в том, что некоторые статьи надо по кругу считать 12 раз дабы получить правильный результат:-)

    ОтветитьУдалить
  5. я пробовaл решить задачку циклических зависимостей через dcalc - все хорошо на стенде - на проде кубу сносит крышу и приходиться лепить loop в правило :(

    кста fix (по периодам) переберет статьи ровно 12 раз :))

    ОтветитьУдалить