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

В этой статье я бы хотел описать основные этапы построение системы аналитической отчетности средствами MS SQL Server 2008 R2 в организации, использующей OLTP системы учета на платформе 1С. В статье описан мой первый опыт построения решений Business Intelligence.

Общие вводные данные

Компания, в которой я работаю, занимается оптовой торговлей и состоит приблизительно из 30 офисов, расположенных в регионах России. В каждом офисе существует информационная база данных 1С, в которой регистрируются данные о продажах. В организации используется два вида конфигураций баз данных 1С. Одна конфигурация используется в центральном офисе в Москве, вторая - в филиалах (в регионах России). В качестве СУБД, обеспечивающей работу систем 1С, используется Microsoft SQL Server 2008 R2 (SP2) Standard Edition (64-bit). Единая общая нормативно-справочная информация (НСИ) отсутствует. Справочник "Продукция" и некоторые другие справочники, являющиеся классификаторами продукции и контрагентов, синхронизируются по коду или другому идентификатору, которые хранятся в системах 1С. Одним из основных отчетов, используемых в организации, является отчет о продажах. Существующий отчет о продажах позволяет извлекать данные только из той системы, в которой он формируется. Сформированные отчеты выгружаются в MS Excel, где происходит их дальнейшая обработка. В связи с ростом компании и появлением новых офисов руководство поставило перед IT-подразделением задачу о разработке консолидированного отчета, позволяющего автоматически получать информацию о продажах в разрезе всех офисов организации.

Требования бизнеса

Основным требованием бизнеса было автоматическое формирование отчета о продажах по всем офисам компании. Кроме этого, в отчете должны быть данные о количестве и сумме продаж в следующих аналитических разрезах:

• Период (год, квартал, месяц, день).
• Продукция (включая атрибуты, классифицирующие продукцию).
• Контрагенты (включая атрибуты, классифицирующие контрагентов).

Отчет должен позволять накладывать фильтры на выборку по любому из аналитических разрезов. В качестве фильтра может быть задано произвольное количество значений. Отчет должен формироваться не дольше минуты. Формирование отчета не должно существенно влиять на производительность учетных систем 1С. Реализация и дальнейшее сопровождение отчета должны быть минимально затратными.

Предварительная оценка и выбор решения

На основании имеющихся вводных данных и требований заказчику было предложено следующее решение:

• Разработать хранилище данных, включающее всю информацию, необходимую для формирования консолидированного отчета о продажах.
• Развернуть хранилище данных на экземпляре SQL-севера SQL Server Database Engine в центральном офисе.
• Разработать многомерную модель данных, содержащую меры и измерения, необходимые для формирования отчета о продажах.
• Развернуть многомерную базу данных, содержащую многомерную модель, на экземпляре SQL-севера SSAS в центральном офисе.
• Разработать ETL-пакеты SSIS, с помощью которых будет производиться обновление данных в хранилище данных и в многомерной базе данных.
• Развернуть пакеты SSIS на экземпляре SQL-сервера SSIS в центральном офисе.
• Обеспечить автоматическое выполнение пакетов SSIS с уведомлением по e-mail специалистов технической поддержки о статусе выполнения пакетов.
• Обеспечить доступ сотрудникам компании к многомерной базе данных для формирования консолидированного отчета о продажах с помощью объекта PivotTable Report в MS Excel.
• Выполнить обучение сотрудников, занимающихся формированием отчетов о продажах.

Реализация решения

Этап №1. Сбор информации об источниках данных в системах 1С. Создание представлений (View) для получения доступа к необходимым данным

Перед началом проектирования хранилища я создал представления (View) в базах данных SQL, обеспечивающих работу систем 1С. У меня получилось два набора представлений: набор для базы данных в центральном офисе (см. рис. 1) и набор для баз данных в филиалах (рис. 2). Напомню, что структура баз данных в филиалах организации одинаковая, но отличается от структуры базы данных в центральном офисе.

Рис. 1. Представления в SQL-базе данных центрального офиса

Рис. 2. Представления в SQL-базах данных филиалов

Состав представлений в центральном офисе и филиалах получился разный, так как часть НСИ является общей и хранится в полном объеме в базе данных в центральном офисе. В частности речь идет о представлениях:

• dbo.ChainStores (Торговые сети клиентов).
• dbo.Countries (Классификатор стран мира).
• dbo.Products (Продукция).
• dbo.ProductAnalogs (Аналоги продукции).
• dbo.ProductTypes (Классификатор типов продукции).
• dbo.Projects (Классификатор видов клиентов).
• dbo.ProjectsForProductMatrix (Классификатор видов продукции).
• dbo.CrossProductsAndProjectsForProductMatrix (представление для обеспечения связи типа "много-ко-многим" между представлениями dbo.Products и dbo.ProjectsForProductMatrix).

