Привет, Хабр! Меня зовут Николай, я DBA-разработчик SimbirSoft. В 2022 году многие мировые IT-гиганты дружно свернули свою деятельность и покинули старательно обустроенный под себя российский рынок. Клиенты быстро столкнулись с последствиями этих событий и вынуждены были искать альтернативные решения. Не будет преувеличением сказать, что наибольшему стрессу подвергались компании, использующие облачные решения. Стабильность их работы непосредственно зависела от провайдера в отношении обслуживания, обновлений и клиентской поддержки. Если провайдер прекращал  предоставлять качественные услуги, то это могло привести к значительным сбоям в работе клиента on-cloud сервиса. В интересах одного из них, пользователя Microsoft Azure Analysis Services (золотого стандарта современного IT), в течение почти 4-х месяцев наша небольшая проектная команда в срочном порядке решала задачу переноса критичной для бизнеса аналитической отчетности на привычный on-premise. 

Что нам необходимо было сделать: 

• Подготовить новую инфраструктуру. 

• Перенести, развернуть и адаптировать аналитическую модель.

• Подтянуть данные из необходимых источников.

• Перенести из MS Azure 15 бизнес-отчетов и запустить их в работу.

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

Всю работу выполняла команда из 6 человек: руководитель проекта и пять разработчиков. Важный момент — на проекте не было предусмотрено аналитиков, их суровый хлеб был поделен между разработчиками. Методология разработки — Agile. На ежедневные митинги отводилось около 30 минут.

График работ в виде диаграммы Ганта для разработчиков имел свою ценность. В том месте, где ее характерные полоски заканчивались звездочками (Рис. 1),  располагались вехи, привязанные к конкретной дате.

Они все время были в фокусе нашего внимания. К этой дате мы должны были отчитаться перед заказчиком за выполнение очередного этапа работ. Поэтому специалистов, которые совмещали роли разработчиков и аналитиков, ближе к вехе ждала более интенсивная работа. Шло плотное общение с техническими специалистами заказчика, с менеджером проекта и, конечно, с самим собой как разработчиком. Мы помнили главное — полтора десятка критичных для бизнеса отчетов должны были заработать на новой инфраструктуре через 3,5 месяца после начала работ. 

Предыстория

На время начала проекта облачное решение заказчика располагалось на платформе Microsoft Azure. Платформа известна с октября 2008 года (Рис. 2). Ее основные преимущества — это искусственный интеллект, продукты на основе блокчейна и тесное сотрудничество с корпоративными поставщиками, такими как SAP.

Также известно, что развитие облачных решений для MS является приоритетным в сравнении с on-premise. Все новое появляется сначала в Azure. Этот сервис в мире пользуется стабильно высоким спросом. Занимает на глобальном облачном рынке второе место с долей ~21% (на начало 2022 года) после Amazon Web Services. Надо сказать, что Microsoft многое для этого делает и не стесняется в средствах, когда речь заходит о безопасности ее cloud- сервисов. Например, из публичных источников известно, что для команды из более чем 3500 специалистов по кибербезопасности корпорация выделяет годовой бюджет на исследования в размере более 1 миллиарда долларов.

В 2022 году Microsoft сообщила, что выручка ее коммерческой облачной среды  официально достигла $75,3 млрд.

Судя по всему, у клиента Azure должно было быть все хорошо.

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

В общем, как писал Даниель Дефо в «Робинзоне Крузо»:

«Все наши сетования по поводу того, чего мы лишены, проистекают, мне кажется, от недостатка благодарности за то, что мы имеем». 

Всё разрешилось само собой. В начале 2022 года ожидания от IT-гиганта стремительно поменялись. В марте Microsoft свернула все операции в России на неопределенный срок, сославшись на американские санкции. Существующие услуги еще не были отключены, но платить за них становилось проблематично. Это наш случай. В июне 2022 компания сообщила, что приступила к сокращению своего бизнеса в России. К концу 2022 уже стало невозможно не замечать риски использования MS Azure (Рис.3):

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

Выбор и построение новой архитектуры

Первым и вполне понятным шагом заказчика стало решение как можно быстрее покинуть сервис Azure. Заместить предполагалось российским аналогом, либо на крайний вариант — on-premise от Microsoft, так как вся локальная IT-инфраструктура, дополняющая Azure, была выстроена вокруг MS SQL. При этом важно было сохранить наработанные в Azure отчеты. По словам заказчика, в них на тот момент были сформулированы и формализованы основные метрики бизнеса. Потрачено немало времени.

