BIEE: YearAGO и 29 февраля

Привет!
На дворе февраль. И я хочу рассказать о пути решения следующей проблемы:


Как видно из скриншота - при детализации YearAGO-показателя до разреза дней в феврале года, следующего за високосным, total-значение по дням не равно значению показателя в разрезе месяца.
Очевидно, проблема возникает из-за того, что в феврале 2013 года 28 дней, а в феврале 2012 - 29 дней.
В приведенном примере YAGO-показатель определяется стандартной time-series функцией AGO. Но этого недостаточно...
Итак, как обработать подобные ситуации?


Для начала продемонстрирую исходные метаданные используемого примера.


где таблицы D_CALEDAR_DAY и F_SALES определяются и заполняются следующим образом:

----------------------------
drop table D_CALENDAR_DAY;
create table D_CALENDAR_DAY
(
  day_key                 DATE not null,
  year_key                INTEGER not null,
  half_year_key           INTEGER not null,
  half_year_name          VARCHAR2(20),
  quarter_key             INTEGER not null,
  quarter_name            VARCHAR2(20),
  month_key               INTEGER not null,  
  month_year              VARCHAR2(20),
  month_num               INTEGER,
  month_name              VARCHAR2(20),
  week_start_key          DATE not null,
  week_key                INTEGER,
  week_month              VARCHAR2(20),
  week_year               INTEGER,
  week_year_with_start_dt VARCHAR2(20),
  day_year                INTEGER,
  day_month               INTEGER,
  day_week                INTEGER,
  day                     VARCHAR2(30),
  date_name               VARCHAR2(20),
  holiday_flag            CHAR(1)
)
/
----------------------------
truncate table D_CALENDAR_DAY;
DECLARE
  StartDate date := TO_DATE('01.01.2000', 'DD.MM.YYYY');
  EndDate   date := TO_DATE('31.12.2015', 'DD.MM.YYYY');
  LastDate  date := TO_DATE('31.12.2999', 'DD.MM.YYYY');

  MiddleDate date;
BEGIN
  execute immediate 'alter session set nls_territory = CIS';
  execute immediate 'alter session set nls_language = RUSSIAN';

  MiddleDate := StartDate;
  loop
    insert into D_CALENDAR_DAY
      (day_key,
       year_key,
       half_year_key,
       half_year_name,
       quarter_key,
       quarter_name,
       month_key,
       month_year,
       month_name,
       month_num,
       week_key,
       week_month,
       week_year,
       week_start_key,
       week_year_with_start_dt,
       day_year,
       day_month,
       day_week,
       day,
       date_name,
       holiday_flag)
      select MiddleDate day_key,
             to_char(MiddleDate, 'YYYY') year_key,
             to_char(MiddleDate, 'YYYY') ||
             decode(to_char(MiddleDate, 'Q'), '1', '1', '2', '1', '2') half_year_key,
             to_char(MiddleDate, 'YYYY') || 'г. ' ||
             decode(to_char(MiddleDate, 'Q'), '1', '1', '2', '1', '2') ||
             'п.г.' half_year_name,
             to_char(MiddleDate, 'YYYYQ') quarter_key,
             to_char(MiddleDate, 'YYYY') || 'г. ' ||
             to_char(MiddleDate, 'Q') || 'кв.' quarter_name,
             to_char(MiddleDate, 'YYYYMM') month_key,
             to_char(MiddleDate, 'YYYY.MM') month_year,
             to_char(MiddleDate, 'fmMonth') month_name,
             to_char(MiddleDate, 'fmMM') month_num,
             to_char(MiddleDate, 'YYYY') * 100 +
             CEIL((TO_CHAR(MiddleDate, 'DDD') +
                  TO_CHAR(TRUNC(MiddleDate, 'YEAR'), 'D') - 1) / 7) week_key,
             trunc((to_number(to_char(MiddleDate, 'dd')) +
                   to_number(to_char(trunc(MiddleDate, 'month'), 'd')) + 5) / 7) week_month,
             CEIL((TO_CHAR(MiddleDate, 'DDD') +
                  TO_CHAR(TRUNC(MiddleDate, 'YEAR'), 'D') - 1) / 7) week_year,
             (MiddleDate - to_char(MiddleDate, 'D') + 1) week_start_key,
             
             to_char(CEIL((TO_CHAR(MiddleDate, 'DDD') +
                  TO_CHAR(TRUNC(MiddleDate, 'YEAR'), 'D') - 1) / 7),'fm09') || ' - ' ||
                  to_char((MiddleDate - to_char(MiddleDate, 'D') + 1), 'DD.MM.YYYY') week_year_with_start_dt,            
             to_char(MiddleDate, 'DDD') day_year,
             to_char(MiddleDate, 'DD') day_month,
             to_char(MiddleDate, 'D') day_week,
             to_char(MiddleDate, 'fmDay') day,
             to_char(MiddleDate, 'DD.MM.YYYY') date_name,
             decode(to_char(MiddleDate, 'D'), '6', 'H', '7', 'H', 'W') holiday_flag
        from dual;
  
    MiddleDate := MiddleDate + 1;
  
    exit when MiddleDate > LastDate;
    if MiddleDate > EndDate then
      MiddleDate := LastDate;
    end if;
  
  end loop;

  commit;

  dbms_stats.gather_table_stats(ownname => USER,
                                tabname => 'D_CALENDAR_DAY',
                                cascade => true);
