Александр Ипатов
Старший разработчик ГК Юзтех
Всем привет!
Я — Ипатов Александр, backend-разработчик в ГК Юзтех. В этой статье хочу поделиться лайфхаками, которые использую при реализации дашбордов в Microsoft Power BI.
Мало просто создать отчет, в который за пару минут можно подключить файловые источники данных (excel или csv-таблицу). А если данные не в формате файлов, а через подключения к БД, то это уже «next level play». Разработчику BI продуктов после создания самого файла отчета (весь «визуал», подписи, метрики и прочие элементы создаются без особых проблем), нужно задуматься над вопросом: а что дальше?
Для таких ситуаций я предлагаю свой шорт-лист лайфхаков в Power BI, среди которых каждый найдет что-то для себя. Ну или просто скажет «я это итак знал», всегда рад за таких специалистов!
Используй Power BI с бесплатной лицензией в формате self-service
Новости осени 2024 года пестрят заголовками «Microsoft уходит из РФ». Да, официально и в формате платных лицензий его продукты уже невозможно использовать (для юрлиц в РФ однозначно нельзя, для юрлиц других стран – можно). Но я не переживаю на этот счет, если хочу построить дашборд в Power BI для себя или для относительно небольшой компании: тем, кому не нужно несколько учетных записей, а будет достаточно работать с одной, на которой будет бесплатная лицензия. Причем, в таких случаях я не ограничен 1 дашбордом, а могу создать и опубликовать себе много таких дашбордов. В этом случае все отчеты данной УЗ с бесплатной лицензией публикуются в «Мою рабочую область». На этих дашбордах можно спокойно установить автоматическое обновление отчетности, предварительно настроив «локальный шлюз данных» на компьютере. Это делается посредством установки программы «On-premises data gateway» для Windows. После установки логин — под имеющейся учетной записью Microsoft Power BI.
Для того, чтобы зайти в УЗ на сайт app.powerbi.com не нужны дополнительные методы, просто прописывай его в адресной строке браузера.
Ускоряй процесс обновления наборов данных в Power BI
Подходи к разработке отчетности как к комплексному процессу. Одно дело – просто подключить SELECT выборку данных из БД в отчет, а совсем другое дело реализовать эту выборку оптимально. Пример: есть обширное хранилище данных с таблицами, данные в которых находятся там с 2010-х годов, то есть имеется приличная историчность. Как думает новичок: «Да сделаю SELECT * FROM Table, а потом внутри Power BI выделю те данные, которые мне нужны, а лишние удалю». Как думает опытный: «Рассмотрю различные варианты оптимизации этого набора данных». Предлагаю 4 варианта решений:
А) Уменьшение выборки данных.
Свяжись с бизнес-пользователями отчетности, уточни, точно ли все данные должны быть отражены в дашборде. Зачастую, исторические данные, тем более 5-летней давности и старше, уже не обновляются, и можно рассмотреть варианты исторического отчета, который вообще не будет требовать его обновления, а просто находиться в формате итогового дашборда за период.
Б) Оптимизация sql-запросов, при невозможности ее уменьшения.
Если бизнес-пользователи настояли на едином дашборде, тогда первым этапом нужно постараться оптимизировать SQL-запрос, который формирует выборку требуемых данных. Разработчику в этом случае желательно уметь читать планы запросов, знать способы доступа к данным (последовательное сканирование, индексный доступ, сканирование по битовой карте) и способы соединений (вложенным циклом, кэшированием и слиянием).
Вот перечень того, что обычно может положительно повлиять на время выполнения запросов (здесь и далее буду говорить про диалект PostgreSQL):
пытайся уходить от LEFT JOIN в сторону INNER JOIN (особенно, если в результате NULL-значения в выборке отсекаются конкретными фильтрами по полям таблицы, которую мы LEFT JOIN-им);
смотри на план запроса (EXPLAIN, EXPLAIN ANALYZE) и обращай внимание на стоимости, кардинальность и способы доступа к таблицам. Возможно, есть большая таблица, по одному из полей которой отсутствует индекс и происходит Seq Scan, тогда как по индексу было бы в разы быстрее. Желательно взаимодействие с dwh-отделом, так как вопрос построения новых индексов на больших таблицах всегда палка о двух концах, тут нужен взгляд аналитиков или dba;
большие таблицы, которые в конце запроса ограничиваются условием в блоке WHERE, попробуй убирать в CTE или вложенные запросы, я бы дал 95% вероятность положительного исхода при этой реализации;
вынеси из блока WHERE условия в условия самих JOIN-ов, конечно, не ошибись с LEFT JOIN при этом;
разбей запрос на части, некоторые из которых вынеси с CTE, подзапрос или временную таблицу (у которой еще и можно реализовать временный индекс);
временные таблицы с 1 столбцом попробуй заменить на массивы;
если в выборке имеются запросы к представлениям (view), к которым в итоге в блоке WHERE накладываются ограничения – уйди от использования представления, сделай CTE, подзапрос или временную таблицу с использованием этих ограничений в изначальном запросе view;
частный случай: при наличии LEFT LATERAL JOIN (SELECT … FROM …. ORDER BY …. LIMIT 1) – преобразуй в каскад из 2-х CTE, в первой из которой дополнительно построй оконную функцию ROW_NUMBER() по полям из ORDER BY, во второй оставить лишь значение со значением оконной функции row_number = 1.
В) Перевод большой, постоянно загружаемой выборки данных в составной вид.
Выборку разделяй на большую не загружаемую (холодную) часть и небольшую загружаемую (горячую) часть с их последующей склейкой внутри Power BI. Тогда общее время выполнения будет включать в себя время на запрос данных к БД за «горячими» данными и на непосредственно склейку этих наборов. Это реализуется отключением настройки «Включить в обновление отчета» в редакторе «Power Query» у набора данных. Главный минус состоит в том, что сам pbix-файл отчета становится очень объемным, и могут возникнуть две следующие проблемы: не качается актуальный pbix-файл с облака Power BI и/или не публикуется pbix-файл в рабочую область. На своей практике имел отчет с объемом в 6 Гб, появлялись такие сложности.
Г) Увеличение канала интернета.
Скорость интернета влияет с 2-х сторон взаимодействия: на самом локальном шлюзе данных интернет забирает данные из БД, но и на самой БД могут быть ограничения на канал передачи данных. Поэтому тут нужно взаимодействовать с отделом ИТ.
Используй DAX Studio при написании мер в Power BI
Метрики внутри Power BI (меры) пишутся с использованием диалекта DAX. Это предоставляет, на мой взгляд, самые широкие возможности среди всех BI продуктов. И специально для этого существует программа DAX Studio. Впервые опробуя эту программу, ты сразу добавишь ее себе на панель задач. В разрезе мер она помогает получить результат выполнения меры, протестировать некоторые сложные случаи, их оптимизировать. Среди более продвинутого функционала это отладка с помощью точек останова и просмотра окон. Используй DAX Studio в тандеме с документацией на официальном сайте Microsoft о DAX-мерах, поскольку новые меры и доработки старых реализуются в практически в ежемесячном формате.
Реализуй табулярную модель с помощью Tabular Editor
Часто бывают случаи, когда нужны несколько разных дашбордов на одинаковых (или почти) наборах данных. В таких ситуациях подходи комплексно к реализации отчетов. Не делай каждый отдельный дашборд со своим наполнением и постарайся уйти от постоянных CTRL+C и CTRL+V этих одинаковых витрин данных внутри самого Power BI. Сделай табулярную модель в SQL Server Analysis Services. Это позволит иметь уже готовую модель данных (когда загружены все источники, налажены связи, добавлены комментарии).
Минус для разработчика:
Теперь меры, которые будут отображаться на дашбордах в Power BI, будут возможны к добавлению / редактированию лишь в специальной программе по работе с моделями Tabular Editor. Поначалу это будет вызывать неудобство, ведь привык все всегда делать в Power BI Desktop. Запрос бизнеса по созданию новой / доработке старой меры – это переход в Tabular Editor (+ уже знаем, что DAX Studio всегда под рукой).
Плюсы для разработчика:
При необходимости доработки наборов данных в трех дашбордах с одинаковым подключением к табулярной модели достаточно внести изменение лишь единожды в саму табулярную модель (будь то изменение sql-запроса выборки данных из БД, изменение мер и другие). Тогда каждый из трех отчетов уже будет считывать измененные данные, в отличие от стандартных подходов по доработке каждого из отчетов в отдельности.
У дашбордов можно настроить режим «живого» подключения (Direct Query) к табулярной модели, что гарантирует наличие самых свежих данных в отчете на «сейчас», и не будет необходимости запускать обновление отчетов, ведь отчеты уже будут иметь актуальную информацию из модели.
Используй все каналы связи с сообществом пользователей Power BI
Найди и используй полезные форумы по Power BI, телеграм-каналы разработчиков да и в принципе аналитиков данных. Там ты можешь задать вопрос или сразу найти подсказки или полные решения схожих с твоими задач. Сообщество очень широкое. И со временем ты будешь помогать коллегам с возникающими на их стороне трудностями!
В свое время, я нашел в телеграм-канале (https://t.me/PBI_Rus — сообщество с 10.000 участников) полезный совет по реализации такой фичи: реализация на дашборде графика со скользящей осью Х, которая должна зависеть от даты, которую выбирают на срезе основного календаря на странице отчета. Например, выбрал сегодня – тогда график строится за последние 30 дней от сегодня, выбрал в срезе другую дату – 30 дней назад от той даты.
Здесь с решением мне помогли как-раз просто участники сообщества, скинув ссылку на статью. В результате я реализовал фичу через второй календарь, с включением связи между календарями и выстраиванием нужной глубины реализации ретроспективной части.
Заключение
В любом деле всегда имеют место привычки и некие особенности того, что ты любишь или не любишь делать (проще скопировать старое, чем писать новое). Все были новичками и совершали массу ошибок, неоптимальных реализаций, и в принципе чаще «костылили», чем задумывались о качественной архитектуре решений. На своем опыте, я действительно прошел это, в связи с чем и возникла идея поделиться своим взглядом на некоторые лайфхаки по разработке BI решений в Microsoft Power BI. Очень надеюсь, что статья поможет начинающим и не только специалистам в ускорении процесса разработки и повышения качества реализаций!
