Рефакторинг Power BI: как ускорить отчёты в 10 раз

Используйте VAR вместо повторных вычислений

Самый банальный и при этом чаще всего пропускаемый паттерн. Если в мере одно и то же выражение встречается несколько раз, Formula движок вычисляет его каждый раз заново. VAR сохраняет результат и переиспользует.

До:

Gross Margin % =
DIVIDE(
    SUM(fact_sales[revenue]) - SUM(fact_sales[cogs]),
    SUM(fact_sales[revenue])
)
// SUM(revenue) вычисляется дважды

После:

Gross Margin % =
VAR _rev = SUM(fact_sales[revenue])
VAR _cogs = SUM(fact_sales[cogs])
RETURN
    DIVIDE(_rev - _cogs, _rev)

На простой мере выигрыш незаметен, но на мере из 15 строк с 10 вложенными SUMX и CALCULATE — ускорение в 2-5 раз. Плюс код становится читаемее: переменные имеют имена, логика видна пошагово.

Замените IF-проверки на DIVIDE

Защита от деления на ноль через IF(x=0, BLANK(), ...) работает, но медленнее встроенного DIVIDE. DIVIDE дополнительно возвращает BLANK на BLANK-делителе автоматически.

До:

Revenue per Customer =
IF(
    DISTINCTCOUNT(fact_sales[customer_id]) = 0,
    BLANK(),
    SUM(fact_sales[revenue]) / DISTINCTCOUNT(fact_sales[customer_id])
)

После:

Revenue per Customer =
DIVIDE(
    SUM(fact_sales[revenue]),
    DISTINCTCOUNT(fact_sales[customer_id])
)

DIVIDE вычисляет знаменатель один раз, а не дважды (как в IF-версии). На матрице с 200 ячейками — экономия ощутимая.

REMOVEFILTERS с конкретной колонкой вместо ALL(таблица)

Частая ошибка: `ALL(fact_sales)` убирает все фильтры с таблицы fact_sales, включая связи с измерениями. Если вам нужно убрать фильтр только с одной колонки, используйте REMOVEFILTERS(колонка) — Storage движок обрабатывает намного меньше строк.

До:

% of Total =
DIVIDE(
    SUM(fact_sales[revenue]),
    CALCULATE(
        SUM(fact_sales[revenue]),
        ALL(fact_sales)    // снимает ВСЁ, включая фильтр по году
    )
)

После:

% of Total =
DIVIDE(
    SUM(fact_sales[revenue]),
    CALCULATE(
        SUM(fact_sales[revenue]),
        REMOVEFILTERS(dim_product[category])
    )
)

Так вы снимаете только фильтр по категории, а фильтр по году/региону/чему угодно сохраняется. И логически правильнее, и в разы быстрее.

Уберите bi-directional фильтры

Bi-directional relationships кажутся удобными: «пусть фильтр идёт в обе стороны». В реальности они превращают каждый запрос в полный скан через all linked tables, убивают ambiguity-обнаружение и делают кэш VertiPaq бесполезным.

Если вам действительно нужна двунаправленная фильтрация в одном сценарии (обычно many-to-many через bridge-таблицу), делайте это в DAX через CROSSFILTER внутри конкретной меры:

До (в модели bi-dir relationship):

// связь dim_product ↔ fact_sales в bi-dir
// все меры работают медленно

После (single direction + CROSSFILTER где нужно):

Customers for Selected Products =
CALCULATE(
    DISTINCTCOUNT(fact_sales[customer_id]),
    CROSSFILTER(
        fact_sales[product_id],
        dim_product[product_id],
        BOTH
    )
)

Теперь bi-directional фильтрация включается только там, где нужна — в одной мере. Все остальные отчёты работают на одностороннем соединении, как и должны.

Режьте cardinality: datetime до даты, GUID в int

VertiPaq сжимает колонки по-разному в зависимости от числа уникальных значений. Колонка order_datetime с секундной точностью имеет миллионы уникальных значений и занимает 40-60% всей модели. Если вам нужен только день или час, округлите в M или в SQL на источнике.

Типовые меры:

• Datetime → Date. Если секунды не нужны, режьте: сжатие улучшается в 10-100 раз.
• GUID → целочисленный суррогатный key. Для sid/nid используйте ROW_NUMBER() на источнике. GUID в Power BI — это 32 байта на строку, int — 4 байта.
• Float / Decimal → Int64, где возможно. Для валюты часто достаточно умножить на 100 и хранить копейки в int.
• Длинные текстовые описания — выносите в отдельный диммер, в факт-таблицу кладите int-ключ.

На одной из наших моделей урезание колонки created_at с секунд до часа уменьшило модель с 6.8 ГБ до 2.1 ГБ. Обновление ускорилось с 14 мин до 4 мин. DAX-запросы — в среднем в 3 раза быстрее.

Snowflake → Star schema

