Olap

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

Если же ваша база данных хранится на MS SQL или может быть задана через связные серверы и нет инструмента для построения OLAP отчета, то можно использовать платформу Клиент Коммуникатор, через использование представлений (однопользовательская версия бесплатная). Для этого необходимо составить SQL запрос и записать его в конфигураторе в разделе Представления.

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

Рассмотрим на примере следующей задачи: провести анализ наполненности реквизитов контрагентов:

• ИНН,
• основной вид деятельности,
• основное юридическое лицо,
• телефон,
• e-mail,
• контактное лицо.

Анализ должен быть в разрезе по куратору, состоянию, типу, сальдо, региону.

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

Шаг 1. В модуле Дизайнер создать шаблон формы. Задать название формы и выбрать основную таблицу.



Шаг 2. Добавить на форму компонент Сводная таблица. Установить компоненту выравнивание Весь клиент (или любой другой на выбор).



Шаг 3. Задать параметры таблицы.

Существует 4 типа области размещения атрибутов:

• строки расположение слева списком, может быть несколько раскрывающихся списков;
• столбцы размещение горизонтально сверху списком, возможно несколько уровней;
• фильтры слева сверху горизонтально;
• данные основная часть с агрегированными данными, обязательно надо задать, как рассчитывать общий итог: количество, минимальное, максимальное, сумма, среднее. Как правило для расчета данных используются поля числовые, которые можно сложить. Для строковых переменных можно использовать только агрегатную функцию Количество. В примере я демонстрирую, как использовать вычисляемое выражение с применением функции SQL CASE.

Шаг 4. Задать параметры просмотра и поведения.



Шаг 5. Вывести форму в меню программы (в модуле Администратор).

Результат:

Между областями строк, столбцов и фильтров можно перемещать атрибуты при помощи мыши, тем самым получаем различные срезы. Имеется возможность экспортировать результат в файл. Как вывести расшифровку по строке отчета, я опубликую позже на своем youtube канале.

Более подробно смотрите в видео.

Привет, Хабр! Меня зовут Иван Вахмянин, и сегодня я хочу рассказать о том, что находится под капотом у современной BI-системы, от чего зависит ее производительность (и как можно её ненароком убить), и какие технические оптимизации позволяют технологии In-Memory OLAP выигрывать по скорости у других подходов.




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

С точки зрения бизнеса это бывает очень грустно. Какой-то пользователь создает дашборд, и всё падает. При этом увеличение объема памяти и количества процессоров не даёт почти никакого эффекта. Предотвратить или быстро решить такую проблему гораздо проще, если хотя бы в общих чертах представляешь, как система работает под капотом.

Когда мы только начинали работать в сфере BI 5 лет назад, в основу продукта Visiology легла open-source библиотека Pentaho Mondrian. Но достаточно быстро мы столкнулись с проблемами по части производительности и начали самостоятельно разрабатывать In-Memory OLAP движок под названием ViQube (об этом можно почитать в другой нашей статье Как разработать BI-платформу наш трудный, но интересный опыт). Собственно, в процессе этой разработки мы и накопили опыт, которым сейчас хотим поделиться.

Как работает OLAP

На первый взгляд, все BI-платформы выглядят одинаково: у вас есть источники информации, у вас есть инструменты загрузки, анализа и визуализации данных, а на выходе пользователь получает разнообразные отчеты от печатных форм до дашбордов, в том числе на мобильных, на видеостенах, на любых устройствах. В своей основе все BI-инструменты используют модель данных на основе OLAP (On-Line Analytical Processing, многомерное представление данных), но техническая реализация OLAP движка (который непосредственно занимается вычислениями) может быть реализован по-разному, и от этого очень сильно зависит производительность и масштабируемость системы.



MOLAP

Технология OLAP возникла ещё в 80-х годах. В то время процессоры были намного медленнее, да и память была в дефиците, поэтому чтобы аналитик мог реально работать с данными в онлайн-режиме, придумали такую вещь как MOLAP (Multidimensional OLAP). Идея подхода в том, что для всего многомерного куба после загрузки данных производится предрасчет: на узлах иерархий предварительно рассчитываются агрегации, чтобы под любой более или менее типовой запрос пользователя можно было получить результат запроса без необходимости пересчитывать все строки. Да, при любом изменении данных нужно долго пересчитывать куб, а объем рассчитанного куба может быть в разы больше исходного датасета, но в то время других вариантов не было. MOLAP до сих пор существует и используется, например, в SQL Server Analysis Services, но на практике его используют все реже и реже.

ROLAP

Позже появилась реляционный OLAP, или ROLAP. Отличие от MOLAP заключается в том, что не происходит никакого предварительного расчёта агрегаций, а вычисления происходят на СУБД из бэкэнда BI-платформы. В этом случае пользователь работает с удобными инструментами, например, с конструктором дашбордов, а под капотом ROLAP-движок преобразует его запросы на лету в SQL, и они просто выполняются на какой-то СУБД.



Подобный подход характерен, например, для таких open-source систем, как Pentaho или Metabase или проприетарного SAP Business Objects, Oracle OBIEE.

У ROLAP есть целый ряд недостатков. Во-первых, если, не использовать на бэкенде специальные аналитические СУБД, такие как ClickHouse или Vertica, все будет работать ооочень медленно (дальше будет понятно, почему). Во-вторых, даже при использовании аналитической СУБД, при работе с ROLAP очень неэффективно используется кэш, потому что СУБД и BI-платформа работают отдельно друг от друга. В-третьих, поскольку не все аналитические задачи можно завернуть в SQL-запрос, ограничивается аналитическая функциональность. Но зато, на сегодняшний день ROLAP это единственный способ работы с реально большими объемами данных, которые не помещаются в память.

In-Memory OLAP

Если речь идет о работе с данными объемом до терабайта, как правило, используется схема In-Memory. Данные постоянно находятся в памяти, и за расчеты отвечает специальный движок. В системах Qlik это QIX, Power BI использует SQL Server Tabular Engine, который раньше был продуктом xVelocity, но Microsoft купил эту компанию, и теперь движок является частью MS SQL Server. У нас в Visiology движок In-Memory OLAP называется ViQube.

In-Memory OLAP привлекает простотой установки и работы, подобные движки изначально интегрированы в BI-платформы и укомплектованы удобными визуальными интерфейсами настройки, загрузки данных, управления правами и т.п. За счет размещения данных в памяти и специальных оптимизаций (про них ниже) многократно растет скорость обработки, расширяются возможности для аналитиков.



При этом у подхода In-Memory есть и свои недостатки. И главный из них это предел емкости памяти. Если объем данных измеряется в терабайтах, вам нужно либо строить дорогой кластер, либо склоняться к ROLAP. Кроме этого, при таком подходе не всегда удается минимизировать задержку отображения обновлений, потому что для этого данные приходится перегружать из источника в память.

Гибридный OLAP

Основной схемой работы для большинства промышленных BI становится гибридная схема с одновременным использованием и In-Memory OLAP, и реляционного OLAP-движка. Горячие данные хранятся в In-Memory, холодные данные, которые не влезли в заданный объем, в СУБД. Такое решение в QlikView, например, называется Direct Discovery, в Power BI Direct Query. В Visiology тоже поддерживаются интеграции с несколькими СУБД, в том числе с ClickHouse.



Кстати, выбор СУБД для гибридного режима также критически важен. Если мы будем работать с PostgreSQL, в котором лежит 5 терабайт данных, аналитические запросы будут исполняться крайне медленно. И если у вас не SAP HANA, придется вручную распределять данные на холодные и горячие. Как следствие, не все аналитические функции будут доступны на полном объёме данных. Но если памяти не хватает, увы, с таким положением дел приходится мириться.

Откуда растут плюсы In-memory OLAP?

Для скорости работы In-Memory OLAP есть как очевидные, так и скрытые причины. Тот факт, что работа движка происходит в памяти, а она намного быстрее, чем жесткий диск (спасибо, кэп) это только 1/10 правды. Но давайте подумаем, как работают реляционные СУБД, например, тот же PostgreSQL. Ведь он в какой-то мере тоже является In-Memory. И вообще, любая современная СУБД активно использует как блочный кэш в памяти, так и внутренний.



