Dynamic Calc Members (BSO)

Когда же лучше всего использовать динамические элементы в BSO-кубах?


1. Плотные (dense) измерения

Использовать нужно всегда т.к. это ведет к уменьшению размера блока => размер куба будет меньше => производительность расчетов улучшится, производительность извлечения данных (retrieve) меняется незначительно +/- 1%! Необходимо только избегать динамических элементов с комплексными формулами т.е. когда при расчете формулы необходимо смотреть в другие блоки. Кандидаты на изменения типа храния на динамический - родители или любой элемент с простой формулой.

Примеры (Период и Статья - плотные, Страна - разряженные):

• Кварталы (Q1, Q2, Q3, Q4)  - можно сделать динамическими

• (+) Acc_Total: Выручка итого
      (+) Acc_01: Выручка A
      (+) Acc_02: Выручка Б
  Статью Acc_Total можно сделать динамической

• Статья: Prof_01 = Acc_01 / Acc_Total - можно сделать динамической (расчет в пределах одного блока)
   
• Статья: Prof_Russia =  Acc_Total->Entity_Russia - нужно делать хранимой т.к. при распаковке любого блока (расчете или просмотре) Essbase будет смотреть на блок по стране Entity_Russia

2. Разряженные (sparse) измерения

Использовать  можно только для уменьшения времени агрегации, при этом ухудшается время извлечения данных (retrieve).

Пример:

• (+) Entity_Russia - dynamic
      (+) Entity_Msc - store
      (+) Entity_Spb - store
  Если пользователь строит запрос по Entity_Russia, то серверу необходимо прочитать 2 дополнительных блока по Msc и Spb

Когда не нужно делать динамическими плотные элементы:

• Когда элемент участвует в выгрузках (report script, dataexport)
• Когда элемент участвует в партициях
• Когда элемент участвует в создании блоков (datacopy с динамических элементов не работает)
• Когда на элементе хранят расчетные данные (аллокации, элиминации)

3. Выводы

Динамические элементы - это хорошая возможность улучшить производительность, но конечно же со своей спецификой.

История одной оптимизации

Часть 1. Как было настроено до оптимизации

Настройки Essbase.cfg

...
CALCCACHEHIGH    200000000 
CALCCACHEDEFAULT 50000000 
CALCCACHELOW     10000000
...

Порядок измерений (песочные часы):

Dimension                    Type    Declared Size  Actual Size
=============================================
Account                          DENSE   809            785
Period                             DENSE   20               18
Currency Rates               SPARSE  6                  5
Scenario                         SPARSE  7                  6
SparseDimA                   SPARSE  8                  6
Years                             SPARSE  8                  7
Currency                        SPARSE  10                8
Version                          SPARSE  15               15
SparseDimB                  SPARSE  17                13
SparseDimC                  SPARSE  125            122
SparseDimD                  SPARSE  125            124
Entity                             SPARSE  198            196
SparseDimE                  SPARSE  199            197
SparseDimF                  SPARSE  288            144

Скрипт агрегации:

SET CACHE ALL;
SET CACHE HIGH;
SET AGGMISSG ON;
SET UPDATECALC OFF;
SET FRMLBOTTOMUP ON;
SET MSG SUMMARY;
SET LOCKBLOCK HIGH;
SET EMPTYMEMBERSETS ON;

FIX(InputValue, FY10, RUR, Work, Budget)

       CALC DIM (Account, Period, SparseDimA, SparseDimB...)

ENDFIX

Часть 2. Что получилось улучшить

1. Сделал динамическими родителей в измерениях Period и Account  +25%

      Размер блока:

• было  Account(785) х Period(18) х 8байт =  113040
• стало  Account(705) х Period(13) х 8байт = 73320 (~уменьшился на 30%)

    P.S. Не делайте динамическими dense элементы с комплексными формулами (есть ссылка на другой блок)

2. Изменил порядок измерений +15%:

• Dense - от большего к меньшему
• Sparse - от меньшего агрегируемого к большему, неагрегируемые измерения, большие плоские измерения для паралельных вычислений

Dimension                    Type    Declared Size  Actual Size
=============================================
/* Dense от большего к меньшему */
Account                          DENSE   809            705
Period                             DENSE   20               13
/* Агрегируемые */
SparseDimC                  SPARSE  125            122
SparseDimD                  SPARSE  125            124
SparseDimE                  SPARSE  199            197
SparseDimA                  SPARSE  8                  6
SparseDimB                  SPARSE  17                13
/* Прочие (неагрегируемые) измерения */
Currency Rates               SPARSE  6                  5
Scenario                         SPARSE  7                  6
Years                             SPARSE  8                  7
Currency                        SPARSE  10                8
Version                          SPARSE  15               15
/* Измерения для параллельных расчетов */
Entity                             SPARSE  198            196
SparseDimF                  SPARSE  288            144

    P.S. Измерения  SparseDimA и SparseDimB логичнее поставить выше, но на них не хватит CalcCache, поэтому эти измерения перемещены ниже
    P.S.S. Какие sparse измерения необходимо переместить вниз, а какие вверх определяется только опытным путем т.е. тестируем-тестируем-тестируем

