Страницы: 1
RSS
DAX Power BI Ошибка "Недостаточно памяти для завершения операции" при вставке формулы столбца
 
Всех приветствую!
Столкнулся вот с какой проблемой... Есть задача определить в столбце номер покупки по счету у каждого клиента, решаю это формулой столбца

Код
=CALCULATE(
COUNTROWS('Операции');
Filter( ALL('Операции');
'Операции'[дата]<=EARLIER('Операции'[дата])&&
'Операции'[Клиент]=EARLIER('Операции'[Клиент])))


Все работает отлично.. но как только в файле появляется хотя бы тысяч 20 строк, то выходит ошибка: "Недостаточно памяти для завершения операции. Освободите место и повторите попытку"

В чем может быть проблема, может есть другой способ решить эту задачу менее ресурсоемким способом?
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
А так?
Код
=var d = 'Операции'[дата]
var c = 'Операции'[Клиент]
return
CALCULATE(
   COUNTROWS('Операции');
   FILTER( ALL('Операции');
      'Операции'[дата]<=d &&
      'Операции'[Клиент]=c ) )

А еще лучше так:
Код
=var d = 'Операции'[дата]
return
CALCULATE(
   COUNTROWS('Операции');
   FILTER( ALLEXCEPT('Операции';'Операции'[Клиент]);
      'Операции'[дата]<=d ) )

Можно на RANKX еще расчет прикрутить, на фига вы такие простые манипуляции делаете ДАКСом? Гораздо проще это все сделать в PQ и в таком состоянии уже грузить в модель. На память нагрузка будет значительно меньше.
И самый главный вопрос, зачем вам вообще доп.столбец с номером покупки? Какую задачу он решает? Если его не нужно выводить в измерения отчета или в фильтры, то наверняка можно без него. Короче говоря, как написано в правилах форума, покажите что у вас за проблема, а не просите помочь с вашим видением её решения.
Изменено: PooHkrd - 20.08.2020 18:19:10
Вот горшок пустой, он предмет простой...
 
PooHkrd, Спасибо большое за варианты, но пока такая же фигня! В PQ у меня не получилось c сделать группировку с подсчетом внутри каждой группы, так как столбцов много и он начинает по каждому делать уникальную покупку. Если подскажете как- буду благодарен.

А столбец потом в измерение выводить нужно, поэтому делаю в столбце.
В общем в иге нужно посчитать номер покупки у каждого клиента, при том считать только по столбцу дата и клиент. Все остальные столбцы выводятся в том виде в котором есть

На всякий случай новый пример с дополненными столбцами.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Ну, блин, индексация внутри групп тут уже разбиралась раз двадцать.
Код
llet
    Source = Excel.CurrentWorkbook(){[Name="Операции"]}[Content],
    Grouped = Table.Group(Source, {"Клиент"}, {{"tab", each Table.AddIndexColumn(Table.Sort(_,{{"дата", Order.Ascending}}), "Индекс", 1, 1)}}),
    Custom1 = Table.Combine( Grouped[tab] )
in
    Custom1

Так?
Изменено: PooHkrd - 20.08.2020 18:44:11
Вот горшок пустой, он предмет простой...
 
PooHkrd, Спасибо!

Да, обсуждалась... https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=98516 вот тут например.
Но  применяя этот метод он у меня считал каждую строку с ее набором данных как уникальный элемент и нумеровал, что это первая покупка для этой группы.

Спасибо еще раз:)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Первоначальная проблема найдена и устранена. Все дело было в формате столбца с датой datetime. Изначально, такой тип я оставлял для того, чтобы считать порядковый номер операции в день. Так как при обычном формате даты для двух покупок совершенных в один день моя формула выдавала одинаковый порядковый номер.
После решения, предложенного PooHkrd, для определения порядкового номера в Power Query я изменил формат столбца с  с датой с datetime на date  и все полетело.
Протестировал ту же формулу при измененном типе столбца- работает в считанные секунды, но как только тип меняется на datetime все виснет и не работает.
Так что в моем конкретном случае решение нужно было искать без установки формата столбца с датой datetime
Спасибо PooHkrd, за помощь.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Dyroff, настоятельно рекомендую загружать в модель данных время (если оно действительно нужно) и даты в раздельных столбцах. Так и модель будет меньше в размерах, и памяти будет меньше кушать и Time Intelligense  можно будет прикрутить, если нужно. И еще время грузить лучше с детализацией до минуты, или еще меньше гранулярность делать в зависимости от задачи.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Можно на RANKX еще расчет прикрутить
не можно, а нужно. Будет очень шустро и с датой-временем, если она необходима.
Код
=RANKX(FILTER('Операции','Операции'[ID Клиента]=EARLIER('Операции'[ID Клиента])),'Операции'[Дата], ASC)