Когда обычной дисковой СУБД, такой как PostgreSQL, нужно считать данные с жёсткого диска, она обращается к накопителю и считывает какую-то страницу. Эта страница помещается в блочный кэш (в Linux он располагается в свободном пространстве памяти). Допустим, у нас есть 128 гигабайт памяти, и 20 из них мы занимаем софтом. Всё остальное может использоваться под блочный кэш. И если СУБД нужно будет считать с этой страницы ещё что-нибудь, она возьмет эту информацию из памяти. И от того, насколько эффективно используется кэш, зависит производительность. Если для анализа используется, скажем, 30-40 гигабайт данных, мы можем расширить емкость оперативной памяти на сервере и уже после первого чтения СУБД все данные окажутся In-Memory, а обращения к диску на чтения будут происходить лишь эпизодически.



Кроме этого, у умных СУБД, в том числе у Postgres, имеется механизм cache-aware управления. Они могут выбирать, что складывать в кэш, а что нет, какие данные надо заново прочитать с диска.


Источник: www.enterprisedb.com/blog/autoprewarm-new-functionality-pgprewarm

На графике выше влияние прогрева кэша на производительность PostgreSQL. Жёлтым показана производительность в зависимости от времени, и наглядно видно, что по мере работы пользователей СУБД считывает данные, постепенно раскладывает всё в In-Memory и достигает предела своей производительности. Если же использовать prewarm и дать Postgres команду поднять все данные в память сразу, максимальная производительность достигается сразу.

Также стоит учитывать, что мы говорим об аналитической нагрузке. Она очень сильно отличается от транзакционных задач, когда в базу нужно внести запись о покупке в интернет-магазине или считать 10 строк с историей заказов. На графике ниже показан типовой аналитический запрос из теста TPC-H. Этот тест состоит из нескольких десятков реальных аналитических запросов и широко используется для нагрузочного тестирования.


Источник: www.tpc.org/information

В SQL-запросе из теста TPC-H можно найти много чего интересного. Во-первых, запрос идет не на поля, а на агрегации. Во-вторых, здесь присутствуют линейные арифметические операции. В-третьих, здесь часто встречаются фильтры по полям с малой кардинальностью: регионы и федеральные округа, в которых работает компания, типы клиента активный, неактивный и так далее. В-четвертых, часто используются фильтры по полю с датой. Если мы изучаем динамику выручки, то нас интересуют такие периоды как год или квартал.

На входе подобного запроса всегда очень большое количество строк миллионы или даже миллиарды. Поэтому СУБД вынуждена делать серию полных сканирований. С другой стороны, на выходе получается небольшое количество строк, ограниченное количеством точек, которые можно отобразить на графике чаще всего десятки или сотни значений. Зная эти особенности аналитических запросов, можно провести оптимизацию и получить прирост производительности.

In-Memory OLAP: конкретные примеры оптимизации для BI

Учитывая особенности аналитических запросов, о которых мы уже говорили ранее, для движка BI возможен целый ряд оптимизаций, причем как технических, так и эвристических. Давайте рассмотрим их подробнее.

1. Колоночное хранение данных

Это первый шаг, дающий заметный эффект. Для обычных транзакционных запросов хранение в строках подходит оптимально. Но для аналитики необходимо получать данные по столбцам. Казалось бы, какая разница, ведь все это уже In-Memory? Но на практике кроме памяти, которая быстрее дисков, у нас также есть кэш процессора, как правило, трехуровневый. Он работает намного быстрее, чем память, доступ к которой занимает примерно 200 тактов процессора.



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


Источник: arstechnica.com/gadgets/2002/07/caching/2

2. Сжатие

В этой оптимизации есть свои особенности. Для дисковых СУБД это обязательная оптимизация. Здесь мы выигрываем за счет того, что реже ходим в медленное хранилище, но также проигрываем, потому что данные надо распаковать, а это вычислительно ёмкая операция. Для дисковых СУБД получается очень выгодно, для In-Memory все не так очевидно, потому что читать из памяти обычно быстрее, чем заниматься распаковкой.


Источник: www.percona.com/blog/2016/03/09/evaluating-database-compression-methods

Самый быстрый из алгоритмов сжатия по скорости распаковки LZ4. Он в среднем уменьшает размер всего в 2 раза, но зато очень быстро распаковывает, со скоростью порядка 500 мегабайт в секунду на ядро процессора. В бенчмарке на графике LZ4 вообще показал результат 3 гигабайта данных в секунду. Такая скорость дает очень хороший выигрыш для дисковых СУБД, скорость чтения для которых те же 500 мегабайт в секунду. Но для памяти скорость передачи данных составляет десятки гигабайт в секунду, получить преимущество за счёт LZ4 оказывается сложно.

Однако не стоит забывать, что мы чаще всего работаем с данными низкой кардинальности. Например, в строке с названиями торговой точки очень часто будут повторяться одни и те же значения или их ID: Москва, Москва, Москва, Москва


Источник: Guassian and speckle noise removal from ultrasound images using bivariate shrinkage by dual tree complex wavelet transform (Professor G R Sinha, 2015)

Для подобных данных есть ещё один алгоритм, который называется Run-length encoding. Он работает очень просто: строки типа ААААВВВВВСС он сжимает в виде 4A5B2C. Это прекрасный подход для данных с низкой кардинальностью, он помогает экономить память и оптимальнее использовать кэш процессора.

3. Векторные инструкции

Чтобы сложить 2 числа, мы кладём в один регистр процессора одно число, а в другой регистр другое. Для ускорения этого процесса существуют векторные регистры и векторные операции (SIMD Single Instruction Multiple Data). Они позволяют за одну операцию сложить сразу N чисел. Это уже очень зрелая технология, которая появилась в процессорах еще в 1993 году. Она поддерживалась еще в Pentium MMX (166 МГц у меня такой был, до сих пор помню, как на него термопасту намазывал).


Источник: www.codetd.com/en/article/9633503

В Pentium MMX векторных регистров было 8, и они были рассчитаны только на целочисленную арифметику. На текущий момент практически во всех процессорах есть 128-битные регистры SSE и наборы инструкций. Регистры AVX уже 256-битные, а в серверах есть даже AVX 512. Они работают с числами с плавающей запятой, и их можно использовать для оптимизации аналитической нагрузки.


Источник: technews.tw/2020/07/16/linus-torvalds-avx-512-critique

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

Вот пример, как для расчёта скалярного произведения используется AVX-инструкции 256-битных регистров.



На нашей практике использование именно AVX даёт от 1 до 10% прирост производительности, в зависимости от задач. Когда вы используете современную BI-систему, в зависимости от конкретного процессора, система будет применять разный код. Производительность от этого увеличивается, но не драматически, а в лишь в небольших пределах.

4. Поздняя материализация

Материализация это процесс формирования результата, ответа на запрос. Например, простой SQL-запрос SELECT C1, С2, D1, D2 из 2 таблиц, получит 2 поля из одной и 2 поля из другой таблицы. Далее мы соединяем их по ключу С1=D1.



В случае ранней материализации мы получаем 4 колонки и работаем с колоночной базой. То есть мы сначала берём С1 и С2, соединяем их в таблицу. Потом делаем то же самое с D1, D2 и после этого выполняем Join, то есть формируем строки из этих 2 таблиц, для которых истинно условие С1=D1.

В случае аналитической нагрузки на выходе всегда мало данных, потому что фильтры отсекают достаточно много значений. Когда выполняется JOIN, создаётся очень большая таблица, которая не нужна. Ведь на последней операции будет отброшено много данных.

При поздней материализации мы сначала разбираемся, что и в какой колонке нам нужно. Сначала выполняется операция JOIN С1 = D1, и мы выбираем нужные нам значения только из правой таблицы. Это можно сделать за один проход. А после этого можем взять из таблицы С только те поля, строки которых после JOIN остались. В итоге не нужно создавать большую промежуточную таблицу.

Конечно, такая оптимизация не сработает на любой нагрузке. Исследования Vertica, например, показывают, как многопрофильная СУБД позволяет выбирать различные стратегии материализации, в зависимости от задач. Но именно для BI характерна нагрузка, связанная с поздней материализацией, поэтому ее использование предпочтительно.

5. Эвристические оптимизации

In-Memory OLAP чаще всего является неотъемлемой частью BI-платформы, а BI-платформа прекрасно знает, какие данные в нее загружаются, как пользователь с ними работает. Конечно, это неочевидные вещи, они происходят под капотом BI-системы и не всегда даже видны, но позволяют получить хороший прирост производительности.

