Добрый день, уважаемые коллеги!
Столкнулся со следующей проблемой, хотел бы обратиться к экспертному сообществу.
На сервере есть массив, примерно 14 млн записей, добавка к нему происходит 1 раз в неделю, содержит сведения о контрагентах за 5 лет. Коллеги привыкли крутить его в экселе, каждый раз перегружая к себе его полностью через ODBC.
При этом необходимости в обновлении всего массива каждый раз нет, можно грубо говоря грузить только кусок 2023 года, а полное обновление делать раз в пол года.
Периодически стала появляться ошибка обновления, в связи с чем возникла необходимость оптимизации. Пробую развести на два запроса, в экселе каждый из них настраиваю в формате "Только подключение", затем третьим делаю Union. В итоге при обновлении он всё равно забирает с сервера весь объем, а при обновлении отдельного куска ругается на то, что "Нельзя обновить запрос, доступный только для подключения".
Сталкивался кто-нибудь с аналогичным? Как решать?

Спасибо!
С уважением, Иван.

 

Консолидация.
Несколько таблиц с агрегатами, каждая по 15 млн строк, там ИД-шники и справочники данных (каждая до 1000 значений) .
Две таблицы первички и полностью сырых данных с орфографическими очепятками, каждая по 10 млн строк, там уже по 120 и 140 граф соответственно, часть из них текстовые, убрать их нельзя при спорных вопросах погружаемся до них. Текст много весит и сжимается плохо.
Я и мои коллеги естественно это SQL-ем крутим когда что-то агрегировать надо. Но сейчас появилась задача передать это 65-70 - летним сотрудницам, для которых нужна типичная сводная таблица с несколькими кнопками фильтров рядом. Политическое решение, изменить не могу.
Для этого eMondrian развернули, промежуточный ROLAP. Файл экселя при этом 25 Кб весит, только строку подключения к кубу содержит. На любой машине летает.
Если сейчас на всех фильтрах что-то одно выбрано - все хорошо. На прошлой неделе кто-то кнопку не проконтролировал, получили умножение в несколько раз. Задача стоит исключить повторение.
Поэтому импорт в эксель не вариант, сервера должны делать свою функцию, а не являться только внешним хранилищем, согласитесь.
Если бы было возможно DAX использовать - выдохнул бы, сделал за пол часа и забыл.
Сейчас нашел вот такую функцию:
Но в ней для Distinctcount надо блин каждый раз набор объявлять. Срань какая-то, уже блин два дня рабочих на неё вылетело.

И да и нет.
Да - потому что Вы правы. Можно.
Нет - потому что только импортом данных с переносом всего массива с сервера и созданием локальной копии. На сейчас это 1,5 Гб файл экселя получается, для открытия которого мне приходится использовать виртуальную Windows'овую терминальную машину со 100 Гб оперативки, иначе никак.  

Спасибо! Пошел искать!

Подниму.
Задача судя по всему сводится к ограничению выполнения функции суммирования в OLAP-кубе при выборе более, чем одного значения в срезах.
Это реализуемо технически?  

Добрый день!
Ситуация следующая. В работе много используем Excel с динамическими моделями, планируем переходить к OLAP (СУБД ClickHouse, ROLAP - eMondrian, но решение не окончательное и его можно заменить).
Сейчас в базах хранятся не первоисточники транзакций, а агрегаты разной гранулярности (неделя, месяц, год) с наличием соответствующего признака.
Соответственно, сейчас при перегрузке данных в Excel я просто прописывал меру и указывал:Рядом со сводной таблицей был фильтр выбора детализации, если было выбрано более 1 значения, модель выдавала пустоту и на этом всё работало и было понятно конечному пользователю.

При создании OLAP-куба я просто могу выбрать одну функцию агрегации sum, min, count и т.д. и это тоже корректно работает, но только когда выбраны соответствующие срезы. Любое изменение структуры и всё валится.

Вопрос: возможно ли при создании куба указывать что-то чуть более сложное, чем просто функция суммы? Можно ли как-то использовать функции DAX при работе с OLAP? Где можно посмотреть или почитать про это? Может быть у кого-то был опыт других инструментов?

