Настройка OLAP-представления при наличии нескольких размерностей с датами

[a.volodina (Анна Володина)]

Краткое описание отчета:
Параметр, задаваемый пользователем - дата.
Данные должны отображаться в разрезе РФ, округов, субъектов РФ.
Показатели - количество граждан льготной категории.

Каждая запись в таблице фактов имеет 4 даты - дата включения, дата исключения, дата получения, дата отмены.

Цель:
Выбрать из таблицы фактов записи, удовлетворяющие следующим условиям:
Дата включения меньше или равна дате, заданной в параметрах
И Дата исключения больше или равна дате, заданной в параметрах
И Дата получения меньше или равна дате, заданной в параметрах
И Дата отмены больше или равна дате, заданной в параметрах.

Настройки OLAP-представления:

Создано тестовое OLAP-представление с одной мерой. Размерностью по РФ, субъектам на оси строк.
На ось фильтров добавляется:

1. Иерархия по дате включения с формулой
   [frllo_include_date].[frllo_include_date_ier].Levels(1).Members
   и с фильтром:
   

При этом время ожидания получения результата несколько секунд.

2. Добавляем иерархия по дате исключения с формулой
   [frllo_exclude_date].[frllo_exclude_date_ier].Levels(1).Members

и с фильтром:

При этом время ожидания - бесконечно долгое, в итоге ошибка по тайм-ауту. До добавления фильтрации по датам получения и отмены дело не доходит.

Вопрос:
Как корректно обработать указанные условия?

Дополнительная информация:

1. Схема - https://alpha.rt-eu.ru/

2. Путь к OLAP-представлению - Администрирование/OLAP/ФРЛЛО_03_тест

3. Объем данных на тестовой схеме:
   Таблица фактов - 2,5 млн записей
   Хранилище с датами - 31 тыс. записей

   Объем данных на продуктовой схеме:
   Таблица фактов - 26-30 млн записей
   Хранилище с датами - 70 тыс. записей

4. Для даты включения, даты исключения, даты получения и даты отмены созданы отдельные размерности, но все на одном хранилище.

5. mdx-запрос:

WITH
SET [default_dimRegion_region_rf] AS {[dimRegion].[region_rf].DefaultMember}
SET [default_frllo_include_date_frllo_include_date_ier] AS FILTER([frllo_include_date].[frllo_include_date_ier].Levels(1).Members, IIF([frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“member_type”) = 2, 1 < 0, (DateValue([frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“frllo_include_date_atr”)) = DateSerial(2021,3,23) OR DateValue([frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“frllo_include_date_atr”)) < DateSerial(2021,3,23))))
SET [default_frllo_exclude_date_frllo_exclude_date_ier] AS FILTER([frllo_exclude_date].[frllo_exclude_date_ier].Levels(1).Members, IIF([frllo_exclude_date].[frllo_exclude_date_ier].CurrentMember.properties(“member_type”) = 2, 1 < 0, (DateValue([frllo_exclude_date].[frllo_exclude_date_ier].CurrentMember.properties(“frllo_exclude_date_atr”)) = DateSerial(2021,3,23) OR DateValue([frllo_exclude_date].[frllo_exclude_date_ier].CurrentMember.properties(“frllo_exclude_date_atr”)) > DateSerial(2021,3,23))))

SELECT
{
[Measures].[count_invalidy]
} DIMENSION PROPERTIES [CHILDREN_CARDINALITY],[MEMBER_TYPE] ON COLUMNS,
NON EMPTY {
HIERARCHIZE(union([default_dimRegion_region_rf],
{{[dimRegion].[region_rf].children}}))
} DIMENSION PROPERTIES [CHILDREN_CARDINALITY],[MEMBER_TYPE] ON ROWS

from [frllo_cube_report_03]
WHERE FILTER([default_frllo_include_date_frllo_include_date_ier], COUNT(INTERSECT([default_frllo_include_date_frllo_include_date_ier], DESCENDANTS([frllo_include_date].[frllo_include_date_ier].CurrentMember))) = 1)*FILTER([default_frllo_exclude_date_frllo_exclude_date_ier], COUNT(INTERSECT([default_frllo_exclude_date_frllo_exclude_date_ier], DESCENDANTS([frllo_exclude_date].[frllo_exclude_date_ier].CurrentMember))) = 1)
CELL PROPERTIES VALUE,FORMATTED_VALUE,FORMAT_STRING[/details]

[v.chilyasov (Василий Чилясов)]

Может придумать некую интегральную дату на основании приведенного условия сочетания дат, в данные ее добавить, а на ней уже условие строить?

[a.volodina (Анна Володина)]