Snowflake-схема (диммеры соединены цепочками: fact → dim_product → dim_category → dim_supergroup) кажется нормализованной и красивой, но для VertiPaq это боль. Каждый фильтр по верхнему уровню заставляет движок делать join через несколько таблиц.

Правильная модель для BI — star schema: все атрибуты сворачиваются в одну dim_product с колонками category, supergroup, brand.

До (snowflake):

fact_sales
    └→ dim_product (product_id, category_id, brand_id)
        ├→ dim_category (category_id, supergroup_id)
        │   └→ dim_supergroup
        └→ dim_brand

После (star):

fact_sales
    └→ dim_product (product_id, product, category, supergroup, brand)

// все атрибуты сплющены в одну dim-таблицу
// join-ы идут одним шагом, а не цепочкой

Денормализация делается на уровне DWH — во вью или stored procedure. В Power BI вы импортируете уже готовую dim_product. Размер памяти вырастает на 5-10%, запросы ускоряются в несколько раз.

Агрегационные таблицы для годовых срезов

Дашборд «Годовая выручка по регионам» не должен сканировать миллионы строк чеков. Добавьте предвычисленную агрегационную таблицу agg_sales_monthly (строк на 2-3 порядка меньше), пометьте её в модели как Aggregation — Power BI автоматически будет обращаться к ней для подходящих запросов.

SQL-вью:

CREATE VIEW agg_sales_monthly AS
SELECT
    YEAR(order_date) AS y,
    MONTH(order_date) AS m,
    region_id,
    category_id,
    SUM(revenue) AS revenue,
    SUM(cogs) AS cogs,
    COUNT_BIG(*) AS rows_count
FROM fact_sales
GROUP BY YEAR(order_date), MONTH(order_date), region_id, category_id;

В Power BI: импортируете agg_sales_monthly, помечаете через Manage Aggregations — какая колонка соответствует какой в fact_sales. Для запроса «SUM(выручка) BY year, region» VertiPaq идёт в агрегат (30 тыс. строк), а не в факт (300 млн).

На реальной модели CFO-дашборд ускорился с 35 сек до 1.2 сек — всё за счёт одной агрегационной таблицы и правильных mappings.

SUMMARIZECOLUMNS вместо SUMMARIZE + ADDCOLUMNS

Старая связка SUMMARIZE + ADDCOLUMNS работает, но выполняется неоптимально: сначала группировка, потом вычисление мер по каждой группе. SUMMARIZECOLUMNS объединяет оба шага и делает запрос планировщик умнее.

До:

Region Summary =
ADDCOLUMNS(
    SUMMARIZE(fact_sales, dim_region[region]),
    "Revenue", CALCULATE(SUM(fact_sales[revenue])),
    "Margin %", CALCULATE(
        DIVIDE(
            SUM(fact_sales[revenue]) - SUM(fact_sales[cogs]),
            SUM(fact_sales[revenue])
        )
    )
)

После:

Region Summary =
SUMMARIZECOLUMNS(
    dim_region[region],
    "Revenue", [Revenue],
    "Margin %", [Gross Margin %]
)

Плюс меры становятся переиспользуемыми (вместо inline DAX в каждой сводной). Plus side — SUMMARIZECOLUMNS корректно работает с фильтрами визуала, чего SUMMARIZE+ADDCOLUMNS не умеет.

TREATAS для несвязанных таблиц

Когда вы хотите отфильтровать таблицу A по значениям из таблицы B, между которыми нет связи в модели, старые решения использовали FILTER+CONTAINS или LOOKUPVALUE. TREATAS делает это напрямую и быстрее.

До:

Sales in Target Segments =
CALCULATE(
    SUM(fact_sales[revenue]),
    FILTER(
        fact_sales,
        CONTAINS(
            target_segments,
            target_segments[customer_id],
            fact_sales[customer_id]
        )
    )
)

После:

Sales in Target Segments =
CALCULATE(
    SUM(fact_sales[revenue]),
    TREATAS(
        VALUES(target_segments[customer_id]),
        fact_sales[customer_id]
    )
)

TREATAS работает как виртуальная связь — filter context применяется корректно, и Storage движок обрабатывает это эффективно. На больших таблицах разница в разы.

Incremental refresh вместо полного пересбора

По умолчанию Power BI при обновлении перечитывает всю факт-таблицу. Для таблицы с 300 млн строк это часы. Настройка incremental refresh (в Power BI Desktop → Модель → Incremental refresh policy) позволяет обновлять только последний период, остальное берётся из кэша.

Типичная политика:

• Store rows from the past: 5 years (держим историю)
• Refresh rows from the past: 30 days (каждое обновление перечитывает последние 30 дней)
• Detect data changes: колонка last_modified (ещё точнее: обновляем только изменённые записи)

Требует колонки партиционирования (обычно order_date) и двух параметров запроса: RangeStart и RangeEnd. Настраивается один раз, дальше работает автоматически. На реальной модели у нас refresh упал с 40 минут до 4. Плюс пользователи перестали ловить lock во время обновления.