END;
/
----------------------------
drop table F_SALES;
create table F_SALES
(
  day_key                 DATE not null,
  sales_amt               NUMBER
)
/
----------------------------
insert into F_SALES
  (DAY_KEY, SALES_AMT)
  select d.day_key, round(dbms_random.value * 100) as sales_amt
    from D_CALENDAR_DAY d
   where d.year_key in (2011, 2012, 2013)
/
----------------------------

Как вы видите, ничего необычного.
Стандартный подход по реализации YAGO-показателей через функцию AGO.

Я же предлагаю отбросить этот подход (если вы смотрели на физические SQL-запросы, которые генерирует BIServer для AGO-показателей, то знаете - они достаточно "тяжеловесны").
Идея в том, чтобы для каждой таблицы фактов (по которой нужны AGO-показатели) был создан алиас на физическом слое RPD.
Также необходимо в таблицу-календарь добавить ключ со смещением на -12 месяцев (YAGO_DAY_KEY).
И связать таблицу-календарь и новый алиас на фактовую таблицу через джойн
Calendar.YAGO_DAY_KEY = Fact.DAY_KEY
Затем следует добавить новый алиас фактовой таблицы как Logical Table Source в логическую фактовую таблицу и переименовать показатель из этого LTS - например, дать префикс "YAGO".

В нашем случае все немного сложнее: нужно размножить строки в таблице календаря для обработки ситуации "29 февраля".
То есть нам необходимо, чтобы строке календаря с ключом 20130228 соответствовало две "YAGO-строки" - 20120228 и 20120229.
Да-да, мы относим YAGO-показатель 29 февраля к 28 февраля текущего года. Я думаю, это небольшая плата за неизменность отображения YAGO-показателя при детализации от месяца к дням...

Итак, я создам дополнительную таблицу D_CALENDAR_DAY_YAGO_LINK

----------------------------
create table D_CALENDAR_DAY_YAGO_LINK (YAGO_DAY_KEY date, DAY_KEY date)
/
----------------------------
alter table D_CALENDAR_DAY_YAGO_LINK add primary key (YAGO_DAY_KEY, DAY_KEY)
/
----------------------------
insert into D_CALENDAR_DAY_YAGO_LINK (YAGO_DAY_KEY, DAY_KEY)
select t.DAY_KEY as YAGO_DAY_KEY,
       decode(to_char(add_months(t.DAY_KEY, 12), 'DDMM'),
              '2902',
              add_months(t.DAY_KEY, 12) - 1,
              add_months(t.DAY_KEY, 12)) as DAY_KEY
  from D_CALENDAR_DAY t
/
----------------------------

В данной таблице определяются связи между днями текущего года с днями прошлого. Используется для этого отдельная таблица - потому как крайне нежелательно дублировать данные в основной таблице-календаре.

Прописываю эту новую таблицу в RPD. а также создаю алиасы на таблицу фактов и календаря.


Определяю связи между таблицами-алиасами


Перетаскиваю в раздел Logical Table Source новый алиас календаря. Добавляю в этот LTS джойн с link-таблицей (строго inner-связь)


Важно, чтобы имеющиеся столбцы логического таблицы были "отмапплены" на новый LTS


Перетаскиваю в раздел LTS новый алиас фактовой таблицы. Переименовываю столбец-показатель этого LTS в "YAGO Sales Amount (Correct)". Задаю агрегацию. Перекидываю на презентационный слой.


Вот что получается в итоге



P.S. Попробуйте сравнить производительность стандартного подхода к реализации AGO-показателей и подхода через алиасы и "сдвиг календаря"...