Автор: Тенгиз Куправа www.kuprava.ru

OLAP-анализ данных 1С в кубах Excel

Получить данные из dbf-файлов 1С в сводные таблицы Excel для анализа - часто встречающаяся и очень заманчивая задача. Традиционный способ состоит в использовании промышленного Microsoft SQL Server c OLAP Analysis Server по следующей схеме:


Первый опыт автора был именно таким (2003г.). Настройка процедур переноса данных из dbf-файлов 1С в таблицы SQL Server и далее формирование OLAP-куба в Analysis Server достаточно трудоемкая задача. Но пользователь подключившись к OLAP-кубу из Excel может анализировать данные в многомерных сводных таблицах. Выгрузка данных в куб должна выполняться ежедневно.

Кроме того, Excel позволяет создавать локальные OLAP-кубы, представляющие собой подмножества данных серверных OLAP-кубов. Локальные кубы хранятся в файлах с расширением *.cub. Их можно копировать, переносить, просматривать на локальном компьютере. Локальный OLAP-куб на основе серверного куба создается через панель Сводные таблицы > Автономный режим OLAP, далее Создать автономный файл данных… . Мастер позволяет выбрать измерения и их уровни, а также меры, которые будут присутствовать в локальном кубе… и сохранить локальный куб в файле с расширением *.cub.

Здесь мы опишем простой способ получения данных 1С для анализа в сводных таблицах Excel, с использованием Access в качестве хранилища данных, вместо громоздкого SQL Server. Аналогично можно в получать для анализа в Excel данные из других систем, например, Navision (2006-2009гг). Схема выборки данных будет такой:


Решение задачи состоит из следующих шагов:

1. Отбор документов и справочников 1С, определение полей для связывания и для анализа.
2. Импорт dbf-файлов документов и справочников в таблицы Access + Изменение одного параметра в реестре Windows. Создание запросов для сводных таблиц.
3. Настройка получения данных в сводную таблицу Excel из 'внешнего источника' - Access.

Рассмотрим каждый шаг в отдельности. Скачайте а) готовый образец сводной таблицы Excel и источник данных для неё б) базу данных этого примера (должна лежать по адресу C:\olap.mdb).
На 1-ом шаге в нашем примере были отобраны следующие документы и справочники 1С:
Таблицы для импорта
-------------------
1. 1SJOURN.dbf __ Журналы
2. DH8815.dbf ___ Документ МояРеализация
3. DT8815.dbf ___ Документ (Мн.Ч.) МояРеализация - табличная часть
4. SC4014.dbf ___ Справочник Фирмы
5. SC493.dbf ____ Справочник ЮрЛица
6. SC796.dbf ____ Справочник Проекты
7. SC84.dbf _____ Справочник Номенклатура
8. SC8721.dbf ___ Справочник Производители
9. SC8723.dbf ___ Справочник Посты

В каждом файле (таблице) были определены интересующие для анализа поля, а также поля для связи этих таблиц. Для этого используйте файл 1Cv7.DD вашей конфигурации 1С. В нем содержится полная информация обо всех таблицах, полях и типах данных полей. Тут вам придется немного попотеть.

На 2-ом шаге следует импортировать выбранные файлы из конфигурации 1С в Access через пункт меню Файл -> Внешние данные -> Импорт, установив тип файлов dBase5 (*.dbf). В итоге у нас получилось так:


Здесь необходимо снять проблему, связанную с неверным отображением кириллицы (если открыть таблицу, то вместо кириллицы вы увидите символы псевдографики). В реестре Windows для Jet -> xBase, параметр DataCodePage необходимо установить в ANSI, точнее HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\xBase далее параметр DataCodePage=ANSI вместо OEM.

Далее построим запросы для выборки нужных данных из таблиц. Для простоты создадим промежуточные запросы Журнал, Документ, Строка, которые затем объединим в один запрос Куб. Куб и будет источником данных для Excel.


Запросы в режиме конструктора показаны ниже. Таблицы в запросах связаны по интересующим полям - назначение полей описано в файле 1Cv7.DD. Скачайте базу данных этого примера, для просмотра таблиц и запросов прямо в Access!

Запрос Журнал:

Результат запроса Журнал:

Запрос Документ:

Запрос Строка:

Итоговый запрос Куб:

Переходим к 3-му шагу - настройке получения данных в сводную таблицу Excel из 'куба' Access. (О загрузке внешних данных в Excel подробно рассказано здесь). Кратко так... Запустите Excel. Через пункт меню Данные -> Сводная таблица запустите мастер, выберите п. 'во внешнем источнике данных', в следующем окне мастера нажмите кн. Получить данные. В окне 'Выбор источника данных' на закладке 'Базы данных' выберите База данных MS Access и нажмите ОК. Далее в окне найдите местоположение скачанной базы данных Access olap.mdb и опять ОК (во избежание ошибок из-за длинного пути к файлу, сохраните файл olap.mdb в корне раздела диска или в папке с коротким именем!). В следующем окне мастер предложит выбрать таблицы и поля для подключения - в левой части найдите только Куб и стрелкой > перенесите все поля запроса Куб в правую колонку! Затем 3 раза кн. Далее и кн. Готово, чтобы вернуть данные в Excel.

Как только данные получены, жмите кн. Далее и вы получите макет сводной таблицы с готовый данными - измерениями и мерами:


Перетащите измерения и меры по своему усмотрению или как показано ниже. Скачайте готовый образец сводной таблицы Excel - источник данных для этой настроенной сводной таблицы должен лежать по адресу C:\olap.mdb. Но лучше самостоятельно выполнить шаг 3. (О работе со сводными таблицами в Excel подробно читайте здесь).