3. Включил параллельные вычисления +40%

    SET CALCPARALLEL 4;
    SET CALCTASKDIMS 2;

4. Расчитал Calculator Cache +5%
   SparseDimC (122) х SparseDimD(124) х SparseDimE (197) х 8байт / 1024 = 23 282 кб

Изменения в essbase.cfg

...
CALCCACHEHIGH    200000000 
CALCCACHEDEFAULT 50000000 
CALCCACHELOW     25000000
...

5. Изменил скрипт агрегации +50%

SET CACHE ALL;
SET CACHE LOW; /* 25Мб */;

SET AGGMISSG ON;
SET UPDATECALC OFF;
SET FRMLBOTTOMUP ON;
SET MSG SUMMARY;
SET LOCKBLOCK HIGH;
SET EMPTYMEMBERSETS ON; 

SET CALCPARALLEL 4;
SET CALCTASKDIMS 2;
/* WaterFall agreggetion */
FIX(InputValue, FY10, RUR, Work, Budget, @RELATIVE(YearTotal,0)) /*main*/

FIX(@RELATIVE(&vEntity,0))

    FIX(@RELATIVE(SparseDimA,0))

        FIX(@RELATIVE(SparseDimB,0))

            FIX(@RELATIVE(SparseDimC,0))

                FIX(@RELATIVE(SparseDimD,0))

                    FIX(@RELATIVE(SparseDimE,0))

                        FIX(@RELATIVE(SparseDimF,0))
                            CALC DIM (Account);
                        ENDFIX

                        FIX(@DESCENDANTS(Account))
                            AGG(SparseDimF);
                        ENDFIX
                    ENDFIX /*SparseDimF*/

                    FIX(@DESCENDANTS(Account)
                        @DESCENDANTS(SparseDimF))
                            AGG(SparseDimE);
                    ENDFIX
                ENDFIX /*SparseDimD*/

                FIX(@DESCENDANTS(Account)
                    @DESCENDANTS(SparseDimF)
                    @DESCENDANTS(SparseDimE))
                        AGG(SparseDimD);
                ENDFIX
            ENDFIX /*SparseDimC*/

            FIX(@DESCENDANTS(Account)
                @DESCENDANTS(SparseDimF)
                @DESCENDANTS(SparseDimE)
                @DESCENDANTS(SparseDimD))
                    AGG(SparseDimC);
            ENDFIX
        ENDFIX /*SparseDimB*/

        FIX(@DESCENDANTS(Account)
            @DESCENDANTS(SparseDimF)
            @DESCENDANTS(SparseDimE)
            @DESCENDANTS(SparseDimD)
            @DESCENDANTS(SparseDimC))
                AGG(SparseDimB);
        ENDFIX
    ENDFIX /*SparseDimA*/

    FIX(@DESCENDANTS(Account)
        @DESCENDANTS(SparseDimF)
        @DESCENDANTS(SparseDimE)
        @DESCENDANTS(SparseDimD)
        @DESCENDANTS(SparseDimC)
        @DESCENDANTS(SparseDimB))
            AGG(SparseDimA);
    ENDFIX
ENDFIX /*Entity*/

FIX(@DESCENDANTS(Account)
    @DESCENDANTS(SparseDimF)
    @DESCENDANTS(SparseDimE)
    @DESCENDANTS(SparseDimD)
    @DESCENDANTS(SparseDimC)
    @DESCENDANTS(SparseDimB)
    @DESCENDANTS(SparseDimA))
            @IDESCENDANTS(&vEntity, -1);
            @ANCESTORS(&vEntity);
ENDFIX

ENDFIX /*main*/

Часть 3. Не забываем про стандартные рекомендации

    - ACR (Average clustering Rate) должен стремится к  1, для этого запускайте dense реструктуризацию
    - Размер блока должен быть между 10 и 100KB (может быть больше для 64 bit essbase)
    - Data cache должен быть около 12.5% от общего размера pag файлов
    - Index cache должен быть таким же как ind файлы
    - В одном приложение только 1 куб

Типовые запросы к реляционным таблицам Planning

Данные запросы создавались для версии 11.1.1.3 на БД Oracle 10g

/* Список групп */ 
SELECT g.object_name as group_nane
FROM hsp_group ug, hsp_object g
WHERE ug.group_id = g.OBJECT_ID
ORDER by ug.group_id; 

/* Список пользователей и групп */ 
SELECT g.object_name as group_nane, u.object_name as user_name
FROM hsp_usersingroup ug
   , hsp_object g
   , hsp_object u