Так не получится, наверное, к сожалению, так как на входе пользователь может задать любую дату. И в зависимости от нее должны накладываться условия по остальным 4-м датам.

[kalyanov (Дмитрий Кальянов)]

Потенциально добавление дат включения/исключения и получения/отмены дает комбинаторный взрыв (в кубе получается N^4 ячеек, где N - количество дней). Скорее всего, это и является причиной медленной работы.

У меня есть несколько предложений:

1. использовать SQL-запрос для получения данных вместо MDX. Например, запрос может иметь вид

select
coalesce('  ' || region, okrug, 'Российская федерация'),
count(*) filter (where lgota = 1) as count_inv,
count(*) filter (where lgota = 2) as count_14vzn,
...
from facts
group by rollup (okrug, region)
order by region nulls first, okrug nulls first

(rollup даст агрегацию по промежуточным уровням)

2. В качестве таблицы фактов использовать материализованное представление с такой структурой, которая бы исключила использование фильтров-неравенств в OLAP-запросе. Например, представление со структурой “Регион, Тип льготы, Дата, Количество”.

[a.volodina (Анна Володина)]

По п1 - изначально как раз остановились на варианте с SQL-запросом к БД. Запрос немного другой, с union. Но здесь немного другая проблема - т.к. данных много, то не всегда запрос отрабатывает за отведенное время. Поэтому хотели рассмотреть вариант реализации на олапе.
Попробую предложенный вариант с rollup, возможно, будет быстрее.

По п2 - Т.к. пользователь может задать любую дату (из первоисточника приходят данные с датами, начиная с 1900г и до 2070), то по идее я должна иметь агрегированные значения на любую из дат в указанном интервале. В принципе итоговое кол-во записей получится не такое и большое - 170лет*365 дней * 85 регионов - около 5-6 млн. Надо подумать.

Дима, спасибо за предложения, рассмотрим!

[kalyanov (Дмитрий Кальянов)]

Возможно, что для ускорения можно добавить индексы:

1. для фильтра по дате хорошие результаты обычно показывает использование оператора daterange && date вместе с gist-индексом по интервальному типу данных. Можно добавить отдельные индексы для обоих интервалов дат, и оптимизатор СУБД должен объединить использование этих индексов через bitmap or.
2. Возможно, что для группировки нужен индекс по округу и региону.

[kalyanov (Дмитрий Кальянов)]

Еще один совет, который применим и для MDX/OLAP, и для SQL-запроса:
т.к. фильтр на диапазон дат одинаковый, то вместо двух диапазонов дат достаточно хранить один, являющийся пересечением исходных двух диапазонов.

[kildushova (Мария Кильдюшова)]

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

1. Для обеспечения более удобной работы пользователя с деревом элементов иерархии в UI.
   1.1. В сгруппированных логически элементах легче ориентироваться и выбрать нужный,
   1.2. Наличие нескольких уровней в дереве позволяет загружать данные элементов иерархии асинхронно - порциями по мере разворачивания узлов.
   При большом количестве элементов это очень важно, т.к. может в десятки раз снижать время загрузки дочерних элементов заданного узла.
2. Для обеспечения возможности получать агрегаты на разных уровнях при применении иерархии на оси строк столбцов.
3. Наличие группирующих уровней дает возможность применять оптимизации при выполнении mdx запросов за счет группировки элементов уровня N в элементы уровня N-1.
   На оси фильтров, в частности, такая оптимизация значительно снижает количество кортежей >> количество предикатов sql >> расходы на их обработку.

Рассмотрим текущий пример.

В данном случае имеем 4 временные иерархии:

• Дата включения,
• Дата исключения,
• Дата получения,
• Дата отмены,

содержащие единственный уровень - Дата, на котором 31866 элементов.

1. Вычислим результирующее количество кортежей на оси фильтров при значении даты в параметрах = 23.03.2021:

• Условию “Дата включения меньше или равна дате, заданной в параметрах” удовлетворяют
  18829 элементов иерархии Дата включения.
• Условию “Дата исключения больше или равна дате, заданной в параметрах” удовлетворяют
  13038 элементов иерархии Дата исключения.
• Условию “Дата получения меньше или равна дате, заданной в параметрах” удовлетворяют
  18829 элементов иерархии Дата получения.
• Условию “Дата отмены больше или равна дате, заданной в параметрах” удовлетворяют
  13038 элементов иерархии Дата отмены.

Результирующее количество кортежей на оси фильтров: 18829 * 13038 * 18829 * 13038 = 245 492 502 * 245 492 502 = … (тут много цифр)

Результат:
Время обработки 245 492 502 кортежей при добавлении второй иерархии на ось фильтров не уложилось в таймаут, что в целом ожидаемо.

