Всех приветствую! Столкнулся вот с какой проблемой... Есть задача определить в столбце номер покупки по счету у каждого клиента, решаю это формулой столбца
Все работает отлично.. но как только в файле появляется хотя бы тысяч 20 строк, то выходит ошибка: "Недостаточно памяти для завершения операции. Освободите место и повторите попытку"
В чем может быть проблема, может есть другой способ решить эту задачу менее ресурсоемким способом?
Можно на RANKX еще расчет прикрутить, на фига вы такие простые манипуляции делаете ДАКСом? Гораздо проще это все сделать в PQ и в таком состоянии уже грузить в модель. На память нагрузка будет значительно меньше. И самый главный вопрос, зачем вам вообще доп.столбец с номером покупки? Какую задачу он решает? Если его не нужно выводить в измерения отчета или в фильтры, то наверняка можно без него. Короче говоря, как написано в правилах форума, покажите что у вас за проблема, а не просите помочь с вашим видением её решения.
PooHkrd, Спасибо большое за варианты, но пока такая же фигня! В PQ у меня не получилось c сделать группировку с подсчетом внутри каждой группы, так как столбцов много и он начинает по каждому делать уникальную покупку. Если подскажете как- буду благодарен.
А столбец потом в измерение выводить нужно, поэтому делаю в столбце. В общем в иге нужно посчитать номер покупки у каждого клиента, при том считать только по столбцу дата и клиент. Все остальные столбцы выводятся в том виде в котором есть
На всякий случай новый пример с дополненными столбцами.
Первоначальная проблема найдена и устранена. Все дело было в формате столбца с датой datetime. Изначально, такой тип я оставлял для того, чтобы считать порядковый номер операции в день. Так как при обычном формате даты для двух покупок совершенных в один день моя формула выдавала одинаковый порядковый номер. После решения, предложенного PooHkrd, для определения порядкового номера в Power Query я изменил формат столбца с с датой с datetime на date и все полетело. Протестировал ту же формулу при измененном типе столбца- работает в считанные секунды, но как только тип меняется на datetime все виснет и не работает. Так что в моем конкретном случае решение нужно было искать без установки формата столбца с датой datetime Спасибо PooHkrd, за помощь.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Dyroff, настоятельно рекомендую загружать в модель данных время (если оно действительно нужно) и даты в раздельных столбцах. Так и модель будет меньше в размерах, и памяти будет меньше кушать и Time Intelligense можно будет прикрутить, если нужно. И еще время грузить лучше с детализацией до минуты, или еще меньше гранулярность делать в зависимости от задачи.
Проверял недавно похожую задачу на 3 млн строк, где метод из #4 работает весьма-весьма медленно (правда, у меня была чуть сложнее задача - гранулярность группировки выше, поэтому около 1 млн групп нужно было внутри проранжировать). Поэтому, если нет дальнейшей необходимости работать с результатом индексации в PQ, то я рекомендую делать это в DAX при помощи RANKX
Максим Зеленский, для такой задачи использую совет от Имке, и группирую по схеме: сортировка по нужным полям + локальная группировка. Работает сильно быстрее. Плюс с Андреем VG когда здесь обсуждали этот вариант он предложил тоже интересное решение: создаем столбец, в нем список из значений по нужным для группировки полей и группируем по одному столбцу - тоже было очень шустро.
PooHkrd, у меня была идея создать здесь топик с тестовым стендом, на котором это все отрабатывается. Потому что я над своей задачей сексился очень долго - даже умудрился в Power BI Service положить Premium capacity своими экспериментами (но там надо было еще хитрые действия другие, так что х.з., за счет чего именно). Но можно будет заморочиться, исходные данные у меня остались, надо только обфусцировать
Максим Зеленский, PooHkrd, Спасибо! Для меня очень ценно, когда помимо решения есть еще и такие обсуждения, которые позволяют глубже проникать в суть вопроса.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Максим Зеленский, вопрос по формуле DAX. Я в книжку (Гайд ту дакс) от итальянцев только вот начал вгрызаться (книга - огонь), и до нужного места с контекстами еще не дошел. Как по идее шустрее должно работать? Использовать EARLIER чтобы выглянуть за пределы текущего контекста, или тупо запомнить его в переменную и её уже использовать уже в CALCULATE? Или это по сути одно и то же?
PooHkrd, по моей формуле с RANKX? Или по начальной формуле? В принципе, для вычисляемого столбца разница будет микроскопической, я так думаю. Причем даже не знаю, в какую сторону. Можно, конечно, сравнить планы запросов, но не вижу большого смысла. Если вопрос такой - что из трех быстрее:
Предварительная сортировка, добавление индекса, отдельный шаг: группировка с вычислением минимального индекса, джойн обратно в основную ветвь и подсчет разности индексов
Всевозможные инсинуации типа применения аккумулятора я даже не рассматриваю, помрут на объеме, имхо. В каждом случае есть свои узкие места с точки зрения производительности, пометил их (как я вижу) цветом. В зависимости от объема данных скорость будет разная и по-разному проявляться будут узкие места.
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.
Public Sub GenerageTestCsvFile()
Const rowCount = 1000000
Dim vData(1 To rowCount, 1 To 10)
Dim fNum As Integer
Dim i As Long, k As Long, l As Long
For i = 1 To rowCount
For k = 1 To 10
vData(i, k) = Chr(96 + k) & Math.Round(100 * Math.Rnd, 0)
Next
If i Mod 200 = 0 Then DoEvents
Next
fNum = FreeFile
Open "c:\path\forGroup.csv" For Output As #fNum
For i = 1 To 10
Write #fNum, "f" & i,
Next
Write #fNum, "f11"
For l = 1 To 3
For i = 1 To rowCount
For k = 1 To 10
Write #fNum, vData(i, k),
Next
Write #fNum, Math.Round(1000# * Math.Rnd, 4)
Next
Next
Close #fNum
End Sub
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 написал: использовать программу импорта. Есть в поставке 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"])