Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Данные из Oracle в модель данных с параметрами!
 
#1
29.07.2016 12:42:12
Доброго времени суток.
Задача: запихнуть в эксель 3-5 миллионов записей. Крутить их во всех pivot'ах и проекциях. Часто нужно подсчитывать количество уникальных элементов.
Последнее умеет делать сводная таблица с источником данных из модели данных.
Как мне посредством VBA запихнуть мой RecordSet в модель данных? Или как мне сразу формировать модель данных с параметризированным запросом к БД?
В качестве фильтров запроса нужно будет выбирать период.
В идеале еще и динамически менять количество выводимых столбцов.
Заранее благодарен за любые подсказки!!!
 
 
 
#2
29.07.2016 13:14:19
3-5 миллиона строк на лист не поместятся )
 
 
 
#3
29.07.2016 13:29:25
Так точно. Поэтому на лист их и не выгрузишь. Только напрямую в Сводную или Модель данных.
 
 
 
#4
29.07.2016 14:18:12
Если открыть в excel вкладку PowerPivot - Управление - Из других источников - Oracle - Написать запрос, указывающий данные для импорта, то можно выгрузить столько строк, на сколько хватит оперативки. Дело в том, что пользователь должен получить уже готовые решения для разных запросов, а не лазить по этим вкладкам, писать SQL и т.д. Как мне формировать такой SQL с помощью VBA?
 
 
 
#5
29.07.2016 15:01:50
Sylercool, PowerPivot не доступен из VBA. Тут только в связке с PowerQuery, там можно исхитриться с параметрами.
Сам не делал, только читал. Так что рыскайте:
Chris Webb's BI Blog
POWER BI DESKTOP QUERY PARAMETERS - тут правда про Power BI, но это все близко, так что читайте и ищите.
 
 
 
#6
29.07.2016 15:03:53
кнопка цитирования не для ответа [МОДЕРАТОР]

