Страницы: 1
RSS
Как избежать повторного обращения к источнику данных в запросе Power query?, использование функции Table.Buffer при работе с большими массивами данных
 
Всем привет! В процессе обработки запроса PQ происходит неоднократное обращение к источнику данных, а так как объем данных достаточно большой, то обработка запроса затягивается на десятки минут, а то и часы. Вопрос таков: как избежать повторного обращения к источнику данных, чтоб сократить время обработки запроса? Ниже привожу код конкретного запроса:
Код
let
    Источник = Folder.Files("X:\Отдел аналитики\Внутренние данные\Выгрузки\Месяц_Счет_Бренд\Корма для животных"),
    #"Отфильтрованные скрытые файлы" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
    #"Вызвать настраиваемую функцию" = Table.AddColumn(#"Отфильтрованные скрытые файлы", "Преобразовать файл из 2018", each #"Преобразовать файл из 2018"([Content])),
    #"Переименованные столбцы" = Table.RenameColumns(#"Вызвать настраиваемую функцию", {"Name", "Source.Name"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Переименованные столбцы", {"Source.Name", "Преобразовать файл из 2018"}),
    #"Замененное значение" = Table.ReplaceValue(#"Другие удаленные столбцы",".xlsx","",Replacer.ReplaceText,{"Source.Name"}),
    #"Проанализированная дата" = Table.TransformColumns(#"Замененное значение",{{"Source.Name", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Проанализированная дата",{{"Source.Name", "Дата"}}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Переименованные столбцы1", each [Дата] >= НовыйПериодС and [Дата] <= НовыйПериодПо),
    #"Развернутый элемент Преобразовать файл из 2018" = Table.ExpandTableColumn(#"Строки с примененным фильтром", "Преобразовать файл из 2018", {"Счет", "Категория", "Группа", "Подгруппа", "Бренд", "ТО"}, {"Счет", "Категория", "Группа", "Подгруппа", "Бренд", "ТО"}),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Развернутый элемент Преобразовать файл из 2018", each [ТО] <> null and [ТО] <> "" and [ТО] > 0),
    #"Сгруппированные строки" = Table.Group(#"Строки с примененным фильтром1", {"Счет", "Группа", "Подгруппа", "Бренд"}, {{"ТО", each List.Sum([ТО]), type number}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Сгруппированные строки",{"Группа", "Подгруппа"},ОтборБренда,{"Группа", "Подгруппа"},"ОтборБренда",JoinKind.LeftOuter),
    #"Развернутый элемент ОтборБренда" = Table.ExpandTableColumn(#"Объединенные запросы", "ОтборБренда", {"Подгруппа"}, {"ОтборБренда.Подгруппа"}),
    #"Строки с примененным фильтром2" = Table.SelectRows(#"Развернутый элемент ОтборБренда", each [ОтборБренда.Подгруппа] <> null and [ОтборБренда.Подгруппа] <> ""),
    #"Объединенные запросы1" = Table.NestedJoin(#"Строки с примененным фильтром2",{"Группа", "Подгруппа", "Бренд"},ОтборБренда,{"Группа", "Подгруппа", "Бренд"},"ОтборБренда",JoinKind.LeftOuter),
    #"Развернутый элемент ОтборБренда1" = Table.ExpandTableColumn(#"Объединенные запросы1", "ОтборБренда", {"Бренд"}, {"Бренд отбора"}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент ОтборБренда1", "Пользовательская", each if [Бренд отбора] = null then "Прочие бренды" else "Бренд отбора"),
    #"Сгруппированные строки1" = Table.Group(#"Условный столбец добавлен", {"Счет", "Пользовательская"}, {{"ТО", each List.Sum([ТО]), type number}}),
    #"Сведенный столбец" = Table.Pivot(#"Сгруппированные строки1", List.Distinct(#"Сгруппированные строки1"[Пользовательская]), "Пользовательская", "ТО", List.Sum),
    #"Условный столбец добавлен1" = Table.AddColumn(#"Сведенный столбец", "Категория покупателя", 
    each if [Бренд отбора] = null then "Не покупал бренд"
    else if [Прочие бренды] = null or [Бренд отбора]/([Бренд отбора]+[Прочие бренды]) >= ПроцентЛояльности then "Лояльный покупатель"
    else "Нелояльный покупатель"),
    #"Переименованные столбцы2" = Table.RenameColumns(#"Условный столбец добавлен1",{{"Прочие бренды", "Прочие бренды (новый)"}, {"Бренд отбора", "Бренд отбора (новый)"}, {"Категория покупателя", "Новый период"}})