Проверял недавно похожую задачу на 3 млн строк, где метод из #4 работает весьма-весьма медленно (правда, у меня была чуть сложнее задача - гранулярность группировки выше, поэтому около 1 млн групп нужно было внутри проранжировать).
Поэтому, если нет дальнейшей необходимости работать с результатом индексации в PQ, то я рекомендую делать это в DAX при помощи RANKX
F1 творит чудеса
 
Максим Зеленский, для такой задачи использую совет от Имке, и группирую по схеме: сортировка по нужным полям + локальная группировка. Работает сильно быстрее. Плюс с Андреем VG когда здесь обсуждали этот вариант он предложил тоже интересное решение: создаем столбец, в нем список из значений по нужным для группировки полей и группируем по одному столбцу - тоже было очень шустро.
Вот горшок пустой, он предмет простой...
 
PooHkrd, у меня была идея создать здесь топик с тестовым стендом, на котором это все отрабатывается. Потому что я над своей задачей сексился очень долго - даже умудрился в Power BI Service положить Premium capacity своими экспериментами (но там надо было еще хитрые действия другие, так что х.з., за счет чего именно).
Но можно будет заморочиться, исходные данные у меня остались, надо только обфусцировать
F1 творит чудеса
 
Максим Зеленский, PooHkrd, Спасибо! Для меня очень ценно, когда помимо решения есть еще и такие обсуждения, которые позволяют глубже проникать в суть вопроса.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Максим Зеленский, вопрос по формуле DAX. Я в книжку (Гайд ту дакс) от итальянцев только вот начал вгрызаться (книга - огонь), и до нужного места с контекстами еще не дошел. Как по идее шустрее должно работать? Использовать EARLIER чтобы выглянуть за пределы текущего контекста, или тупо запомнить его в переменную и её уже использовать уже в CALCULATE? Или это по сути одно и то же?
Вот горшок пустой, он предмет простой...
 
PooHkrd, по моей формуле с RANKX? Или по начальной формуле? В принципе, для вычисляемого столбца разница будет микроскопической, я так думаю. Причем даже не знаю, в какую сторону. Можно, конечно, сравнить планы запросов, но не вижу большого смысла.
Если вопрос такой - что из трех быстрее:
Код
=calculate([measure],table[column]=earlier(table[column]))
=calculate([measure],filter(all(table[column]), table[column]=earlier(table[column])))
=var _v = table[column] return calculate([measure],table[column]=_v)
я думаю, разницы нет, если переменная - просто ссылка на столбец текущей строки.

Цитата
PooHkrd написал:
группирую по схеме: сортировка по нужным полям + локальная группировка. Работает сильно быстрее.
У меня была такая схема, но тут нужно отслеживать узкие места - что именно жрет быстродействие.
Вот напридумываись четыре схемы:
  1. Глобальная группировка с сортировкой+индексацией внутри, разворот (стандарт)
  2. Составной ключ, группировка с сортировкой+индексацией внутри, разворот (предложение Андрея VG)
  3. Предварительная сортировка, локальная группировка, индексация внутри, разворот (Имке)
  4. Предварительная сортировка, добавление индекса, отдельный шаг: группировка с вычислением минимального индекса, джойн обратно в основную ветвь и подсчет разности индексов
Всевозможные инсинуации типа применения аккумулятора я даже не рассматриваю, помрут на объеме, имхо.
В каждом случае есть свои узкие места с точки зрения производительности, пометил их (как я вижу) цветом.
В зависимости от объема данных скорость будет разная и по-разному проявляться будут узкие места.

Ну может руки дойдут, сделаю стенд :)
F1 творит чудеса
 
