Создание кубов в 1с
Обновлено: 07.07.2024
Привет,
Хочу обсудить тему OLAP кубов на основе данных 1С при их анализе средствами EXCEL.
1. Сразу примем за ограничение, что в 1С собирать конкретный отчет не хотим, то есть рассматриваем именно OLAP вариант.
2. Не хочется давать доступ в базу 1С для сглщиков (нарушение лиц. соглашение, небезопасно, непроизводительно)
3. Как админу 1С хочется максимально просто отделаться от этой задачи. Не принимать на себя обязанность "выдавать" данные.
Пока рассматриваю вариант иметь реплику базы и пусть читают данные оттуда как хотят.
Что скажете?
Не взлетит. Нужно изменить подход к задаче. Надо её прочувствовать и решить системно, а не просто отделаться копией базы.
Если нельзя в рабочую, то почему можно в копию (реплику)?
(0) Если подходить серьезно, то репликой не отделаешься. Аналитическая база всегда отдельная. С налаженными ETL процессами, то есть регулярной подгрузкой данных из рабочей.
В общем, про "максимально просто отделаться от этой задачи" забудь. Это целый проект (со своим бюджетом). В связи с этим рекомендую еще раз пересмотреть п. 1.
Но все таки пока ищу легкие пути. Рассматриваю также дать доступ читать по OData.
(6) Это как? создай роль и пользователя только на то, что можно читать.
(8) ставь терабайт памяти на сервере и подключай кубы от 1С
(9) Я вот не в курсе, можно же дать сгл роль с чтением только определенных таблиц. Так?
(14) Ну пошло.. Уважаемый, прекрасно спасибо.. я хорошо понимаю, что СКД может помочь, я бы рад чтоб все было через встроенные отчеты.
Написал же специально, примем за ограничение, что нужен ОЛАП.
(14) Данные нормализованы. в олапе зачастую денормальзованы
(16) в 1с нормализации практически нет в принципе, там все данные ненормализованые.
скд замечательно справляется не только с индексироваными данными, но так-же и вычисляемыми и временными.
(15) я действительно не понимаю разницы и не понимаю чего можно сделать в олап и нельзя в скд.
(16) (15) на мой взгляд самым главным плюсом кубов является возможность быстро перестраивать уровни группировок их направление. Именно это и умеет делать СКД.
На небольших данных (до 100 гигов) Вы разницы не найдете точно, а вот на больших - я не знаю, может Вы подскажете в чем отличие будет?
(18) ТОлько для начала договоримся, что OLAP - это технология, а не программа.
И исходя из этого берем СКД .
(19) Уважаемый, спасибо)
Разговор здесь ведется как сделать без использования СКД, если вы не хотите принять это условие, то можно ничего не писать.
(20) Ну тогда и формулируй что тебе нужен не ОЛАП а анализ средствами EXCEL.
Т.е. в один прекрасный момент 1с поменяет название таблиц/колонок и твой куб сломается. В отличие от СКД который оперирует справочниками и документами, т.е. ему пофиг на физическое расположение данных и их изменений.
(к примеру в одном из релизов 1С отказалось от хранения констант в одной таблице и под каждую константу завела отдельную таблицу)
(22) Хороший такой пример 8 летней давности)
Да все понятно, риск есть, но вряд ли он подменят название регистров.
Кроме того, вариант чтения напрямую это только один из вариантов.
(24) а какой другой вариант? ФОрмировать текстовый файл и скармливать его екселю?
Просто не понятно что обсуждать то хочешь. Кубы в екселе использовались еще во времена 7-ки. Были еще и специальные внешние компоненты для формирования кубов.
Примеров за надцать лет в интернете вагон и малая тележка.
Для небольших даныых, достаточно и СКД.
И вряд ли тут найдутся те которые оперируют данными в 100-500 гигов. Ну т.е. тем кого СКД не устраивает. Что обсуждать то?
Хотя конечно вот тут мне попросили вывести табличку за 7 лет по каждому контрагенту по каждой номенклатуре где будет видно что клиент брал, что не брал. А так же в этом же отчете количественная и качественная дистрибуция Типа мы хотим сразу в одной партянки видеть все эти данные. Вот тут СКД у меня сказала "ой". Причем она данные получила, но умирает на выводе на экран этой портянки. Вот сейчас ведем переговоры об уменьшении данных для вывода
В статье рассмотрена технология 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-системе.
Меры куба в платформе реализованы ресурсами, которые могут принимать значения типа Число и Строка.
Рассмотрим пример. Подключимся из информационной базы «1С:Предприятие» к Microsoft Analysis Services. Все действия выполняются на СУБД Microsoft SQL Server 2008 R2 под управлением операционной системы Windows Server 2008 R2.
Для начала убедимся, что служба Microsoft SQL Server Analysis Services запущена. Проверяем это в Диспетчере конфигурации SQL Server:
Присоединим загруженные базы формата MDF при помощи SQL Management Studio:
Далее подключившись к серверу Analysis Services мы восстанавливаем базу данных из файла Adventure Works DW 2008R2.abf:
После окончания загрузки проверяем работоспособность куба Adventure Works. Щелкаем по нему правой кнопкой мыши и выбираем Обзор.
В открывшемся конструкторе мышью перетаскиваем поля в строки и столбцы сводной таблицы. Данные выбираются из базы, следовательно, загруженные базы функционируют.
После установки служба веб-сервера может останавливаться с ошибкой:
Каталог, указанный для временных файлов конфигурации пула приложений, или отсутствует, или недоступен для службы активации Windows. Укажите существующий каталог и убедитесь, что флаги доступа установлены должным образом. Поле данных содержит номер ошибки.
После создания папки вручную и установки необходимых прав служба веб-сервера запускается стабильно.
Дальнейшие настройки выполняем при помощи Диспетчера служб IIS. Выбираем пункт Ограничения ISAPI и CGI.
Добавляем новый пул приложений, которому присваиваем имя MSOLAP:
В диспетчере служб IIS в дереве разворачиваем пункт Сайты, в строке Default Web Site при помощи правой кнопки мыши добавляем приложение с именем MSOLAP:
Для появившегося приложения заходим в пункт Сопоставление обработчиков и выбираем Добавить сопоставление сценария:
Далее необходимо настроить аутентификацию. В разделе Проверка подлинности включаем настройку Анонимная проверка подлинности, все остальные способы аутентификации отключаем.
Попробуем подключиться к службам аналитики SQL Server из Excel:
Подключение происходит успешно, выбираем куб Adventure Works для подключения:
При нажатии кнопки Далее мастер предложит сохранить файл подключения. Соглашаемся, этот файл нам еще понадобится для соединения с кубами из «1С:Предприятия».
А в Excel можно построить сводную таблицу по данным выбранного куба.
Если открыть сохраненный файл подключения в Блокноте, то можно увидеть атрибут ConnectionString:
В конфигураторе добавляем новый внешний источник данных, в него добавляем новый куб.
Заполняем строку подключения на основании указанного выше фрагмента файла подключения:
После успешного подключения будет открыт список кубов с таблицами измерений, полями и ресурсами. Отмечаем необходимые объекты:
Полученный в конфигурации куб можно использовать как источник данных для запроса, в системе компоновки данных, как источник для динамических списков.
Напишем отчет на СКД, базирующийся на запросе к загруженному кубу:
ВЫБРАТЬ
Adventure_Works.Product_Category_Category,
Adventure_Works.Customer_Customer_Customer,
Adventure_Works.Internet_Sales_Amount
ИЗ
ВнешнийИсточникДанных.ВнешнийИсточникДанных1.Куб.Adventure_Works КАК Adventure_Works
При выполнении отчета в пользовательском режиме получим следующий результат:
Сравним полученные итоги с аналогичным, сформированным в SQL Server Analysis Services:
Также формируем сводную таблицу в Excel:
Как видим, результаты получились одинаковыми.
PDF-версия статьи для участников группы ВКонтакте
Как программистам так и пользователям известно, как долго и сложно создаются отчеты в 1C. Однако, мы можем предложить на рассмотрение не стандартное решение — это загрузка данных в OLAP. OLAP — онлайн аналитическая отчетность для предприятий с возможностью построения таблиц и графиков. Для тех, кто это попробовал, оказалось экстремально удобным средством для построения аналитической отчетности в Microsoft Excel, как говорится, привычным способом для рядовых пользователей на предприятиях использующих 1С:ERP.
Чтобы получить информацию о таблицах и структуре базы данных 1C:ERP будем использовать готовые обработки, которые уже есть в интернет (GetDatabaseStructure.epf или base_structure.epf).
При помощи такого инструмента мы увидим список полей для объектов 1С (справочники, документы, строки документов, перечисления и т.п.) и их назначение, это нам пригодится в дальнейшем для программирования загрузки данных в OLAP. На основе полученных данных мы подготовим таблицы в нашей отдельной базе данных для аналитической отчетности в Microsoft SQL.
В Visual Studio добавим структуру таблиц 1С в Linq to SQL
Накинем еще структуру таблиц нашей аналитической базы в Linq to SQL и создадим связи между ними
Создаем класс в Visual Studio для перегрузки данных из структуры 1C в структуру OLAP
Загрузим в память список идентификаторов документов 1С, уже имеющихся в базе OLAP. Уникальность документов в 1C поддерживается в основном при помощи IDRRef и Version. Tuple поможет нам создать составной индекс, при необходимости.
Заполним индексы из таблицы SQL базы данных OLAP
Делаем выборку документов из 1C:ERP и проверяем их наличие в нашей базе OLAP. Поле _Version содержит значение для сверки модифицированности документа в 1C
Создадим новый или обновим существующий документ
Внутри цикла по документам ссылки на справочники и друге объекты подбираем из предварительно заполненных словарей.
Заполним словарь (Dictionary) для последующего поиска в памяти (значительно ускоряет процесс, по сравнению с поиском из таблиц БД SQL)
Подбирем элемент справочника в процессе загрузки документов
В конце работы цикла сохраним документ в БД OLAP
На этом работа в Visual Studio по заполнению табличек закончена, переходим к сводным таблицам в Excel. Добавляем новую сводную таблицу
Рисуем таблицу в Excel добавляя нужные поля в табличную часть, результат должен быть следующим (примерный вариант)
Мы рассмотрели краткий пример загрузки данных в отдельную базу данных SQL для формирования аналитической отчетности в OLAP и Excel из любых конфигураций 1С. Полученный в процессе такой разработки набор срезов и показателей готов в к тому, чтобы его использовал конечный пользователь в сводной таблице Microsoft Excel, формируя любые варианты отчета, удобные пользователю.
Читайте также: