Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Контур данных (рестроспектива+текущие данные)
 
#1
21.02.2018 12:23:45
Здравствуйте!

Коллеги, товарищи, столкнулся с проблемой,

Суть в следующем:

Из Excel выполняется sql запрос в Oracle, который предположим отдает данные с 1 января 2017 по 20 февраля. Строчек десятки миллионов.Эта ретроспективная информация, которая требуется для дальнейшего анализа. Не хотелось бы выполнять запрос более одного раза, долго и ресурсно.

Далее выполняется второй sql запрос, который запрашивает свежую информацию за предыдущий день. Запрос легкий, строк не особо много.

Соответственно вопрос в следующем, как настроить таблицу таким образом чтобы, новые данные сохранялись в таблице ретроспективы и сохранялись там (автоматически естественно) изо дня в день?
 
 
 
#2
21.02.2018 12:29:14
Доброе время суток.
Цитата
lebedevco написал:
Эта ретроспективная информация, которая требуется для дальнейшего анализа.
Учитывая объём, то скорее всего используется в сводной? Каков уровень детализации сводной? Не проще ли часть детализации переложить на сервер Oracle, чтобы не таскать каждый раз > 10^7 строк?
Цитата
lebedevco написал:
новые данные
Чем определяются новые данные? Попробуйте самосвязанные таблицы в Power Query для слияния информации на листе Excel.
И поконкретнее, пожалуйста, вопрос.
 
 
 
#3
21.02.2018 13:04:21
Цитата
Цитата
[USER=55]Андрей VG написал: Учитывая объём, то скорее всего используется в сводной? Каков уровень детализации сводной? Не проще ли часть детализации переложить на сервер Oracle, чтобы не таскать каждый раз > 10^7 строк?
Андрей, да вы правы, сейчас сводная таблица используется. В Oracle данные сырые (поминутная\секундная детализация) и при выполнении sql запроса данные группируются и в Excel данные приходят с дневной группировкой.Запрос выполняется долго и грузит базу. Не хотелось бы использовать его на ежедневно обновляемой основе.
То есть суть сводится к следующему в локальном файле Excel (на ПК без использования sql запроса) лежат исторические данные и каждый день к исторической базе в Excel подгружаются новые(текущей\today даты) данные.

В текущей реализации у меня есть исторические данные с 1 по 15 февраля. На следующий день запускается sql, который догружает только новые данные предположим это будут данные за 16 февраля. Таблица выглядит верно есть данные с 1-15 февраля и есть данные за 16. НО!! 17 февраля при повторении итерации с sql с запросом новых (today) данными, у меня возникает разрыв данных, а именно 1-15 февраля данные есть, и есть за уже 17 февраля (без 16).

Вопрос: Как настроить параметры таблицы так, чтобы новые данные добавлялись в  таблицу, а не заменялись?

Честно говоря все выше описанное я параллельно пробую решить в Power BI.

Но не принципиально в каком продукте будет решена задача.
 
 
 
#4
21.02.2018 13:18:26
lebedevco, это называется "инкрементальное обновление". На данный момент Excel и разные встроенные в него инструменты (PowerQuery, PowerPivot) не поддерживают такой функционал, PowerBI  к сожалению тоже. Путей решений тут несколько и они зависят от задач, которые вы пытаетесь решить и объема данных, необходимого для решения таких задач.
 
 
 
#5
21.02.2018 13:48:44
Задача следующая:

Построить визуализацию данных бизнес метрики.
Данные на графиках должны обновляться ежедневно (автоматически).
Графики должны отображать исторические данные с 2016 года. Бизнес хочет визуально просматривать тенденции, сезонность и прочие "хочу".
Детализация информации 1 день.

Ограничения:

Большой объем исторических данных (данные сырые). То есть можно было ежедневно кидать sql запрос с датой с 2016 по текущую дату, но на обработку запроса тратиться очень много времени, и это время постоянно будет расти с учетом прироста данных.

Цитата
StepanWolkoff написал:
Путей решений тут несколько и они зависят от задач, которые вы пытаетесь решить и объема данных, необходимого для решения таких задач.

А что это за решения?
Или где можно гайды возможно почитать?
 
 
 
#6
21.02.2018 13:52:14
Цитата
StepanWolkoff написал:
(PowerQuery, PowerPivot) не поддерживают такой функционал
Ну почему, если выгружать результат запроса не в кэш сводной, а в .CSV на локальный диск, то вполне можно объединять данные за нужные дни через PQ и анализировать в РР (сам так делаю). Но при таких объемах, на мой взгляд это извращение.
Гораздо эффективнее на стороне Оракла формировать куб, в котором как раз инкрементальные обновления возможны, и уже через запрос PQ или SQL тащить данные из куба в сводную и крутить. Тогда и БД грузиться не будет и данные всегда будут актуальные.
Есть еще вариант делать это дело макросом:
1 Проверить в кэше сводной крайнюю дату по интересующему вас полю
2 Сформировать текст запроса SQL к Ораклу от этой даты и до обеда ;)
3 Вытащить данные запросом и добавить их к кэшу
Это схематически. Можно ли такое провернуть - точно не знаю, но думаю что это в раздел Работа.
Изменено: PooHkrd - 21.02.2018 13:53:30
Вот горшок пустой, он предмет простой...
 
 
 
