Страницы: 1
RSS
POWER PIVOT. Сводная таблица из нескольких запросов
 
Добрый день!

Столкнулся с ситуацией: необходимо сделать сводную таблицу из порядка 30 миллионов записей.
Записи примерно равномерно распределены по 30 дням.
Данные вытягиваются через подключение к БД Oracle через POWER PIVOT.
Так как итоговый файл получится огромный, и ежедневное обновление необходимо только одному дню, у меня была идея создать отдельные запросы к БД для каждого дня (тем самым ежедневно вытягивая из БД только один день). Итого, к концу месяца у меня будет 30 запросов. Можно ли, и если да, то как, на основе этих 30 запросов построить сводную таблицу?
Да, я вижу, что в сводной таблице в качестве источника данных присутствуют мои запросы (пока тестировался на 2 днях). Но если вытягивать дату в строку, то даты не вытягиваются "горизонтально", а вытягиваются "вертикально". А мне надо так, будто все запросы лежат на одном листе.
Путанно объяснил, но возможно всё же кто то посоветует хорошие вещи)
 
Аристарх Петров, решений может быть несколько. Но главное понимать, что в Excel НЕТ инкрементального(добавочного) обновления.
С моей точки зрения 30млн, не очень большой массив, поэтому я бы сделал вью(представление) в БД и в Pivot тянул одной таблицей.
Второй способ, потребует больше ручного труда. Создаем справочник календарь, связываем с вашими таблицами фактов по дате, тоже самое для всех остальных измерений: товары, магазины, склады, клиенты или что там у вас. Создаем меры по типу Продажи: = SUM('Table1'[Sales])+SUM('Table2'[Sales])+....SUM('TableN'[Sales]) - Соответственно в сводной все измерения берм из справочников. а не из таблиц фактов. Но, с моей точки зрения из-за таких мер это путь в никуда.
 
Цитата
StepanWolkoff написал:
С моей точки зрения 30млн, не очень большой массив, поэтому я бы сделал вью(представление) в БД и в Pivot тянул одной таблицей.
StepanWolkoff, извлечение 1 млн строк занимает примерно 3 минуты.
Итого, ежедневное извлечение будет занимать от 3 минут в 1-ый день месяца до до 90 минут в конце месяца. Это долго.
Можно ли каким-то образом ускорить извлечение?
 
Цитата
Аристарх Петров написал:
1 млн строк занимает примерно 3 минуты
3 минуты - это на стороне БД или Power Pivot? Если на стороне БД, то можно сделать однотипные выгрузки прошлых периодов в какую-нибудь папку файлов csv (недельный период или ежедневный). С помощью Power Query подключаетесь к этой папке и вытягиваете данные. Останется только каждый день добавлять новые свежие данные в папку и обновлять запрос. Если нет дополнительных сложных обработок данных при загрузке - Power Query обработает всю папку за пару минут.
 
Vladimir Chebykin, ну что за колхоз с файлами csv, если есть нормальная БД? Если выгрузка идет так долго, значит надо смотреть на стороне БД: индексы, джойны. У меня 100млн из SQL меньше 10минут выкачивается.
Аристарх Петров, если сами не управляете БД, значит админов за ноздри пусть делают по человечески.
 
Цитата
StepanWolkoff написал:
ну что за колхоз с файлами csv, если есть нормальная БД?
а если
Цитата
StepanWolkoff написал:
смотреть на стороне БД: индексы, джойны.
не поможет по каким либо причинам, то какие еще варианты?
 
То уже ничего не поможет)) Вот сейчас ради эксперимента запустил загрузку из вьюхи. 30млн скачалось меньше чем за 1,5 минуты. А это все на ноутбуке древнем i3 с 4гб оперативы, в которые pivot все впихнул.
Объединять 30 текстовых файлов ИМХО будет еще дольше в PQ. Но, если хочется, то можно и по...развлекаться)

Во, 79.651.425 строк 12минут и 1 секунда, чтобы данные загрузились в модель и файл стал доступен для управления. ~600мб файл получился))
Изменено: StepanWolkoff - 19.04.2021 15:14:29
 
StepanWolkoff, Давайте, так. Я согласен с Вами, что если есть варианты оптимизировать запросы к БД, то это абсолютно правильно. Но дано, что запрос выгружает 1 млн. за 3 мин. или полчаса 90 минут 30 млн. строк. Нужно как-то ускорить это дело. За отсутствием других данных я и предложил "колхоз", который: если требуется просто прогрузить таблицы из файлов, то PQ прогрузит этот объем за 2-3 минуты, а не как
Цитата
StepanWolkoff написал:
удет еще дольше в PQ.
Опять же, из собственного опыта.
Изменено: Vladimir Chebykin - 19.04.2021 15:08:37
 
Доброе время суток
Цитата
Vladimir Chebykin написал:
что запрос выгружает 1 млн. за 3 мин. или полчаса 30 млн. строк
Степан, это смотря что тащится из базы данных :)
Типовая сетка 1Гбит или предельная пропускная способность 119 мегабайт в секунду. А теперь представим, что у ТС 10 столбцов по 32 символа в каждом (UTF-16 - следовательно уже 64 байта). Итого запись где-то с числовыми данными 700 байт (мы же не знаем, что конкретно и в каких условиях тянет ТС). Тогда максимальная скорость загрузки 178 тысяч строк в секунду. Если их 30 миллионов, да выходим на ваши полторы минуты.

Но, а если сетка загружена или 100Мбитная?
 
Андрей VG, у меня есть доступ к БД.
Сделал отдельное материализованное представление, записал туда только нужные мне поля одного месяца.
То есть извлечение через POWER PIVOT идёт не запросом, а выбором таблицы. Не знаю как ещё больше оптимизировать данные на стороне БД...(
Но всё равно, скорость извлечения небольшая, порядка 20 тыс записей в секунду.
30 000 000 / 20 000 = 25 минут.
Как ещё можно что оптимизировать?)
Или уже всё, упёрся в пропускную способность сетки, дальше никак не оптимизировать, разве что изменять типы полей в БД?
Изменено: Аристарх Петров - 19.04.2021 15:37:11
 
Цитата
Аристарх Петров написал:
Но всё равно, скорость извлечения небольшая, порядка 20 тыс записей в секунду.
Объём записи таблицы можете оценить? Минимизируйте его, разбивая на справочники с целочисленными ключами и текстовыми значениями и таблицу фактов с целочисленными внешними ключами и собственно данными. В общем создавайте звезду вместо одной таблицы. Что ещё можно порекомендовать, не зная ничего о входе?
А по поводу сети, вас вполне могут и лимитировать по пропускаемой полосе - вы же не единственный пользователь сервера :)
Изменено: Андрей VG - 19.04.2021 17:53:58
Страницы: 1
Наверх