Інкрементальне оновлення Power BI — не завжди рішення: як 1.3 млн рядків не влізли в 3 ГБ
Оновлено: 2 дні тому
Задача
Є звіт у Power BI, який збирає рекламну аналітику для e-commerce проєкту: Facebook Ads, Google Ads, Pinterest і Shopify. Дані надходять через конектор-агрегатор (Windsor.ai) звичайним REST-запитом:
Source = Json.Document(Web.Contents(
"https://connectors.windsor.ai/facebook?api_key=...&date_from=2026-04-01&fields=..."))
Усі таблиці в режимі Import, по одній партиції на таблицю. Проблема виникала регулярно: date_from фіксований, вікно росте щодня, і раз на один-два місяці оновлення починало падати — API віддавав дедалі більший JSON.
Рішення було ручним. Вивантажити закритий період окремим запитом, вставити в Excel на SharePoint, зсунути date_from вперед, дописати в запит Table.Combine з аркушем історії. Приблизно година робот
и кожні два місяці, плюс ризик, що це забудеться і звіт просто зламається.
Завдання: зробити так, щоб оновлення не потребувало ручних дій узагалі.
Очевидне рішення — інкрементальне оновлення
Класика для зростаючих наборів даних: Power BI ріже таблицю на партиції — незалежні шматки даних, кожен зі своїм діапазоном дат. Архівні партиції будуються один раз і надалі не перебудовуються.
Складність у тому, що Web.Contents до REST API не підтримує query folding. Power BI не може «протиснути» фільтр по даті в джерело — він просто завантажить усе і відфільтрує в пам'яті, тобто стане тільки гірше.
Обхід відомий: підставляти межі партиції прямо в URL через параметри RangeStart і RangeEnd.
let
Source = Json.Document(
Web.Contents("https://connectors.windsor.ai", [
RelativePath = "facebook",
Query = [
api_key = ApiKey,
date_from = Date.ToText(Date.From(RangeStart), [Format="yyyy-MM-dd"]),
date_to = Date.ToText(Date.AddDays(Date.From(RangeEnd), -1), [Format="yyyy-MM-dd"]),
fields = "..."
]
])
),
// ...
#"Filtered Range" = Table.SelectRows(#"Changed Type",
each [date] >= Date.From(RangeStart) and [date] < Date.From(RangeEnd))
in
#"Filtered Range"
Критична деталь. Базовий URL має бути статичним рядком, а всі змінні частини — через RelativePath і Query. Якщо зібрати URL конкатенацією, Power BI Service відхилить датасет із помилкою про dynamic data source: він не зможе зіставити обліковий запис із джерелом, бо URL щоразу інший.
Друга деталь: у формі Query = [...] значення кодуються автоматично. Якщо у вихідному запиті був уже закодований параметр (наприклад, filter=%5B%5B%22spend%22...), його треба передавати сирим — [["spend","gt",0]]. Інакше вийде подвійне кодування, і фільтр мовчки не спрацює.
Підводні камені конектора
Далі пішла серія помилок, жодна з яких не була задокументована. Наводжу, бо для будь-якого REST-джерела набір буде схожим.
Дата з майбутнього дає 500 помилку. Партиція поточного місяця має RangeEnd = початок наступного, тобто date_to майже весь місяць лежить у майбутньому. Лікується так:
Today = Date.From(DateTimeZone.FixedUtcNow()),
ToDate = List.Min({Date.AddDays(Date.From(RangeEnd), -1), Today}),FixedUtcNow замість LocalNow — щоб значення не з'їхало посеред довгого оновлення і щоб співпало з часовою зоною, в якій API віддає дати.
Поточний день дає 400 помилку. На ендпоінті замовлень навіть сьогоднішня дата виявилася недоступною: дані за день ще не закриті на боці джерела.
Фінальний варіант:
Today = Date.AddDays(Date.From(DateTimeZone.FixedUtcNow()), -1),Для рекламного звіту це радше плюс. Дані за поточний день усе одно неповні — конверсії дозаписуються із затримкою і останній день, який завжди виглядає як провал продажів, регулярно провокує зайві питання від замовника.
Дані до певної дати не існують. Запит за липень 2023 повертав 400. Рекламний акаунт тоді або не існував, або вийшов за вікно зберігання платформи. Для Meta це приблизно 37 місяців, і що важливіше — вікно діє на рівні окремих полів, а не дат. Базові метрики на кшталт витрат і кліків віддаються за весь період, а unique-метрики й дії з пікселя мають коротший горизонт. Проба з двома полями цього не показує.
Стіна
Запити запрацювали. У Power BI Desktop оновлення проходило стабільно. У Service:
Resource Governance: This operation was canceled because there wasn't enough memory to finish running it. consumed memory 3262 MB, memory limit 3052 MB,database size before command execution 19 MB.
Ліміт 3 ГБ — стеля Power BI Pro на операцію оновлення. Підняти її можна тільки переходом на PPU або Premium.
Три хибні гіпотези
Далі я послідовно перевірила три версії, і всі три були мимо.
Версія 1: винен Excel
Шість запитів читають один файл history.xlsx, а Excel.Workbook не вміє витягти окремий аркуш — він розпаковує весь воркбук у пам'ять цілком. Шість повних розборів, паралельно, без кешу між контейнерами.
Перевірка: тимчасово вимкнути гілку Excel повністю, усе тягнути з API. Результат — 3421 МБ замість 3262. Стало гірше.
Версія 2: винні довідники
Дві таблиці тягнули date_from=2020-01-01 одним запитом, без інкрементального оновлення, і дедуплікували результат уже після повного розгортання в пам'яті.
Перевірка: зняти з них Include in report refresh. Результат — 3567 МБ. Ще гірше.
Версія 3: винна паралельність
Вимкнути Enable parallel loading of tables у налаштуваннях файлу.
Результат той самий.
Сигнал, який я проґавила
Подивіться на послідовність: 3262 МБ → 3421 МБ → 3567 МБ. Споживання зростало, поки я прибирала навантаження.
Коли усунення передбачуваної причини погіршує симптом — шукати причину треба в чомусь іншому. Це момент зупинитися і переміряти, а не оптимізувати далі. Я цього не зробила відразу і втратила три ітерації по публікації та повному оновленню.
Другий сигнал лежав просто в тексті помилки: database size before command execution 19 MB. Модель була практично порожня, коли процес спалив 3.5 ГБ. Отже, гігабайти йшли не на дані — а я всі три спроби намагалась скоротити обсяг даних.
Третій сигнал: Desktop працює, Service падає. Desktop будує одну партицію — ту, що задана поточними значеннями RangeStart/RangeEnd. Service будує всі одразу і паралельно. Якщо один і той самий запит коректний в одному середовищі й падає в іншому, справа не в логіці запиту, а в тому скільки запитів виконується одночасно.
Вимір, який усе вирішив
Двохвилинна перевірка, з якої слід було починати. Підключаємось до моделі і рахуємо рядки в поточному завантаженому вікні:
EVALUATE
ROW(
"GA", COUNTROWS('GA'),
"FB_ads", COUNTROWS('FB_ads'),
"shopify_orders", COUNTROWS('shopify_orders'),
"shopify_products", COUNTROWS('shopify_products')
)
Вікно — 101 день. Результат:
Таблиця | Рядків за 101 день | На день |
Google Ads | 91 769 | ~908 |
Facebook Ads | 62 652 | ~620 |
Shopify (позиції) | 33 771 | ~334 |
Shopify (замовлення) | 25 679 | ~254 |
Екстраполяція на потрібні 622 дні дає приблизно 1.3 млн рядків разом. Це 40–60 МБ у стисненому вигляді.
3.5 ГБ витрачалися не на дані: 1.3 млн рядків стискаються в 40–60 МБ.
Арифметика контейнерів
При архіві «2 Years» і інкрементальному вікні в 2 місяці Power BI ріже кожну таблицю приблизно так: партиція за позаминулий рік, за минулий рік, далі квартали поточного року, далі останні місяці окремо. Виходить близько семи партицій на таблицю. Чотири таблиці — двадцять вісім партицій.
Кожна партиція в Service будується в окремому mashup-контейнері зі своїм базовим оверхедом. За зворотним рахунком з наших цифр це приблизно 120 МБ на контейнер — незалежно від того, скільки рядків він поверне.
28 × ~120 МБ ≈ 3.4 ГБЗвідси й нечутливість до всіх моїх оптимізацій: я прибирала рядки, а "платити" доводилося за контейнери. Порожня партиція за рік, якого не існує в даних, коштувала рівно стільки ж, скільки повна.
Застереження: 120 МБ — це оцінка, виведена зворотним рахунком з конкретного кейсу, а не задокументована Microsoft константа. Порядок величини, а не норматив.
Правильне рішення
Інкрементальне оновлення прибрати повністю. Залишити те, що реально розв'язало вихідну задачу — помісячну нарізку викликів усередині M. Бо саме вона, а не партиціювання, не дає API віддавати гігантський JSON. Партиції поверх неї виявилися зайвим шаром, який і вбивав оновлення.
(endpoint as text, fields as list, fromDate as date, toDate as date,
optional extraQuery as nullable record) as table =>
let
MonthStarts =
if fromDate > toDate then {}
else List.Generate(
() => Date.StartOfMonth(fromDate),
each _ <= toDate,
each Date.AddMonths(_, 1)),
GetChunk = (mStart as date) as table =>
let
f = List.Max({mStart, fromDate}),
t = List.Min({Date.EndOfMonth(mStart), toDate}),
BaseQuery = [
api_key = ApiKey,
date_from = Date.ToText(f, [Format="yyyy-MM-dd"]),
date_to = Date.ToText(t, [Format="yyyy-MM-dd"]),
fields = Text.Combine(fields, ",")
],
Q = if extraQuery = null then BaseQuery
else Record.Combine({BaseQuery, extraQuery}),
Source = Json.Document(
Web.Contents("https://connectors.windsor.ai",
[RelativePath = endpoint, Query = Q])),
Data = if Record.HasFields(Source, "data") then Source[data]
else error Error.Record("API", "Немає поля 'data': " & endpoint,
Text.FromBinary(Json.FromValue(Source)))
in
if List.IsEmpty(Data) then #table(fields, {})
else Table.SelectColumns(
Table.ExpandRecordColumn(
Table.ExpandListColumn(
Table.FromRecords({[data = Data]}), "data"),
"data", fields, fields), fields)
in
if List.IsEmpty(MonthStarts) then #table(fields, {})
else Table.Combine(List.Transform(MonthStarts, GetChunk))
Ключове: Table.Combine(List.Transform(...)) обчислюється ліниво. Чанки проходять через рушій по черзі, і JSON попереднього звільняється до розбору наступного. У пам'яті в кожен момент один місяць.
Не додавайте сюди Table.Buffer. Він матеріалізує всі чанки одночасно і руйнує весь сенс конструкції.
Виклик у таблиці:
let
Cols = {"source","date","country","campaign_id","clicks","spend", "..."},
ApiCutoff = #date(2026, 4, 1),
Today = Date.AddDays(Date.From(DateTimeZone.FixedUtcNow()), -1),
Archive = Table.SelectRows(
Table.SelectColumns(
Table.Combine({
Table.SelectRows(Hist_2025, each [date] >= #date(2025,1,1) and [date] < #date(2026,1,1)),
Table.SelectRows(Hist_2026, each [date] >= #date(2026,1,1))
}), Cols, MissingField.UseNull),
each [date] <> null and Date.From([date]) < ApiCutoff),
ApiRows = fnWindsorFetch("facebook", Cols, ApiCutoff, Today),
Rows = Table.Combine({Archive, ApiRows}),
// далі типізація
in
Rows
Архів з Excel залишився — але як заморожений файл. Він більше не зростає і не потребує ручного дописування, бо свіжі дані йдуть з API, а нарізка не дає їм переповнити запит. Саме це й було метою: прибрати повторювану ручну роботу, а не сам файл.
Ціна рішення: кожне оновлення тягне всю історію заново, приблизно 21 дрібний виклик на таблицю замість двох-трьох. Оновлення стало довшим — 10–20 хвилин замість кількох. У межах двох годин Pro це з великим запасом.
Що натомість зникло: пастки з Full refresh, залежність від вікна зберігання рекламної платформи, неможливість завантажити PBIX із Service назад. Модель лишилася звичайною — її підхопить будь-хто з команди без пояснень про партиції.
Контрольний список
Що робити, коли Power BI Service падає по пам'яті, а Desktop працює.
1. Порахувати рядки до вибору архітектури. COUNTROWS на одному завантаженому вікні плюс екстраполяція на потрібний період. Менше приблизно 5 млн рядків — інкрементальне оновлення майже напевно зайве, і його накладні витрати перевищать вигоду.
2. Прочитати database size before command execution. Маленька цифра при великому consumed memory означає, що проблема не в обсязі даних. Це одразу відкидає половину гіпотез.
3. Порівняти Desktop і Service. Різниця в поведінці вказує на паралельність і кількість партицій, а не на логіку запиту.
4. Порахувати партиції. Приблизно 120 МБ на контейнер, помножити на кількість таблиць і партицій, порівняти з 3 ГБ ліміту Pro.
5. Стежити за напрямком зміни симптому. Якщо усунення передбачуваної причини погіршує ситуацію — зупинитися і переміряти, а не оптимізувати далі.
6. Після будь-якої зміни джерела — звірити дані. Рядки проти унікальних комбінацій, по місяцях. Окремо перевірити стик: подвоєння і провали живуть саме там.
Підсумок
Інкрементальне оновлення — інструмент для десятків мільйонів рядків. Для 1.3 млн воно створює накладні витрати, більші за самі дані.
Вихідну проблему розв'язало не партиціювання, а нарізка API-викликів усередині Power Query. Це простіше, не має пасток із Full refresh і не прив'язує модель до інфраструктури воркспейсу.
А головний урок методологічний: вимірювати до того, як вибирати архітектуру. Двохвилинний COUNTROWS заощадив би тиждень.
Коментарі