in
    #"Переименованные столбцы2"

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

Владимир, вас в соседней теме просили не такой пример, а несколько файлов с обезличенной информацией, справочники и сам запрос, который вы хотите оптимизировать внутри примера.

Что мне помогло в похожем вопросе:
1. Все конструкции с Table.NestedJoin() меняете на Table.Join() ;
2. В запросах-справочниках, которые джойнятся в основном запросе, последний шаг заворачиваете в Table.Buffer(),

Код
[..]
LastStep = Table.Buffer( code )
   in
LastStep
3. Часть источника, которая уже не обновляется, можно заранее трансформировать и сохранить в .csv, уже в финальной структуре. А вашим кодом обновлять только новую информацию, т.е. ваяете файл.csv, пишите запросы к нему и к новым файлам, потом финальный запрос Table.Combine( {old , new} )

1 - добавляет скорости,
2 - фиксит многократные обращения к источнику,
3 - вот ссылка на макрос от Андрея VG под это дело специально.  
Изменено: genosser - 27.01.2021 16:21:52
 
Из книги Павлова Н.:
"...Добавьте ключ при слиянии запросов
Если вы выполняете слияние (merge) запросов а-ля ВПР, то можно ощутимо ускорить обновление, добавив к таблице, из которой подставляются данные, внутренний невидимый уникальный ключ для каждой строки. Скорость обращения по этому ключу будет существенно выше, чем обычный многоразовый поиск каждого значения.
Добавить ключ можно либо напрямую в М-коде функцией Table.AddKey, либо, что гораздо проще, выполнив операцию удаления дубликатов (Главная  Удалить строки  Удалить дубликаты) над тем столбцом таблицы-справочника, по которому идёт поиск и подстановка данных. Даже если у вас не было повторов, выполнение этого действия активирует внутренний механизм Power Query по добавлению невидимого ключа, что весьма позитивно скажется на скорости обновления такого запроса...."
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
genosser написал:
В запросах-справочниках, которые джойнятся в основном запросе, последний шаг заворачиваете в Table.Buffer(),
А если справочник и таблица фактом находится в одном запросе? Толк от Буфера будет?
 
mechanix 85, Тут всегда надо конкретику смотреть.
Конкретно то что genosser, описывает это результат оптимизации его огроменной (около 50 таблиц с туевой хучей справочников, джойнов и вот этим вот всем) модели в Power Pivot нашими совместными усилиями, и конкретно там все это в связке дало заметный эффект. По крайней мере он остался вполне доволен.
На сколько я понимаю вы работаете с PBI. Там все работает несколько иначе в десктопе, и совсем по-другому в облаке  :D  И давать универсальные советы с утверждением что все будет офигенно лично я не подпишусь. Только на ощупь глядя на тип источников, возможность везде где можно использовать фолдинг и на структуру модели.
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо за ответ. На самом деле на работе работаю в excel, а BI для души)).
Основой рабочий запрос состоит фактов (порядка 150 тыс. строк за месяц), планов, компенсаций. И все это джоинится со справочником складов и менеджеров для правильности формирования столбца "канал продаж".

На данный момент оптимальным для меня стало создание архива поквартальных файлов csv с уже зафиксированной информацией, а текущий квартал прогружаю в модель и через dax создаю текущий архив в том же csv. Потом все эти файлы собираю в одну таблицу (6 млн. строк) с загрузкой порядка 20-30 минут
 
