Построение репозитория Oracle BI 11g для правильного использования избыточности в витрине

Задача:
Увеличить производительность при формировании отчетов Oracle BI Answers

Решение:
Один из вариантов решения – это при формировании отчета Answers в запросе к базе данных уменьшить количество соединений таблиц. Этого можно добиться путем дублирования атрибутов измерений в таблице факта, т.е. создания избыточности. Но возникает вопрос: как настроить BI чтобы на презентационном уровне был один единый столбец атрибута измерения и в зависимости от выбранных фактов он формировался из своей таблицы или из таблицы фактов?

Предлагаю построить в Oracle BI такой пример и посмотреть как он работает. …

1. Возьмем для примера 2 таблицы измерений ZAccount и ZCurrency, а так же 2 таблицы фактов ZFact1 и ZFact2. В таблицах факта создадим избыточный атрибут currency_name.

2. Заполним таблицы тестовыми данными:

ZAccount

ZCurrency

ZFact1

ZFact2

3. Создадим в репозитории аналитического сервера Oracle BI физическую модель из наших тестовых таблиц.

А так же определим для таблиц фактов FK ключи для связи с таблицами измерений.

4. Перетащим нашу физическую модель на логический уровень и удалим из логических таблиц фактов атрибуты currency_name.

5. Теперь необходимо перетащить физические атрибуты ZFact1.currency_name и Fact2.currency_name на логический атрибут ZCurrency.currency_name. И так же перетаскиваем физические атрибуты ZFact1.currency_uk и Fact2.currency_uk на логический атрибут ZCurrency.currency_uk. Получится, что мы определим для этих логческих атрибутов несколько источников, что и видно по списку Sources.

Следует устанавливать одинаковый приоритет для всех источников, т.к. иначе измерение всегда будет запрашиваться из таблицы факта или из таблицы измерения. При равном приоритете источников запрос формируется “по умному”.

6. Далее определим тип агрегации для значений фактов.

7. Перенесем нашу логическую модель на презентационный слой и удалим лишние атрибуты.

8. Теперь создадим отчет Answers с атрибутами и фильтром как показано на картинке ниже.

9. Посмотрим результат и запрос, который сгенерировал BI сервер (Администрирование / Управление сеансами).

В логе видим, что в запросе нет join между фактами и ZCurrency:

[2012-12-05T20:19:15.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: ] [tid: 5e] [requestid: 323a0015] [sessionid: 323a0000] [username: developer] -------------------- Sending query to database named DEV_TEST (id: <<13060841>>), connection pool named DEV_CP, logical request hash 159db1bb, physical request hash 4c16e2a9: [[
WITH
SAWITH0 AS (select sum(T41552.value) as c1,
     T41564.account_name as c2,
     T41552.currency_name as c3
from
     ZAccount T41564,
     ZFact1 T41552
where  ( T41552.account_uk = T41564.account_uk and T41552.currency_name = 'RUR' )
group by T41552.currency_name, T41564.account_name),
SAWITH1 AS (select sum(T41711.value) as c1,
     T41564.account_name as c2,
     T41711.currency_name as c3
from
     ZAccount T41564,
     ZFact2 T41711
where  ( T41564.account_uk = T41711.account_uk and T41711.currency_name = 'RUR' )
group by T41564.account_name, T41711.currency_name),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from
     (select 0 as c1,
               case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end  as c2,
               case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end  as c3,
               D1.c1 as c4,
               D2.c1 as c5,
               ROW_NUMBER() OVER (PARTITION BY case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end , case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end  ORDER BY case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end  ASC, case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end  ASC) as c6
          from
               SAWITH0 D1 full outer join SAWITH1 D2 On D1.c2 = D2.c2 and D1.c3 = D2.c3
     ) D1
where  ( D1.c6 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from
     SAWITH2 D1
order by c1, c2, c3 ) D1 where rownum <= 70001

10. Теперь добавим столбец CurrencyType, который не был избыточно добавлен в таблицы фактов.

В логе видим, что запрос изменился и появился join между фактами и ZCurrency:

[2012-12-05T20:25:51.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: ] [tid: 3e] [requestid: 323a0015] [sessionid: 323a0000] [username: developer] -------------------- Sending query to database named DEV_TEST (id: <<13061746>>), connection pool named DEV_CP, logical request hash 633539c7, physical request hash f605428c: [[
WITH
SAWITH0 AS (select sum(T41552.value) as c1,
     T41564.account_name as c2,
     T41559.currency_name as c3,
     T41559.CurrencyType as c4
from
     ZCurrency T41559,
     ZAccount T41564,
     ZFact1 T41552
where  ( T41552.account_uk = T41564.account_uk and T41552.currency_uk = T41559.currency_uk and T41559.currency_name = 'RUR' )
group by T41559.currency_name, T41559.CurrencyType, T41564.account_name),
SAWITH1 AS (select sum(T41711.value) as c1,
     T41564.account_name as c2,
     T41559.currency_name as c3,
     T41559.CurrencyType as c4
from
     ZCurrency T41559,
     ZAccount T41564,
     ZFact2 T41711
where  ( T41559.currency_uk = T41711.currency_uk and T41559.currency_name = 'RUR' and T41564.account_uk = T41711.account_uk )
group by T41559.currency_name, T41559.CurrencyType, T41564.account_name),
SAWITH2 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6
from
     (select 0 as c1,
               case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end  as c2,
               case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end  as c3,
               case  when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end  as c4,
               D1.c1 as c5,
               D2.c1 as c6,
               ROW_NUMBER() OVER (PARTITION BY case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end , case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end , case  when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end  ORDER BY case  when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end  ASC, case  when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end  ASC, case  when D1.c4 is not null then D1.c4 when D2.c4 is not null then D2.c4 end  ASC) as c7
          from
               SAWITH0 D1 full outer join SAWITH1 D2 On D1.c3 = D2.c3 and D1.c2 = D2.c2 and D1.c4 = D2.c4
     ) D1
where  ( D1.c7 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6 from ( select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6
from
     SAWITH2 D1
order by c1, c2, c3, c4 ) D1 where rownum <= 70001

11. Теперь в редакторе фильтра посмотрим список значений currency_name.

В логе видно, что запрос корректно строится по ZCurrency:

[2012-12-05T20:14:59.000+00:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: ] [tid: 3c] [requestid: 323a0015] [sessionid: 323a0000] [username: developer] -------------------- Sending query to database named DEV_TEST (id: <<13060472>>), connection pool named DEV_CP, logical request hash dd1c8dd6, physical request hash 99bd68ba: [[
select distinct T41559.currency_name as c1
from
     ZCurrency T41559
order by c1

Вывод:

Этот метод работает. Но он не панацея.  Это вполне неплохое решение для случаев, когда много измерений и в них очень много значений, тут никакими индексами нельзя кардинально улучшить ситуацию.