Опанування 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 слугує основним робочим інструментом для підготовки даних, даючи змогу формувати й уточнювати інформацію ще до того, як вона потрапить до моделі даних.

Під час імпорту даних розумно використовуйте налаштування «Enable Load» у Power Query. Вимкніть завантаження проміжних запитів і допоміжних таблиць, щоб у пам'яті моделі залишалися лише ваші остаточні, оптимізовані таблиці фактів і вимірів. Крім того, виконуйте якомога більше кроків фільтрації та перетворення ближче до джерела даних — це так званий query folding. Так оновлення даних відбуватиметься значно швидше.

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

Створення таблиць вимірів і сурогатних ключів

Добре спроєктована таблиця вимірів є справжньою основою вашої схеми «зірка». Таблиці вимірів містять багатий контекст щодо ваших бізнес-операцій. Щоб виділити виміри з плоского експорту даних, потрібно ізолювати унікальні атрибути сутності та прибрати дубльовані рядки.

Вилучення атрибутів і видалення дублікатів

Коли ви будуєте таблицю вимірів із плоского дампу даних, залишайте лише стовпці, важливі для фільтрації та групування. Наприклад, таблиця вимірів товарів має містити ідентифікатор товару, назву товару, категорію, підкатегорію та ціну за одиницю. Після цього видаліть дубльовані записи, щоб кожна сутність траплялася рівно один раз. Унікальні рядки гарантують, що зв'язки «один-до-багатьох» працюють без несподіваних побічних ефектів перехресної фільтрації.

Щоб зробити таблиці вимірів стійкими до змін у вихідних системах, розгляньте можливість створення сурогатних ключів. Сурогатний ключ — це штучний стовпець цілих чисел з автоінкрементом, який створюється під час кроків перетворення в Power Query. Використання сурогатних ключів замість нестабільних природних текстових ключів захищає історичні дані таблиці фактів, коли описові атрибути джерела, наприклад домашня адреса клієнта чи назва товару, змінюються з часом.

Побудова та агрегування таблиці фактів

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

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

Налаштування зв'язків і оптимізація моделі

Щойно таблиці завантажено в Power BI, налаштуйте зв'язки моделі у поданні «Model». Створюйте чисті зв'язки «один-до-багатьох», спрямовані від сторони «один» таблиць вимірів до сторони «багато» центральної таблиці фактів.

За замовчуванням використовуйте однонаправлену перехресну фільтрацію. Уникайте двонаправленої фільтрації, якщо вона справді не потрібна, адже вона створює складні шляхи фільтрації, знижує продуктивність рушія VertiPaq і часто дає несподівані аналітичні результати. Регулярно перевіряйте модель, щоб видаляти невикористані стовпці, мінімізувати текстові поля з високою кардинальністю у зв'язках і переконатися, що безперервна календарна таблиця явно позначена як таблиця дат. Це відкриває шлях до безшовних обчислень часового інтелекту.

Звітність і аналіз за допомогою вашої схеми «зірка»

Зрештою, усі зусилля з моделювання даних знаходять втілення у шарі звітності. Коли семантична модель має структуру чистої схеми «зірка», створювати інтерактивні панелі справді одно задоволення. Зрізи та фільтри миттєво стають на місце, міри DAX залишаються читабельними та легкими для налагодження, а функції природномовних запитань і відповідей демонструють неабияку точність.

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

Висновок

Перетворення плоских, неструктурованих масивів даних на оптимізовану схему «зірка» за допомогою Power Query — один із найцінніших навичок, які може опанувати розробник Power BI. Ретельно спланувавши схему, очистивши виміри, згенерувавши стабільні сурогатні ключі та налаштувавши суворі зв'язки «один до багатьох», ви отримаєте блискавичну швидкодію запитів, спрощені обчислення DAX і надійну звітність для великих організацій.

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