mechanix 85, 6 млн строк за 20 мин? Это долго. Диск такой медленный что ли?
Вот горшок пустой, он предмет простой...
 
PooHkrd, нет гружу с ССД. Я так думаю это из-за большого количества мер и связей
 
mechanix 85, в общем по поводу пользы от буферизации очень подробно пережевывалось тут. Посмотрите, может поможет.
Вот горшок пустой, он предмет простой...
 
PooHkrd, в продолжение нашей переписки:)
Имеем 1 запрос, в рамках которого есть несколько обращений к одному источнику данных (к примеру, вся таблица из источника; минимальная дата одного из полей источника; максимальная дата). Загрузка из файла источника происходит 3 раза.
Схематично:
Код
src = Csv.Document(),
a = List.Min(src[Дата]),
b = List.Max(src[Дата]),
c = src[[Дата],[Поле2],[Поле3]] 
Table.Buffer(Csv.Document()) решает проблему нескольких загрузок источника, но сама по себе буферизация происходит долго (быстрее 3 раза прочитать источник).
Как бы в такой ситуации оптимизировать?:)
 
surkenny, даже если схематично то приводите пример полностью, а то не понятно, что является итогом запроса.
Конкретно для вашего примера, если он будет выглядеть так:
Код
let src = Csv.Document(),
    a = List.Min(src[Дата]),
    b = List.Max(src[Дата]),
    c = src[[Дата],[Поле2],[Поле3]]
in c

произойдет одно чтение источника при выводе шага с, а шаги a и b будут проигнорированы, так как они никак не задействованы при расчете последнего шага с. В таком случае ясень пень выполнение запроса без буфера будет быстрее, т.к. не будет затрачено время на запихивание источника в память. Именно в этом вся суть ленивых вычислений языка М. Пользователь может нагенерить тонну бесполезного кода, но он не будет выполняться если этого не требуется для получения финального результата.
Изменено: PooHkrd - 12.10.2021 12:26:17
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
шаги a и b будут проигнорированы
В приведенном выше коде - конечно будут:) Ок, если так будет понятнее: создаем календарь на основе дат фактов:
Код
let
  src = Csv.Document(), 
  dateStart = Date.StartOfMonth ( List.Min ( src[Дата] ) ), 
  dateEnd = Date.EndOfMonth ( List.Max ( src[Дата] ) ), 
  generate = Table.FromRecords (
    List.Generate (
      () => [ Дата = dateStart ], 
      each [Дата] <= dateEnd, 
      each [ Дата = Date.AddDays ( [Дата], 1 ) ]
    ), 
    type table [ Дата = Date.Type ]
  )
in
  generate
Для вычисления начальной и конечной даты документ читается 2 раза.
 
surkenny, для вашего примера, если и использовать буфер, то для размещения туда списка с датами из источника, перед расчетом мин/макс. Даст ли это преимущество перед двукратным чтением источника по скорости, очень сильно зависит от многих внешних параметров, которые влияют на скорость чтения с диска. Еще раз буфер, это не панацея от всех бед, это всего лишь инструмент, как молоток, которым пользоваться надо с умом, чтобы пальцы не отбить.
Вот горшок пустой, он предмет простой...
 
PooHkrd, благодарю! Я и не считаю буфер панацеей: там, где он может улучшить производительность, смотрю скорость с ним и без него и выбираю наиболее оптимальный вариант.  
 
Генератором календарь создавать как-то расточительно.
Код
let
  src = Csv.Document(), 
  dates = List.Buffer(src[Дата]),
  dateStart = Date.StartOfMonth ( List.Min ( dates ) ), 
  dateEnd = Date.EndOfMonth ( List.Max ( dates ) ), 
  generate = Table.FromColumns(
    {List.Dates(dateStart, Duration.TotalDays(dateEnd-dateStart)+1, #duration(1, 0, 0, 0) )},
    type table [Дата = Date.Type]
  )
in
  generate
F1 творит чудеса
Страницы: 1
Наверх