Демонстрирую попытку реализации 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.

Спасибо всем, кто отзовется.

Доброго времени суток!

Задача состоит в создании в запросе 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'ов, то как поэтапно отделить уже найденные первичные серии от еще искомых, чтобы сократить число строк для объединения на следующем шаге, а потом эти лоскуты собрать в таблицу, решающую задачу для всего исходного массива?

Пример в квоту по размеру файла не поместился, поэтому прикладываю ссылку на него: https://drive.google.com/open?id=15iDtsyaaGxtzwRIkFCYqFOQxbaMCRa3i

Подрезал файл: оставил движения за 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

Файл-источник добавил.
Код запроса к источнику:

Варианты кода запроса Андрея 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.