Демонстрирую попытку реализации RLS в модели данных PowerPivot. Логика задумки: 0) существует большой отчет по продажам, в который запросами загружается и трансформируется информация о продажах из разнородных источников. Читателей много: 30 человек с разным уровнем доступа (руководитель может видеть всю инфу, супервайзер - только по своей торговой команде) - надоело обновлять срезы по основному отчету, т.к. это не быстро; 1) существует запрос с юзерами и паролями в режиме "только подключение" - users; 2) пользователь при открытии заполняет таблицу Авторизация и прожимает кнопку, к которой привязан макрос, обновляющий соответствующий запрос; 3) при успешной авторизации (то есть в запросе users существует совпадение по ключам login и password ) пользователь видит доступную ему часть отчета, если совпадения нет - пустой отчет. (Директор имеет полный доступ, пароль 5678, Трус, Балбес и Бывалый - ограниченный, пароли соответственно abcd, efgh, 1234; пользователи ФамилияХ не имеют доступа вообще). 4) ограничение доступа прописывается в коде меры в DAX; 5) при закрытии книги отрабатывается макрос, очищающий таблицу "Авторизация" и обновляющий запрос в модели, чтобы скрыть отчет и сохранить книгу. 6) установленная защита книги не позволяет засунуть нос в текст запросов, в модель данных, чтобы подглядеть пароль либо удалить RLS-фильтр в мере, а скрытые от просмотра объекты не дают дернуть их значение функцией куба. (пароль для снятия защиты книги 159357)
Прошу у уважаемых форумчан критику решения. Возможно макрос, сохраняющий книгу при закрытии, можно записать более корректно/отказоустойчиво (фрагменты кода надергал отсюда и из приемов Николая Павлова)
Также остается ощущение, что от того, кто захочет что-то выудить из модели, я ничего-то и не защитил: прошу подсказку на тему того, что еще сюда можно добавить для защиты от несанкционированного чтения. (в VBA я серый ... очень серый)
Проблема: в рабочем файле, после добавления кода из примера в конец запроса, скорость загрузки упала с 12 000 до 100 строк в секунду.
Собственно, сам код наколхозил, пока делал файл-пример для этого же сабжа, но в контексте "Как это сделать?". То, что хочу получить, в запросе Sales2. Прошу умельцев подсказать, в чем косяк, даже если я запорол что-то базовое.
Спасибо всем, кто отзовется!
З.Ы2.: Эта же задача решена на DAX в PowerBI, и мера, в расчете которой используются таксы, что я сейчас хочу сджойнить в PowerQuery, пересчитывается до 20 сек. на 5М строк.
Во вложенном архиве файл LeftJoinSQL.pbix. Запрос "PQJoin" возвращает таблицу, которую я хочу получить кодом на SQL внутри OleDb.DataSource. Запросом "SQLJoin (1 таблица)" я осилил соединение одной таблицы; А в запросе "SQLJoin (2 таблицы)" висит ошибка, которую я никак не могу понять. Попытки воспроизвести на SQL шаг с агрегированием, соответственно, не было
Предыстория: Запрос "PQJoin" до завершения шага с первым левым соединением на реальных данных (к 1,08кк строк тяну 42к) выполняется в два раза (!) быстрее, чем "SQLJoin (1 таблица)". Но на шаге #"Вычисленное значение Receipt" в рабочем файле запрос "PQJoin" умирает: за 4 часа не справляется (в таблицах в столбце 'Receipt' от 1 до 10 строк), дальше мне терпения не хватило ждать.
Прошу подсказать: 1) Что я сделал не так в коде SQL в "SQLJoin (2 таблицы)" и возможно ли это вообще? 2) Как будет выглядеть готовый код SQL, если после второго объединения еще добавить агрегирование (вернуть таблицу из шага #"Сгруппированные строки" в запросе "PQJoin"? 3) Есть ли менее ресурсоемкий, чем Table.AggregateTableColumn, способ в PQ развернуть с агрегированием присоединенные таблицы? При этом, применяемые функции PQ должны быть доступны в версии надстройки для Excel2013.
Спасибо всем, кто отзовется.
Изменено: genosser - 27.02.2020 13:37:39(на все вопросы ответ получил, пример обновил)
Задача состоит в создании в запросе PowerQuery пользовательских столбцов с номером, датой, видом прихода и подразделением родительской серии товара. Количество строк в таблице расхода до трансформации должно остаться таким же после выполнения запроса
Спецификация:
Скрытый текст
Таблицы: Перемещения - таблица содержит записи о расходе товара по документам, датам, сериям и видах расхода из одного подразделения в другое, Приход - содержит все записи о приходе по документам, датам, сериям и видам прихода, Расход - таблица содержит все записи о расходе, в ней и пытался делать трансформацию запросом MapID.
Виды движения: в расходе - PRZ_RAS, в приходе - PRZ_VOZ - это список ID операций. Их значение состоит в том, что они представляют собой либо "окончательный" расход (первичный приход), либо операцию, связанную с внутренним перемещением.
1) В базе-исходнике есть подразделения "PODR", код товара "ID СГП", номер серии "KCEN", который и ищем; реквизиты документа: номер и дата ("DOK" и "DT_DOK"), которые инициируют приходы-расходы; 2) Система генерирует новые номера серий в момент прихода в подразделение. Если товар "ID СГП" перемещается из одного подразделения в другое, то запись в расходе ID СГП - KCEN1 (parent серия) - K_K (получатель) - KOL (количество) - CENA (цена) - DT_DOK - DOK - PRZ_RAS - PODR (подразделение, которое делает расход) соответствует записи в приходе: PODR (подразделение, принимающее приход) -ID СГП - KCEN2 (child серия) - DT_PRI (=[DT_DOK] из записи расхода)-DOK (=[DOK])-NACENKA (=CENA в расходе) -PRZ_VOZ- K_K(=PODR из записи расхода). //K_K в приходе не равен К_К в расходе.
Фактически, искомой является связка PODR-ID СГП-KCEN-DT_PRI-PRZ_VOZ , но есть проблема: система группирует серии, если внутри одного приходного документа у разных серий одного товара равен реквизит "CENA". Из-за этого серия-child может иметь >1 parent, и не получается применить функцию PATH в DAX. Поиск следует прекратить, если вид прихода (PRZ_VOZ) найденной родительской серии принадлежит списку "Первичные"
Простите, уважаемые форумчане, за этот длинный опус, но без него, вроде, не объяснить, чего хочу.
Смог слепить что-то похожее на решение через 7 кругов левых Join'ов, но, во-первых, нахожу это решение уродливым, во вторых, для большей части массива (более 80%) задача решается на первом круге, в-третьих, выявились серии, для которых не нашлось родителя за 7 кругов (либо родитель нашелся, но строка с анализируемым последним номером размножилась за несколько объединений, и после шага, на котором вроде найден родитель, запрос продолжает искать его для клонов) - таблица с результатом запроса отфильтрована по этому замечанию, зеленым выделены строки с найденными родителями, красным - строки, в которых поиск продолжился.
Скрытый текст
Кроме того, что решение уродливо, оно еще и массивно: Join'ы вычисляются за пару минут, но после этого, чтобы вернуть исходное количество строк, я делаю еще один левый Join с регистром первичного прихода (для другой задачи в модели для каждой родительской серии вычисляются стоимостные показатели; задумка была в том, чтобы рассчитать из них агрегаты для дочерних серий и завершающим шагом удалить дубликаты в таблице MapID), в пример не добавил, потому что шаг агрегирования "укладывает" машину: в оригинальной таблице "Расход" содержится 84 000 строк, которые после трансформаций запросом MapID превращаются в 240 000, а шаг с агрегированием не выполнился за 2 часа.
Подскажите, пожалуйста, есть ли способ решить задачу как-то более красиво (или правильно)? Или, если оставлять 7 кругов Join'ов, то как поэтапно отделить уже найденные первичные серии от еще искомых, чтобы сократить число строк для объединения на следующем шаге, а потом эти лоскуты собрать в таблицу, решающую задачу для всего исходного массива?
Подрезал файл: оставил движения за 2019 г. и убрал серии, для которых из-за подрезания отсутствуют родительские номера. Строки с проблемами остались с заливкой.
Доброго времени суток! Прошу помочь с такой проблемой: пытаюсь создать источник из файла *.dbf (их довольно много, по структуре разные, есть >1,5 млн строк и т.п., т.е. вариант открыть в excel и сохранить не вариант), и натыкаюсь на ошибку :
Скрытый текст
DataSource.Error: OLE DB: Невозможно найти устанавливаемый ISAM. Сведения: DataSourceKind=OleDb DataSourcePath=data source="path";extended properties="dBASE IV";provider=Microsoft.ACE.OLEDB.12.0 Message=Невозможно найти устанавливаемый ISAM. ErrorCode=-2147467259
Файл-источник добавил. Код запроса к источнику:
Код
let
Source = OleDb.DataSource(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\path\;extended properties=dBASE IV",
[Query="select * from [SETI.dbf]"])
in
Source
Варианты кода запроса Андрея VG для одного файла и папки с файлами дают такую же ошибку. Одинаково: и в PQ и в PowerBI. Может, у меня с софтом что-то не так, что-то с чем-то не дружит? Версии ПО: MS Office ProPlus 2013 (x64) и MS Access Runtime 2013 (x64) 15.0.4569.1506 PQ (x64) 2.59.5135.201 Power BI (x64, upd.06.2019) 2.70.5494.761 VisualStudio 2010 (Tools for Office Runtime) 10.0.50903 Версия Фоксы, которая генерит эти dbf-ки, 2.6
Доброго времени суток! Застрял с такой задачей: есть обновляемая таблица фактов продаж, справочник клиентов и справочник-календарь, в котором указаны рабочие и выходные дни, - пытаюсь в строке с продажей рассчитать ожидаемую дату платежа по данным из справочника клиентов, где закреплен срок отсрочки в банковских днях. В файле-примере решил с помощью РАБДЕНЬ(), но есть инциденты с нерелевантным результатом, когда формула не учитывает рабочие субботы. Нужно в PowerQuery в настраиваемом столбце запроса "Продажи" получить [Дата отгрузки]+количество дней отсрочки, учитывая только дни со статусом "Р". Прошу, умельцы, отзовитесь.
В таблице "Продажи" в вычисляемом столбце нужно подтянуть цену прейскуранта на ближайшую к дате отгрузке дату, дата вступления цены в силу ('Прайс'[Дата]) меньше даты отгрузки ('Продажи'[DT_DOK]). И то, что получилось в Excel формулой массива (искомый итог на листе "Факт продаж", колонка "Прайс"), в DAX не могу сообразить, как воспроизвести. Прошу подсказать решение. Задачу решаю в Excel 2010 Pro Plus и в Power BI Desctop.