Во-первых, часто применяется автоматическая нормализация или денормализация. Данные с низкой кардинальностью иногда бывает выгодно нормализовывать, а иногда наоборот. Чаще всего BI-платформы стараются максимально денормализовать таблицы. Такой подход позволяет максимально избегать достаточно тяжелых операций JOIN. Пользователь может даже не видеть этого: если мы загрузили в систему 2 таблицы и связали их по ключу, система может сразу превратить их в одну таблицу.

В некоторых случаях, наоборот, происходит автоматическая нормализация. Если поле очень жирное, например, с текстовым комментарием, держать его по всей колонке будет неоптимально. Вместо этого такое поле можно автоматически вывести в справочник. Вы будете видеть в таблице и строку, и столбец, а на самом деле данные будут находиться в отдельной таблице, а в памяти будет находиться только ID. Такие методики используются довольно часто.

6. Сортировка по календарю

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

7. Разделяемый кэш

Пользователи BI часто работают с одними и теми же данными. Поэтому хорошую эффективность показывает разделяемый кэш. Если пользователь А запустил дашборд и выполнил свой запрос, то пользователь В на том же дашборде сможет получить результат быстрее, потому что необходимые данные будут уже в кэше.

Разделяемый кэш под запросы позволяет кэшировать данные внутри запросов. Например, один пользователь запросил аналитику и отфильтровал её по отделу А, а другой хочет по отделу В. В этом случае оптимизации не получится, потому что фильтрация происходит на раннем этапе. Но в некоторых случаях и для подобных ситуаций удается оптимизировать запрос.

8. Обратный индекс

Многие BI-системы выстраивают обратный индекс для строковых полей. Он позволяет ускорить поиск с операторами по строкам. И хотя BI-системы и OLAP движок In-Memory это не замена полнотекстовому поиску, подобные оптимизации встречаются достаточно часто.

Каков эффект?

Все перечисленные меры помогают сделать BI-систему, основанную на In-Memory OLAP, более устойчивой и производительной, не прибегая к гибридным схемам работы с подключением реляционных БД. Рост производительности сильно зависит от используемых задач, но в процессе работы над ViQube мы убедились в том, что оптимизации лучше всего закладывать на этапе исходного кода и изначально проектировать систему с учетом особенностей аналитических запросов.

Кроме этого, на своем опыте мы нашли ряд кейсов, которые позволяют легко убить производительность In-Memory OLAP. Можно считать этот набор вредных советов пасхалочкой для тех, кто дочитал до конца :)

5 способов убить производительность In-Memory OLAP

BI-система, работающая на базе In-Memory OLAP, уже по определению имеет высокую производительность однако она не будет неубиваемой! Ниже список из 5 вредных советов по In-Memory, выстраданных на своей шкуре в процессе разработки движка ViQube и его использования в реальных проектах.

1. Сложные вложенные выражения

Прекрасными убийцами производительности In-Memory систем являются запросы, в которых плохо работает векторизация. Любые вычисления с использованием нелинейных функций, которые задействуют много полей, приводят к радикальному снижению скорости работы системы.

Очень часто в своей практике я сталкивался с тем, что аналитик пишет какое-то выражение на Qlik Expressions или на DAX в Power BI, которое хорошо работает, когда в базе 10 тысяч строк. Но по мере роста масштабов производительность начинает деградировать невероятными темпами.

Обычно это происходит потому, что формула запроса сложна и не даёт движку использовать преимущества колоночного хранения векторных инструкций. Вместо этого системе приходится находить все поля по строкам, перебирать их одну за другой. В этом случае производительность падает до уровня СУБД со строчным хранением. Конец оптимизации.

2. Вложенные запросы

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

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

Кстати, такой запрос может вообще не пройти в принципе, если движок упрётся в предельную емкость доступной памяти. А может привести к очень сильному замедлению работы BI-системы. Поэтому при создании вложенных запросов нужно оценивать размеры промежуточного результата.

3. Частое обновление данных

Частое обновление данных не так страшно, если вы используете специальные инструменты для работы в режиме Real-time. Для чего? Почему BI-платформа In-Memory OLAP не очень любят Real-time и для Real-time предлагают, по сути, отдельные инструменты. В Power BI, например, Streaming Dataset. Зачем это сделано? Почему просто не дописывать и не считать заново?

Каждый раз, когда мы что-то меняем в исходных данных, происходит серьёзная инвалидация кэша. То есть и общий кэш, и разделяемый кэш (свой для каждого пользователя), кэш под запрос всё это приходится выкинуть и ждать, пока оно рассчитается заново. Даже добавление одной новой строки приводит к инвалидации, особенно если она реализована не слишком умно. К тому же BI-платформы чаще всего инвалидируют кэш с запасом, чтобы исключить показ пользователю неправильных данных.

Частая инвалидация ведёт к деградации производительности BI-системы, и это особенно заметно, если одновременно BI-система обрабатывает тысячи запросов. Как правило, большинство пользователей работают с готовыми дашбордами, для которых кэш основной источник оптимизации. Когда мы проводили нагрузочное тестирование для профиля пользователя, который просто работает с дашбордом, 90-95% запросов в принципе не доходили до движка и обслуживались из кэша. Именно поэтому частая инвалидация ведет к падению производительности в 10 и более раз.

4. Маленький запас свободной памяти

Иногда кажется, что для работы системы неважно, сколько имеется свободной оперативной памяти, лишь бы хватало общей емкости. Но на самом деле свободная память используется для буферного кэша. Можно сказать, что для In-Memory движков это не так критично, потому что они не так часто работают с жёстким диском. Но в те моменты, когда он поднимает данные, использует snapshot, сохраняет или загружает что-то, наличие буферного кэша оказывается очень даже важным фактором. К тому же, помимо In-Memory движка в любой BI есть и другие части системы, например, компоненты ОС. И если память кончится, они начнут резко тормозить, потому что не смогут использовать буферный кэш.

Но и это еще не все. Нехватка свободной памяти ведет к рискам просадки производительности в сложных запросах. Когда происходит создание больших объемов промежуточных данных, они тоже могут вытеснить буферный кэш, не позволяя использовать его. Что ещё хуже, такие запросы могут достичь предела доступной памяти, и вся система упадёт в swap. Это будет полный коллапс с точки зрения производительности.

5. Строковые поля с высокой кардинальностью

Последний вредный совет загружать строковые поля с высокой кардинальностью. Добавляя к датасету комментарии к заказам, сообщения из чата или что-то подобное, можно сильно просадить производительность. То, что хорошо подходит для полнотекстового поиска, плохо работает для движков In-Memory OLAP. Такие данные не дают использовать RLE, векторные инструкции. Здесь мы снова падаем в выполнение строковых операций, производительность для которых намного меньше, чем для арифметических. BI-система в принципе не всегда может создать индекс на такие строковые поля со всеми вытекающими последствиями.

Да пребудет с вами производительность

Как я уже говорил, In-Memory OLAP это продвинутая и умная технология, которая, чаще всего, просто работает и позволяет не задумываться о том, что внутри у BI-платформы. Но, исключения из правил случаются, и, я надеюсь, что эта статья поможет быть к ним готовым.

Всех с наступающим, и отличной производительности всем вашим сервисам в Новом Году! :)

Как мало пройдено дорог как много сделано отчетов

Введение

Василий, мы установили новый BI продукт, наш САМЙ ГЛАВНЙ от него просто в восторге!
Да, но я не знаю, как выгрузить данные для анализа из этой системы?! Он, похоже, только в html может что-то показывать.
Ничего, я думаю ты справишься, сам понимаешь, чем шире улыбка шефа, тем выше премия.
Но, Иван Васильевич, этот продукт в качестве источника данных использует только PDF файлы.
Зато он показывает шикарные разноцветные графики, у него анимация как в Звездных войнах, а руководство просто в восторге от его интерактивных возможностей. Там ещё и пасхалочка есть. Если три раза кликнуть в правом нижнем углу, появится Дарт Вейдер и споёт Марсельезу. Да и в целом, Вася, будь оптимистом! Хочешь анекдот в тему?

Что у вас запланировано на 1 января?
Катание на санках
А если снег не выпадет?
Это нас огорчит, но не остановит.

Не грусти Вася, принимайся за работу, а мне пора спешить утренняя планерка, эээ Daily Standup Meeting точнее, всё никак не могу запомнить.

Вася садится за свой рабочий стол и с грустью смотрит в монитор. Да уж, красивые графики, только толку от них? В Excel не выгрузить, с формулами не сверить, хоть бери тетрадку с ручкой и делай всё на бумаге. Плюс ещё как-то KPI на основе этого надо посчитать. Зато в ИТ отдел, говорят, художника взяли, чтобы он красивые отчеты для руководства оформлял. Глядя на новый продукт, Вася загрустил. В голове у него крутились пару строк из стихотворения C.А. Есенина Мне грустно на тебя смотреть:

Так мало пройдено дорог,
Так много сделано ошибок.

Ну что ж, оставим Васю на едине со своей болью и посмотрим на проблему шире. Видя переделку строк C.А. Есенина, которая вынесена в цитату к этой статье, мне кажется, что он не одинок в своих мыслях. Сложно понять, как работают современные BI системы и для кого их пишут то ли для аналитиков, то ли для руководителей. Очень много теории и информации, причём, в зависимости от источника, эта информация может противоречить самой себе. К этому стоит добавить обилие научных терминов и трудный для понимания язык описания. Сложно угадать с выбором, а цена ошибки велика, так как системы дорогие и работа с ними часто требует определенной квалификации. Понимая всё это, я решил поделиться своим опытом в BI сфере. Попытаюсь написать об этом простым языком и не вдаваться глубоко в теорию. Речь пойдет о Microsoft Analysis Services и о том, как он может решить часть проблем связанных с аналитической отчетностью. Другую часть этих проблем, я решил, написав специальную программу, которая позволяла формировать отчеты непосредственно в Excel, минуя HTML формы и минимизируя нагрузку на Web сервер, но о ней я уже писал тут http://personeltest.ru/aways/habr.com/ru/post/281703/, а тут даже видео снял: https://youtu.be/_csGSw-xyzQ. Приятного вам чтения.

Отчетность и её проблемы

Все началось с задачи, поставленной финансовым отделом крупного банка. Надо было создать систему отчетности, которая бы позволяла быстро и оперативно оценивать текущую ситуацию в организации. Для решения этой задачи мы взяли базу данных. Организовали в ней Хранилище (Data Warehouse), настроили процессы загрузки данных и установили систему отчетности. В качестве которой мы взяли SQL Server Reporting Services, так как этот продукт входил в MS Sharepoint, использовавшийся в тот момент в банке. В принципе всё работало, но у заказчика были претензии:

• Претензия 1. HTML -> MS Excel: отчеты изначально формируются в HTML, а аналитики работают с MS Excel. Надо постоянно делать экспорт из одного формата в другой. При этом часто сбивается разметка и в Excel часто подгружается множество дополнительной информации, большой объём которой, в некоторых случаях, существенно влияет на производительность.
• Претензия 2. Параметры для отчета: данные в отчетах зависят от параметров, причём при их изменении формируется новый отчет, который надо опять выгружать в Excel, что не всегда удобно.
• Претензия 3. Добавление изменений в отчет: для того, чтобы что-то изменить в отчете, добавить новую колонку или создать группировку, надо обращаться к специалисту, который может поправить этот отчет на сервере.
• Претензия 4. Анализ данных: отчеты получаются статическими и когда нужно посмотреть различные разрезы, поменять строки с колонками, отфильтровать или добавить, либо удалить какие-то значения, надо делать все эти манипуляции в Excel, что не всегда удобно, а порой и сложно, из-за проблем с производительностью компьютеров, на которых работают аналитики.

Стоит отметить, что сотрудники банка не рассматривали для себя никакого другого инструмента в качестве замены MS Excel. И на то были веские основания. Весь его функционал сложно чем-то заменить. К примеру, аналитики очень часто:

• сверяли данные из базы по формулам, которые можно добавить в Excel,
• хранили одни и те же срезы данных, сделанные в разные дни, с целью обнаружить ошибки,
• добавляли туда свои данные,
• писали различные скрипты

В общем использовали его на все 100%. Хотя были те, кто предлагал им что-то другое, точнее не столько предлагал, сколько заставлял. Как итог таких предложений, у нас в системе появились SAP BO, Oracle Reports Services и ряд других BI инструментов. Возможно, они в чем-то превосходили SQL Server Reporting Services, но суть работы с ними кардинально не изменилась:

1. формируем отчет в HTML,
2. экспортируем его в Excel,
3. начинаем заниматься бесконечными танцами вокруг данных.

Требовалось что-то более кардинальное, не просто отчет, а некий набор данных, с которым удобно было бы работать.

Выход из ситуации

К найденному решению подтолкнули PivotTable в Excel



