logo

2 дек. 2012 г.

BIEE 11g: каскадно-зависимые приглашения инфопанели

Всем привет!
Сегодня хочу рассказать про зависимые наборы значений в приглашениях инфопанелей. Вернее про сопутствующую им проблему...
Допустим, имеется отчет, построенный на базе демо-репозитория SH, показывающий продажи по месяцам года в заданном Region, Subregion и Country.
Отчет помещен на инфопанель, которая снабжена приглашением. Это приглашение содержит зависимые параметры.


Как видно из скриншота, значения набора "Country" должны ограничиваться не только при выборе "Subregion", но и "Region".


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


А это приводит к неприятным последствиям - "просадке" производительности таких приглашений.


В этом нетрудно убедиться, посмотрев какой физический запрос к БД генерируется в момент нажатия на какое-либо значение столбца "Region".


Нас интересует последний, самый "тяжелый" запрос.
(При установке свойства LogLevel в значение большее 5 - в журнале запроса доступен и физический запрос к БД)
select distinct T175.COUNTRY_NAME as c1
  from SH.COUNTRIES T175,
       SH.CUSTOMERS T186,       
       (SELECT DISTINCT CALENDAR_MONTH_NUMBER,
                        CALENDAR_MONTH_DESC,
                        CALENDAR_MONTH_ID,
                        DAYS_IN_CAL_MONTH,
                        END_OF_CAL_MONTH,
                        CALENDAR_MONTH_NAME,
                        CALENDAR_QUARTER_DESC,
                        CALENDAR_QUARTER_ID,
                        DAYS_IN_CAL_QUARTER,
                        END_OF_CAL_QUARTER,
                        CALENDAR_QUARTER_NUMBER,
                        CALENDAR_YEAR,
                        CALENDAR_YEAR_ID,
                        DAYS_IN_CAL_YEAR,
                        END_OF_CAL_YEAR
          FROM TIMES) T2464
  full outer join(SH.SALES T3836 /* SALES CurrentMonth */
 inner join SH.TIMES T3936 /* TIMES Current Month */
    On T3836.TIME_ID = T3936.TIME_ID
   and T3936.CALENDAR_MONTH_ID = 1719.0) On 1 = 1
 where (T175.COUNTRY_ID = T186.COUNTRY_ID and
       T175.COUNTRY_REGION = 'Americas' and T186.CUST_ID = T3836.CUST_ID and
       T2464.CALENDAR_YEAR = 2001 and T3936.CALENDAR_MONTH_ID = 1719.0 and
       (T2464.CALENDAR_MONTH_DESC in
       ('2001-01', '2001-02', '2001-03', '2001-04', '2001-05', '2001-06')))
 order by c1

Очевидно, что запрос "плохой". В качестве связи между не связанными напрямую таблицами измерений BIServer использует таблицу фактов (с наименьшей кардинальностью, конечно).

Я считаю, что эту ситуацию можно и нужно исправлять.
И решение на поверхности: вместо реальной фактовой таблицы (с сотнями тысяч и миллионами строк) следует использовать псевдо-таблицу фактов... на основе таблицы DUAL.


Создадим новую таблицу на физическом слое, которая будет являться альясом таблицы DUAL (т.е. будет содержать всегда 1 строку).
Соединим на физическом слое новую таблицу со всеми таблицами измерений комплексной связью
1 = 1

Протянем новую таблицу F_LINKS на логический слой; свяжем со всеми логическими таблицами измерений inner-связью.
Укажем уровни детализации LTS таблицы F_LINKS.


Создадим новый столбец-показатель DUMMY_MEASURE, для которого зададим агрегацию - COUNT.


И наконец, на презентационном слое укажем в свойствах SubjectArea значение для Implicit Fact Column равным созданному DUMMY_MEASURE


Теперь всякий раз, когда BIServer должен решить какую таблицу фактов выбрать для связи таблиц измерений, будет использоваться F_LINKS.

Полученный выше запрос теперь будет выглядеть следующим образом.
select T175.COUNTRY_NAME as c1, count(1) as c2
  from SH.COUNTRIES T175,
       (SELECT DISTINCT CALENDAR_MONTH_NUMBER,
                        CALENDAR_MONTH_DESC,
                        CALENDAR_MONTH_ID,
                        DAYS_IN_CAL_MONTH,
                        END_OF_CAL_MONTH,
                        CALENDAR_MONTH_NAME,
                        CALENDAR_QUARTER_DESC,
                        CALENDAR_QUARTER_ID,
                        DAYS_IN_CAL_QUARTER,
                        END_OF_CAL_QUARTER,
                        CALENDAR_QUARTER_NUMBER,
                        CALENDAR_YEAR,
                        CALENDAR_YEAR_ID,
                        DAYS_IN_CAL_YEAR,
                        END_OF_CAL_YEAR
          FROM TIMES) T2464,
       (select 1 as X from sys.DUAL) T4295
 where (T175.COUNTRY_REGION = 'Americas' and T2464.CALENDAR_YEAR = 2001 and
       (T2464.CALENDAR_MONTH_DESC in
       ('2001-01', '2001-02', '2001-03', '2001-04', '2001-05', '2001-06')))
 group by T175.COUNTRY_NAME
 order by c1

P.S. Прилагаю использованный в примере файл репозитория - SH.rpd (создавался в AdminTool от версии 11.1.1.6.2; пароль: Admin123)

4 комментария:

  1. Есть один момент,
    Опция Limit Valuest by All Prompt изначально подразумевает не фильтрацию одного уровня относительно другого, а фильтрацию только тех значений справочников для которых присутствуют данные в фактовой таблице.
    Если требуется только фильтрация одного уровня относительно другого, то ваш способ вполне пригоден. Есть еще правда Промпт-Иерархия, которая позволяет сделать тоже самое, и я думаю она даже быстрее.

    И еще одно, опция Implicit Fact Column одна на всю предметную область.
    Если например в одной предметной области захочится фильтровать и по данным факта и вашим способом? То придется выбирать что-то одно!

    ОтветитьУдалить
  2. 1) Но допустим что данных в фактовой таблице очень-очень много?
    Или допустим что у вас несколько независимых друг от друга измерений (с разными иерархиями), например, "Календарь" с "Год-месяц-неделя-день" и "Товар" с "Сектор-Отдел-Группа-Артикул".
    И вам нужно дать пользователю возможность "подрезать" дни годом, месяцем; а артикул "подрезать" сектором и отделом.
    Понятно, что в таком случае связь с фактовой таблицей не нужна. И лучше сэмулировать фактовую таблицу.

    2) В таком случае, я просто добавлю новый LTS в фактовую лог.таблицу "линков" по физической таблице фактов.

    ОтветитьУдалить
  3. 1) Да согласен, хотя иерархический промпт по "Товарам" и "Календарю" тем более, но этот способ новый и интересный в визуальном плане, и думаю более интерактивный.

    2) Я вот не ухватил, что такое LTE и лог таблица "линков"?

    ОтветитьУдалить
  4. Роман,
    1) LTS - это Logical Table Source.
    2) "лог. таблица линков" - это логическая таблица F_LINKS приведенного в посте примера. Обычно я ее так и обзываю в RPD - "F_LINKS"/"F.99 Links"/"F.99 Связи"

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