Цитата
PooHkrd написал:
Я в книжку (Гайд ту дакс) от итальянцев только вот начал вгрызаться
Цитата
PooHkrd написал:
Использовать EARLIER чтобы выглянуть за пределы текущего контекста, или тупо запомнить его в переменную и её уже использовать уже в CALCULATE?
Вопрос по свежепрочитанному, так что влезу с ответом :)  Итальянцы пишут, что с появлением переменных необходимость в EARLIER отпала:

Цитата
There are no further reasons to use EARLIER in newer DAX code because variables are a better way to save the required value when the right row context is accessible. Using variables for this purpose is a best practise and results in more readable code.
Т.е. насчёт производительности можно сделать вывод, что она у переменных точно не хуже EARLIER.
Изменено: Aleksei_Zhigulin - 22.08.2020 12:53:11
 
Цитата
Максим Зеленский написал:
Ну может руки дойдут, сделаю стенд
Собственно, какие проблемы?
Скрытый текст

CSV, будучи загнанным:
в SQL Sever, импортирует данные с локальной нумерацией за 36 секунд (собственно импорт идёт секунд 15, остальное время чем-то не внятным занимается)
Код
let
    Source = Sql.Database("(LocalDb)\MSSQLLocalDB", "localDemo", [Query="Select f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, Row_Number() Over (Partition By f1, f2, f3, f4, f5, f6, f7, f8, f9, f10 Order By f11 Desc) As id From dbo.f1_f10"])
in
    Source

в SQLite, импортирует данные с локальной нумерацией за 130 секунд (опять же сам импорт секунд 15, что делает остальное время - чего-то инициализирует)
Код
    Source = Odbc.Query(
        "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\sqlite\speed.sqlite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;", 
        "Select f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, Row_Number() Over (Partition By f1, f2, f3, f4, f5, f6, f7, f8, f9, f10 Order By f11 Desc) As id From f1_f10"
    )
in
    Source

Ну, и собственно из CSV с кодом локальной нумерации в Power Query - 7 минут! Это что же такое надо делать для "оптимизации", чтобы загружались все ядра процессора и так долго - все претензии к авторам Power Query и их рукам.
Код
let
    Source = Csv.Document(File.Contents("C:\Path\forGroup.csv"),[Delimiter=",", Encoding=1251]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    typed = Table.TransformColumnTypes(#"Promoted Headers", {{"f11", Number.Type}}, "en-US"),
    groupFields = List.Transform({1..10}, each "f" & Text.From(_)),
    addGroupList = Table.AddColumn(typed, "list", each Text.Combine(Record.FieldValues(Record.SelectFields(_, groupFields)), "|")),
    grouped = Table.Group(addGroupList, {"list"}, {{"temp", each Table.AddIndexColumn(Table.Sort(_, {"f11", Order.Descending}), "id", 1)}}),
    result = Table.RemoveColumns(Table.ExpandTableColumn(grouped, "temp", groupFields & {"f11", "id"}), {"list"}),
    return = Table.TransformColumnTypes(result,{{"f1", type text}, {"f2", type text}, {"f3", type text}, {"f4", type text}, {"f5", type text}, {"f6", type text}, {"f7", type text}, {"f8", type text}, {"f9", type text}, {"f10", type text}, {"f11", Number.Type}, {"id", Int64.Type}})
in
    return

P. S. Просто загрузка из Access с теми данными с группировкой по 10 столбцам и функцией агрегации - количество идёт 53 секунды - это на древнем движке! Ещё раз, что за руки писали код? На VBA - в худшем случае минуты две (предсказание - код не писал) :)
 
Андрей VG, как загнать csv в ms sql без обрамления двойными ковычками?
А то код так не работает:
Код
= Sql.Database(".", "abc", [Query="Select ""f1"", ""f2"", ""f3"", ""f4"", ""f5"", ""f6"", ""f7"", ""f8"", ""f9"", ""f10"",""f11"", Row_Number() Over (Partition By ""f1"", ""f2"", ""f3"", ""f4"", ""f5"", ""f6"", ""f7"", ""f8"", ""f9"", ""f10"" Order By ""f11"" Desc) As id From forGroup"])
 
Цитата
Михаил Л написал:
как загнать csv в ms sql
например,  использовать программу импорта. Есть в поставке SQL Server Express. Можно  подобное и в VBA написать.
Цитата
Михаил Л написал:
А то код так не работает
а почему  он должен  работать  для импорта  из csv?  Указанный код используется  для получения  данеых из, а не в.
 
Цитата
Андрей VG написал:
использовать программу импорта. Есть в поставке SQL Server Express
Его и использовал, мастер импорта, но там не выставляется разделитель строк - "," . Выставляется только один любой знак, например, - , - запятая. И двойные кавычки так и остаются со значениями, потому и спросил. Убрал кавычки только у первой строки, которая для заголовков.
Этот код у меня на двух гигах озу 32-разрядюсистеме работал минут 13.
Код
= Sql.Database(".", "abc", [Query="Select f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, Row_Number() Over (Partition By f1, f2, f3, f4, f5, f6, f7, f8, f9, f10 Order By f11 Desc) As id From forGroup"])
Через Получение внешних данных минут 5
 
Цитата
Михаил Л написал:
Этот код у меня на двух гигах озу 32-разрядюсистеме работал минут 13.
Скорее всего недостаточно памяти. У меня Power BI Desktop 64 bit в пике до 2200 мегабайт памяти при выполнении запроса отъедал.
 
Цитата
Андрей VG написал:
с кодом локальной нумерации
а разве 1 это код локальной? я думал, что у локальной 0 :):):)