и PivotGrid от фирмы DevExpress ( https://demos.devexpress.com/blazor/PivotGrid).

Детально изучив эти решения вышли на MS Analysis Services и решили попробовать. Его можно использовать в Excel, и он может работать с Oracle, как с источником данных, что нас на тот момент устраивало. С точки зрения архитектуры, источником данных для него может служить что угодно, был бы нужный провайдер. Суть его в том, что он способен хранить в себе большие объемы данных со всеми их агрегациями и выдавать их клиенту максимально быстро. К Excel его можно легко подключить и манипулировать данными в Pivot Table.



В MS Analysis Services есть возможность партиционирования данных (хранение их в виде множества отдельных частей) и так же инкрементальное обновление данных. Это даёт ему возможность загружать данные из внешних систем небольшими кусочками и хранить их во множестве партиций. С точки зрения максимальных объемов, у него есть ограничения, но они довольно большие https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/maximum-capacity-specifications-analysis-services?view=asallproducts-allversions.

MS Analysis Services является OLAP системой, которая использует отдельный сервер для хранения данных, либо части данных. Его плюсом является то, что он способен довольно быстро работать с миллионами записей, будучи установленным на обычный, современный компьютер. Так же он позволяет анализировать данные непосредственно в Excel и может заменить собой десятки отчетов на MS Reporting Services или ему подобных. Причем при работе с ним не надо писать и править различные запросы типа SQL, хотя при желании можно, только вместо SQL он использует MDX.

Правда есть тут и ложка дегтя. В Excel можно запросить разом очень большой объём данных и OLAP их вернет, но отобразить такой объем Excel не сможет, либо сможет, но работать при этом будет очень медленно. На первых порах это раздражало аналитиков, но поняв причину и настроив фильтры в Pivot Table эту проблему решили.

Секрет быстродействия MS Analysis Services, как и любой другой OLAP системы, кроется в архитектуре хранения данных. В нем все храниться в максимально подготовленном и оптимизированном для запросов виде. Такая подготовка требует времени и запись вновь пришедших данных в OLAP происходит не быстро, но, с другой стороны, чтение данных получается очень быстрым. Выходит долго пишем быстро читаем.

Немного теории

Чаще всего, когда анализируют данные их объединяют в группы, а сами группы так же объединяют в иерархии. Для примера возьмём торговую точку. С точки зрения бизнеса, интерес представляют продажи. То есть сколько товара было продано за день (1-группа), за месяц (2-ая) и за год (3-я). Где день месяц и год это разные уровни одной иерархии. Получается, что продажи за месяц это сумма всех продаж за все дни в месяце, а продажи за год это сумма продаж за все месяцы в этом году. Отсюда получается, что для получения максимального быстродействия, можно заранее собрать данные в группы и рассчитать агрегаты (в нашем примере суммы продаж) для каждого уровня иерархи. Вот на этом принципе и работают MS Analysis Services. Им достаточно сказать что надо считать, по какой формуле и на какие группы это можно разбить. Остальную работу они сделают сами. Тут немного о том как они это делают: http://citforum.ru/consulting/BI/molap_overview/node7.shtml. Стоит отметить, что в современных OLAP системах все агрегаты, чаще всего, не рассчитываются заранее. Это всё делается на лету, в момент запроса.

Теперь о терминах:

MS Analysis Services это одна из OLAP систем, где OLAP это аббревиатура online analytical processing. Дословно это означает интерактивная (online) аналитическая обработка данных. Со временем данная формулировка утратила свой первоначальный смысл, так как появились системы, способные обрабатывать данные с большой скоростью и передавать их пользователю без использования подходов, декларируемых в OLAP. Поэтому, сейчас есть более полное описание требований к системам, которые могут называться OLAP, это:

• Тест FASMI Fast Analysis of Shared Multidimensional Information. Более детально об этом можно прочитать здесь: http://www.olap.ru/basic/fasmi.asp.
• И описание правильного куба, которое дал в 1993 году Е.Ф. Кодд: http://www.calligraph.ru/kodda.htm .

По своему опыту, могу сказать, что чем больше ваш OLAP куб удовлетворяет описанию Е.Ф. Кодда, тем лучше, как с точки зрения работы с ним, так и с точки зрения его создания.

Вкратце, OLAP это система хранения, организованная таким образом, чтобы данные в ней:

• были взаимосвязаны,
• собирались в иерархии,
• имели функции агрегации по всему множеству иерархий

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

При построении OLAP выделяют Факты и Измерения. Факты это цифровые значения измеряемых величин. Измерения это сами измеряемые величины. Совокупность всех связанных между собой измерений, фактов и функций для их агрегации называют OLAP-кубом. Факты и Измерения связанны между собой. По типу связи выделяют 2 схемы организации хранения данных Звезда и Снежинка. Звезда это когда все измерения напрямую связаны с фактом, снежинка это когда есть измерения, которые связанны с фактом через другие измерения. Эти схемы можно создавать и просматривать в разделе Data Source Views в SSAS.

Создание OLAP-куба в Microsoft Analysis Services

Построение OLAP кубов делается через проект в Visual Studio. По большей части там реализована технология визуального программирования перетащить, кликнуть мышкой, настроить. Отсюда это проще показать, чем описать. Что я и сделал в моем видео: https://youtu.be/f5DgG51KMf8. Так же стоит отметить то, что Microsoft, в ознакомительных целях, предоставляет свои продукты бесплатно. Отсюда, посмотреть, как он работает можно на любом компьютере с ОС Windows 10, удовлетворяющем следующим требованиям: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15. Требования по ссылке к MS SQL Server, так как MS Analysis Services являются его частью.

Заключение

OLAP это относительно простой способ повысить скорость и удобство работы с данными. В данный момент существует множество решений, основанных на этой технологии. Я работал с MS Analysis Services (SSAS) и вот что мне в нём понравилось:

• теоретически он позволяет работать с любым источником данных, не требуя при этом существенных затрат с точки зрения оборудования.
• хранит данные на выделенном сервере, что снижает вероятность их потри, в отличие от систем, хранящих такие данные в памяти.
• интегрирован с MS Excel, что облегчает работу с ним для тех, кто привык работать с данным продуктом.

В заключение, хочу сказать, что мне MS Analysis Services, в свое время, очень сильно помог и я надеюсь, что знания, которыми я поделился в этой статье, так же кому-то помогут.

Какой у Вас выбор?

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

Разумеется, все BI-системы имеют чрезвычайно сложную архитектуру и их внедрение в компании, является не простой задачей, требующей крупной суммы для решения и интеграторов высокой квалификации. Вам придется неоднократно прибегать к их услугам, так как внедрением и введением в эксплуатацию все не закончится в будущем будет требоваться доработка функционала, разработка новых отчетов и показателей. Нужно учесть, что в случае успешной работы системы, вы захотите, чтобы в ней работало все больше сотрудников, а это означает приобретение дополнительных пользовательских лицензий.

Еще одной неотъемлемой чертой продвинутых бизнес-аналитических систем, является чрезвычайно большой набор функций, многими из которых Вы никогда не воспользуетесь, но будете за них продолжать платить каждый раз, продлевая лицензии.

Приведенные особенности BI-систем заставляют задуматься о подборе альтернативы. Далее я предлагаю сравнить решение стандартного набора задач при подготовке отчетности с помощью Power BI и Excel.

Power BI или Excel?

Как правило, для построения квартального отчета по продажам, аналитик выгружает данные из учетных систем, сопоставляет их со своими справочниками и собирает с помощью функции ВПР в одну таблицу, на основе которой строится отчет.

А как решается эта задача с помощью Power BI?

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

Какие преимущества применения Power BI по сравнению с традиционным подходом можно заметить в приведенном примере?

1 Автоматизация процедуры получения данных и подготовка их к анализу.
2 Построение бизнес-модели.
3 Невероятная визуализация.
4 Разграниченный доступ к отчетам.

А теперь давайте разберем каждый пункт по отдельности.

1 Для подготовки данных к построению отчета, нужно единожды определить процедуру, выполняющую подключение к данным и их обработку и каждый раз, когда понадобится получить отчет за другой период, Power BI будет пропускать данные через созданную процедуру. Таким образом автоматизируется большая часть работы по подготовки данных к анализу. Но дело в том, что Power BI осуществляет процедуру подготовки данных с помощью инструмента, который доступен в классической версии Excel, и называется он Power Query. Он позволяет выполнить поставленную задачу в Excel абсолютно тем же способом.

2 Здесь та же ситуация. Инструмент Power BI для построения бизнес-модели имеется и в Excel это Power Pivot.

3 Как Вы, наверное, уже догадались, с визуализацией дело обстоит подобным образом: расширение Excel Power View справляется с этой задачей на ура.

4 Остается разобраться с доступом к отчетам. Тут не так все радужно. Дело в том, что Power BI это облачный сервис, доступ к которому осуществляется через персональную учетную запись. Администратор сервиса распределяет пользователей по группам и задает для этих групп различный уровень доступа к отчетам. Этим достигается разграничение прав доступа между сотрудниками компании. Таким образом, аналитики, менеджеры и директора заходя на одну и туже страницу видят отчет в доступном для них представлении. Может быть ограничен доступ к определенному набору данных, либо к отчету целиком. Однако, если отчет находится в файле формата Excel, то усилиями системного администратора можно попытаться решить задачу с доступом, но это будет уже не то. Я еще вернусь к рассмотрению этой задачи, когда буду описывать особенности корпоративного портала.

Стоит отметить, что, как правило, потребность компании в сложных и красивых дашбордах не велика и часто, для анализа данных в Excel, после построения бизнес-модели не прибегают к возможностям Power View, а пользуются сводными таблицами. Они предоставляют OLAP-функциональность, которой вполне достаточно чтобы решить большинство бизнес-аналитических задач.

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

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

ETL и DWH

В ранее рассматриваемых подходах к построению бизнес-отчетов, загрузка и подготовка данных к анализу осуществлялась с помощью технологии Power Query. Этот способ остается вполне оправданным и эффективным до тех пор, пока источников данных не много: одна учетная система и справочники из Excel-таблиц. Однако, с увеличением числа учетных систем, решение этой задачи посредством Power Query становится очень громоздким, трудным для поддерживания и развития. В таких случаях на помощь приходят инструменты для ETL.

С их помощью осуществляется выгрузка данных из источников (Extract), их преобразование (Transform), что подразумевает очистку и сопоставление, и загрузка в хранилище данных (Load). Хранилище данных (DWH Data Warehouse) это, как правило, реляционная база данных, расположенная на сервере. Эта база содержит данные, пригодные для анализа. По расписанию запускается ETL-процесс, который обновляет данные хранилища до актуальных. Кстати говоря, всю эту кухню прекрасно обслуживает Integration Services, входящие в состав MS SQL Server.

Далее, как и раньше для построения бизнес-модели данных и визуализации можно воспользоваться Excel, Power BI, либо другими аналитическими инструментами, такими как Tableau или Qlik Sense. Но прежде, мне бы хотелось обратить Ваше внимание еще на одну возможность, о которой Вы могли не знать, несмотря на то, что она Вам давно доступна. Речь идет о построении бизнес-моделей с помощью аналитических служб MS SQL Server, а именно Analysis Services.

Модели данных в MS Analysis Services

Этот раздел статьи будет более интересен тем, кто уже использует MS SQL Server в своей компании.

На данный момент службы Analysis Services предоставляют два вида моделей данных это многомерная и табличная модели. Кроме того, что данные в этих моделях связаны, значения показателей модели предварительно агрегируются и хранятся в ячейках OLAP кубов, доступ к которым осуществляется MDX, либо DAX запросами. За счет такой архитектуры хранения данных, запрос, который охватывает миллионы записей, возвращается за секунды. Такой способ доступа к данным необходим компаниям, таблицы транзакций которых содержат от миллиона записей (верхний придел не ограничен).

Excel, Power BI и многие другие солидные инструменты умеют подключаться к таким моделям и визуализировать данные их структур.

Если Вы пошли продвинутым путем: автоматизировали процесс ETL и построили бизнес-модели при помощи служб MS SQL Server, то Вы достойны иметь свой собственный корпоративный портал.

Корпоративный портал

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

Однако, пока не понятно, как будет организовано отображение отчетов на странице портала. Чтобы ответить на этот вопрос, сначала нужно определиться с технологией, на основе которой будет строиться портал. Я предлагаю взять за основу один из фреймворков: ASP.NET MVC/Web Forms/Core, либо Microsoft SharePoint. Если в Вашей компании имеется хотя бы один .NET разработчик, то выбор не составит труда. Теперь можно подбирать встраиваемый в приложение OLAP-клиент, способный подключаться к многомерным или табличным моделям служб Analysis Services.

Выбор OLAP-клиента для визуализации

Сравним несколько инструментов по уровню сложности встраивания, функциональности и цене: Power BI, компоненты Telerik UI for ASP.NET MVC и компоненты RadarCube ASP.NET MVC.

Power BI

Чтобы организовать доступ сотрудников компании к отчетам Power BI на странице своего портала, нужно воспользоваться функцией Power BI Embedded.

Сразу скажу, что потребуется лицензия Power BI Premium и дополнительная выделенная емкость. Наличие выделенной емкости позволяет публиковать дашборды и отчеты для пользователей в организации без необходимости приобретать лицензии для них.

Сначала отчет, сформированный в Power BI Desktop, публикуется на портале Power BI и потом, с помощью не простой настройки, встраивается в страницу web-приложения.

С процедурой формирования простого отчета и его публикацией вполне может справится аналитик, а вот со встраиванием могут возникнуть серьезные проблемы. Так же очень непросто разобраться с механизмом работы этого инструмента: большое количество настроек облачного сервиса, множество подписок, лицензий, емкостей сильно повышают требование к уровню подготовки специалиста. Так что эту задачу лучше поручить IT-специалисту.

Компоненты Telerik и RadarCube

Для встраивания компонентов Telerik и RadarCube достаточно владеть программными технологиями на базовом уровне. Поэтому профессиональных навыков одного программиста из IT-отдела будет вполне достаточно. Все что нужно, это разместить компонент на web-странице и настроить их под свои нужды.

Компонент PivotGrid из набора Telerik UI for ASP.NET MVC встраивается на страницу в изящной манере Razor и предоставляет самые необходимые OLAP-функции. Однако, если требуется более гибкие настройки интерфейса и развитый функционал, то лучше использовать компоненты RadarCube ASP.NET MVC. Большое количество настроек, богатый функционал с возможностями его переопределения и расширения, позволят создать OLAP-отчет любой сложности.

Ниже приведу таблицу сравнения характеристик рассматриваемых инструментов по шкале Низкий-Средний-Высокий.


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

Условия выбора Power BI

• Вас интересуют отчеты, насыщенные разнообразными показателями и элементами, связанными с данными.
• Вы хотите, чтобы сотрудники, работающие с отчетами, могли легко и быстро получать ответы на поставленные бизнес-задачи в интуитивно понятной форме.
• В штате компании имеется IT-специалист, с навыками BI-разработки.
• В бюджет компании заложена крупная сумму на ежемесячную оплату облачного сервиса бизнес-аналитики.

Условия выбора компонентов Telerik

• Нужен простой OLAP-клиент для Ad hock анализа.
• В штате компании имеется .NET разработчик начального уровня.
• Небольшой бюджет на разовую покупку лицензии и дальнейшее ее продление со скидкой менее 20%.

Условия выбора компонентов RadarCube

• Необходим многофункциональный OLAP-клиент с возможностью кастомизации интерфейса, а также поддерживающий встраивание собственных функций.
• В штате компании имеется .NET разработчик среднего уровня. Если такого нет, то разработчики компонента любезно предоставят свои услуги, но за дополнительную плату, не превышающую уровня оплаты труда штатного программиста.
• Небольшой бюджет на разовую покупку лицензии и дальнейшее ее продление со скидкой 60%.

Заключение

Правильный выбор инструмента для бизнес-аналитики позволит полностью отказаться от формирования отчетности в Excel. Ваша компания сможет постепенно и безболезненно перейти к использованию передовых технологий в области BI и автоматизировать работу аналитиков всех отделов.

Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку Postgres-новостей.

PostgreSQL 14 Beta 1

Релизная группа в составе Пит Гейган (Pete Geoghegan, Crunchy Data), Мишель Пакье (Michael Paquier, VMWare) и Эндрю Данстан (Andrew Dunstan, EDB) предлагают опубликовать бету 20-го мая, как это и происходило с предыдущими бетами.

Commitfest afterparty

PostgreSQL 14: Часть 5 или весенние заморозки (Коммитфест 2021-03)

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

Вот авторский тизер:

• Может ли один запрос параллельно выполняться на разных серверах?
• Как найти запрос из pg_stat_activity в pg_stat_statements?
• Можно ли добавлять и удалять секции секционированной таблицы не останавливая приложение?
• Как пустить разработчиков на прод чтобы они могли всё видеть, но ничего не могли изменить?
• Почему VACUUM после COPY FREEZE заново переписывает всю таблицу и что с этим делать?
• Можно ли сжимать TOAST чем-то кроме медленного zlib?
• Как понять сколько времени длится блокировка найденная в pg_locks?
• Для чего нужны CYCLE и SEARCH рекурсивному запросу?
• Текст функций на каких языках (кроме C) не интерпретируется при вызове?

Миграция

CHAR(1) to Boolean transformation while migrating to PostgreSQL

В Oracle нет типа boolean, а в PostgreSQL есть. Но почему бы не использовать этот тип, если в исходной оракловой базе есть столбец boolean, который хранится там в виде CHAR(1) с ограничением CHECK? Можно. Но хотелось бы ещё получить гарантию, что значения, отличные от резрешенных для Postgres не остановят работу приложения, а будут должным образом обработаны. Для этого можно создать CAST:

CREATE CAST (char as bool) WITH FUNCTION char_to_bool(char);

Далее автор Дилип Кумар (Dileep Kumar, MigOps) показывает изменение поведения при определении CAST как IMPLICIT, а потом прогоняет запрос (обычный SELECT) на тестах, чтобы увидеть разницу CHAR(1) vs Explicit Casting vs Implicit Casting vs Boolean. Побеждает, как и ожидалось, Boolean.

Choice of Table Column Types and Order When Migrating to PostgreSQL

В статье Стивена Фроста (Stephen Frost) с участием его коллеги по Crunchy Data Дэвида Юатта (David Youatt) тоже говорится о том, какой тип выбрать в PostgreSQL при миграции, но ещё и о том, в каком порядке располагать столбцы, чтобы данные выбранных типов хранились максимально эффективно. Сначала самые широкие поля с фиксированной шириной, затем менее широкие с фиксированной и только потом поля переменной ширины иначе появятся дыры в данных. Стивен рассказывает и про неприятные сюрпризы с выравниванием, которые можно получить, излишне рьяно экспериментируя с типами PostgreSQL. Ещё совет: выбирайте NUMERIC или DECIMAL только тогда, когда необходимо (считая деньги, например), а если нет, то обходитесь NTEGER, BIGINT, REAL, DOUBLE PRECISION это проще и эффективней.

Масштабирование

Lessons Learned From 5 Years of Scaling PostgreSQL

Джо Уилм (Joe Wilm) обобщает опыт использования PostgreSQL в компании OneSignal. Система доросла за 5 лет до 75 ТБ на 40 серверах. Понятно, что не все технические решения были приняты сразу на вырост. Как решают проблемы масштабирования, и как их можно было избежать об этом и рассказывает автор. Для удобства он разбил статью по разделам (сознательно не перевожу, слишком много английских слов пришлось бы писать кириллицей):
Bloat таблиц и индексов. Коротко о (хорошо известных) причинах распухания. pg_repack справлялся так себе (см. причины), написали собственный демон, координирующий его работу. Перешли к pgcompacttable там, где pg_repack обваливает производительность (перешли не везде, pgcompacttable работает надёжней, но медленней). Есть и об уловках по ситуации: в системе были таблицы, в которых большие поля (около 1 КБ) в личных данных, и поле last_seen_time int, которое часто обновлялось. Их разнесли по разным таблицам: одним JOIN больше, зато не копятся килобайты при обновлении строки.
Database upgrade. Мажорные и минорные. С мажорными справлялись при помощи логической репликации pglogical. При минорых просто перестартовывали postgres.
Wraparound. Серьёзная проблема для таких нагрузок. Остановились на оповещениях при приближении к 250 млн оставшихся XID. Напомним, конечно, что в Postgres Pro Enterprise 64-битные XID.
Replica Promotion. Для этого обходятся средствами haproxy. Упоминается только Patroni, но и то в контексте мы не используем, но может и стоило. Для каждой логической базы данных есть два бэкенда: один read-write, другой read-only. Переключение занимает пару секунд.
Partitioning и Sharding. Важнейшая штука для такой базы, конечно. Сначала порезали на 16 секций, потом на 256, а в ближайших планах 4096. Резали на куски выбирая в качестве критерия разбиения id пользователей системы. Сейчас думают над созданием data proxy слое, который будет знать, как разрезаны данные и где лежат, и действовать соответственно. Чтобы приложениям этого не требовалось знать для нормальной работы. Сетуют, что не сделали так с самого начала.

Самокритика

Чего энтерпрайзу в PostgreSQL не хватает

Вот чего ему не хватает в порядке важности (по Кириллу Боровикову, автору статьи)

• легковесного менеджера соединений (он же built-in connection pooler);
• 64-bit XID;
• микротаблиц (речь о том, что у каждой таблицы и индекса в PostgreSQL есть 3 форка файла, но почему бы не обойтись 1 файлом (heap) для мелких справочных табличек?);
• zheap;
• append-only storage (а в идеале, считает Кирилл хотелось иметь возможность назначать часть полей индексов или целых таблиц как no-MVCC чтобы иногда экономить на полях поддержки MVCC);
• отложенная индексация (чтобы сервер мог размазать необходимые операции во времени для балансировки нагрузки эта тема особенно важна для конкуренции с поисковыми системами, где основная задача найти вообще, а не найти прямо сразу сейчас);
• columnar storage (в идеале в ядре или в contrib);
• in-memory storage (очень быстрого нетранзакционного хранилища без сброса на диск);
• не пухнущих TEMPORARY TABLE, в том числе на репликах;
• multimaster из коробки;
• SQL-defined index (уметь описывать новые виды индексов прямо на SQL/PLpgSQL);
• мониторинга производительности запросов (здесь Кирилл предлагает глянуть, как это визуализируется на родном explain.tensor.ru);
• снапшотов статистики таблиц (как в pg_profile [а тем более в pgpro_pwr примечание редакции]).

К ЭТОМУ ДОБАВИЛИСЬ ХОТЕЛКИ ИЗ КОММЕНТАРИЕВ:

• IS NOT DISTINCT FROM при индексации;
• failover из коробки (аналогично Always on у MS SQL) без Patroni и сопутствующих;
• Asynchronous IO и Direct IO;
• бесшовного обновления мажорной версии;
• flashback queries;
• edition-based redefinition;
• нормальной компрессии.

Некоторые из этих хотелок на пути к дальнейшим версиям, некоторые уже есть в Postgres Pro Enterprise (о чём не умалчивает и автор).


Видео-вторник s02e15: Десять проблем PostgreSQL. Мониторинг запросов, pg_profile

(это продолжение вторника ) с Андреем Зубковым)

