Business Intelligence средствами MS SQL Server 2008 R2 в компании, использующей системы учета 1С

[imageofyou]

Очень хотелось бы узнать, как можно извлекать данные из 1С через ее штатные интерфейсы, а не залезая в БД.

Наличие NULL-ов в таблице фактов приводит к ошибкам при процессинге многомерной базы данных

Обработка NULL-ов при процессинге настраивается: Cube — Dimension Usage — Define Relationship — Advanced — Measure Group Bindings — Null Processing. Там есть несколько вариантов.

[Query]

Для этого существует механизм распределенных информационных баз http://v8.1c.ru/overview/RaspredBases.htm

За подсказку по поводу обработки NULL-ов большое спасибо, буду знать.

Поскольку это мое первое BI решение, очень хотелось бы услышать комментарии знатоков, на что еще обратить внимание. В том же вопросе с NULL-ами, как правильнее поступить? Оставить NULL-ы в хранилище и обрабатывать их в кубе при процессинге? Или сразу в хранилище от них избавляться?

[imageofyou]

У вас не так много деталей реализации описано, чтобы обратить на что-то внимание. Вот за NULL-ы зацепился. Вы, в общем, правильно сделали, что не стали пропускать NULL-ы в хранилище по умолчанию. Возможно, вам когда-нибудь пригодится отразить в хранилище именно _отсутствие связи_, а не просто неизвестную ссылку. Вот тогда ими и воспользуетесь.

Не понравилась снежинка. С ней все хорошо до тех пор, когда данных не очень много. Когда счет пойдет на сотни гигабайт, то время процессинга на снежинке будет сильно хуже, чем на звезде (из-за более сложных запросов). Хотя, конечно же это зависит от вашего железа. Ну и объемов.

Еще не видно, используете ли вы суррогатные ключи для измерений или родные 1С-вские. Перейдя с char(9) на int можно здорово уменьшить размер таблиц фактов и, следовательно, увеличить скорость выполнения запросов. Еще бы они вам пригодились для Distinct Count'ов при секционировании, но у вас Standard Edition и секционирования у вас нет.

[Query]

Спасибо. Деталей действительно не много. Описал, можно сказать, вершину айсберга, иначе бы статья получилась слишком большая.

По поводу снежинки. Я решил проверить, насколько быстро будет происходить обновление данных в хранилище и многомерной БД при снежинке. Получилось 20 минут, вполне приемлемо. Решил оставить как есть, но будущее учту ваше замечание. Microsoft в книжке «Implementing a Data Warehouse with Microsoft SQL Server 2012» пишет: «If you do not use OLAP cubes and your reports query your data warehouse directly, then using a Star instead of a Snowflake schema might speed up the reports, because your reporting queries involve fewer joins». Сейчас мы не используем хранилище непосредственно для отчетов.

Я использую суррогатные ключи типа int, 1С-вские char(9) используются только для сопоставления записей в хранилище и базах данных 1С.

[imageofyou]

Да, вы поступили совершенно правильно (исходя из ваших вводных). Просто в моем случае самый большой известный мне куб (мы делаем серийное решение и данные не обо всех инсталляциях мне известны) процессится порядка 200 минут. Не могу не обращать на это внимание.
Свой первый куб я делал вообще без хранилища. Прямо на представлениях поверх 1Совских таблиц. И знаю, что многие и сейчас так делают. Создавать хранилище действительно дорого.

[Flyd]

Чем не устроил вариант использования0 стандартного механизма распределенной информационной базы? Помимо отчета по продажам можно было бы использовать все остальные стандартные отчеты 1С + разрабатывать отчеты используя СКД

[Query]

