Детерминанты - Тайна Cognos Framework Manager раскрыта

Перевод-пересказ статьи за авторством RALPH BAKER .

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

Детерминанты используются когда таблица с одной гранулярностью (уровнем детализации) взаимодействует с другой, хранящей данные с другой гранулярностью. Чаще всего, такое бывает в таблицах размерностей, к которым разные таблицы фактов присоединяются к одной размерности, но на разных уровнях. Но это не единственный случай их ипользования, остальные достаточно редки и специфичны.

Ситуация

Например, имеется размерность времени, с гранулярностью до дня. Если все факты присоединяются к самому детальному уровню Day, тогда детерминанты не нужны. Но, как показывает практика, идеал бывает редко достижим. По множеству разных причин, факты в таблицах частенько агрегированы или хранят данные на разных уровня гранулированности.

Западня

Неприятности всплывают вместе с желанием впихнуть невпихуемое AKA присоединиться к размерности времени не по самому нижнему уровню. Рассмотрим таблицу фактов с месячными прогнозами (monthly forecast fact table); с детализацией "одна строка - один месяц". Связь через month_id будет каждый раз, в зависимости от месяца, возвращать от 28 до 31 записи, и таким образом, портить расчёты и настроение. Детерминанты решают эту проблему.

Запрос SQL

Часто, при моделировании, полезно думать о том, какой код SQL хотелось бы получить на выходе. Без детерминантов день рожденья на праздник непохож, и неправильный SQL выглядит примерно так:
SELECT
F.FORCAST_VALUE,
D.MONTH_ID,
D.MONTH_NAME
FROM SALES_FORECAST F INNER JOIN DATE_DIM D ON
F.MONTH_ID = D.MONTH_ID
Этот код, на каждую запись из прогноза, получит примерно в 30 раз больше записей, чем нужно. Математические функции, вроде Sum и Count, вернут неверный результат. Правильный же запрос, должен сначала вытащить все уникальные месяцы, каждого по одной штуке, и ТОЛЬКО ПОСЛЕ ЭТОГО присоединить факт:
SELECT
F.FORCAST_VALUE,
D1.MONTH_ID,
D1.MONTH_NAME
FROM SALES_FORECAST F INNER JOIN
( SELECT DISTINCT
D.MONTH_ID,
D.MONTH_NAME
FROM DATE_DIM D ) AS D1
ON F.MONTH_ID = D1.MONTH_ID
Как показано выше, трюк заключается в понимании какие колонки в размерности относятся к month_id, и потому уникальны в пределах ключа. Как раз в этом и заключается работа детерминантов.

Разоблачение тайны Framework Manager

Следуя лучшим практикам Cognos, детерминанты необходимо указывать в слое модели, где происходит связывание таблиц.
Ниже приведена размерность времени с 4 уровнями: Year, Quarter, Month и day.

Это означает, что, в зависимости от гранулированности таблиц фактов, представленных в модели, в тематическом запросе можно определить до 4-х детерминантов. Первые три уровня: Year, Quarter, Month должны быть группирующими, т.к. не описывают уникальную строку в таблице; и установка флага "group-by" объясняет Cognos'у, что данные по этому уровню необходимо "схлопнуть" (оставить только одно значение). Другими словами, здесь описываются правила для формирования секции "group by" в запросе SQL (такого как Год или Месяц). Уровень Day - это конечный (Leaf) уровень детализации. С целью однозначного определения любых строк внутри размерности, необходимо установить флаг “Uniquely Identified”. Детерминантов "group by" может быть множество, но детерминант по уникальному ключу, как правило, только один единственный. Детерминант "uniquely identified" по определению содержит все неключевые столбцы как атрибуты, и автоматически создаётся во время импорта таблицы, если получается его обнаружить (т.е. в таблице БД имеется первичный ключ или индексы).
Секция Key содержит колонки, которые однозначно определяют уровень. В идеале, это одна колонка, но иногда требуется больше; например, если Год и Месяц (1-12) находятся в отдельных колонках. Итак, ключ - это любые колонки, которые нужны чтобы однозначно обозначить уровень.
Исходя из размышлений выше, настройки будут выглядеть следующим образом:

Секция Attributes описывает все остальные колонки, которые относятся к уровню. Например на уровне month_id, такими являются: название месяца, дата начала месяца, количество дней в месяце (month name, month starting date, number of days). И, очевидно, что здесь отсутствуют элементы с более низкого уровня, такие как Дата или День недели (date, day-of-week).
Вообще говоря, порядок расположения детерминантов не соотносится с уровнями измерения. Однако, для понимания как строится запрос SQL, очень важно знать, что поиск колонок ведётся сверху-вниз. Если в отчёте используется Год, Квартал и Месяц, то группировка будет сделана по колонкам Year-key, Quarter-key и Month-key. Но если для отчёта нужны только Год и Месяц (без Квартала), то в секции "group by" ключ Quarter-key будет пропущен.

Сколько уровней требуется?

Насколько необходимы все четыре уровня детерминантов? Имейте в виду, что детерминанты используются для присоединения ко всем уровням измерения, кроме самого нижнего. В примере выше, присоединяемся на уровне месяц (по ключу month_id). Пока отсутствуют дополнительные связи на уровнях года и квартала, строго говоря, указывать эти детерминанты необходимости нет. Помните, что год и квартал однозначно определяются ключом month_id, поэтому их необходимо добавить на уровень месяца как атрибуты:

Заключение

Следуя этим простым правилам, Cognos создаст следующий запрос SQL. Выделенный кусок кода создан на основе настроек детерминантов. Обратите внимание, каким образом "схлапывается" Month_ID; для обеспечения уникальности значения уровня используется функция min. Cognos-параноик и недостотачно доверяет чистоте входящих данных, чтобы делать просто SELECT DISTINCT. Второе выражение 'group by' это обычное агрегирование, необходимое для отчёта. Так как связь теперь происходит корректно (каждый факт соединён только с одной записью размерности), в отчёт попадают правильные цифры.