Работа с OLAP-системой Microsoft SQL Server Analysis Services при помощи внешних источников данных в «1С:Предприятии 8.3.5»
(бесплатная статья по Программированию в 1С)

О чем эта статья

В статье рассмотрена технология OLAP в части использовании ее как внешний источник данных для платформы «1С:Предприятие» редакции 8.3.5. Прочитав статью вы узнаете:

  • Что такое технология OLAP и какие средства есть в платформе для работе с ней?
  • Как опубликовать куб OLAP SQL Server с помощью Internet Information Service (IIS) и обращаться к нему из Excel?
  • Как обратиться к кубу OLAP из системы «1С:Предприятие»?

Применимость

В статье используется Microsoft SQL Server 2008 R2, работающий под управлением Windows Server 2008 R2 и платформа «1С:Предприятие» редакции 8.3.5. Материал актуален и для текущих релизов платформы.

Работа с OLAP-системой Microsoft SQL Server Analysis Services при помощи внешних источников данных в «1С:Предприятии 8.3.5»

В предыдущей статье ( Запись во внешние источники данных в «1С:Предприятие 8» ) мы познакомились с функционалом записи во внешние источники данных при помощи платформы 8.3.5.823.

Сегодня мы остановимся на еще одной очень интересной возможности работы с внешними источниками данных – взаимодействие с OLAP.

OLAP (от англ. online analytical processing – аналитическая обработка в реальном времени) – технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу.

Данные в OLAP-системах формируются на основании данных OLTP-систем. OLAP-системы предназначены для быстрой выборки сложных многомерных данных, которые в OLTP-системах из-за сложной табличной структуры базы данных будут выполняться медленно.

Для обеспечения скорости получения данных OLAP-системы используют специальную структуру хранения данных, называемую кубом.

Куб (cube) можно представить в виде пространства, оси которого представляют собой измерения (dimensions), а в узлах этого пространства располагаются некоторые меры (measures). Каждое измерение куба характеризуется определенными членами (members) измерения.

Куб - структура хранения данных OLAP-системы

Можно провести аналогию между OLAP-кубом и регистром накопления. Измерение регистра схоже с измерением куба, значения измерения регистра соответствует членам измерения куба, а ресурс регистра представляет меру куба.

OLAP-куб схож с регистром накопления

Рассмотрим, как устроен куб OLAP-системы в «1С:Предприятии». Куб состоит из таблиц измерений, измерений и ресурсов.

Таблицы измерений описывают набор членов измерений куба. Измерения объекта метаданных соответствуют измерениям куба в OLAP-системе.

Меры куба в платформе реализованы ресурсами, которые могут принимать значения типа Число и Строка.

Кубы в платформе

Для работы с многомерными внешними источниками данных используется механизм XMLA (XML for Analysis). Платформа получает доступ к данным с помощью HTTP-запросов к веб-серверу.

Рассмотрим пример. Подключимся из информационной базы «1С:Предприятие» к Microsoft Analysis Services. Все действия выполняются на СУБД Microsoft SQL Server 2008 R2 под управлением операционной системы Windows Server 2008 R2.

Для начала убедимся, что служба Microsoft SQL Server Analysis Services запущена. Проверяем это в Диспетчере конфигурации SQL Server:

 Диспетчер конфигурации SQL Server

Для экспериментов загрузим тестовую базу данных AdventureWorks и подготовленный куб с сервера http://msftdbprodsamples.codeplex.com/releases/view/59211.

Присоединим загруженные базы формата MDF при помощи SQL Management Studio:

Подключение баз MDF

Далее подключившись к серверу Analysis Services мы восстанавливаем базу данных из файла Adventure Works DW 2008R2.abf:

Восстановление базы

После окончания загрузки проверяем работоспособность куба Adventure Works. Щелкаем по нему правой кнопкой мыши и выбираем Обзор.

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

Analysis Services для работы по HTTP требуется IIS. Значит, следующей нашей задачей будет развертывание веб-сервера IIS. При помощи Диспетчера сервера устанавливаем роль Веб-сервер (IIS) со следующими службами ролей:

Служба ролей