Создание представлений в SQL-базах данных позволяет сделать решение более универсальным. Например, при изменении структуры таблиц в базах данных 1С нам не придется вносить изменения в ETL-пакеты, достаточно будет переделать представления.

Этап №2. Разработка структуры хранилища данных. Развертывание хранилища данных

Завершив первый этап, мы можем с легкостью получить информацию о составе и типах данных, хранящихся в источниках данных, и спроектировать структуру хранилища. Для этого достаточно взглянуть на типы колонок представлений. Например, представление dbo.Clients выглядит следующим образом.

Рис. 3. Представление dbo.Clients

Обратите внимание, что в представлении dbo.Clients существует поле ParentId. С помощью этого поля в последствии мы сможем построить иерархию Parent-child в многомерной модели данных для измерения "Клиенты". Аналогичное поле присутствует в представлениях dbo.Products и dbo.Managers.

Прежде чем начать проектировать хранилище данных, необходимо определиться с его схемой. Существует две схемы хранилища данных - это звезда и снежинка. Обе схемы имеют свои плюсы и минусы, и их сравнение выходит за пределы данной статьи. Я выбрал схему снежинка, руководствуясь тем, что при переходе на SQL Server 2012 и использовании в будущем self-service BI пользователям, вероятно, будет удобнее оперировать более нормализованными данными из хранилища данных при разработке собственных моделей данных в PowerPivot for Excel. Структура разработанного мной хранилища данных изображена на следующем рисунке.

Рис. 4. Структура хранилища данных

Таблицы dim.DimDates (даты), dim.DimOffices (офисы), dim.DimRegions (регионы России) были заполнены один раз и не предполагают автоматического обновления. Таблица dim.DimOffices содержит наименования офисов компании. Таблица dim.DimDates содержит сведения о датах для соответствующего измерения в многомерной модели данных. В таблицах измерений содержится суррогатный ключ, выполняющий роль первичного ключа. Это связано с тем, что ключи записей в различных источниках данных могут пересекаться.

Этап №3. Разработка многомерной модели данных. Развертывание многомерной базы данных

При создании многомерной модели данных было создано представление Data Source View, в которое были включены все таблицы из хранилища данных, кроме таблицы stage.FactSales. Эта таблица будет использоваться только для временного хранения данных о продажах перед загрузкой в таблицу фактов fact.FactSales.

В кубе Sales реализованы две группы мер (см. рис. 5).

Рис. 5. Меры

Группа мер Cross Products And Projects For Product Matrix обеспечивает связь много-ко-многим между измерениями Товары и Каналы сбыта для товарной матрицы.

Список измерений изображен на рисунке 6.

Рис. 6. Измерения

Для измерений Товары, Клиенты, Менеджеры реализована иерархия Parent-child.

Рис. 7. Измерение Товары

Для управления доступом к многомерной базе данных создана роль Analists, которой предоставлены права Read и Drillthrough для куба Sales. Права Drillthrough позволяют пользователям получать расшифровку с информацией о том, как были рассчитаны значения ячеек в отчете.

Рис. 8. Роль Analists

Чтобы развернуть многомерную базу данных на сервере, указываем в свойствах проекта имя экземпляра SQL-сервера SSAS, имя базы данных на сервере и в меню BIDS нажимаем Deploy. Подключаемся к экземпляру SSAS с помощью SMS и видим, что многомерная база данных была создана.

Рис. 9. Многомерная база данных Sales OLAP

Этап №4. Разработка ETL-пакетов. Развертывание ETL-пакетов. Настройка автоматического выполнения ETL-пакетов

Наиболее трудоемкий этап при проектировании решений Business Intelligence - это, разработка ETL-пакетов. Связано это с тем, что источники данных, как правило, имеют разную структуру, а данные, хранящиеся в них, содержат ошибки и имеют различный формат. Например, пол сотрудника в разных базах данных, может быть представлен буквами М и Ж или цифрами 0 и 1, и перед загрузкой этих данных в хранилище, необходимо выполнить их очистку и приведение к общему виду. Кроме того, в хранилище данных необходимо обновлять только те данные, которые были введены или изменены с момента последней загрузки. Это только основные сложности, на самом деле их гораздо больше. Однако благодаря инструментам SSIS большинство подобных проблем могут быть решены. В моей реализации данные в таблицах измерений обновляются полностью, т.е. новые записи добавляются, а существующие записи перезаписываются. Таблица фактов очищается и заполняется снова за период по умолчанию равный трем месяцам. Глубина обновления таблицы фактов в месяцах хранится в конфигурации SSIS пакетов, которая представляет из себя отдельную таблицу в хранилище данных.