Статья Рика Брэнсона: (Rick Branson) 10 things I Hate In Postgres внезапно попала в топ обсуждаемых. Вот её не миновали и устроители ruPostgres.Вторников Николай Самохвалов и Илья Космодемьянский.

О ней мы писали в Postgreso 20. На ruPostgres.вторнике s02e15 6-го апреля самые жаркие вопросы возникали, как всегда, вокруг MVCC и VACUUM, переполнения 32-битных счётчиков XID.

На 50-й минуте обсуждения 10 ненавистных вещей Андрей Зубков продолжил рассказал о pg_profile (до pgpro_pwr речь опять не дошла, говорили даже о том, чтобы наверстать в 3-й серии) и о своём патче pg_stat_statements: Track statement entry timestamp (ровно 1:00 записи).

Вторник 20-го апреля назывался Как поменять тип колонки в таблице PostgreSQL с 1 млрд строк без даунтайма?. Два разных варианта решения на уровне колонки и на уровне таблицы.

А совсем недавний 4-го мая о разном, например, о WAL-G vs. pgBackRest, об амазоновских инстансах на ARM, о которых чуть ниже. Список тем лежит в файле.

Облака и контейнеры

Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances

Александр Коротков в своём блоге пишет об опыте работы с новейшими облаками инстансы Graviton2 работают на амазоновских ARM-процессорах. Но следующие за модой расплачиваются некоторыми сложностями у ARM есть специфика (по мнению Александра работа с ними скорее напоминает работу с IBM Power).

