gifts2017

Настройка MS Excel для анализа данных из SQL-базы данных 1С

Опубликовал Алексей Бочков (Aleksey.Bochkov) в раздел Программирование - Практика программирования

Приведен пример реализации механизма прямого доступа к базе данных 1С на SQL-сервере с целью извлечения необходимых данных для получения аналитических отчетов.
Основными достоинствами данного метода являются:
1. Очень высокая скорость выборки данных.
2. Использование всех возможностей сводных таблиц.
Наиболее серьезным недостатком является сложность организации анализа данных в процессе выполнения запроса.
Пример реализован с помощью "MS Excel 2007"
 
Общий порядок действий:

1. Написать на понятном SQL-серверу языке запрос, выбирающий все необходимые данные из БД 1С.
Это конечно можно сделать и ручками, а можно использовать для построения запроса "Конструктор SQL-запросов 1C++" (Freeware 2005, 2006 by Ivan T Berezdetsky). Конструктор полностью копирует внешний вид и функциональность конструктора из восьмерки, поэтому трудностей с его использованием быть не должно. Полученный через конструктор текст запроса нельзя использовать в таком виде, т.к. он еще требует "перевода" имен таблиц, полей и т.д. Для этого можно воспользоваться "Просмотр регистров 1С++ без ВТ.ert" (к сожалению автора не помню), если установить галку "Фиксировать" и "Debug", а также вставить имеющийся текст запроса, то обработка выдаст нам уже готовый к использованию текст SQL-запроса. Для большей надежности рекомендую проверить его работоспособность прямо в консоли запросов SQL-сервера.
При формировании текста запроса необходим учесть то, что SQL сервер не умеет типизировать объекты 1С, а значит, например, нельзя просто выбрать измерения регистра продажи, обязательно необходимо делать связи между измерением "Номенклатура" и таблицей "номенклатура", а выбирать наименование из справочника. Если этого не учесть, то вместо наименований объектов в Excel будут непонятные надписи типа " 9N2Х", которые является идентификаторами объектов в БД.

Пример правильного текста запроса:

SELECT
Номенклатура.DESCR AS [1. Номенклатура],
Продажи.SP2375 AS [2. Количество],
Продажи.SP2350 AS [3 Продажная стоимость],
Продажи.SP2349 AS [4. Себестоимость]
FROM
dbo.RA2351 AS Продажи INNER JOIN
dbo.SC84 AS Номенклатура ON Продажи.SP2343 = Номенклатура.ID INNER JOIN


2. Необходимо создать файл подключения к БД.

Для этого можно в моих документах найти папку "Мои источники данных", в ней на "+Новое подключение к SQL-серверу.odc" щелкнуть правой, выбрать "Изменить в Excel". Далее необходимо ввести имя SQL-сервера, логин и пароль (рекомендуется создать служебного пользователя, имеющего права только на чтение в SQL-базе), далее выбрать необходимую БД и снять галку "Подключение к отдельной таблице", далее необходимо сохранить настройки подключения в файл. Затем при запросе таблицы, к которой мы хотим подключиться, щелкаем на любой. В появившемся окне меняем тип команды на SQL, а в поле "Текст команды" вставляем текст SQL-запроса, полученный ранее. Еще раз сохраняем файл подключения, и на этом Excel должен закрыться.

3. Непосредственный импорт данных.

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

Для примера, база 4 Гб, количество реализаций за день около 1000, среднее количество позиций в документе 10 шт. За 3 года запросом выбирается около 1 млн. записей. Общее время формирования сводной таблицы, учитывая не только время выборки но и обновление структуры сводной таблицы порядка 90 секунд!!!

В дальнейшем можно сохранить Excel'евский файл, создать в нем несколько различных сводных таблиц с одним источником данных. При обновлении данных обновляются сразу все сводные таблицы.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Герман (German) 09.10.07 09:34
в достоинства забыл добавить универсальность
2. Сhe Burashka (CheBurator) 09.10.07 12:37
угум... думаю востребовано будет там, где руководство само не знает чего хочет...
3. albochkov (Aleksey.Bochkov) 09.10.07 14:16
(2) Не только. Думаю что это достаточно серьезная альтернатива OLAP кубам
4. Герман (German) 09.10.07 14:57
Альтернатива то она может и есть .. Только очень редкие люди использующие 1С имеют в этом необходимость и вообще представляют себе что такое олап
5. it.net (avhrst) 22.10.07 09:46
тема актуальна, особенно при использовании отдельного скл сервера скорость прямого доступа по сравнению с 1С быстрее в разы! плюс
6. Sasha_H (logarifm) 18.12.07 14:46
в достоинства забыл добавить универсальность
Со старта -1, видимо вы не понимаете, что речь идет об прямых запросах через консоль.

Универсальность можно сделать в 1С++, ToySQL

Тема актуальна...
7. Герман (German) 18.12.07 14:55
(6) как же не понимаю ..:) очень! хорошо понимаю...

Универсальность для любого типа данных.
Универсальность в версиях 7 и 8*.

А уж ваши 1С++, ToySQL далеко не универсальны.
8. Евгений Мартыненков (JohnyDeath) 18.12.07 15:16
(7) Ну как это не универсальны?
Например, есть две одинаковые КОНФИГУРАЦИИ, но разные базы. В одной базе в регистре "Продажи" измерение "Количество" в таблице представляетсяполем SP1111, а в другой - SP2222. Т.о. получается, что один и тот же отчет я немогу таскать по разным клиентам. А имея 1с++, я могу в запросе написать просто: $Продажи.Количество и метапарсер сам подставит нужное значение и отдаст уже чистый запрос серверу!
9. Алекс Ю (AlexO) 11.01.12 11:35
>Для примера, база 4 Гб, количество реализаций за день около 1000, среднее количество позиций в документе 10 шт. За 3 года запросом выбирается около 1 млн. записей.
Либо база не 4ГБ, либо - не три года заполнялась... 4ГБ SQL-база для 1С8 - это, можно сказать, демобаза :)
Или здесь 7.7 база? в теме не указано.
(8) где взять этот метапрасер и как им вопользоваться?