После установки служба веб-сервера может останавливаться с ошибкой:

Каталог, указанный для временных файлов конфигурации пула приложений, или отсутствует, или недоступен для службы активации Windows. Укажите существующий каталог и убедитесь, что флаги доступа установлены должным образом. Поле данных содержит номер ошибки.

Поиск в интернете (http://technet.microsoft.com/en-us/library/cc734935%28v=ws.10%29.aspx) подсказал решение этой проблемы: не была создана папка %SystemDrive%\inetpub\temp\appPools.

После создания папки вручную и установки необходимых прав служба веб-сервера запускается стабильно.

Затем настройка IIS выполнялась по следующему руководству http://blogs.technet.com/b/isv_team/archive/2010/02/10/3311694.aspx.

Копируем в новую папку c:\inetpub\OLAP_HTTP содержимое каталога c:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi.

Дальнейшие настройки выполняем при помощи Диспетчера служб IIS. Выбираем пункт Ограничения ISAPI и CGI.

Диспетчер служб IIS

Добавляем новое ограничение, указываем путь к файлу в нашем новом каталоге C:\inetpub\OLAP_HTTP\msmdpump.dll.

Изменение ограничений ISAPI или CGI

Добавляем новый пул приложений, которому присваиваем имя MSOLAP:

Добавление пула приложений

В диспетчере служб IIS в дереве разворачиваем пункт Сайты, в строке Default Web Site при помощи правой кнопки мыши добавляем приложение с именем MSOLAP:

Диспетчер служб IIS

Для появившегося приложения заходим в пункт Сопоставление обработчиков и выбираем Добавить сопоставление сценария:

Изменение сопоставления сценария

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

Попробуем подключиться к службам аналитики SQL Server из Excel:

Подключение к  SQL Server из Excel

В строке соединения указываем http://localhost/msolap/msmdpump.dll. Имя пользователя и пароль оставляем пустыми, поскольку была настроена анонимная проверка подлинности.

Подключение происходит успешно, выбираем куб Adventure Works для подключения:

Подключаем куб Adventure Works

При нажатии кнопки Далее мастер предложит сохранить файл подключения. Соглашаемся, этот файл нам еще понадобится для соединения с кубами из «1С:Предприятия».

А в Excel можно построить сводную таблицу по данным выбранного куба.

Если открыть сохраненный файл подключения в Блокноте, то можно увидеть атрибут ConnectionString:

ConnectionString

В конфигураторе добавляем новый внешний источник данных, в него добавляем новый куб.

Заполняем строку подключения на основании указанного выше фрагмента файла подключения:

http://localhost:80/msolap/msmdpump.dll?Provider=MSOLAP.4;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008R2

Подключение к источнику данных

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

Конструктор кубов внешнего источника данных

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

Напишем отчет на СКД, базирующийся на запросе к загруженному кубу:

ВЫБРАТЬ
Adventure_Works.Product_Category_Category,
Adventure_Works.Customer_Customer_Customer,
Adventure_Works.Internet_Sales_Amount
ИЗ
ВнешнийИсточникДанных.ВнешнийИсточникДанных1.Куб.Adventure_Works КАК Adventure_Works

Определим один ресурс – Internet_Sales_Amount.

При выполнении отчета в пользовательском режиме получим следующий результат:

Отчет OLAP

Сравним полученные итоги с аналогичным, сформированным в SQL Server Analysis Services:

 SQL Server Analysis Services

Также формируем сводную таблицу в Excel:

Сравнение итогов в Excel

Как видим, результаты получились одинаковыми.

PDF-версия статьи для участников группы ВКонтакте

Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.

Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.

Статья по программированию - в PDF-формате

Статья в PDF-формате

Вы можете скачать эту статью в формате PDF по следующей ссылке: Курсы-по-1С.рф – Работа с OLAP-системой при помощи внешних источников данных в 1С-Предприятии 8.3.5.pdf

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

Комментарии / обсуждение (20):

  1. Тимофей

    Сделал все по инструкции, Эксель пишет: “Не удается получить список баз.” Подключился “Microsoft SQL Server Management Studio” Если подключаюсь указанием сервер баз данных, то все нормально показывает, а через веб базы не показывает и пишет, что прав у меня нет. Это мне где права добавить то?

    • hanv

      Рекомендую подробно ознакомиться с управлением безопасностью объектов и данных служб Analysis Services. Начните со страницы http://msdn.microsoft.com/ru-ru/library/ms174840(v=SQL.90).aspx, затем далее по ссылкам про предоставление доступа.

  2. Виктор

    никак не могу победить ошибку:
    при подключении из Excel строка
    http://vrn-sql3/olap/msmdpump.dll?Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works
    ошибок не выдает (авторизация windows)
    С этой же машины по этой же строке 1С выдает ошибку
    Ошибка MDX. Код ошибки: 401
    Описание ошибки: требуется авторизация
    Что можно сделать?

    • hanv

      У меня получилось подключиться к службам аналитики SQL Server только при использовании анонимной проверки подлинности. Для настройки реальной аутентификации требуются более детальные знания IIS. Единственное, что могу порекомендовать, – проанализировать журнал IIS (http://support.microsoft.com/kb/907273) на предмет ошибки 401. Если кто-то из коллег сталкивался с подобной ошибкой – просьба прокомментировать.

      • Виктор

        подтверждаю, что надо использовать анонимную проверку подлинности. В этом случае работает

  3. Сергей Костяков

    Большое спасибо автору статьи, Ханевичу Василию, очень нужный материал.
    И персональное спасибо Гилеву, за слова отвечает, обещания помнит:).

  4. Escander

    Интересная статья! Продолжение конечно-бы неплохо!

  5. Денис

    Огромное спасибо за статью! Давно был заинтересован этой темой

  6. Natalie

    Статья замечательная. Тема актуальная. И ссылки необходимые есть, и примеры приводятся. Только вот правда интересно, в каких случаях лучше использовать кубы в 1С.

  7. Поддержка курса по EDT

    Шикарно. Вот только бы пару сравнительных тестов по скоростям. Что бы понимать – стоит с этим работать, и если да, то начиная с каких объемов.

    • hanv

      В статье описывается только получение данных из внешней, уже предварительно созданной демонстрационной базы. К сожалению, клиентов, использующих Business Intelligence, нет, поэтому практических рекомендаций, основанных на реальных объемах данных, дать не могу. Специальных замеров не делал, “на глаз” отметил, что отчет на СКД – достаточно медленный, по сравнению с тем же Excel. Поэтому тоже жду комментариев читателей, которые на практике сталкивались с использованием OLAP-систем, возможно, и в связке с 1С:)

      • Сергей Костяков

        Отчеты с кубов формируются в Excel через источник данных. Размер базы фактов кубов 150 ГБ. Размер базы агрегатов – 50 ГБ. База фактов сформирована из данных двух отчетных систем 1С и внутреннего оперативного учета. По сравнению с отчетами 1с отчеты практически любой детализации за редким исключением формируются мгновенно.

    • Антон Губарьков

      Прежде чем делать отчеты в MS OLAP (вместо СКД) надо решить задачу по выгрузке необходимых данных из регистров/документов 1С в MS SQL таблицы в реляционном формате (сделать датамарт), а потом уже на основании этих таблиц можно делать кубы MS OLAP. И это тема для продукта MS SSIS, здесь не освещенного.

  8. Константин

    Добрый день, спасибо за статью!
    А будет ли продолжение в стиле – как создать свой “куб” на MS Analysis Services?

    • Евгений Гилев (Мастер-тренер)

      Добрый день!

      Пожелание записали :)

      • Дмитрий

        тогда тоже вопрос: можно ли из 1С писать запросы на языке MDX?

        • hanv

          Можно, если использовать OLE DB для OLAP. Т.е. программно инициировать выполнение MDX-запроса с помощью метода Execute() объекта ADODB.Connection.

Комментарии закрыты

Вход на сайт

Зарегистрироваться

Подтверждение регистрации будет отправлено на указанный e-mail.

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

Восстановить доступ

E-mail или логин

Ссылка на создание нового пароля будет отправлена на указанный e-mail.