В этой статье я бы хотел описать основные этапы построение системы аналитической отчетности средствами 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С. Реализация и дальнейшее сопровождение отчета должны быть минимально затратными.
На основании имеющихся вводных данных и требований заказчику было предложено следующее решение:
Перед началом проектирования хранилища я создал представления (View) в базах данных SQL, обеспечивающих работу систем 1С. У меня получилось два набора представлений: набор для базы данных в центральном офисе (см. рис. 1) и набор для баз данных в филиалах (рис. 2). Напомню, что структура баз данных в филиалах организации одинаковая, но отличается от структуры базы данных в центральном офисе.
Рис. 1. Представления в SQL-базе данных центрального офиса
Рис. 2. Представления в SQL-базах данных филиалов
Состав представлений в центральном офисе и филиалах получился разный, так как часть НСИ является общей и хранится в полном объеме в базе данных в центральном офисе. В частности речь идет о представлениях:
Создание представлений в SQL-базах данных позволяет сделать решение более универсальным. Например, при изменении структуры таблиц в базах данных 1С нам не придется вносить изменения в ETL-пакеты, достаточно будет переделать представления.
Завершив первый этап, мы можем с легкостью получить информацию о составе и типах данных, хранящихся в источниках данных, и спроектировать структуру хранилища. Для этого достаточно взглянуть на типы колонок представлений. Например, представление 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 содержит сведения о датах для соответствующего измерения в многомерной модели данных. В таблицах измерений содержится суррогатный ключ, выполняющий роль первичного ключа. Это связано с тем, что ключи записей в различных источниках данных могут пересекаться.
При создании многомерной модели данных было создано представление 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
Наиболее трудоемкий этап при проектировании решений Business Intelligence — это, разработка ETL-пакетов. Связано это с тем, что источники данных, как правило, имеют разную структуру, а данные, хранящиеся в них, содержат ошибки и имеют различный формат. Например, пол сотрудника в разных базах данных, может быть представлен буквами М и Ж или цифрами 0 и 1, и перед загрузкой этих данных в хранилище, необходимо выполнить их очистку и приведение к общему виду. Кроме того, в хранилище данных необходимо обновлять только те данные, которые были введены или изменены с момента последней загрузки. Это только основные сложности, на самом деле их гораздо больше. Однако благодаря инструментам SSIS большинство подобных проблем могут быть решены. В моей реализации данные в таблицах измерений обновляются полностью, т.е. новые записи добавляются, а существующие записи перезаписываются. Таблица фактов очищается и заполняется снова за период по умолчанию равный трем месяцам. Глубина обновления таблицы фактов в месяцах хранится в конфигурации SSIS пакетов, которая представляет из себя отдельную таблицу в хранилище данных.
Рис. 10. Пакеты SSIS
На рисунке 10 изображены 4 пакета SSIS, назначение которых следующее:
Логика (Control Flow) мастер-пакета следующая (см. рис. 11).
Рис. 11. Пакет Update DW and Process Sales OLAP
Рассмотрим каждый элемент этой схемы:
Описанные выше пакеты развернуты на экземпляре SQL-сервера SSIS. Для автоматического запуска мастер-пакета на SQL-сервере создано задание Update DW and Process Sales OLAP (см. рис. 12).
Рис. 12. SQL Job для запуска пакета SSIS
Для контроля выполнения ETL-процесса в задании настроено уведомление специалистов службы поддержки по e-mail о завершении задания (см. рис. 13).
Рис. 13 Настройка уведомления о выполнении задания по e-mail
Доступ к многомерной базе данных предоставлен сотрудникам организации с помощью включения их доменных учетных записей в роль Analists многомерной базы данных с помощью SMS (см. рис. 14).
Рис. 14. Членство в роли Analists
Для обучение пользователей был записан 15 минутный видео-ролик, в котором были продемонстрированы возможности MS Excel, позволяющие подключиться к многомерной базе данных и построить отчет с помощью объекта PivotTable Report. Один из возможных вариантов отчета изображен на рисунке 15.
Рис. 15. Пример отчета PivotTable Report в Excel
Требования заказчика были реализованы в полной мере. Бета-тестирование выполнялось ключевыми пользователями компании, ежедневно формирующими отчеты о продажах. В своем отчете ключевые пользователи охарактеризовали созданное решение как очень удобное, быстрое и достаточное для проведение всестороннего анализа продаж. Для оценки решения привожу некоторые цифры:
Общие вводные данные
Компания, в которой я работаю, занимается оптовой торговлей и состоит приблизительно из 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 минут.
- Формирование отчетов выполняется в пределах нескольких секунд.