Дело в том, что для целей упр. учета у нас используется очень доработанная конфигурация «1С: Торговля и склад 7.7». Нужно было конечно раньше переходить на 1С 8, но тогда у бизнеса были другие задачи и требования. Сейчас проект по переходу обсуждается, и будущая архитектура решения очень вероятно будет использовать стандартный механизм РИБ, но сколько будет длиться этот переход пока не понятно, очевидно что долго и мучительно. Кроме того, я не думаю, что стандартные отчеты в 1С могут в полной мере заменить возможности анализа данных с помощью многомерных данных, так что BI решение будет развиваться параллельно, вне зависимости от архитектуры решения 1С.

[movsb]

Что ж Вы сразу не признались, что ведете учет в системе, которая была разработана 15 лет назад (хорошо еще не под DOS-ом). Она же даже с Microsoft SQL Server 2008 R2 не работает без бубна — подмены dll 1C.
Интересно было бы сравнить современные решения от 1С (Управление торговлей 8, 1С: Консолидация) и OLAP на SQL Server через Pivot Table в Excel по скорости формирования отчетов, удобству настройки измерений, затратам на внедрение.

[Query]

Да, мне тоже было бы интересно сравнить эти продукты. Одно могу предположить смело, что решение на SQL Server и PivotTable Report в Excel будет работать быстрее. Еще один большой плюс решения на SQL Server — это возможность использования других «плюшек» от Microsoft, таких как интеграция с SharePoint, интерактивные отчеты на Power View, подписки на отчеты и т.д…

Если 1С: Консолидация умеет работать с другими источниками данных кроме баз данных на основе конфигураций 1С 8, то это хорошо, а если нет, то при переводе OLTP на другую платформу придется внедрять другой инструмент BI. Моё же решение будет работать вне зависимости от платформы, на которой работает OLTP.

Предполагаю, что 1С максимально упростила процесс настройки решения BI, и, возможно, внедрение этого продукта будет по трудозатратам выгоднее, однако нужно помнить, что 1С: Консолидация стоит денег, а в моем случае решение было построено без покупки дополнительного ПО.

Один критерий, по которому 1С однозначно выигрывает — это количество специалистов на рынке, которых очевидно больше, чем девелоперов Microsoft BI.

[Flyd]

Удобство исполнения отчетов от 1С зависят от того можно ли выбрать все данные из одной базы. Если имеется зоопарк из десятка бессвязных баз, из которых нужно сделать единую выборку, то построение отчета превратится в танцы с бубном по скрещиванию ежа с ужом.

Другое дело, если необходимые данные есть в одной базе данных. Например, в случае распределенной информационной базы у каждого офиса может быть своя база, документы, из которых будут стекаться в одну центральную базу для анализа. Тогда можно использовать стандартные механизмы построения отчетов, например СКД.
Если взять для примера требования к отчету, указанные в данной статье (есть правда и стандартные отчеты, удовлетворяющие условиям), то построение отчета будет выглядеть следующим образом:

1) Создаем в конфигураторе новый отчет, открываем схему компоновки данных, нажимаем кнопку конструктор запросов

2) Выбираем в конструкторе запросов необходимые поля и характеристики (характеристики в данном случае используются для создания дополнительных реквизитов в пользовательском режиме)
s017.radikal.ru/i441/1402/dc/ebf9ae2e3fde.png
s018.radikal.ru/i523/1402/c6/9bd5aeb7a56e.png
Получается вот такой запрос:
s006.radikal.ru/i213/1402/4c/c17df7f4d661.png

3) Добавляем в СКД ресурсы (суммируемые поля)
s52.radikal.ru/i137/1402/87/f08a35a041e8.png

4) Создаем настройки по-умолчанию. В данном случае будут продажи в разрезе контрагентов и номенклатуры с отображением количества и стоимости.
s52.radikal.ru/i137/1402/1c/ffade7bbb6a9.png

5) Сохраняем и проверяем
По-умолчанию будет сформирован отчет вот в таком виде:
s006.radikal.ru/i213/1402/ce/ed5830e6853a.png

Далее пользователь может перенастроить отчет в том виде, в котором ему необходимо, например, добавить группировки:
s52.radikal.ru/i138/1402/c0/48f06e227fb5.png

