Освоение Power Query: преобразование плоских данных в схему «звезда»

By Mirko Peters · 2026-08-12 · Updated 2026-08-29

Automatically translated with AI from the original post. Read the English original

Titelbild: Mastering Power Query: Transforming Flat Data into a Star Schema

Добро пожаловать обратно в блог-компаньон к подкасту! В сегодняшнем посте мы подробно разберём технические механизмы моделирования данных. Если вам когда-либо доставался огромный плоский дамп данных из корпоративной системы, где каждая транзакция повторяет названия клиентов, категории товаров и географические локации миллион раз, вы знаете, какие проблемы это создаёт для отчётов. Медленная работа, запутанные формулы DAX и раздутые размеры файлов — это лишь верхушка айсберга. К счастью, существует проверенный архитектурный шаблон, на который полагаются профессиональные BI-разработчики, чтобы решить именно эту задачу: схема «звезда».

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

Введение в Power Query и основы схемы «звезда»

При создании решений в Microsoft Power BI структура исходных данных определяет всё, что идёт дальше. Схема «звезда» — это зрелая, высокооптимизированная техника моделирования данных, в которой бизнес-метрики хранятся в центральной таблице фактов, а описательные атрибуты бизнес-сущностей — в окружающих таблицах измерений. На диаграмме такая структура визуально напоминает звезду. Большинство BI-разработчиков выбирают этот метод, потому что он даёт решение для бизнес-аналитики, которое исключительно легко поддерживать и масштабировать.

Исследования последовательно показывают, что переход на этот шаблон приносит огромную отдачу. Так, примерно 80% пользователей Power BI, создающих модели по схеме «звезда», отмечают повышение производительности и эффективности анализа по сравнению с подходами на основе плоских таблиц. И наоборот, отказ от схемы «звезда» создаёт серьёзные трудности в ходе разработки:

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

Чтобы понять, как перестроить ваши данные, рассмотрим базовые шаги, которые необходимо выполнить до написания хотя бы одной строки кода преобразования.

Планирование схемы «звезда»

Прежде чем начать писать шаги преобразования в Power BI, вам нужен ясный и продуманный план для схемы «звезда». Тщательное планирование помогает избежать путаницы, ускоряет работу отчётов и делает модель существенно проще в поддержке. Как BI-разработчик, вы всегда должны проанализировать источники данных и спроектировать схему, прежде чем импортировать необработанные дампы в Power BI Desktop.

Анализ источников данных и определение ключевых таблиц

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

Совет: всегда по возможности используйте целочисленные числовые ключи для соединения таблиц. Числовые ключи ускоряют запросы движка VertiPaq и делают связи заметно надёжнее, чем сопоставление по текстовым значениям.

Определив таблицы, нарисуйте наглядную диаграмму «сущность–связь». Убедитесь, что центральная таблица фактов напрямую связана со своими таблицами измерений, и строго избегайте прямого соединения двух таблиц фактов между собой. Если модель сохраняет один переход между фактами и измерениями, это предотвращает неоднозначность фильтров и делает семантическую модель чистой.

Импорт и очистка данных с помощью Power Query

Импорт данных в Power BI закладывает абсолютный фундамент для надёжной схемы «звезда». Нужно подключиться к источникам данных, очистить и преобразовать поступающие данные и аккуратно загрузить таблицы. Power Query служит основным инструментом подготовки данных: он позволяет формировать и уточнять информацию до того, как она попадёт в модель данных.

При загрузке данных внимательно используйте настройки «Включить загрузку» в Power Query. Отключите загрузку промежуточных запросов и вспомогательных таблиц, чтобы в памяти модели оставались только итоговые, оптимизированные таблицы фактов и измерений. Кроме того, выполняйте как можно больше шагов фильтрации и преобразования ближе к источнику данных — это так называемое свёртывание запросов (query folding). Так обновление данных будет происходить заметно быстрее.

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

Создание таблиц измерений и суррогатных ключей

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

Выделение атрибутов и удаление дубликатов

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

Чтобы таблицы измерений были устойчивы к изменениям в исходных системах, рассмотрите создание суррогатных ключей. Суррогатный ключ — это искусственный столбец с автоинкрементным целочисленным индексом, который создаётся на шагах преобразования в Power Query. Использование суррогатных ключей вместо изменчивых естественных текстовых ключей защищает исторические данные таблицы фактов, когда описательные атрибуты источника, например домашний адрес клиента или название продукта, меняются со временем.

Построение и агрегирование таблицы фактов

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

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

Настройка связей и оптимизация модели

Когда таблицы загружены в Power BI, настройте связи модели в представлении «Модель». Создайте чистые связи «один ко многим», которые идут от стороны «один» в таблицах измерений к стороне «многие» в центральной таблице фактов.

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

Отчётность и анализ на основе звёздообразной схемы

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

Чтобы глубже разобрать этот архитектурный подход и услышать мнения экспертов о типичных ошибках моделирования, обязательно послушайте связанный выпуск подкаста: Build a Power BI Star Schema for Faster Reports.

Заключение

Преобразование плоских и беспорядочных выгрузок данных в оптимизированную звездообразную схему (star schema) с помощью Power Query – один из самых полезных навыков, которым может овладеть разработчик Power BI. Тщательно продумывая схему, очищая измерения, создавая стабильные суррогатные ключи и настраивая строгие связи «один ко многим», вы получаете высокую скорость запросов, упрощённые вычисления DAX и надёжную корпоративную отчётность.

Не забывайте опираться на чек-листы по моделированию, сверять количество строк с исходными системами и регулярно проверять модели по мере изменения потребностей организации. Чтобы глубже разобраться в этих подходах и практических аспектах лучших практик экосистемы Microsoft, обязательно послушайте полный выпуск по ссылке: Build a Power BI Star Schema for Faster Reports. Удачного моделирования!