На российском рынке аналогов оказалось более чем достаточно. Но в конечном итоге в предварительную оценку мы включили только облако от Яндекса, это решение показалось самым проработанным. Таким образом, на чаше весов оказались две совершенно разные технологии: Yandex Cloud из будущего и on-premise от Microsoft из настоящего, которая одной ногой уже в прошлом. Казалось, выбор очевиден.

Yandex Cloud. Несмотря на то, что его выручка от cloud-сервисов уступает MS Azure, более чем в 650 раз (по данным за 2022 год), сложилось впечатление, что Яндекс может предоставить впечатляющий набор современных технологий. А основной минус предложения заключается в качестве техподдержки. На тот момент также сформировалась некоторая убежденность, что в преодолении инцидентов служба поддержки Яндекса делает акцент на активном вмешательстве своих инженеров, а не на обучении пользователей преодолевать сложные моменты эксплуатации.

Microsoft on-premise. Обычно представляется набором серверных приложений, запущенных на собственном оборудовании компании. Перед нами стояла тактическая задача бегства из Azure и скорейшего запуска критичных для бизнеса отчетов. И потенциально решение on-premise для этой цели не выглядело атавизмом. Оно минимизировало любые потенциальные риски, которые могли исходить из применения Яндекс-облака, для которого не было ни подготовленных кадров, ни накопленного опыта использования. Также существенным плюсом в on-premise было то, что на нем мы сохраняем отчеты неизменными. Из очевидных минусов — лицензионные риски.

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

Совместно с заказчиком оценили варианты. И выбор состоялся в пользу MS on-premise. Для многих технических специалистов, участвующих в проекте, этот выбор был неочевидным и технически небезупречным, но с точки зрения достижения целей проекта вполне целесообразным. Сохранность отчетов, стабильность команды, проверенные технологии, управляемые риски — что еще надо, чтобы руководители проекта спали спокойно? Ну а минусы … эти риски не показались такими уж неподъемными. Было решено их принять. 

Так стала выглядеть новая структурная схема решения на MS SQL Server:

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

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

Данные. Поначалу в процессинг аналитической модели был включен только один источник – корпоративное хранилище данных (DWH), которое параллельно еще продолжало подпитывать опальное «облако». А целостность модели временно поддерживалась за счет привлечения справочников, оставшихся в Azure. Впоследствии источники Azure планировалось переподключить на DWH и другие необходимые для отчетов данные. Но и текущего объема уже было достаточно для первичной оценки всего решения и составления плана дальнейших действий.

Отчеты. На рисунке данные модели, полученные от DWH, проходят этап агрегирования в SSAS и становятся доступными для отчетов. Сами отчеты  разместили на web-сервере Power BI Report Server, и их перенос из облака не был затратным. После того как в редакторе Power BI Desktop RS настроили подключение к новым источникам, они заработали. Конечно, часть дашбордов, за неимением полных данных, пришлось отключить. Их последующее включение по мере наполнения модели данными должно стать одной из фоновых задач проекта.

Первые результаты тестирования всех слоев решения on-premise несколько охладили начальный оптимизм. Прежде всего потому, что:

1. Отчеты стали загружаться в 6-12 раз медленнее, чем в Azure. 

2. Время процессинга одной таблицы фактов из девяти могло достигать 1,5 часа.

3. Скорость работы отдельных функций SSAS была непредсказуемой. 

Такие проблемы характерны для уровня SSAS. Задача по их решению будет называться адаптация модели данных. Этот процесс вполне естественный. По правде сказать, он возникает при любом изменении критичных параметров модели (структура, резкое изменение объема данных, количество объектов и так далее). Особенно если эти изменения прошли по «железу». Как в шахматах… если противник двинул пешку, то надо корректировать свои планы. Тут же облака заменили на on-premise. Другая среда — другая модель. Придется разбираться.

Решение задач по адаптации аналитической модели

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

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

В процессе адаптации модели данных мы выделили шесть основных задач:

1. Оптимизация модели.

2. Оптимизация запросов DAX.

3. Оптимизация отчетов.

4. Материализация источников данных и секционирование таблиц фактов.

5. Распределение серверных ресурсов. 

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

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

1. Оптимизация модели

Решение на базе SSAS имеет несколько этапов обработки данных. 

Источники транзакций → DWH → DM → SSAS TABULAR (BISM на Рис. 5) → Отчеты

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