Или фильтры по периодам и реквизитам:
s57.radikal.ru/i158/1402/9b/991d2c52be39.png

Можно разложить по периодам, например, помесячно (немного коряво получилось, там запрос нужно поправить, чтобы красиво отображалось).
s017.radikal.ru/i443/1402/99/52353e0e0c13.png

Любую цифру можно расшифровать по документам, периодам, договорам и т.д.
i065.radikal.ru/1402/56/05a164ac7922.png

Для отборов, группировок, расшифровок доступны также реквизиты и свойства объектов.
s006.radikal.ru/i213/1402/d5/d013cecea65b.png

На написание отчета у меня ушло минут 10. По скорости наверняка будет проигрывать решениям BI, но не уложиться в норматив одну минуту на нашей рабочей базе мне не удалось. Дамб базы весит около 60Гб, MSSQL.
Кроме того подобный отчет уже есть из коробки в базах 1С + еще десятки полезных и не очень готовых отчетов

[Query]

Спасибо за такой развернутый ответ.

Некоторый опыт работы с 1С у меня есть, поэтому описанное не вызывает вопросов. Очевидно в будущем мы будем использовать и РИБ и СКД.

[Query]

Наша OLTP БД в ЦО весит 150 Гб. Плюс 30 филиалов, в каждом БД примерно по 15 Гб. Итого 600 Гб.

[imageofyou]

SSRS хорошо подходит для создания регламентированной отчетности. Но совершенно не подходит для аналитической (ad-hoc). Просмотр информации по стране в целом (как в случае у автора поста) — это скорее аналитическая отчетность. Тут OLAP-у не будет равных.
Более того, SSRS — это средство доступа к данным (front-end) и в этом смысле его корректнее сравнивать с Excel-ем, а не с SSAS.

[Query]

SSRS мы используем. Собственно с него и началось мое знакомство с BI от Microsoft. Подписки действительно очень понравились пользователям, привыкшим работать в 1С. У 1С аналога я не видел. Кроме того, большой плюс SSRS заключается в возможности кеширования отчетов. Это позволяет существенно разгрузить SQL-сервер.

[zotchy]

Не до конца понял из ETL части: вы данные каким-то образом аггригируете? Одно из серьезных преимуществ OLAP. У нас в компании наиболее объемные таблицы с фактами продаж на еженедельной основе аггригируются по неделям/месяцам/годам и для вывода этих значений созданы дополнительные условия. К примеру, если в репорте присуствуют только dimension'ы Year или Month, то факты брать из заранее посчитанных годовых или месячных таблиц. Так что пересчитывать в real-time ничего не нужно. Очень серьезно увеличивает производительность.

[imageofyou]

В случае с OLAP кубами агрегировать на уровне ETL, как правило, не надо. Достаточно определить агрегаты на уровне куба (это делается с помощью простого визарда). Сервер рассчитает агрегаты в момент процессинга куба, а при выполнении запроса сам определит, какой из агрегатов использовать наиболее выгодно. В этом и есть фишка ОЛАП.

[Query]

ETL-процесс собирает данные из источников, экспортирует их в хранилище и процессит куб. В таблице фактов есть поле с датой, это означает, что все продажи одного офиса по одному покупателю и одному товару за один день агрегируются в одну строку. Эта агрегация выполняется на уровне view в источнике. Т.е. view dbo.Sales в базе данных 1С представляет данные в сгруппированном виде по офису, клиенту, товару, дню. Агрегация по дням, месяцам, годам происходит не в хранилище, а в многомерной модели данных, путем создания иерархии в измерении «Календарь». Если вы агрегируете данные прямо в хранилище, то, вероятно, вы не используете SSAS, а строите отчеты непосредственно из хранилища данных. Если я прав, то советую вам использовать колоночные индексы в таблице фактов, при условии, что у вас SQL Server 2012.