BIEE 11g: остатки на произвольную дату

Сегодня я расскажу о подходе к отображению остатков в Oracle BI, который я использую в проектах.
Подход позволяет отображать остатки на произвольную дату. Он прост в реализации.
Рассмотрим его на примере репозитория SH (ссылка на скачивание готового примера) и схемы SH базы данных Oracle.
Предполагаем, что нашей задачей является создание оборотно-сальдовой ведомости по истории продаж.

Нам потребуется поддержка (с помощью ETL) таблицы остатков на текущую дату.

create table sales_current_balance as
select t.prod_id,
       t.cust_id,
       t.channel_id,
       t.promo_id,
       sum(t.quantity_sold) as quantity_sold,
       sum(t.amount_sold) as amount_sold
  from sales t
 group by t.prod_id, t.cust_id, t.channel_id, t.promo_id

Импортируем в репозиторий OBIEE следующие таблицы:


Создадим альясы:


Создадим связи между физическими объектами-альясами:


Обратите внимание, что таблица _F.02_SALES_CURRENT_BALANCE не содержит внешнего ключа TIMES_ID.
А также таблица _F.03_SALES_DELTA связана комплексно с таблицей календаря.

Далее «протянем» используемые таблицы на логический и презентационный слой.




Заметьте, что для показателей всех логических таблиц фактов установлен тип агрегации – SUM.
А также, что для показателей таблицы _F.02_SALES_CURRENT_BALANCE установлен уровень агрегации по измерению календаря – TOTAL (Это важно. Установка уровня агрегации на Logical Tables Source лишь позволяет BIServer’у выбрать какой источник использовать для конкретного логического запроса, в то время как установка уровня агрегации на конкретном столбце – относится непосредственно к логике обработки).
Ну и самое интересное – это создание отдельной логической таблицы (фиктивное). Которая содержит вычисляемые логические показатели – сальдо на начало и на конец по сумме и по количеству продаж. Функции их формирования говорят сами за себя.
Для AMT_BALANCE_OPENING:
"SH with balances"."_F.02_SALES_CURRENT_BALANCE"."AMOUNT_SOLD" - IFNULL("SH with balances"."_F.03_SALES_DELTA"."AMOUNT_SOLD",0)
Для AMT_BALANCE_CLOSING:
"SH with balances"."_F.04_SALES_BALANCE"."AMT_BALANCE_OPENING" + IFNULL("SH with balances"."_F.01_SALES_TURNOVER"."AMOUNT_SOLD" ,0)

Теперь проверим что у нас получилось – создадим простенький анализ по новой предметной области.


В режиме просмотра результатов (PivotTable) видно, что мы очень близки к нашей цели: в несколько кликов мышью сформирован оборотно-сальдовый отчет по суммам продаж в разрезе городов. При желании можно детализировать отчет до клиентов конкретного города. Причем обороты + сальдо будут отображаться корректно уже в разрезе клиентов.




Отлично!
Правда, мы забыли настроить важнейшее измерение – календарь. С ним не все очевидно в используемом подходе (при желании можно проанализировать физические запросы, генерируемые к БД).
Нам потребуется создать (и поддерживать, конечно) отдельные таблицы на каждый уровень иерархии календаря.
Допустим, нам хватит лишь 3 уровней иерархии календаря – год, месяц, день.
Значит нам достаточно создать 2 новые таблицы:

create table times_year as
select *
  from times t
 where t.time_id = trunc(t.time_id,'Y')

create table times_month as
select *
  from times t
 where t.time_id = trunc(t.time_id,'MM')

Существующая уже таблица TIMES – это и есть таблица под уровень иерархии «Day».

Импортируем 2 новые таблицы в наш репозиторий. Создадим альясы. Настроим физические связи.


Перенесем новые таблицы как дополнительные Logical Table Source в логическую таблицу календаря - _D.06_TIMES.


Не забываем корректно настроить отображение логических столбцов на LTS.
А также создать нужные 3 уровня иерархии календаря (с корректными ключами).

Теперь можно возвращаться к нашему анализу:




Вот и все!
Теперь наш отчет поддерживает как детализацию по измерениям, так и детализацию по календарю.
Несложно, не правда ли!