Команды LSE (Large System Extensions), доступные с версии 8.1, действительно ускоряют работу. Вот здесь это разъясняют с некоторыми подробностями, испытывая MySQL на включенных и отключенных LSE. Александр же получил колоссальный выигрыш на pgbench, скомпилировав PostgreSQL 14 с поддержкой LSE. Но это касается только амазоновских ARM AWR Graviton2. Apple M1 не удалось оптимизировать (возможно, в этих процессорах есть какая-то внутренняя оптимизация), а на китайских Kunpeng 920 результаты даже ухудшились.

Что делать

Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL

Кит Фиске (Keith Fiske, Crunchy Data) регулярно пишет в своём собственном блоге Keith's Ramblings о вакууме, распухших индексах и других важнейших для вдумчивого постгресиста вещах.

В этой статье есть конкретные SQL-запросы, использующие autovacuum_freeze_max_age для получения внятной информации о происходящем с конкретными таблицами, так как vacuumdb --all --freeze --jobs=2 --echo --analyze всего кластера баз данных во многих случаях слишком радикальная мера. Если недовакуумированных таблиц очень много, то Кит советует вакуумировать в батчах не больше сотни в каждом. Сам он предпочитает держать max XID < to 50% autovacuum_freeze_max_age, лучше 30-40%.

Он написал статью и о настройке автовакуума: Per-Table Autovacuum Tuning. Но даже аккуратно настроив автовакуум, стоит с не меньшей аккуратностью мониторить ситуацию. Риск не велик, но ставка высока, как говорили наши деды.

Не удержусь от перечисления собственных проектов Кита (или с его существенным участием):
pg_partman расширение с автоматической поддержкой секционирования по времени и serial id;
pg_extractor продвинутый фильтр дампа;
pg_bloat_check скрипт для мониторинга таблиц и индексов;
mimeo расширение PostgreSQL для потабличной логической репликации;
pg_jobmon расширение для логирования и мониторинга автономных функций.

Postgres is Out of Disk and How to Recover: The Dos and Don'ts

Статья Элизабет Кристинсен (Elizabeth Christensen) с участием Дэвида Кристинсена (David Christensen), Джонатана Каца (Jonathan Katz) и Стивена Фроста (Stephen Frost) все из Crunchy Data. Почему забился диск, что НЕ делать, и что делать.
Возможные причины:

• отказала archive_command и WAL начал заполнять диск;
• остались слоты репликации у стендбая, а реплика стала недоступна: опять же WAL заполняет диск;
• изменения в базе настолько большие, что генерящийся WAL съедает всё доступное дисковое пространство;
• просто-напросто данных было слишком много, а средства мониторинга и предупреждения не сработали.

Что НЕЛЬЗЯ делать:
удалять WAL-файлы нельзя категорически;

• не дайте переписать существующие данные, восстанавливаясь из бэкапа;
• Никакого resize.

Что надо делать:

• сделайте сразу бэкап на уровне файловой системы;
• создайте новый инстанс (или хотя бы новый том) с достаточным местом, убедитесь, что Postgres остановлен и сделайте бэкап директории данных PostgreSQL (обязательно директории pg_wal и недефолтные табличные пространства), чтобы вам было куда вернуться, если понадобится;
• когда база данных заработала, просмотрите логи, разберитесь, из-за чего возникли проблемы и почините поломки, если это возможно.