SSAS Tabular – это не что иное, как колоночная база данных c движком VertiPaq. Потребление памяти является ее функцией. Она специально разработана для задач аналитики, быстро отрабатывает запросы и имеет высокую степень сжатия, примерно 1/10.

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

Необходимо заметить, что даже среди IT-специалистов встречается смысловая путаница терминов «SSAS Tabular» и «OLAP-кубы». По сути это совершенно разные технологии для задачи анализа данных. Технологию OLAP-кубов MS более десяти лет не развивает и постепенно сворачивает ее поддержку в своих продуктах. Поэтому в новых аналитических проектах обычно выбирают SSAS Tabular (на платформе on-premise или Azure). В нашем случае с самого  начала SSAS Tabular  был установлен на довольно шустрое «железо» с 32 процессорными ядрами, ОЗУ 256Gb и дисками SSD. 

Аналитическую модель данных SSAS обычно представляют в виде связанных таблиц по схеме «звезда» или «снежинка». На рисунке ниже представлен список таблиц первоначальной модели с измененными названиями (NDA) и без дополнительных  источников. Таблицы описываются параметрами: наименование, кардинальность (сardinality), количество колонок, размер в байтах, способ кодирования и другими.

Cardinality характеризует уникальность данных в столбце (также в переводной литературе встречается  синоним «кратность столбцов»). Это одна из ключевых характеристик модели данных не только VertiPaq, но и любой другой колоночной СУБД. Высокая кардинальность – значит, столбец имеет большое количество уникальных значений. Наоборот, низкая кардинальность означает много повторяющихся значений.

Таким образом, если посмотреть, к примеру, на таблицу «Фкт_Счета», то можно сказать, что всего строк в таблице не менее 916 млн, а по факту более 1 млрд. Размер таблицы около 46 Gb с данными за два года. Это не устраивало заказчика. Поэтому на последних этапах проекта в таблицах фактов планировалось иметь данные за последние 8 лет. Ну а на старте работ аналитическая модель занимала вместе со словарями и индексами в памяти сервера около 120 Gb. И ей нужна была оптимизация.

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

1. В таблицах фактов — удаление несвязанных ID столбцов

2. Минимизация количества уникальных записей

3. Минимизация количества вычисляемых столбцов

4. Оптимизация сортировки столбцов

5. Разделение текстовых полей с высоким показателем cardinality на составные части.

6. Минимизация количества двунаправленных связей и устранение двусмысленности в расчетах.

7. Изменение кодировки столбцов и так далее.

В свою очередь, каждый из способов состоит из достаточно большого количества операций. Но у всех общий целевой принцип: «Уменьшаем объем данных и кратность столбцов». Последовательно и разумно применяя эти методы, получаем оптимизированную модель. 

Эти способы в основном продиктованы тем, что мы имеем дело с колоночной СУБД класса IN-Memory, в которой аналитическая модель и все вычисления над ней занимают один и тот же дорогостоящий ресурс – оперативную память сервера. 

2. Оптимизация запросов DAX

Вторым шагом в адаптации модели была оптимизация запросов DAX. В отчетах Power BI на верхних уровнях дерева вычислений часто используется полезная функция SWITCH()  (аналог CASE в T-SQL). Она вычисляет выражение, сравнивает его по списку с заранее подготовленными значениями и возвращает итоговый результат в виде вычисленного скалярного выражения. Так вот в нашем случае она повела себя довольно необычно. 

Рассмотрим пример. Есть три выражения, которые необходимо вычислить. Измеряя время автономного выполнения каждого выражения, получим t1, t2 и t3. А затем (Рис. 8) поместим их в функцию SWITCH() и три раза измерим уже время выполнения всей функции, задавая выражение «SELECTEDVALUE» таким образом, чтобы ее значение приводило к перебору и последовательному вычислению скалярных выражений 1, 2, 3 соответственно. Полученное время работы функции для трех случаев также присваиваем следующей группе переменных T1, T2, T3.

Сравнивая значения группы T1, T2, T3 и t1, t2, t3, увидим, что вместо ожидаемого

получаем

При выборе <2> или <3> время выполнения включало в себя не только время расчета выбранного скалярного выражения, но и всех предыдущих выражений тоже. Такого точно не наблюдалось в Azure. Гугл ответов не давал. После длительных и безуспешных попыток привести функцию SWITCH() к порядку, возникли идеи протестировать ее на другой, более свежей версии SSAS 2019. И, когда все было сделано, чудеса закончились. 

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