Со сводными вроде как уже успешно работал, но фишка с количеством уникальных элементов не работает. А PowerPivot значит совсем закрыт для разработчиков? ((( Грусть. Спасибо
 
 
 
#7
29.07.2016 18:07:18
Power Query поможет.
в 2016 офисе к нему (к готовым запросам) уже можно обращаться из VBA. Но по большому счету это не нужно.  В 2010-2013 уже через VBA можно просто менять параметры в таблице параметров и жамкать Refresh All
Если запрос составлен правильно, то еще и query folding сработает.

В итоге PowerPivot можно будет подключать напрямую к запросу Power Query (без выгрузки его на лист), и телемаркет.
F1 творит чудеса
 
 
 
#8
29.07.2016 20:14:50
Не понял, можете навести на статью по этому поводу? Что за таблица параметров запроса?
 
 
 
#9
01.08.2016 23:37:59
Sylercool, сорри за поздний ответ.
Таблица параметров запроса - это таблица, которую вы создаете в Excel и из которой PowerQuery будет тащить вводные параметры для использования в других запросах.
Вы можете создать такую таблицу, например, с двумя строчками - начальная и конечная дата, и вторую - с перечнем нужных столбцов (или с перечнем всех столбцов но с отметкой, какие тащить, а какие нет).
Дальше PowerQuery обращается к этим таблицам, берет из них указанные вами параметры, и использует их в своих запросах (например, при обращении к БД).
Естественно, нужно будет сказать PQ, как поступить с тем или иным параметром, куда его запихнуть и сколько раз провернуть.

Немного про мультиселект параметров тут: https://blog.crossjoin.co.uk/2015/01/08/multiselect-filtering-and-functions-in-power-query/
Интересный приемчик тут: http://www.thebiccountant.com/2015/10/31/tip-for-parameter-tables-in-power-query-and-power-bi/
F1 творит чудеса
 
 
 
#10
02.08.2016 08:01:22
Цитата
Sylercool написал:
Если открыть в excel вкладку PowerPivot - Управление - Из других источников - Oracle - Написать запрос, указывающий данные для импорта, то можно выгрузить столько строк, на сколько хватит оперативки. Дело в том, что пользователь должен получить уже готовые решения для разных запросов, а не лазить по этим вкладкам, писать SQL и т.д. Как мне формировать такой SQL с помощью VBA?
Вы бы определились что нужно, универсальное решение, или под конкретную задачу.  Такие универсальные инструменты как PowerPivot требуют от пользователя определенных навыков. Если это не так напишите SQL-запрос необходимыми параметрами отбора под конкретную задачу.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#11
02.08.2016 09:23:16
Набор возвращаемых строк будет неизменным (для начала), меняются только фильтры запроса. Это конкретная задача? Вытаскиватьодну и ту же информацию за разные периоды. Конечный пользователь не должен знать sql, не должен лазить по менюшкам и править запросы руками. Я понимаю, что нужен нормальный ОЛАП под такие задачи, но его пока нет. Его внедрение займет много времени и сил. Ищу альтернативы.
 
 
 
#12
02.08.2016 09:31:39
Тогда почему бы не добавить сводную, а в качество источника подключение к таблице сервера?
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#13
02.08.2016 09:32:41
Потому что только сводная построенная на основе Модели данных может подсчитывать количество уникальных элементов.
 
 
 
#14
02.08.2016 09:51:21
Не очень понятно ограничение. Если на сервере сформировать запрос с ключевым словом DISTINCT, а в значение сводной вывести количество показателя, то Вы решите поставленную задачу.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#15
02.08.2016 09:53:44
Вы понимаете суть сводной таблицы? Пользователю нужна возмоность крутить многомерные данные с автоматическим подсчетом уникальных значений "на лету". Стандартная сводная такого делать не умеет. Я же могу в БД расчитать одну из множества проекций. То, что вы предлагаете называется OLAP CUBE и уже совсем другая история...
 
 
 
#16
02.08.2016 09:58:34
Цитата
Sylercool написал:
Пользователю нужна возмоность крутить многомерные данные с автоматическим подсчетом уникальных значений "на лету". Стандартная сводная такого делать не умеет.
Вы бы пример выложили, только воздух сотрясаете и никому не верите.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#17
02.08.2016 10:00:09
Sylercool, вам дважды дали ссылки, где доступно написано, как это сделать.
Во вложении на примере загрузки из файла Excel с запросом периода. Положите файлы в папку C:\1
 
 
 
#18
02.08.2016 10:06:17
Спасибо огромное, ваши ссылки и ссылки Максим Зеленский как раз и изучаю. Отвечаю только TheBestOfTheBest. Вижу, что ваши примеры то что мне и надо. Осталось разобраться с интерфейсом PowerQuery и найти границы допустимых объемов данных.
 
 
 
#19
02.08.2016 10:46:03
Для того же файла БазаДанных.xlsx вариант без PP.
Суть в том, что такой же запрос можно вставить в качество источника данных для обычной сводной таблицы без РР.
Изменено: TheBestOfTheBest - 02.08.2016 10:48:14 (замена файла)
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#20
02.08.2016 10:51:04
Отчечаю TheBestOfTheBest. Пример во вложении. Ради 1 пункта в фильтрах поля сводной и делаю все это.

рисунки удалены: превышение допусимого размера вложения [МОДЕРАТОР]
 
 
 
#21
02.08.2016 11:16:27
Повторил примеры StepanWolkoff и Максим Зеленский. Каждый раз получаю ошибку при попытке запихнуть данные в Модель данных )
И грузит оно весь объем, а к нему применяет фильтры. А объема у меня на 10-15ГБ. Видимо что-то я делаю не так...
 
 
 
#22
02.08.2016 12:02:57
Sylercool, Пример запроса к базе данных с параметрами
Если кратко суть, то создаете в excel таблицу, где в ячейке будет SQL-скрипт, который формируется с помощью функции СЦЕПИТЬ() и этот текст подставляется в источник
У меня получилось как то так:
Код
let
    Источник = Oracle.Database("255.255.255.255", [Query=Excel.CurrentWorkbook(){[Name="ТекстЗапроса"]}[Content][ТекстЗапроса]{0}])
in
    Источник
 
 
 
#23
02.08.2016 16:08:47
Загрузил в excel 13 миллионов строк. Идея мертворожденная. Ворочать подобным на клиентской машине - идиотизм. По крайней мере дохлый ПК и excel на это нормально не способны. Спасибо всем. Буду искать другие способы.
 
 
 
#24
02.08.2016 16:54:25
Цитата
Sylercool написал: Видимо что-то я делаю не так...
Параметризация запроса Power Query посредством таблиц Excel, как правило, препятствует query folding. Равно как и формирование собственной строки SQL-запроса внутри скрипта M, или редактирование параметров подключения Power Query к источнику.

С другой стороны, всё зависит от конечной цели. Может быть, можно ограничить объем вынимаемых данных, может быть, спасет Access, а может быть Power BI.
F1 творит чудеса
 
 
 
#25
03.08.2016 08:06:58
Если кому интересно.
Сделал параметрический запрос в Oracle через PowerQuery. Текст запроса формируется в таблице Excel, как описано в #22. Исходная таблица в базе более 140млн строк (продажи товаров по чекам). Запрос возвращает данные за указанный период с группировкой Дата-Магазин-Товар. Делал выборку за один месяц - возвращается 700-900тыс строк. И делал аналогичную выборку и выгрузку в csv через PL\SQL. PowerQuery возвращает данные более чем в 2 раза быстрее 15-20сек против 40-50сек.
Выгрузка за 1 год заняла порядка 5 минут ~15млн строк.  В csv через PL\SQL 13минут.
Комп с далеко не топовым процессором "intel celeron g1840 2,8GHz", 4Гб оперативной памяти, Win7 64х, Excel2010 64х
PowerPivot вполне нормально справляется. Не могу сказать, что отчеты строятся в секунду, но было бы смешно этого требовать при таких исходных данных.
1-2млн строк загруженных в PowerPivot вообще будут летать)) Так что все зависит от задач и модели данных, которую вы построите под них.
 
 
 
#26
03.08.2016 11:18:59
StepanWolkoff, везет вам, БД под рукой, а мне только о сферическом коне порассуждать :)

Есть еще вещи, которые не стоит делать в PQ на стороне клиента, например, сортировать без крайней необходимости :)
F1 творит чудеса
 
 
 
#27
03.08.2016 12:06:27
Также можно написать на сервере процедуру, которая учитывает указанные пользователем параметры отбора и сортировки, тогда при вызове этой процедуры с определенными параметрами весь отбор, сортировку и группировку будет делать сервер. Что положительно скажется на скорости получения данных и на их размере на стороне клиента.

Цитата
Исходная таблица в базе более 140млн строк....
Запрос возвращает данные за указанный период с группировкой Дата-Магазин-Товар. Делал выборку за один месяц - возвращается 700-900тыс строк
Месяц - один, магазинов -100, товаров - 10000, после группировки выборка будет занимать не более 1 000 000 строк.

Цитата
везет вам, БД под рукой, а мне только о сферическом коне порассуждать
Используйте виртуальную машину. У Oracla даже готовые инсталляции серверов есть под виртуалки, и кстати фриваре.
Изменено: TheBestOfTheBest - 03.08.2016 12:12:29
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
Страницы: 1
Наверх