В статье рассказывается, как архивируется WAL, об попорченных архивах, кое-что о pgBackRest, а ещё предлагается почитать How to Recover When PostgreSQL is Missing a WAL File.

Кстати, о WAL. Если нужно порекомендовать хорошую статью англоязычным коллегам, то в блоге Postgre Pofessional опубликован перевод 3-й части серии Егора Рогова о WAL: WAL in PostgreSQL: 3. Checkpoint. Оригинал её здесь, en-начало-серии здесь, а ru-начало здесь.

Из блога БРЮСА МОМДЖАНА

(то есть отсюда)

Jsonb Multi-Column Type Casting

Брюс делится радостью, что есть jsonb_to_record() и можно без всяких двойных двоеточий сразу сказать:

SELECT a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER);

(А ведь добавим от себя есть ещё и jsonb_to_recordset(jsonb)).

Брюс обращает внимание на устройство таких запросов. Если сказать

SELECT x.a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER)WHERE b <= 4;

то это будет работать, ведь b уже integer потому, что запрос уже создал табличку x с областью видимости только внутри запроса, где типы уже преобразованы. Немногословный (как обычно в своём блоге) Брюс предлагает ознакомиться с деталями в тредах json_to_record Example и Abnormal JSON query performance.

Oracle vs. PostgreSQL

Брюс решил оценить функциональную полноту обеих СУБД в %, в ответ на чьё-то сравнение Postgres и Oracle это как резиновая уточка против танкера водоизмещением 300 тыс. тонн. Он считает:
Более реалистичной была бы оценка в 80-90%, в зависимости от того, какая функциональность для вас важней. Но можно бы поговорить и том, что в Postgres есть, а в Oracle нет. С точки зрения админа получится, может быть, и меньше 80%, а вот с точки зрения разработчика в Oracle нет многого, и оценка перевалит за 100%.

Challenging Assumptions

Следующие, некогда справедливые допущения теперь сомнительны:

• платный софт всегда лучше бесплатного;
• открытый код не столь безопасен, так как слабые места видны;
• серьёзные люди софт с открытым кодом не разрабатывают;
• Oracle лучшая СУБД;
• со знанием Oracle без работы я не останусь;

Кто закрывает дыры и латает щели (в оригинале Database Software Bundles)

Проект Postgres дал миру великолепную, полнофункциональную СУБД. Но когда пользователь думает о бэкапе, мониторинге, высокой доступности, ему приходится смотреть на сторону, так как возможности Postgres могут не совпадать с его потребностями. Иногда бреши закрывают проекты с открытым кодом, но в других случаях решают проблемы коммерческие Postgres-компании: Cybertec, edb, HighGo, Ongres, Postgres Pro, sra oss и другие, которые поставляют сервисы последней мили для корпоративных решений.

Также можно заглянуть в

Shared Memory Sizing
или, скажем, в
Replica Scaling by the Numbers

ИИ

Regression Analysis in PostgreSQL with Tensorflow

Дейв Пейдж (Dave Page, вице-президент и главный архитектор EDB) продолжает серию, посвященную ИИ и статистическим методам анализа данных. Из последнего: вышли две статьи посвященные регрессионному анализу, который ускоряют с помощью Tensorflow. В приведенных примерах можно увидеть много ласкающих слух питониста слов: pandas, numpy, matplotlib и seaborn. Подчеркнём, что используется расширение PostgreSQL plpython3u, а не просто внешние по отношению к базе библиотеки.

Во второй части дело доходит до пред-обработки данных. Используется популярный у педагогов машинного обучения набор данных Boston Housing Dataset по ним тренируются угадывать цену дома в Бостоне в зависимости от некоторых факторов. Из набора выкидывают значения, сильно отличающиеся от общей массы, чтобы не запутать нейронную сеть при обучении. Ещё смотрят распределения и строят корреляции. Третья статья ещё не вышла. Обещано, что в ней уже воспользуются достижениями 2-й части, чтобы обучать нейронную сеть регрессионному анализу.

Релизы

Kubegres

Обычно в разговоре о PostgreSQL в Kubernetes на третьей фразе появляются операторы от Crunchy Data и Zalando. Kubegres, возможно, вклинится в разговор. Разработчик Алекс Арика (Alex Arica, Reactive Tech Limited). Создавался Kubegres на базе фреймворка Kubebuilder version 3 (SDK для разработки Kubernetes APIs с использованием CRD. Можно забрать отсюда.

KuiBaDB

KuiBaDB это Postgres для OLAP, переписанный с Rust и многопоточностью. У этой СУБД есть только базовая функциональность. Она, например, поддерживает транзакции, но не вложенные транзакции. KuiBaDB создан для разработчиков, чтобы они могли быстренько проверить на ней свои идеи. В ней есть векторный движок и колоночное хранение, она опирается на каталоги (catalog-driven).

pgBackRest 2.33

Появилась поддержка нескольких репозиториев данные и WAL можно копировать сразу в несколько хранилищ.
pgBackRest поддерживает теперь GCS Google Cloud Storage.
Отныне можно задать путь вручную с ./configure --with-configdir. Стало удобней работать с не-Linux ОС, например с FreeBSD.
Появилось логирование в процессе бэкапа.

pg_probackup 2.4.15

В новой версии pg_probackup при бэкапе в инкрементальном режиме автоматически обнаруживается переключение таймлайнов, за счёт использования команды TIMELINE_HISTORY протокола репликации (предложил Алексей Игнатов).

При операциях merge и retention merge теперь тоже можно использовать флаги --no-validate и --no-sync.

pgmetrics 1.11.0

pgmetrics утилита с открытым кодом для сбора статистики работающего PostgreSQL, распространяемая в виде единого бинарного файла без внешних зависимостей. Разработчик RapidLoop, у которой есть ещё и pgDash, для которой pgmetrics собирает статистику.

Новое в версии:

• собирает и парсит логи из AWS RDS и Aurora, используя CloudWatch;
• поддержка пулера Odyssey v1.1;
• улучшена поддержка Postgres 13;
• улучшена поддержка метрик AWS RDS;
• появились бинарники для ARMv8

Скачать можно отсюда.

HypoPG 1.2

HypoPG одно из произведений Жульена Руо (Julien Rouhaud). Это расширение для работы с гипотетическими индексами. Новое в версии: работая на стендбае, hypopg использует фальшивый (fake) генератор oid, который одалживает их внутри интервала FirstBootstrapObjectId / FirstNormalObjectId, а не генерит реальные. Если потребуется, можно работать по-старому, используя опцию hypopg.use_real_oids. Есть и ещё изменения, hypopg_list_indexes(), подробности в документации.

pgstats.dev

Это динамическая диаграмма Postgres Observability упрощенное представление устройства PostgreSQL и доступные системные представления и функции для получения статистики о работе подсистем Postgres. Этому необычному произведению Алексея Лесовского (Data Egret) всего 5 месяцев, но её знают многие DBA, спорят и интересуются: что новенького? Новое, например, вот:

• стрелки, которые раньше показывали связи между блоками и метками статистики, теперь исчезли, а соответствующие цвета введены, чтобы показать их отношения;
• на страницах описания статистик (см. pg_stat_progress_create_index в качестве примера) улучшена внутренняя навигация за счет добавления ссылок на связанные элементы;
• добавлены ресурсы внешние ссылки с дополнительной информацией;
• теперь есть управление версиями, чтобы вы могли видеть, как Postgres эволюционировал от одной версии к другой.

AGE 0.4.0

Расширение, добавляющее графовую функциональность. Новшества в 0.4.0 здесь.

pg_log_statements 0.0.2

pg_log_statements расширение PostgreSQL, которое позволяет логировать SQL-запросы так, что переменная log_statement может быть установлена для отдельного серверного процесса (по id или фильтру), а не на уровне базы или инстанса.

Можно зайти на PGXN или на гитхабе создателя Пьера Форстмана, специалиста по Oracle.

Конференции

PostgresLondon 2021

Состоится уже 12-го мая, виртуальная. Расписание.

Highload++

Состоится офлайн 17 -18 мая в Крокус-Экспо, Москва. Расписание.

Postgres Vision 2020

Postgres Vision виртуальная конференция EDB, но участие свободное. Состоится 22-23 июня. Регистрация.

Следующий номер Postgresso 32 выйдет в первых числах июня.