WHERE ug.group_id = g.OBJECT_ID
AND ug.user_id = u.OBJECT_ID
ORDER by ug.group_id;

/* Статусы сценариев */
SELECT scenario.object_name Scenario
     , start_y.object_name Start_Year
     , end_y.object_name End_Year
     , start_p.object_name Start_Period
     , end_p.object_name End_Period
FROM HSP_SCENARIO s
   , hsp_object scenario
   , hsp_object start_y
   , hsp_object end_y
   , hsp_object start_p
   , hsp_object end_p
WHERE 1=1
AND s.scenario_id = scenario.object_id
AND s.start_yr_id = start_y.object_id
AND s.end_yr_id = end_y.object_id
AND s.start_tp_id = start_p.object_id
AND s.end_tp_id = end_p.object_id;

/* Настройка безопасности */
SELECT user_name, o_name memeber,
case when ACCESS_MODE=-1 then 'None'
when ACCESS_MODE=3 then 'Write'
when ACCESS_MODE=1 then 'Read'
else 'UnKnown'
end access_type,
case when FLAGS=0 Then 'MEMBER'
when FLAGS=9 Then '@IDESCENDANTS'
when FLAGS=8 Then '@DESCENDANTS'
when FLAGS=6 Then '@ICHILDREN'
when FLAGS=5 Then '@CHILDREN'
else 'UnKnown'
end member_type
FROM HSP_ACCESS_CONTROL
inner join (SELECT object_name as user_name, object_id as u_id 
            FROM hsp_object) u 
      on hsp_access_control.user_id=u_id
inner join (SELECT object_name as o_name, object_id as o_id 
            FROM hsp_object WHERE hsp_object.object_type !=7 ) o 
      on OBJECT_ID=o_id;

/* Пример построение parent-child запроса */
SELECT object_name
FROM hsp_object
WHERE 1=1
--and has_children = 0 -- только уровень 0
START WITH UPPER(object_name) = UPPER('Account') -- все потомки Account
CONNECT BY PRIOR object_id = parent_id;

/* Пример запроса для выгрузки настроек измерения Account */
SELECT o2.object_name PARENT ,
    o.object_name CHILD ,
    o.generation ,
    o.position ,
    DECODE(m.data_storage, 2, 'O', 5, 'X', 'N') data_storage, -- N Never, O - Label, X - Dynamic ,
    DECODE(m.twopass_calc, 1, 'T', '') twopass_calc ,
    m.data_type ,
    DECODE(m.consol_op1, 6, '^', 5, '~', '+') consol_op ,
    DECODE(a.time_balance, 2, 'L', '') time_balance ,
    a.account_type ,
    DECODE(a.variance_rep, 1, 'E', '') variance_rep ,
    path
  FROM hsp_member m ,
    (SELECT parent_ID,
      OBJECT_ID,
      object_name,
      generation,
      position,
      sys_connect_by_path( acc.object_name, '/') path
    FROM hsp_object acc
    WHERE 1 =1
      START WITH acc.object_name     = 'Account'
      CONNECT BY PRIOR acc.object_id = acc.parent_id
    ) o ,
    HSP_OBJECT o2 ,
    hsp_account a
  WHERE 1         =1
  AND O.OBJECT_ID = m.member_id
  AND O.parent_ID = O2.OBJECT_ID
  AND m.member_id = a.account_id
  AND a.used_in   = '1' -- For 1-st plan;

/* Список измененных объектов в Planning, которые должны обновиться в Essabse */ 
 SELECT   'PL' AS application,
          ot.type_name object_type,
          t.object_id,
          t.plan_type,
          t.xact_type,
          t.object_name,
          t.old_name,
          t.xact_date,
          t.src_mbr,
          o.modified_by
   FROM   hp_PL.hsp_pending_xacts t,
          hp_PL.hsp_object o,
          hp_PL.hsp_object_type ot
  WHERE   t.object_id = o.object_id(+)
          AND o.object_type = ot.object_type(+) 

Ссылки по настройке производительности

http://download.oracle.com/docs/cd/E17236_01/epm.1112/epm_install_start_here/ch03s01.html - Official Sizing Info

www.innowavetech.com/downloads/090623_odtug_s629.pdf - Windows + Unix tuning

http://ilearning.oracle.com/ilearn/en/learner/jsp/rco_details_find.jsp?srchfor=null&rcoid=873918036  - EPM Performance Tuning Webcast (только для партнеров)

www.norcaloaug.com/seminar_archive/2008_training_day_pres/6_16_helmer.pdf  - System 9 Overview + Sizing

http://dlc.sun.com/pdf/817-0404/817-0404.pdf  - Sun Solaris Tuning

http://www.scribd.com/doc/18384637/Essbase-Optimization  - Essbase Optimization

ODTUG Hyperion Tech Content - много всего :)

http://essbase.ru/archives/663 - оптимизация Essbase

http://blogs.oracle.com/pa/ - оптимизация EPM