После замены версии сервера задачи по оптимизации запросов только начинались. Как и всякая другая оптимизация, этот процесс не лишен творчества, имеет перманентный характер, и для него MS предлагает использовать Visual Studio. На имеющихся у нас объемах данных ее возможностей было явно недостаточно. 

Чтобы задействовать все рычаги оптимизации, дополнительно понадобились инструменты партнеров MS (Рис. 9): 

1. DAX Studio для анализа модели, отладки запросов

2. Tabular Editor, который обеспечивает debugging запросов, создание вычисляемых групп. 

Причем debugging tools очень хорошо раскладывает запросы, но была доступна только для версии Tabular Editor 3.0. К тому же она платная. 

Хочется отметить, что стратегия MS в развитии Visual Studio нестандартная. В последующих выпусках этого инструмента не будут дублироваться те функции обработки Analysis Services, которые уже есть в разработках ее партнеров. Создатели MS так решили, потому что им так удобно. 

Поэтому разработчикам при работе с большими данными в дальнейшем придется мириться с таким набором «цветных карандашей» для одной задачи. Покупать и применять. Если, конечно, у вас есть SSAS (или Power BI).

3. Оптимизация отчетов

Ниже представлен пример отчета Power BI (Рис. 10), полученный из открытых источников. Подобным образом выглядели и отчеты заказчика. Отчеты интерактивные. Изменение одного или нескольких управляемых параметров приводит к полному обновлению всей страницы отчета. Если же количество дашбордов для прорисовки было чрезмерно большим для одной страницы, то это становилось еще одной задачей для оптимизации. 

Источник

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

Это значение может быть существенным и даже сопоставимым с временем исполнения запросов. А значит, оно напрямую влияет на общее время обновления отчета.

Соответственно, чтобы картинка обновлялась быстрее, Т _{обновления отчета} должен быть минимальным и удовлетворять техническим требованиям заказчика, а для этого необходимо стремиться к оптимальному дизайну страницы и не увлекаться количеством элементов. 

При оптимизации мы исходили из принципа «чем проще, тем быстрее» и разносили дашборды по страницам.  

4. Материализация источников данных и секционирование таблиц фактов

Хорошо известно, что аналитическую модель периодически требуется пополнять новыми данными. Как правило, не реже одного раза в сутки. Загрузка данных (она же процессинг) стартует после завершения всех процедур обработки на уровне слоя DWH — DataMart. Если процессинг выполняется продолжительное время, то он становится узким местом всего решения. В нашем случае, на первых этапах, процессинг мог длиться до 20 часов. То есть почти сутки загружаем вчерашние данные, и бизнесу для работы с ними времени уже не остается. Это проблема, которую надо было решать.

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

Решали мы эту задачу в два этапа.

1. Материализация источника данных. Она убирала из времени загрузки расчетные операции, которые свойственны представлениям, но по-прежнему оставляла однопоточный режим процессинга.

2. Партиционирование (то есть секционирование) больших таблиц фактов в самой аналитической модели, что позволило достичь режима многопоточности при загрузке данных. Причем на самих источниках создавали необходимые индексы, что еще больше повышало производительность загрузки таблиц фактов.

В секционировании есть и минус — повышенные накладные расходы инженеров на обслуживание и поддержание актуального состояния секционированных таблиц. Особенно если секции добавляются часто. В ходе проекта пришлось искать способ по устранения такого недостатка. И после его внедрения модель SSAS в части секционирования могла уверенно работать в режиме самообслуживания.

5. Распределение серверных ресурсов

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

Другими словами, процессорная многопоточность — это мощный и полезный механизм, который желательно настроить и использовать в работе Analysis Service. И это еще одна обязательная часть работ по оптимизации SSAS в архитектуре on-premise. Ее выполняли сотрудники заказчика.

Итоги

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

Если сосредоточиться только на технической части проекта, то можно утверждать, что процесс вынужденной миграции из cloud на on-premise решение Microsoft сопровождается следующими особенностями:  

• вынужденным вниманием к оптимизации большего количества критичных процессов;

• принятием дополнительных издержек при эксплуатации решения on-premise, разваленного по отдельным серверам;  

• повышенными лицензионными рисками.

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

Спасибо за внимание!

Больше полезных материалов для backend-разработчиков мы также публикуем в наших соцсетях – ВК и Telegram.