Спрашиваю в блоке Excel, потому что может быть возможно это как-то ограничить на уровне Excel?

Спасибо!
С уважением, Иван.

Добрый день, уважаемые форумчане!
Имеется задача, которую до настоящего времени решали сводными таблицами и моделью данных в Excel.
Регулярно получаем выгрузки из нескольких систем, которые у себя загружаем через Query, обогащаем данными из связанных справочников и других источников, делаем вычисляемые столбцы и дальше на базе всего этого - формируем сводники.
При этом оказалось, что массив, которые экономисты тащат в эксель и крутят там при его сохранении в формате экселевского файла - около 2 гигов, в CSV уже превышает 5 гигов и обработка его занимает около 2-3 суток.... Уменьшить объем невозможно, проговорили.
Сейчас на базе представленной во вложении таблицы (в реальной боевой таблице около 400 граф) формируются сводники в разных разрезах и их работа реализована на мерах. Их уровень примерно следующий, но эти алгоритмы могут меняться почти еженедельно и сейчас их количество около 200. Дальше они выводятся в сводные таблицы, например строки - столбец п.1, графы - п.2 и п.3. В значения - мера.

Данные попробуем перенести на SQL, для групповой загрузки CSV единственное что нашел - ПО ImportExportDataSql (https://habr.com/ru/post/485994/)
Дальше придется видимо временно использовать прокладку в виде PowerBI с live-подключением к этим загруженным данным и переносом мер туда. Построение матриц - экспорт - загрузка агрегатов эксель.

Основной вопрос в следующем: есть ли какая-то программа для SQL типа конструктора отчетов, в котором можно заложить алгоритмы по аналогии таких мер и получать результат в сводных таблицах как в Excel или PowerBI? Или для каждой графы таблицы необходимо иметь четко заданный алгоритм и его редактирование вести только на уровне кода?

Есть ли у кого аналогичный опыт или может быть посоветуете где почитать по этому вопросу?

Спасибо!
С уважением, Иван.

Два варианта.
Первый - самый простой и он же "в лоб".  Поместите поверх текстовое поле без данных. Уберите у него фон и границу. При попытке нажать на заголовок столбца пользователь просто не сможет этого сделать.

Второй - ищите в магазинах альтернативные визуальные элементы.

Добрый день!
Есть модель, созданная на данных, импортируемых из различных файлов Excel через PowerQuery. Модель собирается локально, потом выкладывается на Power BI Report Server с заменой предыдущего файла. Перехода на SQL пока не планируется, поэтому полностью автоматизировать этот процесс пока не представляется возможным.
Возникла необходимость добавить информацию о том, когда производилось обновление модели.
Нигде в более ранних темах аналогичных задач найти не смог.

Я вижу это как некую таблицу из двух столбцов, содержащих время обновления и имя ПК или логин учетной записи ответственного сотрудника, который это обновление производил.

Возможно ли это сделать через PowerQuery? Где про это почитать или может быть кто-то сталкивался с аналогичным? Спасибо.

Можно и там прописать. Просто дело привычки.

И Ваш уровень работы абсолютно не говорит о Вашей недообученности, я бы даже сказал наоборот.

Срез просто выводит лишние строки, которые Вы не хотели бы видеть, но работать он не перестает.
Я обычно в таких случаях у себя их по условию ставлю, что если значение меры = 0, равнять её значение пустоте ( = blank() ).
Посмотрите, так ли?

Татьяна, добрый день.
Честно пытаюсь разобраться, что именно не работает, но Ваше ТЗ написано очень сложно и не по понятно. Ткните пальцем, где и что работает не корректно и при каком условии? В матрице пустые строки смущают или что?

Приветствую.
Реализация первого пункта.

Спасибо большое!

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

Пробовал и через Filterи через Calculate

С уважением, Иван.

Добрый день!
Подскажите пожалуйста, никак не могу разобраться и найти решение.
Имеется ряд подразделений, по которым собирается информация. В некоторых из них - имеется свое деление на филиалы. Примерная структура следующая:


Раньше в Pivot модели создавался общий справочник соответствий, включавший в себя и подразделения, и филиалы. Соответственно, Подразделение 3 в некоторых местах было задвоено и таблицы фактов в этих местах со справочником стыковались по связям многое ко многому. Сейчас возникла потребность адаптации модели на связи один ко многому (серверный Power Bi). Каким образом при этом организовать справочник подразделений? Что выводить на фильтры если у части данных есть деление на филиалы, а у части нет?
Где и что про это можно почитать?

Спасибо!

Спасибо большое!

Добрый день!
Да!
Спасибо!

Руками сейчас это перепишу. Функция на кнопках для этого есть какая-то?

Добрый день!
Подскажите пожалуйста, второй день бьюсь:
имеется файл с несколькими листами.
Требуется в Query добавить индекс строки на каждом листе, начинающийся с начала при переходе на новый лист.

Подскажите пожалуйста, каким образом это можно сделать?

Файл Книга 1 - данные, Книга 2 - запрос.

DemonAMT, спасибо!
В данном случае, к сожалению это не совсем то. Но решение рабочее, главное сначала номера выстроить не забыть.
В Excel это грузиться не будет, это только на PowerBI. Количество таких записей - около 20 млн. строк.

Думается мне, что надо создавать массив уникальных номеров по каждому из каналов и считать количество их пересечений по полю "№ заявки". Или некий аналог сводной таблицы строить на этих пересечениях.
Где бы почитать про это?

Коллеги, работающие с SQL, посоветовали посмотреть в сторону функции EXISTS
Насколько смог найти, её аналог в DAX - это CONTAINS
Если возможно, посоветуйте каким образом её применить непосредственно к этой выгрузке?

Добрый день!
Подскажите пожалуйста, никак не могу решить одну сложность: в выгрузке есть два канала поступления заявок - офис и Call-центр. Каждая заявка может быть отработана на своем этапе, а может быть передана на другой (причем как в одну сторону, так и в другую). Номер заявки при передаче при этом остается тот же.

Как среди них выделить и посчитать с использованием DAX :
1) количество заявок, переданных с Call-центра на офис
2) количество заявок, решенных изолированно на уровне Call-центра без последующей передачи дальше?
3) стоимость возвратов в каждом из этих пакетов
Честно говоря, нет даже идей как подойти к реализации.

