Всем привет.
Мы продолжаем цикл публикаций о том, как наша BI-платформа «Форсайт» работает с данными. В этой статье мы бы хотели продолжить рассказ про виртуализацию данных. И рассказать о том, как с помощью различных «магических» приемов трансформации измерений в кубе можно адаптировать многомерные данные в совершенно новые структуры. Раскрыть количественную статистику некоторых замеров производительности и скорости быстродействия такой виртуализации. Показать, как мы это тестируем. Провести сравнение подходов формирования витрин данных с помощью технологии BI и СУБД. За всеми этими подробностями добро пожаловать под кат!
Данная статья является продолжением цикла статей про связку «BI+Data Lake». Предыдущие части:
Часть 1. Технология гетерогенных ROLAP-кубов
Часть 2. Виртуализация данных
В прошлой статье мы начали рассказ о том, почему при работе связи «BI+Data Lake» важна виртуализация данных. Определили две основные сложности трансформации многомерного куба к «шаблонным» отчетам, а именно:
необходимость формирования «гибкой» и часто слабо структурированной шапки/боковика отчета по заданному шаблону;
объединение в шаблоне нескольких разноразмерных источников данных.
Второй пункт мы уже рассматривали в прошлой статье, когда говорили о виртуальных кубах и денормализации исходных данных. Сегодня мы хотели сосредоточится на первом пункте и рассказать о возможностях трансформации кубов через их измерения (которые в итоге как раз и влияют на внешний вид отображения нашей таблицы в отчёте).
1. Измерения – снова из основ по «Кимбаллу»
Чуть менее 10 лет назад Kimball Group опубликовали документ «Kimball Dimensional Modeling Techniques», в котором финализировали основные принципы многомерного моделирования данных для BI-систем. В нем отдельное место уделяется вопросам измерений (Dimensions). Правда, в документе идет речь о наборе требований и рекомендаций к таблицам-справочникам и их связи с таблицами фактов.
Когда переходим от плоского реляционного представления данных к многомерному, то измерения у них тоже имеют важное значение. Визуальное представление любого куба в первую очередь определяется расположением этих измерений в Pivot (по строкам, по столбцам, фиксированные), а также структурой элементов этих измерений (сортировка, иерархия, фильтрация и т.п.). Но это только верхушка айсберга. Измерения в кубе могут иметь правила внутреннего соотношения между собой. Взаимозаменять или взаимодополнять друг друга. Одно измерение может управлять составом элементов в другом. Несколько измерений могут иметь взаимоисключающие комбинации своих элементов, разные варианты их представления, иерархии и порядка.
Все эти условия как раз и отражаются в шаблонах документов. И они с помощью BI должны быть воспроизведены в «Data Reporting» (об этом мы говорили в прошлой статье). И чем «гибче» возможности BI-платформы по работе с этими измерениями, тем элегантнее можно подготовить отчет требуемой структуры или шаблона. Но давайте обо всем по порядку.
2. Методы объединения нескольких измерений в одно общее
Первая, и наверное, самая распространенная задача – это объединение нескольких измерений в одно общее (как с сохранением иерархии, так и в линейный список). Например, получить единое виртуальное измерение «продукт-контрагент». Но не декартово произведение всех их элементов, а только «значимые» пары, которые имеют смысл (наличие долгосрочного контракта, реальные поставки в этом месяце или что-то еще). Правила связи тут играют первостепенную роль.
2.1. Составной справочник, или как несколько измерений иерархически соподчинить друг другу
Если все же нужно автоматически объединить декартово произведение выбранных элементов у нескольких справочников – это можно сделать в платформе «Форсайт» с помощью составного справочника (CompoundDimension). О таком измерении мы уже немного рассказывали в прошлой статье про виртуальные кубы. Для составного измерения нужно указать все необходимые измерения-источники, а также их порядок по отношению друг к другу (в зависимости от Проект –> Инвестиционный объект». Для системы учет кадров – «Город –> Филиал –> Сотрудник» и т.п.">логики их соподчиненности). Затем выбрать состав необходимых элементов в каждом измерении-источнике. На их основе платформа самостоятельно сформирует общую иерархию в CompoundDimension. Как декарт всех этих выбранных элементов. При этом иерархия и порядок элементов определяются платформой автоматически, и менять их для отдельных элементов нельзя.
Зачем использовать составной справочник, а не табличный из нескольких связанных dataSet’ов?
Важно отметить, что построение составного справочника полностью выполняется средствами BI-платформы и учитывает все заданные правила бизнес-логики у измерений-источников. В том числе ролевую модель доступа. Если, например, одному пользователю доступно только десять публичных элементов в измерении-источнике, а другому еще 50 конфиденциальных, то и в составном измерении они смогут увидеть разное количество элементов (первый 10, а второй 60). В этом заключается основной принцип виртуализации для составных справочников – виртуализируются данные из уже сформированных в BI метаобъектов. Действительно, аналог комбинированного справочника можно было бы реализовать и на основе обычного справочника из нескольких блоков, связав их с разными таблицами из БД (об этом мы рассказывали в предыдущей статье). Но это был бы самостоятельный новый метаобъект, который получал бы данные элементов на основе прямого обращения в БД без какой-либо виртуализации. Соответственно, все права доступа для него нам пришлось бы настраивать повторно. А виртуальный составной справочник делает это автоматически через посредников (измерения-источники).
Также при настройке нескольких блоков в табличном справочнике нам обязательно нужно было бы указать их иерархические связи между собой. Иными словами, как элементы из второго dataSet’а будут подчинены элементами-родителями из первого dataSet’а. Для этого нам потребуются или отдельные соответствующие поля в физических таблицах, или нужно будет как-то формировать декарт этих записей через дополнительный sql-запрос. Для составного справочника такой сложности не существует. BI-платформа автоматически свяжет все элементы по принципу «каждый с каждым» или будет использовать разные правила фильтрации (об этом читайте далее).
2.2. Метаиерархия по наличию фактических данных
Составное измерение автоматически позволяет «склеить» несколько измерений, но результатом такого объединения является декартово произведение элементов. Это не всегда подходит для случаев с разрежёнными матрицами куба. Если необходимо оставить только элементы с данными, для этого в платформе «Форсайт» реализовано специально измерение метаиерархии.
Каждый листовой элемент такого измерения связан с определенной точкой данных. Но для удобства навигации в нем реализована иерархия. Каждый узловой элемент-родитель в иерархии – это элемент из измерений-источников. Уровни в измерении метаиерархии определяются последовательностью объединяемых исходных измерений. Меняя их местами, пользователь сможет сам управлять структурой иерархии (например, что будет первым, территории или показатели). «Тупиковые» ветки иерархии, где нет данных, просто сразу отфильтровываются. Таким образом, появление новых данных и изменение порядка следования объединяемых справочников будут самоорганизовывать такое измерение метаиерархии.
2.3. Атрибутивная фильтрация нескольких измерений в Pivot
Еще один способ фильтрации декарта – это устойчивые логические связи между элементами измерений-источников. Рассмотрим, например, куб с итогами спортивных соревнований на олимпийских играх. В такой куб входят измерения «спортсмены» и «спортивные дисциплины». Понятно, что декарт «спортсмен*дисциплина» не несёт смысловой нагрузки, а данные в кубе всегда будут сильно разрежённые (сложно представить, как гимнастки пойдут на соревнования по боксу). С другой стороны, один и тот же спортсмен в рамках одного вида спорта может участвовать в нескольких дисциплинах одновременно. Например, для спортивной гимнастики это кольца, параллельные брусья, перекладина и т.п.
В платформе «Форсайт» такие условно постоянные связи можно указать в атрибутах справочников. Связи могут быть как «один-к-одному», так и «один-ко-многим», в том числе и для дата-зависимых справочников (SCD2). Далее при работе с таким источником уже на уровне Pivot, можно указать попарные связки этих атрибутов для разных измерений (как одну такую связку, так и несколько). На основе этих связей платформа автоматически определит значимые комбинации элементов. Дополнительно Pivot позволяет использовать разные варианты объединения этих измерений в кросс-таблице: один общий слот или разные слоты под каждое измерение/уровень измерения. Для случая с одним общим слотом все измерения сгруппируются в одной колонке в общую единую иерархию.
2.4. Фильтр-куб для сокращения декартового произведения элементов составного измерения
Хорошо, когда между элементами измерений-источников существуют устойчивые и неизменные связи, которые можно определить в дополнительных атрибутах у этих справочников. Иная ситуация, когда эти связи всё время меняются. Например, нужно сформировать куб «Размер выручки» по отгрузке разной продукции разным контрагентам-покупателям. При этом структура объемов отгрузки на разные отчётные даты изменяется. И задать такие связи «продукция-покупатель» в атрибутах измерений уже нельзя, т.к. они не являются «устойчивыми» и все время будут устаревать.
В разные месяцы один и тот же продукт может быть отгружен разным контрагентам. Для таких случаев фильтрацию составного справочника можно выполнить с помощью внешнего фильтр-куба. Это чем-то похоже на случай с метаиерархией, только наличие данных проверяется не в том же кубе (размерность которого мы сокращаем), а во внешнем источнике, например, куб «Объемы отгрузки».
3. Модификация структуры элементов одного измерения
В рамках виртуализации данных часто возникает ситуация, когда необходимо не объединить измерения, а поменять их структуру в кубе. Это и состав элементов в измерении, и их порядок, и иерархия, и многое другое. Например, страны мира можно группировать по континентам, торгово-политическим союзам, степени экономического развития и т.п. Для таких случаев в платформе «Форсайт» доступны разные подходы. Важно одно, что такие трансформации в большинстве случаев не требуют физических изменений структуры данных в озере. Вся виртуализация происходят уже на стороне BI.
3.1. Группировка элементов измерения
Применение группировки позволяет изменять структуру справочника на основе заранее заданных правил. Эти правила могут как расширять, так и сокращать состав элементов:
При сокращении нужно заранее у справочника создать набор предопределенных групп элементов (DimElementGroup). Суть каждой такой группы – по определенным наборам правил (примитивам, GroupPrimitive) отфильтровать элементы измерения, оставив только часть из них. Примитивы могут быть разные:
- совершенно конкретные (перечисление элементов для фильтрации);
- автоматические (выбор всех дочерних элементов для определенного элемента-родителя и т.п.);
- абстрактные (выбрать все элементы определенного уровня или использовать программный скрипт для фильтрации элементов).
Например, можно настроить группу из двух примитивов: «элемент Россия» и «все подчиненные для элемента Приволжский федеральный округ». Если мы применим такую группу, то в измерении останется только узловой элемент «Российская Федерация», и ему будут подчинены 14 регионов ПФО. Иерархия элементов для группы (с учетом заданных правил фильтрации) автоматически корректно скорректируется. Например, при исключении второго уровня, все элементы третьего уровня автоматически станут подчиненными соответствующих элементов из первого уровня.Другое направление по модификации измерения – это группировка элементов по атрибутам (DimAttributeGroup). Она, наоборот, приводит к увеличению элементов в виртуальном справочнике, т.к. на основе уникальных значений атрибутов появляются новые группирующие элементы-родители. Это особенно актуально для длинных линейных справочников-реестров. Например, для справочника ЕГРЮЛ можно включить группировку по атрибутам: ОКВЭД, форма собственности, регион расположения и т.п. Тогда очень длинный линейный список автоматически трансформируется в иерархическое измерение, с которым станет гораздо удобнее работать.
3.2. Альтернативные иерархии
Группировка измерения автоматически фильтрует количество элементов или расширяет их иерархию. Но не может произвольно поменять порядок следования элементов или их родителей. На практике часто возникают ситуации, когда это необходимо. Выше уже приводили пример про страны мира и разные варианты их группировки (континенты, союзы, уровень развития). Для этого в платформе «Форсайт» можно одно измерение связать с другим измерением. Не имеет значения, что в двух измерениях будет разный первичный ключ, порядок элементов или иерархия. Главное, чтобы был набор одинаковых атрибутов, по которым можно однозначно связать логически одинаковые элементы в этих двух измерениях (например, классификационный код или мнемоника). Если такое правило связи есть, то одно измерение в любом кубе автоматически может быть заменено на другое. Технически связка осуществляется через индексы этих двух справочников.
С помощью таких связей у одного справочника можно формировать неограниченное количество альтернативных иерархий (IDimHierarchy). Они позволяют трансформировать представление данных в одном и том же кубе. Например, одна альтернатива – это справочник с географической иерархией стран по континентам. Другой – группировка по торгово-политическим союзам. Третий – по степени экономического развития. Четвертая – все эти три группировки вместе (тогда каждая страна в измерении повторяется три раза с разным ключом, но одинаковой мнемоникой). Переключая у справочника альтернативную иерархию, мы видим в кубе одни и те же данные, но в разной последовательности и группировке.
4. Календарные измерения
В большинстве BI-приложений работа с отчётными датами в многомерном источнике происходит по отдельным правилам. Платформа «Форсайт» для этих целей использует специальный тип измерения – календарное. Одно может быть трех типов:
Стандартный календарь (CalendarDimension). Для него не требуется создавать отдельных таблиц-справочников. Достаточно определить год начала и окончания действия календаря, а также набор необходимых уровней (год, полугодие, квартал и т.д.) На основе этой информации платформа сама сформирует виртуальное измерение. Причем оно будет содержать все даты нужной гранулярности (уровней) между годом начала и годом окончания действия календаря. Иерархия в нем будет полностью симметрична (от 1 января до 31 декабря для всех лет) и строго сбалансирована.
Пользовательский календарь (DimAsCalendar). При необходимости любое измерение платформы можно преобразовать в календарное. Для этого оно должно иметь определенную структуру. Такой подход востребован, когда в календаре нужно исключить некоторые даты (например, оставить только периоды утвержденной отчётности или сформировать ежегодный производственный календарь каждого сотрудника без выходных, праздничных и отпускных дней и т.п.). Или в таблице фактов поле с отчётной датой использует специализированный суррогатный ключ, который в чистом виде не поддерживается стандартным календарным измерением (пользовательский календарь позволит это сделать). Формирование такого «специализированного» календаря можно реализовать тремя способами:
- на основе реляционного источника БД (таблица или sql-запрос);
- с помощью программного скрипта;
- в виде заранее определенного фиксированного измерения (элементы которого сохранены вместе с метаданными конструируемого справочника).Составной календарь (CompoundCalendar). Позволяет объединить несколько календарных измерений с разными уровнями в один общий справочник. После объединения у него будет единая совместная динамика. При объединении учитывается несбалансированность и несимметричность пользовательских календарей. Такое объединение нужно при соединении нескольких кубов с разной динамикой в один общий виртуальный куб.
Какие преимущества дает использование календарного измерения?
Если в куб добавлено календарное измерение, то для таких данных в платформе можно использовать дополнительные функции:
агрегацию или дезагрегацию данных (например, хронологическое среднее);
лаги по датам с учетом уровней календаря и производные от них расчетные значения (например, % к аналогичному периоду, накопленным итогом с начала года);
оконные календарные функции (например, скользящая сумма диапазона дат);
методы экстраполяции и прогнозирования временных рядов: тренды, регрессия, ARIMA и т.п.;
во всех видах отчётности для выбора элементов календаря доступно два режима: обычный (выбор отдельных элементов как во всех измерениях) и диапазонный (выбор всех элементов определенных уровней между двумя указанными датами);
и многое другое.
5. Модификация структуры нескольких измерений многомерного источника (альтернативный авто-куб)
Иногда возникает ситуация, когда в BI-инструменте для многомерного куба нужно «подменить» сразу несколько измерений. Причем заменить раз и навсегда. Создать новый куб с новой структурой элементов, но в качестве источника данных должны использоваться сразу многомерные данные куба-источника (со своей бизнес-логикой), а не таблица БД. Например, все данные о социально-экономическом развитии регионов России из одного большого гиперкуба источника нужно разделить на части: по рубрикам (демографическое развитие, производство, финансы и т.п.) и по принадлежности субъектов РФ к федеральным округам. Тогда в каждом таком альтернативном микрокубе будут «урезанные» измерения показателей и территорий. Но каждый из них будет получать данные из одного и того же большого гиперкуба.
Исключение измерений и агрегация (как это было в кубе-представлении, о нем мы рассказывали в прошлой статье) здесь не решает задачу. Нужно не исключение измерений, а трансформация многомерного источника. Для этого в платформе «Форсайт» можно создать автоматический куб (AutoCube) и указать для него куб-источник. Затем между измерениями у куба-источника и нового авто-куба необходимо настроить связи. Они могут быть одного из трех типов: связь по порядковому номеру элементов, связь по идентификатору (ключу) элементов или связь по любому индексу измерений. Альтернативный авто-куб умеет как получать данные из куба-источника, так и обратно сохранять все изменения.
Что еще можно получить, связав измерения куба-источника и авто-куба?
При мэппинге измерений источником данных для авто-куба становится не реляционный dataset (как в случае с ROLAP-кубом), а уже многомерная матрица куба-источника. Связи с полями таблицы фактов здесь заменяют связи между измерениями. В общем случае такой подход можно использовать не только для замены измерений, но и для корректировки их количества (увеличения или уменьшения). Если размерность куба-источника и авто-куба различаются или какие-то измерения не связаны, то:
для непривязанных измерений куба-источника нужно зафиксировать элементы (один или несколько, тогда будет использоваться Matrix Aggregator);
для непривязанных измерений авто-куба информация будет «размножаться» по всем его выбранным элементам.
6. Повышение размерности куба (модель «снежинка»)
Другой случай, когда одно измерение многомерного источника может быть разделено на несколько. Это актуально, когда ROLAP-куб построен на модели данных «снежинка». Например, в таблице фактов есть поле «Сотрудники». Оно связано с одноименной таблицей-справочником, которая в свою очередь связана с таблицами «Департамент/Отдел» и «Город трудоустройства». Тогда в справочник сотрудников можно добавить два этих атрибута (отдел и город). Платформа «Форсайт» позволяет на их основе формировать для измерения атрибутивные иерархии (IDimAttributeHierarchy).
В отличие от альтернативной иерархии, такая иерархия не формирует иной вариант представления справочника, а разделяет его на несколько измерений. Элементы каждого нового отделенного измерения формируются на основе уникальных значений этих атрибутов. Т.е. один справочник сотрудников может быть автоматически разделён в матрице куба на два: измерения «Департаментов» и «Городов». При построении Pivot можно активировать любую комбинацию таких атрибутивных иерархий. Разделив исходный справочник, можно уже по-другому задавать правила фильтрации данных. Например, автоматически получить список всех сотрудников на пересечении выбранных отделов и городов.
Пример из практики
Интересный практический пример атрибутивной иерархии можно рассмотреть для справочника сотрудников. Но задача уже звучит так: «Получить сумму по сотрудникам в возрасте от xx до yy лет». Сложность в том, что возраст – это изменяемая величина и на каждый день или месяц он разный. Практическая реализация тут потребует несколько шагов. Первым шагом нужно в справочник сотрудников добавить ещё один атрибут – «Дата рождения». С ним все понятно и просто. Когда добавляем новый элемент в справочник, то нужно ввести значение этого атрибута. Это значение будет всегда постоянно, и больше уже не поменяется (если введено без ошибок). Второй шаг – делаем справочник дата-зависимым и добавляем в нем параметр «Дата актуальности». Третьим шагом создаем в справочнике вычисляемый атрибут «Возраст сотрудника» и задаем формулу его расчета. Формула использует значение параметра и на каждую дату актуальности возраст будет рассчитываться. Платформа «Форсайт» позволяет реализовать вычисления как с помощью СУБД (добавляя текст формулы или PL/SQL процедуры в sql-скрипт), так и с помощью самой платформе на BI сервере.
Далее, при каждом открытии справочника, в значении параметра указывается нужная дата актуальности. На ее основе вычисляется актуальный на эту дату промежуток всех возможных возрастов. Далее, указав в Pivot для атрибута «Возраст» признак атрибутивной иерархии, мы получаем доступ к этому измерению. Выбрав возраст из нужного нам диапазона xx-yy, куб уже автоматически может определить соответствующий список сотрудников. При этом в зависимости от выбранной даты список сотрудников будет разным.
7. Ведущие и ведомые измерения в кубе
Часто между измерениями в кубе существует связь «ведущий-ведомый». В первую очередь это актуально для справочников, параметризованных от даты. Когда на 01.01.2015 г. нужно увидеть один список сотрудников, а на 01.01.2022 г. уже другой. Для всех таких случаев в платформе «Форсайт» внутри куба можно настроить правила управления между измерениями. Необходимо указать ведущее (управляющее) измерение, значения выбранных элементов которого автоматически будут передаваться в параметр ведомого (управляемого) измерения. В платформе такой механизм называется CubeSelectionControl. С его помощью можно, например, связать календарное измерение с измерением сотрудника. Тогда если изменять в календаре у OLAP-куба состав выбранных дат, то автоматически будет изменяться и состав сотрудников.
Стоит отметить, что выбор элементов в управляющих измерениях куба может быть как единичным, так и множественным. А вот внутренняя логика параметров у управляемого измерения не всегда может корректно принимать такие множественные значения. Для этого при связи параметров в платформе реализовано три стратегии поведения: передача значений всех выбранных элементов (All), первого выбранного элемента (First) или последнего (Last). Также в значение параметра может быть передан любой атрибут управляющего измерения: key, name, datein, dateout, mnemonica и др. Кроме прямой связи «атрибут-параметр» может использоваться произвольная скрипт-функция, которая будет применять сложную логику мэппинга между управляющим и управляемым измерениями. Например, для стратегии «All» использовать только те элементы, у которых datein старше 01.01.2021 г., а city={Москва, Санкт-Петербург}.
8. Скорость и производительность для виртуализации данных в BI
В теории вроде все звучит хорошо. Используем только единственную неизменяемую структуру физического источника в СУБД (одна или несколько таблиц фактов) и над ней формируем неограниченное количество виртуальных представлений в BI (виртуальные справочники, виртуальные кубы, разные альтернативные варианты представления и многое другое). Но сразу же возникает вопрос: какая цена производительности и времени быстродействия такого подхода? Выигрывает или проигрывает он перед денормализованными представлениями, созданными средствами только СУБД. Может, проще все сделать сразу на уровне озера, а в BI «отдавать» уже на 100% готовые витрины, которые останется только разместить в отчете «As Is»?
8.1. Условия нагрузочного теста
Для ответа на эти вопросы мы хотели продемонстрировать некоторые результаты нагрузочного тестирования для «сложного» шаблонного отчёта с информацией о инвестиционных проектах, который рассматривался в предыдущей статье. Для целей тестирования структура этого отчёта была изменена так, чтобы задействовать почти все из приёмов по BI виртуализации данных в платформе «Форсайт».
Какие приемы виртуализации тестировались?
использовалось 4 разноразмерных куба-источника и их денормализация в виртуальном кубе, в отчёте присутствовал только один плоский срез данных (объединяющий эти 4 источника);
боковик таблицы состоял из трех измерений (проекты, статьи, валюта), а шапка являлась комбинацией нескольких частных измерений разных источников;
в шапке таблицы отчёта попеременно чередовались разные фрагменты кубов-источников, причем один и тот же куб повторялся несколько раз (в начале, в конце и в середине таблицы);
была задействована атрибутивная фильтрация между измерениями «Статьи расходов» и «Валюта» в боковике таблицы (для OPEX используем только RUB, а для других трех статей доступна еще и валютная составляющая в USD/EURO);
иерархия боковика была расширена атрибутивной группировкой для измерения «Проекты» по значениям атрибутов «Программа» и «Ответственное подразделение»;
для горизонта планирования в плановых затратах применялся пользовательский календарь;
использовался Matrix Aggregator для суммирования квартальных и годовых финансовых затрат (в таблице БД хранились только месячные данные);
для одного из вариантов тестирования виртуальный куб фиксированной структуры был определен как источник для авто-куба, а в авто-кубе в свою очередь использовалась альтернативная иерархия (чтобы точно воспроизвести структур элементов в шапке таблицы отчета в соответствии с шаблоном).
В отчёте были настроены два параметра управления: список инвест. проектов и версия/сценарий планирования. При тестировании нагрузки версия/сценарий была зафиксирована (с ее помощью мы только «размножали» эталонный объем информации в исходных dataSet’ах, тем самым увеличивая объемы БД). А вот количество задействованных проектов изменялось. Тестирование проходило итеративно (всего 10 повторяющихся итераций). В каждой итерации фиксировались результаты для 50 шагов. На каждом шаге количество проектов в отчёте увеличивалось на одну сотню (1 шаг – 100 случайных проектов, 2 шаг – 200 проектов и т.п.) При увеличении шага возрастало общее количество строк в отчёте и объем подгружаемых из БД данных. Максимальное количество выбранных проектов на 50-м шаге тестирования было 5 тысяч (из 10 тыс. доступных в измерении), что соответствовало отчёту с объемом в 55 тыс. строк. Количество колонок в таблице отчёта было на всех шагах фиксировано – 62 позиции.
Данные для тестирования были синтетические и заполняли все доступные для ввода ячейки (с учетом денормализации, см. первый рис. в разделе 5 из прошлой статьи). Использовались разные типы данных: целые и вещественные числа, строки и даты. Максимальное количество заполненных ячеек на пике тестирования составило 2,3 млн. точек. Понятно, что такой тест был избыточным и в реальных условиях объемы данных (и время открытия отчёта) будет намного меньше, т.к. 100% всех доступных ячеек в подобных отчётах заполнено быть не может. Но мы хотели понять максимум. И вот что у нас получилось.
8.2. Результаты тестирования
В первую очередь, этим сценарием мы стремились проверить производительность виртуализации в оперативной памяти на сервере BI. Тестировались два подхода:
виртуальный куб с фиксированной структурой данных;
изменяемый виртуальный куб (Multi-OLAP) c формированием виртуального среза в отчётности «на лету».
Оба виртуальных куба через кубы-источники получали данные из четырех исходных нормализованных таблиц фактов (это была тестовая БД):
основные характеристики инвестиционных проектов (в таблице было 10 тыс. записей);
показатели эффективности проектов в разрезе 100 версий/сценариев (8 млн. записей);
фактическое финансирование проектов по направлениям затрат в месячной динамике за 10 лет (26,4 млн. записей);
плановое финансирование проектов в разрезе 100 версий/сценариев (66 млн. записей).
Дополнительно в результаты тестирования мы добавили замеры скорости такой денормализации данных только средствами СУБД. Для этого были созданы sql-представления, которые полностью повторяли структуру шаблона отчёта (шапки и боковика). Цель была простая – сравнить виртуализацию на BI и в СУБД. В БД использовались представления двух типов: «широкие» и «длинные». С точки зрения способа получения данных, как для «длинных», так и для «широких» витрин рассматривались два подхода: материализация запроса (хранимая витрина) или прямой sql-запрос к первичным данным при каждом обращении. Для разворота широких витрин мы не использовали специфичных sql-команд из разных СУБД. Ограничились только join, union и group by, но настроили два варианта «разворота» широкой витрины из нормализованных исходных данных (см. рисунок ниже).
Два варианта sql-запроса для разворота в широкую витрину
Суть первого варианта разворота в широкую витрину заключалась в том, что каждая колонка отчета формировалась отдельным подзапросом из исходной таблицы, а их объединение происходило через Join…On. А полная структура боковика отчета была сохранена в отдельную таблицу БД – LeftHeader. Это имитировало атрибутивную фильтрацию измерений в BI. Итоговый запрос выглядел примерно так:
select LH.dim1, LH.dim2,… T1.val, T2.val, … from
LeftHeader LF where (%условия фильтрации проектов для шагов тестирования%)
Left Join
-- колонка отчета 1 (данные из 1-ой исходной таблицы)
Table1 T1 on LH.dim1=T1.dim1 and …
Left Join
-- колонка отчета 2 (данные из 1-ой исходной таблицы)
Table1 T2 on LH.dim1=T1.dim1 and …
……………
Left Join
-- колонка отчета n (данные из 2-ой исходной таблицы)
Table2 TN on LH.dim1=TN.dim1 and …
……………
Второй вариант «разворачивал» каждую исходную таблицу через механизм Group By… и фильтровал поля через условия Case When…Then…. Затем четыре развернутых исходных таблицы и таблица LeftHeader тоже объединялись через Join…On. В общем виде это выглядело вот так:
select LH.dim1, LH.dim2,… T1.val1, T1.val2,… T2.val1, T2.val2 … from
LeftHeader LF where (%условия фильтрации проектов для шагов тестирования%)
Left Join
(
select
dim1, dim2,…
max(case when … then VAL end) as Val1 -- колонка отчета 1
sum(case when … then VAL end) as Val2 -- колонка отчета 2
………
from
Table1
where
%дополнительные общие условия фильтрации таблицы фактов% group by dim1, dim2,…
) T1 on on LH.dim1=T1.dim1 and …
Left Join
(………) T2 on ……… Мы сознательно не использовали специализированные инструменты разных СУБД по развороту таблиц (CROSSTAB для PostgreSQL, CUBE/PIVOT для Oracle и MS SQL Server и др.) Данный тест мы сейчас проходим на разных СУБД и не хотелось использовать вендоро-специфичных функций, а ограничиться только универсальными. Дополнительно этим экспериментом мы пробовали оценить, как лучше в BI-платформе формировать виртуальные данные. Или использовать универсальный конструктор для СУБД и генерировать из BI подобные «сложные» запросы (но тут остро вставал вопрос мульти-независимости sql-скриптов и как раз нужно смотреть в сторону универсальных команд). Или же формировать виртуальные данные в оперативной памяти BI сервера из исходных многомерных матриц. В итоге для большинства случаев работа с многомерными объектами показывала более высокую производительность. Плюс она автоматически решала вопрос гетерогенных источников (используя ROLAP-кубы и IMatrix, о них мы говорили в первой статье).
В итоге было сформировано 7 кубов. На их основе было созданы семь полностью одинаковых отчётов. Кросс-таблица, оформление, количество строк/столбцов и т.п. – всё было одинаковое. Разница была только в способе получения данных в кубах. Где-то использовались только возможности СУБД (ROLAP-кубы), где-то виртуализация в оперативной памяти BI-сервера (виртуальные кубы и виртуальные измерения).
Сравнение скорости работы всех семи вариантов представлены на графике ниже. Каждая линия – это результаты усредненного тренда для всех 10 тестов по каждому из семи кубов. Они показывают общую динамику скорости работы для каждой из тестируемых технологий. Дополнительно отчёты аналогичного объема были преобразованы в xlsx-файлы и зафиксировано время их открытия с помощью MS Excel. Этот 8 вариант был больше для информации. Для понимания, за сколько по времени подобные отчёты открываются в статичном виде (вообще без использования СУБД).
Обобщенные результаты тестирования и характеристики для сравнения представлены в таблице ниже. Результаты мы рассматривали с точки зрения следующих критериев:
быстродействие, которое определялось по общему времени открытия отчёта;
«оторванность» денормализованных данных куба от исходных таблиц БД, для случая изменения и актуализации исходной информации;
эффективность и оптимальность хранения данных в СУБД (потребление аппаратных ресурсов и др.);
простота модификации виртуальной структуры (добавление новых или исключение существующих строк/граф в шаблон отчёта);
поддержка версионности шаблонов отчётности (в том числе SCD2).
Как видно из таблицы, самая быстрая скорость работы у материализованного «широкого» представления в БД (и еще статичных Excel-файлов). Но на этом преимущества этих вариантов заканчиваются. Широкая структура в БД не очень оптимальна для хранения, и ее сложно поддерживать (особенно при частом изменении версий шаблонов у отчёта). Excel предлагаем вообще не рассматривать. Поэтому по совокупности характеристик на первое место оба варианта не претендуют.
Далее по скорости получения данных идут виртуальные кубы в BI (фиксированной структуры и гибкий Multi-OLAP). Затем чуть отстают прямые «широкие» запросы и материализованные «длинные» витрины. Но вот по совокупности всех характеристик виртуализация в BI существенно опережает витрины в БД. В первую очередь, за счет гибкости в управлении и удобстве настроек. В BI-платформе пользователю всё доступно из интерфейса, и чтобы поменять структуру отчёта, не требуется модифицировать sql-скрипты. Также инструментарий BI хорошо адаптируется к одновременной работе сразу с несколькими версиями. На разные отчётные даты пользователь видит разные шаблоны отчёта. К сожалению, витрины БД не могут похвастаться тем же, особенно «широкие». Фактически для каждой версии (если сильно изменилась структура шапки отчёта) нужно написать индивидуальный текст sql-запроса. Это большое и существенное ограничение для промышленной эксплуатации ИТ-системы.
Последним в рейтинге по быстродействию были «длинные» прямые sql-запросы. Но по совокупности характеристик они опережают некоторые другие аналоги из списка витрин БД. В том числе за счет неплохой способности адаптироваться к медленно меняющимся справочникам и изменениям версий шаблона. Конечно, для такой адаптации все же потребуется sql-программирование, но по сравнению с вариантом «широких» витрин, сложность скриптов намного проще. Поэтому мы бы не рекомендовали совсем «списывать» этот вариант со счетов. Стоит присмотреться к нему более внимательно.
Таким образом, виртуализацию исходных данных для произвольных заданных шаблонов можно эффективно выполнить средствами BI. Платформа «Форсайт» обладает для этого набором интересных инструментов и позволяет настраивать достаточно «сложные» отчёты, основанные на информации из озера данных. Мы постарались подробно рассказать о них в этой и предыдущей статьях. Конечно же, виртуализация – это не панацея. Если мы говорим о связке «BI + Data LikeСУБД», то здесь всё во многом зависит от задачи и конкретного случая. Тут всегда должна быть рациональная эквилибристика. Баланс между возможностями BI и СУБД. Где-то нужно наклониться влево, где-то вправо.
Думаем, стоит отметить другой важный момент. Виртуальное извлечение данных даёт только половину успеха, даже для случаев, когда это оправдано и рационально. Для отображения таких данных нужны еще гибкие инструменты их визуального и графического оформления (для полного 100% соответствия шаблону формы: шрифт, цвет, поворот текста, объединение ячеек и много другое). И еще динамические средства взаимодействия front-end и back-end (для быстрого вывода отчётов с десятками тысяч строк в веб-клиенте у пользователя). Такие возможности тоже есть в платформе «Форсайт», и мы обязательно о них подробно расскажем в следующих наших публикациях. До скорых встреч!