Рис. 10. Пакеты SSIS

На рисунке 10 изображены 4 пакета SSIS, назначение которых следующее:

• Update DW and Process Sales OLAP.dtsx - мастер-пакет, в котором реализована общая логика ETL-процесса и который запускает все остальные пакеты.
• Import Dimensions and Facts from Moscow.dtsx - пакет для загрузки данных в таблицы измерений и фактов из базы данных центрального офиса в хранилище данных.
• Import Dimensions and Facts from Filials.dtsx - пакет для загрузки данных в таблицы измерений и фактов из баз данных филиалов в хранилище данных.
• Process Sales OLAP.dtsx - пакет, который выполняет обновление данных (процессинг) в многомерной базе данных.

Логика (Control Flow) мастер-пакета следующая (см. рис. 11).

Рис. 11. Пакет Update DW and Process Sales OLAP

Рассмотрим каждый элемент этой схемы:

• Сначала выполняется Set Package's Variables Values (Execute SQL Task). Задача этого элемента - получить значения из конфигурации пакета и записать их в переменные пакета. В конфигурации пакета в том числе хранится информация о глубине обновления таблицы фактов в месяцах. Конфигурации пакета хранится в отдельной таблице в хранилище базы данных и может изменяться IT-специалистами.
• Далее Insert Default Values In Dimensions (Execute SQL Task) выполняет проверку и заполнение хранилища данных пустыми элементами. Например, в таблице dim.DimProducts после выполнения этого задания должен появиться элемент с идентификатором (Id), равным нулю. Записи с нулевыми идентификаторами будут созданы во всех таблицах измерений для обеспечения логической целостности данных, так как все поля таблицы фактов определены как NOT NULL и имеют значение по умолчанию равное нулю. Наличие NULL-ов в таблице фактов приводит к ошибкам при процессинге многомерной базы данных.
• Get List of Source OLTP Databases in Moscow (Execute SQL Task) получает список баз данных центрального офиса (в моем случае такая база данных одна, но для большей универсальности решения, я предположил, что их может быть несколько). Список баз данных хранится в таблице dim.DimOffices. Так же в этой таблице хранятся строки подключения к базам данных. Полученная выборка записывается в переменную пакета.
• For All OLTP Databases in Moscow (Foreach Loop Container) выполняет обход выборки, полученной на предыдущем шаге, и для каждой строки выборки (т.е. для каждой базы данных) выполняет пакет Import Dimensions and Facts from Moscow.dtsx. Передача параметров из мастер-пакета вызываемому пакету происходит с помощью установки значений конфигурации пакета, которую выполняет задача Set Package Configurations (Execute SQL Task).
• Следующие два шага Get List of Source OLTP Databases in Filials (Execute Package Task) и For All OLTP Databases in Filials (Foreach Loop Container) аналогичны двум предыдущим, только выполняются для баз данных филиалов.
• Последний шаг Process Sales OLAP (Execute Package Task) запускает пакет обновления данных в многомерной базе данных.

Описанные выше пакеты развернуты на экземпляре SQL-сервера SSIS. Для автоматического запуска мастер-пакета на SQL-сервере создано задание Update DW and Process Sales OLAP (см. рис. 12).

Рис. 12. SQL Job для запуска пакета SSIS

Для контроля выполнения ETL-процесса в задании настроено уведомление специалистов службы поддержки по e-mail о завершении задания (см. рис. 13).

Рис. 13 Настройка уведомления о выполнении задания по e-mail

Этап №5. Предоставление доступа к многомерной база данных

Доступ к многомерной базе данных предоставлен сотрудникам организации с помощью включения их доменных учетных записей в роль Analists многомерной базы данных с помощью SMS (см. рис. 14).

Рис. 14. Членство в роли Analists

Этап №6. Обучение сотрудников организации

Для обучение пользователей был записан 15 минутный видео-ролик, в котором были продемонстрированы возможности MS Excel, позволяющие подключиться к многомерной базе данных и построить отчет с помощью объекта PivotTable Report. Один из возможных вариантов отчета изображен на рисунке 15.

Рис. 15. Пример отчета PivotTable Report в Excel

Выводы

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

• На реализацию данного решения было потрачено 40 человеко-часов. Все описанные было выполнено одним человеком, т.е. мной. Предварительно я посетил курсы и успешно сдал экзамены Microsoft, получив сертификат Microsoft Certified Solutions Expert в области Business Intelligence.
• Таблица фактов в рабочей базе данных содержит ~40 миллионов строк.
• ETL-процесс выполняется приблизительно 20 минут.
• Формирование отчетов выполняется в пределах нескольких секунд.