Спасибо!

Андрей VG,спасибо!
Это как раз именно то, что и надо!
Отсутствие измерений для строк - и являлось основной проблемой для формирования сводных.

Vladimir Chebykin, предствалена справа в исходном файле. Слева - исходные данные (таблица исходных фактов), справа - формат, в котором данные должны быть (агрегированные)

Добрый день, уважаемые форумчане!
Подскажите пожалуйста, может быть у кого-то уже был аналогичный опыт:
Есть вертикальная таблица фактов с указанием количества проданных позиций. Требуется на базе Excel создать сводную с перечислением позиций построчно с расположением названий позиций по вертикали в соответствующей графе:



Честно говоря, не могу никак представить алгоритм. Каким образом автоматизировать формирование?

С уважением, Иван.

AndreyЧella,  Добрый день!
Нет, к сожалению не нашел.
Если найдется - не сочтите за труд сбросить - будет полезно всем.

Андрей VG,
спасибо!

Добрый день!
В Excel 2019 пытаюсь собрать сводную таблицу на основании данных, хранящихся на SQL-сервере.
Шагом 1 настроил подключение к данным,
Шагом 2 - вставляю сводную таблицу и специально отметил - данные в модель не загружаю.
При выполнении того же самого в PowerBI обработка данных происходит на стороне сервера, Excel же автоматически начинает перезаливать все исходники внутрь себя, в итоге тормозит по страшному, а модель весит больше гигабайта.
Подскажите, это каким-то образом можно исправить или просто Excel для этого не предназначен?

Спасибо!

Не прибедняйтесь, батюшка 🙂

Каким образом реализовать можно? Аль почитать где?
Мож образец получится какой сделать?