PS ну собственно так и есть:


PPS
блин, не туда посмотрел :):)
В общем, в коде PQ не указана же локальная группировка
Изменено: Максим Зеленский - 24.08.2020 12:34:52
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
В общем, в коде PQ не указана же локальная группировка
А смысл? Не уловил, прошу, пожалуйста, перевести. Реализовывалось следующее же, для примера на одном столбце группировки, а не на десяти, как выше
Код
let
    source = #table(type table [key = Int64.Type, value = Int64.Type], {
        {1, 11}, {2, 101}, {2, 103}, {1, 9}
    }),
    group = Table.Group(source, {"key"}, {"temp", each Table.AddIndexColumn(Table.Sort(_, {"value", Order.Descending}), "id", 1)}),
    expand = Table.ExpandTableColumn(group, "temp", {"value", "id"}),
    typed = Table.TransformColumnTypes(expand,{{"key", Int64.Type}, {"value", Int64.Type}, {"id", Int64.Type}})
in
    typed
 
это я перепутал "код локальной нумерации" с "локальной группировкой", не обращайте внимание.
а что если поменять вот эту часть:
Код
grouped = Table.Group(addGroupList, {"list"}, {{"temp", each Table.AddIndexColumn(Table.Sort(_, {"f11", Order.Descending}), "id", 1)}}),

на вот такую:
g
Код
rouped = Table.Group(addGroupList, {"list"}, {{"temp", each Table.FromColumns({List.Sort(_[f11], Order.Descending)}, {1..Table.RowCount(_)}}, type table [f11=nullable number, id=Int64.Type]), type table [f11=nullable number, id=Int64.Type]}}),

будет ли работать быстрее? или то же, только в профиль?
на моем текущем компе всё медленно
вот такой код:
Код
// forGroup
let
    Source = Csv.Document(File.Contents("C:\Path\forGroup.csv"),[Delimiter=",", Columns=11, Encoding=1251, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    groupHeaders = {"f1", "f2", "f3", "f4", "f5", "f6", "f7", "f8", "f9", "f10"},
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"f11", type number}}, "en-US"),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type with Locale",groupHeaders,Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.FromColumns({List.Sort(_[f11], Order.Ascending), {1..Table.RowCount(_)}}, type table [f11=nullable number, index = Int64.Type]), type table [f11=nullable number, index = Int64.Type]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"f11", "index"}, {"f11", "index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Count", "Merged", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), groupHeaders)
in
    #"Split Column by Delimiter"
Изменено: Максим Зеленский - 24.08.2020 17:52:42
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
будет ли работать быстрее?
Увы, нет - 16 минут. Прогнал сразу же свой, всё те же 7.
Страницы: 1
Наверх