О чем эта статья
В статье рассмотрена технология 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-системы в «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:
Для экспериментов загрузим тестовую базу данных AdventureWorks и подготовленный куб с сервера http://msftdbprodsamples.codeplex.com/releases/view/59211.
Присоединим загруженные базы формата MDF при помощи SQL Management Studio:
Далее подключившись к серверу 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.
Добавляем новое ограничение, указываем путь к файлу в нашем новом каталоге C:\inetpub\OLAP_HTTP\msmdpump.dll.
Добавляем новый пул приложений, которому присваиваем имя MSOLAP:
В диспетчере служб IIS в дереве разворачиваем пункт Сайты, в строке Default Web Site при помощи правой кнопки мыши добавляем приложение с именем MSOLAP:
Для появившегося приложения заходим в пункт Сопоставление обработчиков и выбираем Добавить сопоставление сценария:
Далее необходимо настроить аутентификацию. В разделе Проверка подлинности включаем настройку Анонимная проверка подлинности, все остальные способы аутентификации отключаем.
Попробуем подключиться к службам аналитики SQL Server из Excel:
В строке соединения указываем http://localhost/msolap/msmdpump.dll. Имя пользователя и пароль оставляем пустыми, поскольку была настроена анонимная проверка подлинности.
Подключение происходит успешно, выбираем куб Adventure Works для подключения:
При нажатии кнопки Далее мастер предложит сохранить файл подключения. Соглашаемся, этот файл нам еще понадобится для соединения с кубами из «1С:Предприятия».
А в Excel можно построить сводную таблицу по данным выбранного куба.
Если открыть сохраненный файл подключения в Блокноте, то можно увидеть атрибут 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.
При выполнении отчета в пользовательском режиме получим следующий результат:
Сравним полученные итоги с аналогичным, сформированным в SQL Server Analysis Services:
Также формируем сводную таблицу в Excel:
Как видим, результаты получились одинаковыми.
PDF-версия статьи для участников группы ВКонтакте
Мы ведем группу ВКонтакте – http://vk.com/kursypo1c.
Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.
Сделал все по инструкции, Эксель пишет: “Не удается получить список баз.” Подключился “Microsoft SQL Server Management Studio” Если подключаюсь указанием сервер баз данных, то все нормально показывает, а через веб базы не показывает и пишет, что прав у меня нет. Это мне где права добавить то?
Рекомендую подробно ознакомиться с управлением безопасностью объектов и данных служб Analysis Services. Начните со страницы http://msdn.microsoft.com/ru-ru/library/ms174840(v=SQL.90).aspx, затем далее по ссылкам про предоставление доступа.
никак не могу победить ошибку:
при подключении из 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
Описание ошибки: требуется авторизация
Что можно сделать?
У меня получилось подключиться к службам аналитики SQL Server только при использовании анонимной проверки подлинности. Для настройки реальной аутентификации требуются более детальные знания IIS. Единственное, что могу порекомендовать, – проанализировать журнал IIS (http://support.microsoft.com/kb/907273) на предмет ошибки 401. Если кто-то из коллег сталкивался с подобной ошибкой – просьба прокомментировать.
подтверждаю, что надо использовать анонимную проверку подлинности. В этом случае работает
Большое спасибо автору статьи, Ханевичу Василию, очень нужный материал.
И персональное спасибо Гилеву, за слова отвечает, обещания помнит:).
Интересная статья! Продолжение конечно-бы неплохо!
Интересная статья.
Огромное спасибо за статью! Давно был заинтересован этой темой
Автор красаучег :)
Впечатляет.
Статья замечательная. Тема актуальная. И ссылки необходимые есть, и примеры приводятся. Только вот правда интересно, в каких случаях лучше использовать кубы в 1С.
Шикарно. Вот только бы пару сравнительных тестов по скоростям. Что бы понимать – стоит с этим работать, и если да, то начиная с каких объемов.
В статье описывается только получение данных из внешней, уже предварительно созданной демонстрационной базы. К сожалению, клиентов, использующих Business Intelligence, нет, поэтому практических рекомендаций, основанных на реальных объемах данных, дать не могу. Специальных замеров не делал, “на глаз” отметил, что отчет на СКД – достаточно медленный, по сравнению с тем же Excel. Поэтому тоже жду комментариев читателей, которые на практике сталкивались с использованием OLAP-систем, возможно, и в связке с 1С:)
Отчеты с кубов формируются в Excel через источник данных. Размер базы фактов кубов 150 ГБ. Размер базы агрегатов – 50 ГБ. База фактов сформирована из данных двух отчетных систем 1С и внутреннего оперативного учета. По сравнению с отчетами 1с отчеты практически любой детализации за редким исключением формируются мгновенно.
Прежде чем делать отчеты в MS OLAP (вместо СКД) надо решить задачу по выгрузке необходимых данных из регистров/документов 1С в MS SQL таблицы в реляционном формате (сделать датамарт), а потом уже на основании этих таблиц можно делать кубы MS OLAP. И это тема для продукта MS SSIS, здесь не освещенного.
Добрый день, спасибо за статью!
А будет ли продолжение в стиле – как создать свой “куб” на MS Analysis Services?
Добрый день!
Пожелание записали :)
тогда тоже вопрос: можно ли из 1С писать запросы на языке MDX?
Можно, если использовать OLE DB для OLAP. Т.е. программно инициировать выполнение MDX-запроса с помощью метода Execute() объекта ADODB.Connection.