2. Рассмотрим как изменится ситуация, если для каждой иерархии добавить уровни год и месяц.

Применим группировки элементов временных иерархий.
Пример формулы в настройках иерархии на оси фильтров, обеспечивающей выполнение условия “Дата включения меньше или равна дате, заданной в параметрах”:

Mdx

FILTER(descendants([frllo_include_date].[frllo_include_date_ier].[All frllo_include_date_iers]),
([frllo_include_date].[frllo_include_date_ier].CurrentMember.Properties(“LEVEL_NUMBER”) = 1
AND [frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“nyear”) < CAST(Format({Дата}, 'yyyy') AS INTEGER)) OR ([frllo_include_date].[frllo_include_date_ier].CurrentMember.Properties("LEVEL_NUMBER") = 2 AND [frllo_include_date].[frllo_include_date_ier].CurrentMember.properties("nmonth") < CAST(Format({Дата}, ‘MM’) AS INTEGER)
AND [frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“nyear”) = CAST(Format({Дата}, 'yyyy') AS INTEGER)) OR ([frllo_include_date].[frllo_include_date_ier].CurrentMember.Properties("LEVEL_NUMBER") = 3 AND [frllo_include_date].[frllo_include_date_ier].CurrentMember.properties("nyear") = CAST(Format({Дата}, ‘yyyy’) AS INTEGER)
AND [frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“nmonth”) = CAST(Format({Дата}, 'MM') AS INTEGER) AND (DateValue([frllo_include_date].[frllo_include_date_ier].CurrentMember.properties("frllo_include_date_atr")) = {Дата}
OR DateValue([frllo_include_date].[frllo_include_date_ier].CurrentMember.properties(“frllo_include_date_atr”)) < ${Дата}))
)

Вычислим результирующее количество кортежей на оси фильтров при значении даты в параметрах = 23.03.2021 с учетом группировки элементов:

• Условию “Дата включения меньше или равна дате, заданной в параметрах” удовлетворяют
  106 элементов уровня Год + 2 элемента уровня Месяц + 23 элемента уровня День = 131 элементов иерархии Дата включения.
• Условию “Дата исключения больше или равна дате, заданной в параметрах” удовлетворяют
  150 элементов уровня Год + 9 элементов уровня Месяц + 9 элементов уровня День = 168 элементов иерархии Дата исключения.
• Условию “Дата получения меньше или равна дате, заданной в параметрах” удовлетворяют
  106 элементов уровня Год + 2 элемента уровня Месяц + 23 элемента уровня День = 131 элементов иерархии Дата получения.
• Условию “Дата отмены больше или равна дате, заданной в параметрах” удовлетворяют
  150 элементов уровня Год + 9 элементов уровня Месяц + 9 элементов уровня День = 168 элементов иерархии Дата отмены.

Результирующее количество кортежей на оси фильтров: 131 * 168 * 131 * 168 = 22 008 * 22 008 = 484 352 064

Результат:
Время выполнения запроса при 22 008 кортежах на оси фильтров при добавлении второй иерархии на ось фильтров:
4 мин на холодном кэше,
23,52 сек на прогретом кэше.

Вывод:

В текущем сценарии более 2-х иерархий с таким размером выборки на ось фильтров положить уже не удастся по следующим причинам:

1. С добавлением каждой иерархии на ось фильтров мы получаем экспоненциальный рост количества результирующих кортежей (пропорционально растет время выполнения запроса).
2. Результирующие кортежи оси фильтров возвращаются на клиент в составе ответа на mdx запрос.
   Размер ответа линейно зависит от количества кортежей на оси фильтров.
   На практике при текущей реализации формата и способа обработки ответа корректно могут быть обработаны до 400 000 - 500 000 трехмерных кортежей в составе ответа.
   Но не 484 352 064.

Таким образом для решения исходной задачи без доработок продукта я вижу следующие варианты в порядке приоритетов:

1. Решить исходную задачу с применением sql запроса - тип источника Реестр.
2. Если вариант 1. не применим по каким-то причинам, то придется пересматривать способ решения исходной бизнес задачи, возможно, в том числе способ хранения и заполнения данных. По результатам анализа вернуться либо к п.1, либо к варианту с источником на OLAP представлении.

И независимо от выбранного способа реализации данного отчета необходимо повышать приоритет задач в продукте, направленных на улучшение функционала отчетов для закрытия подобных сценариев.

[a.volodina (Анна Володина)]

В данный момент остановились все-таки на варианте реализации с помощью SQL, на реестре.
Избавились от двух дат (то есть, если понадобится, то можно будет продолжить работу и с OLAP-представлением).

Коллеги, еще раз большое спасибо за консультации!