#7
21.02.2018 14:02:05
Цитата
PooHkrd написал:
Ну почему, если выгружать результат запроса не в кэш сводной, а в .CSV на локальный диск, то вполне можно объединять данные за нужные дни через PQ и анализировать в РР (сам так делаю).
ну это само собой, но, беря во внимание объем данных заявленный автором, при обработке в PowerQuery это не спасет)) да и я более, чем уверен, что есть нюансы с "историческими" данными.
Ну и да, где-то в англоязычном сегменте видел статью или видео про "псевдоинкрементальное" обновление в модели DAX, вот только точно не помню, там про PowerBI было, или про SSAS tabular с помощью функции UNION. Но факт в том, что я на своих данных, еще не встретив эту статью пытался так сделать со 100млн строк на памяти 32Гб - не взлетело. :D
Изменено: StepanWolkoff - 21.02.2018 14:06:44
 
 
 
#8
21.02.2018 14:36:44
Цитата
PooHkrd написал:
Ну почему, если выгружать результат запроса не в кэш сводной, а в .CSV на локальный диск, то вполне можно объединять данные за нужные дни через PQ и анализировать в РР (сам так делаю). Но при таких объемах, на мой взгляд это извращение.
1)Вы имеете ввиду выгрузить исторические данные в CSV.
2)Из файла Excel сделать запрос к этому историческому файлу
3)Из файла Excel сделать sql запрос к базе и запросить текущие (today) данные
4)Соединить таблицы.

Если это работает, то подскажите как реализовать? Чуть не забыл: Excel это должен делать автоматически =)

Я запнулся на шаге при котором данные today не сохраняются, а заменяются с каждым новым днем. А требуется сохранять целостность и последовательность дат.

@В текущей реализации у меня есть исторические данные с 1 по 15 февраля.  На следующий день запускается sql, который догружает только новые данные  предположим это будут данные за 16 февраля. Таблица выглядит верно есть  данные с 1-15 февраля и есть данные за 16. НО!! 17 февраля при  повторении итерации с sql с запросом новых (today) данными, у меня  возникает разрыв данных, а именно 1-15 февраля данные есть, и есть за  уже 17 февраля (без 16).@

Цитата
StepanWolkoff написал:
Но факт в том, что я на своих данных, еще не встретив эту статью пытался так сделать со 100млн строк на памяти 32Гб - не взлетело.

Я был уверен в том, что мне 16 ГБ хватит  :DD
 
 
 
#9
21.02.2018 14:40:06
Нет, я имею ввиду все данные вообще (и исторические и оперативные) выгружать в файлы в одну папку, после чего объединять их запросом PQ и из него в сводную либо напрямую либо через PP.
В принципе можно извратиться и по вашему предложению, но оно мне не нравится - слишком много тонких мест, где обязательно вылезут косяки при сборке исторических данных с текущими.
Изменено: PooHkrd - 21.02.2018 14:42:34
Вот горшок пустой, он предмет простой...
 
 
 
#10
21.02.2018 14:50:31
Цитата
lebedevco написал:
Я запнулся на шаге при котором данные today не сохраняются,
Да, не проблема. Можно использовать лист Excel для такого накопления. Self Referencing Tables in Power Query, ну, и пример на базе Access (распаковать в папку C:\Path).
Но, если вы будете создавать меры, то вам нужно будет соединять с таблицей исторических данных, хранящихся в кэше Power Pivot). Да, Union - это замечательно. Только над вопросом, а как будут действовать связи с таблицами измерениями (можно конечно динамически, но тормоза, а ваши объёмы не для таких ухищрений)? Хорошо, у вас нет связей и измерений. Но данные о значениях для столбцов группировок берутся только из таблицы исторических данных, а если у вас в инкрементной таблице есть данные о новых категориях? Что тогда?
Можно и Power BI собирать. Раз загруженная таблица исторических данных и загружаемая с некоторой начальной даты по текущую инкрементная таблица. В Power BI вы можете сделать таблицу внутри, используя Union. Для этой таблицы настраивать связи с измерениями. Но, вы же хотите инкрементный запрос выполнять 1 раз?
Тогда придётся плодить каждый день через PQ ежедневный запрос и дописывать Union для внутренней таблицы  - и как долго вы собираетесь так делать?
 
 
 
Страницы: 1
Наверх