Построение репозитория 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

Вывод:

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

  • Jack Carver

    u0425u043eu0440u043eu0448u0430u044f u0441u0442u0430u0442u044cu044f.nu0412u043eu0442 u0442u043eu043bu044cu043au043e u044du0442u043e u0434u0435u0439u0441u0442u0432u0438u0442u0435u043bu044cu043du043e “u043du0435 u043fu0430u043du0430u0446u0435u044f” ((nu041fu043eu0441u043cu043eu0442u0440u0438u0442u0435 u043fu043eu0436u0430u043bu0443u0439u0441u0442u0430 u0441u043bu0430u0439u0434 26 nnhttp://www.peakindicators.com/media_pi/Knowledge/performance%20tuning%20oracle%20bi%2011g%20-%20all%20the%20options.pdf

  • To Jack Carver: u041fu043eu0445u043eu0436u0435 u043fu0440u0438u043cu0435u0440 u0440u0435u0437u0443u043bu044cu0442u0430u0442u044b, u043au043eu0442u043eu0440u043eu0433u043e u043fu0440u0438u0432u0435u0434u0435u043du044b u043du0430 u0441u043bu0430u0439u0434u0435 u043du0435 u043eu0447u0435u043du044c u043fu043eu0434u0445u043eu0434u0438u0442 u0434u043bu044f u044du0442u043eu0433u043e u043cu0435u0442u043eu0434u0430.nu041cu043eu0433u0443 u043fu0440u0438u0432u0435u0441u0442u0438 u0434u0440u0443u0433u043eu0439 u043fu0440u0438u043cu0435u0440, u043du0435u0434u0430u0432u043du043e u0432 u0431u0430u043du043au043eu0432u0441u043au043eu043c u043fu0440u043eu0435u043au0442u0435 u0434u0435u043bu0430u043b u0434u0440u0438u043bu043b u043fu043e u043eu0441u0442u0430u0442u043au0430u043c u043du0430 u0441u0447u0435u0442u0430u0445 u043au043bu0438u0435u043du0442u043eu0432. u0414u0440u0438u043bu043b u0442u0440u0435u0431u043eu0432u0430u043b u0432u044bu0431u0438u0440u0430u0442u044c u043du0435u0441u043au043eu043bu044cu043au043e u0442u044bu0441u044fu0447 u0441u0447u0435u0442u043eu0432 u0438u0437 u0438u0437u043cu0435u0440u0435u043du0438u044f u0441u0447u0435u0442u043eu0432 (90u041cu043bu043d u0437u0430u043fu0438u0441u0435u0439) u0441u043au043bu0435u0438u0432 u0447u0435u0440u0435u0437 u0444u0430u043au0442 u043eu0441u0442u0430u0442u043au043eu0432 u043du0430 u0441u0447u0435u0442u0430u0445 (22u041cu043bu043d.) u0441 u0438u0441u0442u043eu0440u0438u0435u0439 u0441u0434u0435u043bu043eu043a (19u041cu043bu043d) u0438 u0435u0449u0451 u043fu044fu0442u043au043eu043c u043cu0430u043bu0435u043du044cu043au0438u0445 u0438u0437u043cu0435u0440u0435u043du0438u0439 . u0422u043eu043bu044cu043au043e u043fu043eu0441u043bu0435 u0438u0437u0431u0435u0436u0430u043du0438u044f join u043fu043e u0431u043eu043bu044cu0448u0438u043c u0438u0437u043cu0435u0440u0435u043du0438u044fu043c u0432u0440u0435u043cu044f u0444u043eu0440u043cu0438u0440u043eu0432u0430u043du0438u044f u0441u0442u0430u043bu043e u0443u043au043bu0430u0434u044bu0432u0430u0442u044cu0441u044f u0432 u0442u0440u0435u0431u0443u0435u043cu044bu0435 u0440u0430u043cu043au0438. u0414u043e u044du0442u043eu0433u043e u043fu0440u043eu0431u043eu0432u0430u043bu0438 u043cu043du043eu0433u043eu0435, u0438u0437u0431u044bu0442u043eu0447u043du043eu0441u0442u044c u0431u044bu043bu0430 u043fu043eu0441u043bu0435u0434u043du0438u043c u0448u0430u0433u043eu043c. u041au043eu043du0435u0447u043du043e, u0441u043bu0443u0447u0430u0439 u0440u0435u0434u043au0438u0439, u0432u043eu0442 u0438 u0440u0435u0448u0438u043b u043du0430u043fu0438u0441u0430u0442u044c u043au043eu0433u0434u0430 u043cu0435u0442u043eu0434 u043fu0440u0438u0433u043eu0434u0438u043bu0441u044f u0432 u0431u043eu044e 🙂

    • u0420u043eu043cu0430,nu0435u0441u0442u0435u0441u0442u0432u0435u043du043du043e Left JOin u0432 u0441u043fu0438u0441u043au0435 u0438u0441u043fu043eu043bu044cu0437u0443u0435u043cu044bu0445 u0438u043du0441u0442u0440u0443u043cu0435u043du0442u043eu0432 u043fu0440u0438u0441u0443u0442u0441u0442u0432u0443u0435u0442 ? )nnn2012/12/6 Disqus

      • u043du0435u0442, u0432u0441u0451 u043fu0440u043eu0441u0442u043e – u0442u0430u043c Inner Join

        • u0412u043eu0442 u0432 u0442u043eu043c u0442u043e u0438 u0434u0435u043bu043e n select aa.r1 , bb.r2 from n aan left join bb on bb.p1=aa.p1n where bb.p1 is not null nnn u0420u0430u0431u043eu0442u0430u0435u0442 u043fu043e u0441u043cu044bu0441u043bu0443 u043au0430u043a Inner , u043du043e u0432u044bu043fu043eu043bu043du044fu0435u0442u0441u044f u0432 u0420u0410u0417u042b u0431u044bu0441u0442u0440u0435u0435

          • u041bu044eu0431u043eu043fu044bu0442u043du043e) u0421u043bu044bu0448u0430u043b u0438 u043fu0440u043eu0431u043eu0432u0430u043b u0442u0430u043au043eu0439 u0441u043fu043eu0441u043eu0431, u043du043e u043cu043du0435 u043eu043d u043au0430u043a-u0442u043e u043du0438u0447u0435u0433u043e u043du0430u0441u0442u043eu043bu044cu043au043e u0445u043eu0440u043eu0448u0435u0433u043e u043du0435 u043fu0440u0438u043du043eu0441u0438u043b. u042f u0441u043fu0435u0446u0438u0430u043bu044cu043du043e u044du043au0441u043fu0435u0440u0438u043cu0435u043du0442u043eu0432 u043du0435 u0441u0442u0430u0432u0438u043b, u043du043e u0447u0442u043eu0431u044b u0431u044bu043bu043e u0431u044bu0441u0442u0440u0435u0435 u0432 u0440u0430u0437u044b u043du0435 u0432u0438u0434u0435u043b. u041cu043eu0436u0435u0448u044c u043fu043eu0434u0441u043au0430u0437u0430u0442u044c u0431u043eu043bu0435u0435 u043fu043eu0434u0440u043eu0431u043du044bu0435 u0443u0441u043bu043eu0432u0438u044f u043au043eu0433u0434u0430 u0442u0430u043au043eu0439 u0441u043fu043eu0441u043eu0431 u0434u0430u0435u0442 u0442u0430u043au043eu0439 u0440u0435u0437u0443u043bu044cu0442u0430u0442 u0438u043bu0438 u0441u0441u044bu043bu043au0443 u043du0430 u043eu043fu0438u0441u0430u043du0438u0435?

          • u0412 u044du0442u043eu043c u0441u043bu0443u0447u0430u0435 u043fu0440u043eu0441u0442u043e u043eu0440u0430u043au043bu0443 u043du0435 u043du0430u0434u043e u0434u0443u043cu0430u0442u044c u043au0430u043au0430u044f u0442u0430u0431u043bu0438u0446u0430 u0432u0435u0434u0443u0449u0430u044f, u0430 u043au0430u043au0430u044f u0432u0435u0434u043eu043cu0430u044f.. u0413u043eu0442u043eu0432u043eu0433u043e u0442u0435u0441u0442 u043au0435u0439u0441u0430 u0441 u0438u043bu043bu044eu0441u0442u0440u0430u0446u0438u0435u0439 u0443 u043cu0435u043du044f u043du0435u0442 .

  • Jack Carver

    u042fu0441u043du043e.nu0422u043eu043bu044cu043au043e u0441u043bu0443u0447u0430u0439 u043du0435 u0440u0435u0434u043au0438u0439, u0438 u0441u0442u0430u0442u044cu044f u043fu043eu043bu0435u0437u043du0430u044f.nu041cu044b u0442u0430u043au0438u043c u043cu0430u043au0430u0440u043eu043c u0434u0443u0431u043bu0438u0440u043eu0432u0430u043bu0438 u043cu043du043eu0433u0438u0435 u0432u043du0435u0448u043du0438u0435 u043au043bu044eu0447u0438 u0444u0430u043au0442u043eu0432u044bu0445 u0442u0430u0431u043bu0438u0446 u0432 u0442u0430u0431u043bu0438u0446u0430u0445 u0438u0437u043cu0435u0440u0435u043du0438u0439 (u0431u0438u043bu043bu0438u043du0433 u044du043b.u044du043du0435u0440u0433u0438u0438)

    • Evgeniy Rasyuk

      u0421u0435u0440u0433u0435u0439, u0447u0442u043e -u0442u043e u044f u043du0435 u043fu043eu043du044fu043b u0442u0432u043eu0435u0433u043e u043au043eu043cu043cu0435u043du0442u0430u0440u0438u044f n – u0442u044b u0432u043e u0432u0441u0435u043c u0441u043eu0433u043bu0430u0448u0430u0435u0448u044cu0441u044f u0441 u0420u043eu043cu043eu0439 ? )

      • Jack Carver

        u042f u0441u043eu0433u043bu0430u0441u0435u043d, u0447u0442u043e u0434u0430u043du043du044bu0439 u043cu0435u0442u043eu0434 u043cu043eu0436u0435u0442 u0431u044bu0442u044c u043fu043eu043bu0435u0437u0435u043d. u041du043e u043fu0440u0438u043cu0435u043du044fu0442u044c u0435u0433u043e u043du0443u0436u043du043e u0441 u043eu0433u043bu044fu0434u043au043eu0439… u0413u0443u0440u0443 u0438u0437 PeakIndicators u0435u0433u043e u0441u0447u0438u0442u0430u044eu0442 “u043fu043bu043eu0445u0438u043c” (u0445u043eu0442u044f u0443 u0420u043eu043cu0430u043du0430 u043du0435 u0432u044bu043du043eu0441u0438u0442u0441u044f u043fu043eu043bu043du043eu0441u0442u044cu044e u0432 u0442u0430u0431u043bu0438u0446u0443 u0444u0430u043au0442u043eu0432 u0432u0441u0435 u0430u0442u0440u0438u0431u0443u0442u044b u0438u0437u043cu0435u0440u0435u043du0438u044f, u043du043e u044du0442u043e u043fu043eu0434u0440u0430u0437u0443u043cu0435u0432u0430u0435u0442u0441u044f)

        • Evgeniy Rasyuk

          u043cu043du0435 u043du0435 u0440u0430u0437u0443 u043du0435 u0434u043eu0432u043eu0434u0438u043bu043eu0441u044c u0438u0441u043fu043eu043bu044cu0437u043eu0432u0430u0442u044c u043au043bu0430u0441u0442u0435u0440u043du044bu0435 u043au043eu043bu043eu043du043au0438 nhttp://docs.oracle.com/cd/B10500_01/server.920/a96521/clustrs.htmnn u0443 u0442u0435u0431u044f u0435u0441u0442u044c u043cu043du0435u043du0438u0435 u043fu043e u043fu043eu0432u043eu0434u0443 u0430u043au0442u0443u0430u043bu044cu043du043eu0441u0442u0438 u0438u0441u043fu043eu043bu044cu0437u043eu0432u0430u043du0438u044f u0442u0430u043au043eu0433u043e u0441u043fu043eu0441u043eu0431u0430 u043eu043fu0442u0438u043cu0438u0437u0430u0446u0438u0438? n u0415u0441u0442u044c u043au0430u043au0438u0435-u0442u043e u043du0435u044fu0432u043du044bu0435 u043fu043eu0434u0432u043eu0434u043du044bu0435 u043au0430u043cu043du0438, u043au043eu0442u043eu0440u044bu0435 u043eu0441u0442u0430u0432u0438u043bu0438 u044du0442u043e u0432 u0442u0435u043eu0440u0438u0438 ?